PostgreSQL Backup and Recovery

Backup

• As with any database, PostgreSQL database should be backed up regularly.

• There are three fundamentally differentapproaches to backing up PostgreSQL data:
• SQL dump
• File system level backup
• On-line backup

Lets discuss them in detail.

Backup – SQL Dump

• Generate a text file with SQL commands

• PostgreSQL provides the utility program pg_dump for this purpose.

• pg_dump does not block reads or writers.

• pg_dump does not operate with special permissions. In particular, it must have read access to all tables that you want to back up, so in practice you almost always have to run it as a database superuser.

• Dumps created by pg_dump are internally consistent, that is, the dump represents a snapshot of the database as of the time pg_dump begins running.

Syntax:
pg_dump [options] [dbname]

pg_dump Options

•  -a – Data only. Do not dump the data definitions (schema)

•  -s – Data definitions (schema) only. Do not dump the data

•  -n - Dump from the specified schema only

•  -t - Dump specified table only

•  -f - Send dump to specified file

•  -Fp – Dump in plain-text SQL script (default)

•  -Ft – Dump in tar format

•  -Fc – Dump in compressed, custom format

•  -v – Verbose option

•  -o use oids

Restore – SQL Dump

• The text files created by pg_dump are intended to be read in by the psql program. The general commandform to restore a dump is– psql dbname < infile where infile is what you used as outfile for the pg_dump command. The database dbname will not be created by this command, so you must create it yourself.

• pg_restore is used to restore a database backed up with pg_dump that was saved in an archive format – i.e., a non-text format Files are portable across architectures

Syntax:
pg_restore [options...] [filename.backup]

pg_restore Options

• -d - Connect to the specified database. Also restores to this database if –C option is omittedselect pg_start_backup('label_goes_here')

• -C – Create the database named in the dump file & restore directly into it

• -a – Restore the data only, not the data definitions (schema)

• -s – Restore the data definitions (schema) only, not the data

• -n - Restore only objects from specified schema

• -t - Restore only specified table

• -v – Verbose option

Backup & Restore entire database

Backup:
(Backup data to a file)

• pg_dumpall is used to dump an entire database cluster in plain-text SQL format

• Dumps global objects - user, groups, and associated permissions

• Use PSQL to restore

Syntax:
pg_dumpall [options...] > filename.backup

pg_dumpall Options

• -a – Data only. Do not dump the data definitions(schema)

• -s - Data definitions (schema) only. Do not dump the data

• -g - Dump global objects only – i.e., users and groups

• -v – Verbose option

Recovery:
(Recover data from a file)

Syntax:
psql –d template1 < filename.backup
or
psql –d template1 –f filename.backup

Any database in the cluster can be used for the initial connection – it doesn’t have to be template1

Backup - File system level backup

• An alternative backup strategy is to directly copy the files that PostgreSQL uses to store the data in the database.

• You can use whatever method you prefer for doing usual file system backups, for example:
• tar -cf backup.tar /usr/local/pgsql/data

• The database server must be shut down in order to get a usable backup.

• File system backups only work for complete backup and restoration of an entire database cluster.

Backup - On-line backup

• Use when database must stay up while backup is occurring.

• postgres=# select pg_start_backup('label_goes_here')

• Copy the files/directory

• postgres=# select pg_stop_backup();

• Archive_command must be set in postgresql.conf which archives WAL logs and supports PITR

Point-in-Time Recovery - PITR

PITR

• Point-in-time recovery (PITR) is the ability to restore a database cluster up to the present or to a specified point of time in the past

• Uses a full database cluster backup and the write-ahead logs found in the /pg_xlog subdirectory 

• Must be configured before it is needed (write-ahead log archiving must be enabled)

Step 1 

• Edit the "postgresql.conf" file and set the “archive_command” parameter

• Unix:
  archive_command= ‘cp –i %p /mnt/server/archivedir/%f

• Windows:
  archive_command= 'copy "%p" c:\\mnt\\server\\archivedir\\"%f"
  
  %p is absolute path of WAL otherwise you can define the path 
  %f is a unique file name which will be created on above path.

Step 2

• Make a base backup

• Connect using edb-psql and issue the command: 

• SELECT pg_start_backup(‘any useful label’);

• Use a standard file system backup utility to back up the /data subdirectory 

• Connect using edb-psql and issue the command:

• SELECT pg_stop_backup();

• Continuously archive the WAL segment files 

Final Step: 

• Recovering the database

• Clean out all existing files in the /data directory and subdirectories (be sure to backup configuration files if you have not already done so)

• Restore the database files from the backup dump

• Copy any unarchived WAL files into the /pg_xlog directory

• Create a recovery.conf file in the /data directory

• Restart the database server

Settings in the recovery.conf file:

• restore_command(string)

       Unix:
       restore_command = 'cp /mnt/server/archivedir/%f "%p"‘

     Windows:
      restore_command = 'copy c:\\mnt\\server\\archivedir\\"%f" "%p"'

      recovery_target_time(timestamp)

      recovery_target_xid(string)

      recovery_target_inclusive(boolean)