Overview

Logical backup refers to simple export of a data, achieved by the pg_dump. Logical backups are vastly used between different versions. For example transfer of a database between PostgreSQL 9.6 → 11. 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.

Backup

Of course with logical backup we can backup:

Entire Server

Particular Database

All roles and privileges

Particular Database

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:

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

Backup all roles and Users

To backup users and roles, we have to use a way similar to backing up the entire server, just we will backup only the globals as follows:

Dump Roles & Users

Restore

In terms of logical backup, restore and recovery are the same thing. It means, execute the script in order to REDO the changes.

However BARE IN MIND ONE PROBLEM :)

Backup taken with: pg_dump: Restore with psql

Backup taken with: pg_dump -Fc: Restore with pg_restore

If you have many databases you can restore them via bash script.

The following script will restore all databases in the current directory.

Appendix

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

Which can be put in crontab as follows: