Postgres: Backup

(Last Updated On: )

Backing up a postgres instance is rather straight forward. You can automate the backup or manual backup. I have documented either or below. If you have a better way please feel free to let me know.

Manual:

pg_dump -h ##SERVERNAME## -p ##SERVERPORT## -U ##USER## -j 8 -c --if-exists -Fd -b -v -f ##FILENAME_W_PATH## ##DB_NAME## > ##OUTPUT_LOG## 2>&1

There are a variety of options you can choose from but the above is a standard I like to use.

1. -Fc: This compresses the data and allows for restoring via pg_restore
2. -Fd: This compresses the data to a directory structure for restoring via pg_restore
3. -j 8: This runs 8 concurrent jobs.
4. -N mySchema: This excludes the schema mySchema from being backed up.
5. –if-exists: Used with -c

Automated:

You can also use PgAgent to do scheduled backups and I recommend this however I don’t have the steps yet. That will hopefully come shortly.

You should create a backup user for the automated version. Here is what the user should have.

CREATE USER ##BACKUP_USER## WITH ENCRYPTED PASSWORD '##PASSWORD##';
GRANT CONNECT ON DATABASE ##DATABASE NAME## TO ##BACKUP_USER##;
GRANT USAGE ON SCHEMA public TO ##BACKUP_USER##;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO ##BACKUP_USER##;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO ##BACKUP_USER##;

Whichever OS you are using there is a different way to set it up.

Windows:

1. You need to create “pgpass.conf” file in “%APPDATA%\postgresql” directory.
2. pgpass should contain connection information such as “hostname:port:database:username:password”
3. You can then create a batch file to run pg_dump. Example of such a batch file below. You could also write a PS script which is the preferred direction to go.

 @echo off
   for /f "tokens=1-4 delims=/ " %%i in ("%date%") do (
     set day=%%i
     set month=%%j
     set year=%%k
   )
 
   for /f "tokens=1-4 delims=: " %%i in ("%time%") do (
     set hour=%%i
     set minute=%%j
     set second=%%k
   )
 
   set backupDir=\\NETWORK_SHARE\%month%_%day%_%year%
   set backupFile=DBName_%month%_%day%_%year%_%hour%_%minute%_%second%.backup
 
   if not exist %backupDir% (mkdir %backupDir%)
 
   C:\"Program Files"\PostgreSQL\9.4\bin\pg_dump.exe -h ##SERVERNAME## -p ##SERVERPORT## -U ##BACKUP_USER## -j 8 -c --if-exists -Fd -b -v -f %backupDir%\%backupFile% ##DB_NAME## > ##OUTPUT_LOG## 2>&1

Ubuntu:

You need to create the .pgpass file in the users home directory. It should contain information such as hostname:port:database:username:password. You will also need to modify the permissions to be owner read/write.

touch .pgpass
chmod 600 .pgpass

Next we need to set the crontab job. By entering “crontab -e”. You can call the backup.sh file on whatever schedule you want.

#! /bin/sh
currentDate=$(date +"%m_%d_%Y")
currentDateTime=$currentDate$(date +"_%H_%M_%S")
 
if [ ! -d ##NETWORK_SHARE##/##DB_NAME## ]
then
        mkdir ##NETWORK_SHARE##/##DB_NAME##
fi
 
if [ ! -d ##NETWORK_SHARE##/##DB_NAME##/$currentDate ]
then
        echo $currentDate
        mkdir ##NETWORK_SHARE##/##DB_NAME##/$currentDate
fi
 
pg_dump -h ##SERVERNAME## -p ##SERVERPORT## -U ##BACKUP_USER## -j 8 -c --if-exists -Fd -b -v -f ##NETWORK_SHARE##/##DB_NAME##/$currentDate/$currentDateTime.backup ##DB_NAME## > ##OUTPUT_LOG## 2>&1