Backup & Recovery - PostgreSQL 9 Administration - page 283

To repair a damaged table we want to replace the data in the table in a single transaction.
There isn't a specific option to do this, so we need to do the following:
F Dump the table to a script file as follows:
pg_restore -t mydroppedtable dumpfile > mydroppedtable.sql
F Edit a script named restore_mydroppedtable.sql with the following code:
BEGIN;
TRUNCATE mydroppedtable;
\i mydroppedtable.sql
COMMIT;
F Then, run it using the following:
psql -f restore_mydroppedtable.sql
F If you've dropped a table then you need to:
Create a new database in which to work, name it restorework , as follows:
CREATE DATABASE restorework;
Restore the complete schema to the new database as follows:
pg_restore --schema-only -d restorework dumpfile
F Now, dump just the definitions of the dropped table into a new file, which will contain
CREATE TABLE, indexes, other constraints and grants. Note that this database has no
data in it, so specifying --schema-only is optional, as follows:
pg_dump -t mydroppedtable --schema-only restorework >
mydroppedtable.sql
F Now, recreate the table on the main database as follows:
psql -f mydroppedtable.sql
F Now, reload just the data into database maindb as follows
pg_restore -t mydroppedtable --data-only -d maindb dumpfile
If you've got a very large table, then the fourth step can be a problem, because it builds the
indexes as well. If you want you can manually edit the script into two pieces, one before the
load ("pre-load") and one after the load ("post-load"). There are some ideas for that at the end
of the recipe.
LOGICAL (from script dump):
The easy way to restore a single table from a script is as follows:
F Find a suitable server, or create a new virtual server.
F Reload the script in full, as follows:
psql -f myscriptdump.sql