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 this:
- Reload the script in full on the staging server, as follows:
psql -f myscriptdump.sql
- From the recovered database server, dump the table, its data, and all the definitions of the dropped table into a new file:
pg_dump -t mydroppedtable -F c mydatabase > dumpfile
- Now, recreate the table in the original server and database, using parallel tasks to speed things up:
pg_restore -d mydatabase -j 2 dumpfile
The last step can be executed remotely without having to transfer the dumpfile between systems. Just add connection parameters to pg_restore, as in the following ...