Overview

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

In terms of logical backup, restore and recovery are the same thing. It means, execute the script in order to REDO the changes. Simple way will be just to execute the script as follows:

Appendix

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

Which can be put in crontab as follows:

pg_dump (options) -p, –port=PORT database server port number -i, –ignore-version proceed even when server version mismatches -h, –host=HOSTNAME database server host or socket directory -U, –username=NAME connect as specified database user -W, –password force password prompt (should happen automatically) -d, –dbname=NAME connect to database name -v, –verbose verbose mode -F, –format=c|t|p output file format (custom, tar, plain text) -c, –clean clean (drop) schema prior to create -b, –blobs include large objects in dump -v, –verbose verbose mode -f, –file=FILENAME output file name

pg_restore (options) -p, –port=PORT database server port number -i, –ignore-version proceed even when server version mismatches -h, –host=HOSTNAME database server host or socket directory -U, –username=NAME connect as specified database user -W, –password force password prompt (should happen automatically) -d, –dbname=NAME connect to database name -v, –verbose verbose mode