|The PostgreSQL 9.0 Reference Manual - Volume 1A - SQL Language Reference
by The PostgreSQL Global Development Group
Paperback (6"x9"), 454 pages
RRP £14.95 ($19.95)
Sales of this book support the PostgreSQL project! Get a printed copy>>>
12.4.9 Some Notes About pg_dump
Dump scripts generated by pg_dump automatically apply several, but not all, of the above guidelines. To reload a pg_dump dump as quickly as possible, you need to do a few extra things manually. (Note that these points apply while restoring a dump, not while creating it. The same points apply whether loading a text dump with psql or using pg_restore to load from a pg_dump archive file.)
By default, pg_dump uses
COPY, and when
it is generating a complete schema-and-data dump, it is careful to
load data before creating indexes and foreign keys. So in this case
several guidelines are handled automatically. What is left
for you to do is to:
Set appropriate (i.e., larger than normal) values for
If using WAL archiving or streaming replication, consider disabling
them during the restore. To do that, set
max_wal_sendersto zero before loading the dump. Afterwards, set them back to the right values and take a fresh base backup.
Consider whether the whole dump should be restored as a single
transaction. To do that, pass the
--single-transactioncommand-line option to psql or pg_restore. When using this mode, even the smallest of errors will rollback the entire restore, possibly discarding many hours of processing. Depending on how interrelated the data is, that might seem preferable to manual cleanup, or not.
COPYcommands will run fastest if you use a single transaction and have WAL archiving turned off.
If multiple CPUs are available in the database server, consider using
--jobsoption. This allows concurrent data loading and index creation.
A data-only dump will still use
COPY, but it does not
drop or recreate indexes, and it does not normally touch foreign
So when loading a data-only dump, it is up to you to drop and recreate
indexes and foreign keys if you wish to use those techniques.
It's still useful to increase
while loading the data, but don't bother increasing
maintenance_work_mem; rather, you'd do that while
manually recreating indexes and foreign keys afterwards.
And don't forget to
ANALYZE when you're done; see
Volume 3: Updating Planner Statistics
and Volume 3: The Autovacuum Daemon for more information.
|ISBN 9781906966041||The PostgreSQL 9.0 Reference Manual - Volume 1A - SQL Language Reference||See the print edition|