Database Reference
In-Depth Information
Recovery of all databases
Recovery of a complete database server, including all of its databases, is an important
feature. This recipe covers how to do that in the simplest way possible.
Some complexities are discussed here, though most are covered in later recipes.
Getting ready
Find a suitable server on which to perform the restore.
Before you recover onto a live server, always take another backup. Whatever problem you
thought you had could be just about to get worse.
How to do it...
LOGICAL (from custom dump
-F c
):
F
Restore of all databases means simply restoring each individual database from each
dump you took. Confirm you have the correct backup before you restore:
pg_restore --schema-only -v dumpfile | head | grep Started
F
Reload globals from script file as follows:
psql -f myglobals.sql
F
Reload all databases. Create the databases using parallel tasks to speed things
along. This can be executed remotely without needing to transfer dumpfile between
systems. Note that there is a separate dumpfile for each database.
pg_restore -d postgres -j 4 dumpfile
LOGICAL (from script dump created by
pg_dump
-F p
):
As above, though with this command to execute the script. This can be executed remotely
without needing to transfer dumpfile between systems.
F
Confirm you have the correct backup before you restore. If the following command
returns nothing, then the file is not timestamped, and you'll have to identify it in a
different way:
head myscriptdump.sql | grep Started
F
Reload globals from script file as follows:
psql -f myglobals.sql
