Comparison of backup and recovery techniques for PostgreSQL database

There are three completely different ways to back up PostgreSQL data:

· SQL Dump

· file system level backup

· Online Backup

First, SQL Dump

SQL dump uses the pg_dump utility to export the current block of data to a SQL file, and when the database needs to be rebuilt, the SQL file is fed back to the server, restoring the database state at the time of the rebuild.

When backing up the entire DB cluster, you can use the pg_dumpall program to pg_dumpall each database in the given cluster, while also ensuring that global data states such as users and groups are retained.

When the size of a table in a database is larger than the maximum file size allowed by the system, the problem needs to be addressed using standard UNIX tools.

Insufficient :

To obtain Superuser privileges on the database, these permissions are still required to be present when the database is restored.

Updates to the database will not be dumped when the Pg_dump is running.

Second, file system-level backup

Copy directly the files in the PostgreSQL database for storing database data.

Way :

"Consistent snapshot Mode"

using rsync to perform file system backups

Insufficient :

You must close the database when you make a backup

When a database is distributed across multiple file systems, only the database is shut down long enough to fully backup

Large file after dump

Third, Online Backup and instant recovery

This method combines the backup of the system backup with the Wal file and, when recovery is needed, restores the file backup and then replays the Wal file.

Advantages :

There is no need for a perfect consistent backup at the initial time, so you do not need to use the snapshot feature,tar or similar archiving tools to achieve backup;

Continuous database backup can be achieved through continuous backup of the WAL file archive;

The database can be restored to the state at any point since the start of the backup;

Requirements :

Only support the recovery of the whole database cluster;

requires a large amount of archival storage;  

Operation :

1. Set up the WAL Archive

1) Create a WAL backup directory, for example:d:\pg_xlog_archive

2) Modify the Archive_command settings in the postgresql.conf , for example:

Archive_command = ' copy '%p ' d:\\pg_xlog_archive\\%f '

Note:%p to use double quotation marks to handle spaces in the installation directory.

Archive_mode = On

wal_level=' archive '

in the postgresql.conf file, use Archive_command to declare the Shell command to copy a complete segment file to the specified location, where the shell the command needs to use %p to represent the absolute path of the file to be archived, %f represents the file name.

eg:archive_command = ' cp-i%p/mnt/server/archivedir/%f </dev/null '

The archive command executes under the permissions of the same user running the PostgreSQL server. In addition, it returns 0if and only if the archive command is successfully run.

Note: Although the WAL Archive allows the recovery of any modifications made to data in the PostgreSQL database, it does not revert to the configuration file after the initial base backup (that is,postgresql.conf ,pg_hba.conf and pg_ident.conf), because these files are edited manually, rather than through SQL operations. So you may need to put your profile in a routine file system backup process where you can handle it.

2. Perform basic backup

1) Make sure that the WAL archive is turned on and ready to operate.

2) connect to database as Superuser, issue command SELECT pg_start_backup (' label ');

The label here is a unique identifier for any of the backup operations you want to use. (A good habit is to use the full path of the destination where you want to place the backup dump file.) pg_start_backup with your backup information, in your cluster directory, create a backup label file called Backup_label.

3) perform a backup and use any convenient file system tools, such as tar or cpio. During these operations, you do not need to close the database and do not want to close the operation of the database.

4) Once again connect to the database as Superuser, then issue the command SELECT pg_stop_backup ();

5) as long as the WAL segment file used during the backup process is backed up as part of normal database activity, your backup is done.

Note: To ensure that your backup dump includes all files in the database cluster directory (for example,/usr/local/pgsql/data) and tablespace.

Comparison of backup and recovery techniques for PostgreSQL database