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:

Physical

Logical

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. In nutshell there are two things which you should remember:

Dump global data: like roles and users with pg_dumpall

Dump a particular database: with pg_dump

Furthermore, the best way is to use a customer format “-Fc” in order to preserve consistency across versions:

Our backup will not be so much readable with text editors:

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

Appendix

Here is a nice script to automate the export of all databases on a server:

Which can be put in crontab as follows: