Overview

PostgreSQL like any other DB, has to be backed up from time to time. From one side to assure recovery from other to sleep better during the night :). In order to simplify things, there are couple major concepts to sync first:

Restore - The physical restore of files (datafiles, configuration files, logs and so on)

Recovery - The synchronization and adjustments between these files. For example, replying an XLOG file is a recovery operation, while restoring the file for that XLOG to be applied on is the restore operation.

Backup

Basically there are two ways how a PostgreSQL database can be backed up:

Physically

Logically

Physical

Base backup is done in 3 steps

Ensure that WAL archiving is enabled and working.

Connect to the database as a superuser and issue the command:

SELECT pg_start_backup('label'); you can use SELECT pg_start_backup('label', true); - to minimaze the checkpoint and start ASAP

Perform a backup (either via rsync or cp or others)

SELECT pg_stop_backup();

Manually

This will copy all files from the source folder into the destination folder, while the PostgreSQL server is running. Please do not forget to exit the backup mode. Even though it will be safe to leave it as it is. It is a good practice to finish the backup mode as soon as possible.

Automatic

You can use pg_basebackup which will perform all steps automatically.

You can of course configure more sophisticated options, such as a tar file using gzip utilities. After the base backup is done, it a copy of the database to a specific point of time, that copy can be pushed forward (recovered) via XLOGs. The barman application, which is configured in the next section, uses pg_basebackup in order to perform the backups.

You can also use the pg_basebackup tool to take the backup, instead of manually copying the files. This tool will do the equivalent of pg_start_backup(), copy and pg_stop_backup() steps automatically, and transfers the backup over a regular PostgreSQL connection using the replication protocol, instead of requiring file system level access. pg_basebackup does not interfere with file system level backups taken using pg_start_backup()/pg_stop_backup().

Some file system backup tools emit warnings or errors if the files they are trying to copy change while the copy proceeds. When taking a base backup of an active database, this situation is normal and not an error. However, you need to ensure that you can distinguish complaints of this sort from real errors. For example, some versions of rsync return a separate exit code for “vanished source files”, and you can write a driver script to accept this exit code as a non-error case. Also, some versions of GNU tar return an error code indistinguishable from a fatal error if a file was truncated while tar was copying it. Fortunately, GNU tar versions 1.16 and later exit with 1 if a file was changed during the backup, and 2 for other errors.

It is not necessary to be concerned about the amount of time elapsed between pg_start_backup and the start of the actual backup, nor between the end of the backup and pg_stop_backup; a few minutes' delay won't hurt anything. (However, if you normally run the server with full_page_writes disabled, you might notice a drop in performance between pg_start_backup and pg_stop_backup, since full_page_writes is effectively forced on during backup mode.) You must ensure that these steps are carried out in sequence, without any possible overlap, or you will invalidate the backup.

Logical

Logical backup refers to simple export of a data, achieved by the pg_dump. It is important to note that logical backup cannot be used in conjunction of XLOG or other incremental features, but it is very convenient for data migration.

Logical backup can also be configured in many ways, as simple as to dump a particular database in SQL script file:

Or even, further to dump the database in a directory using 5 slaves:

Restore & Recovery

Restore and Recovery have been ALWAYS a different topics in the eyes of a DBA:

Restore - Relates to the physical restore of files, not for making the files work together and be consistent between themselves, but only to put some version of the dataifiles somewhere, that is all what restore stands for.

Recovery - Relates to the synchronization between the datafiles and log files, in other words making everything consistent.

Physical

Whole server

With Barman we can easily restore your, if you have one :). Please ensure that YOU NEVER RESTORE IN YOUR ORIGINAL DATA DIRECTORY, this might cause problems.

To list existing backups, log in on your central barman server and issue the following command:

This will show us the list of the backups, their status and the late command, will show us the latest one:

The barman will prepare also your recovery.conf script.

You can also instruct PostgreSQL to use barman for the recovery of the xlog files. Here example of such recovery.conf file.

So in order to start the recovery, just rename the data folder and restart the service:

Once the recovery has performed, the server will change the recovery.conf file to recovery.done, signaling to the DBA that the recovery is completed:

Tablespace

Table

Logical

The recovery depends on the backup, like any other database, backup and recovery tools come in pairs. So let's see how to recover a file generated by pg_dump.

This command will execute and show all the input of file: hunter_dev_pgsql.sql