Backup & Recovery - PostgreSQL 9 Administration - page 284

F From the recovered database server, dump the table, its data, and all the definitions
of the dropped table into a new file as follows:
pg_dump -t mydroppedtable -F c mydatabase > dumpfile
F Now, recreate the table into the original server and database, using parallel tasks
to speed things along. This can be executed remotely without needing to transfer
dumpfile between systems.
pg_restore -d mydatabase -j 2 dumpfile
The only way to extract a single table from a script dump without doing all of the preceding is
to write a custom Perl script to read and extract just the parts of the file you want. That can be
complicated, because you may need certain SET commands at the top of the file, the table,
and data in the middle of the file, and the indexes and constraints on the table are near the
end of the file. It's complex; the safe route is the one already mentioned.
PHYSICAL:
To recover a single table from a physical backup, we need to:
F Find a suitable server, or create a new virtual server.
F Recover the database server in full, as described in previous recipes on physical
recovery, including all databases and all tables. You may wish to stop at a useful point
in time, in which case you can look at the recipe on that topic later in the chapter.
F From the recovered database server, dump the table, its data, and all the definitions
of the dropped table into a new file as follows:
pg_dump -t mydroppedtable -F c mydatabase > dumpfile
F Now, recreate the table into the original server and database using parallel tasks
to speed things along. This can be executed remotely without needing to transfer
dumpfile between systems as follows:
pg_restore -d mydatabase -j 2 dumpfile
How it works...
At present, there's no way to restore a single table from a physical restore in just a single step.
See also
Splitting a pg_dump into multiple sections, "pre" and "post" was proposed by me for an earlier
release of PostgreSQL, though I haven't had time to complete that yet. It's possible to do that
using an external utility also; the best script I've seen to split a dump file into two pieces is
available at the following website:
http://bucardo.org/wiki/split_postgres_dump