|The PostgreSQL 9.0 Reference Manual - Volume 3 - Server Administration Guide
by The PostgreSQL Global Development Group
Paperback (6"x9"), 274 pages
RRP £9.95 ($14.95)
Sales of this book support the PostgreSQL project! Get a printed copy>>>
10.1.1 Restoring the dump
The text files created by pg_dump are intended to be read in by the psql program. The general command form to restore a dump is
psql dbname < infile
where infile is the
file output by the pg_dump command. The database dbname will not be created by this
command, so you must create it yourself from
before executing psql (e.g., with
createdb -T template0 dbname). psql
supports options similar to pg_dump for specifying
the database server to connect to and the user name to use. See
psql reference page for more information.
Before restoring an SQL dump, all the users who own objects or were granted permissions on objects in the dumped database must already exist. If they do not, the restore will fail to recreate the objects with the original ownership and/or permissions. (Sometimes this is what you want, but usually it is not.)
By default, the psql script will continue to
execute after an SQL error is encountered. You might wish to run
ON_ERROR_STOP variable set to alter that
behavior and have psql exit with an
exit status of 3 if an SQL error occurs:
psql --set ON_ERROR_STOP=on dbname < infile
Either way, you will only have a partially restored database.
Alternatively, you can specify that the whole dump should be
restored as a single transaction, so the restore is either fully
completed or fully rolled back. This mode can be specified by
command-line options to psql. When using this
mode, be aware that even a minor error can rollback a
restore that has already run for many hours. However, that might
still be preferable to manually cleaning up a complex database
after a partially restored dump.
The ability of pg_dump and psql to write to or read from pipes makes it possible to dump a database directly from one server to another, for example:
pg_dump -h host1 dbname | psql -h host2 dbname
Important: The dumps produced by pg_dump are relative to
template0. This means that any languages, procedures, etc. added via
template1will also be dumped by pg_dump. As a result, when restoring, if you are using a customized
template1, you must create the empty database from
template0, as in the example above.
After restoring a backup, it is wise to run
ANALYZE on each
database so the query optimizer has useful statistics;
see section 9.1.3 Updating Planner Statistics
and section 9.1.5 The Autovacuum Daemon for more information.
For more advice on how to load large amounts of data
into PostgreSQL efficiently, refer to Volume 1A: 12.4 Populating a Database.
|ISBN 9781906966072||The PostgreSQL 9.0 Reference Manual - Volume 3 - Server Administration Guide||See the print edition|