PostgreSQL 11 Administration Cookbook

Logical – from the script dump

Once you have located the PostgreSQL server on which you will prepare and verify the data to restore (the staging server), you can proceed like so:

1. Reload the script in full on the staging server, as follows:

psql -f myscriptdump.sql

2. From the recovered database server, dump the table, its data, and all of the definitions of the dropped table into a new file:

pg_dump -t mydroppedtable -F c mydatabase > dumpfile

3. Now, recreate the table in the original server and database, using parallel tasks to speed things up (here, we will pick two parallel jobs as an example):

pg_restore -d mydatabase -j 2 dumpfile

The last step can be executed remotely without having to transfer dumpfile between systems. Just add connection ...