Backup & Recovery - PostgreSQL 9 Administration

Database Reference

In-Depth Information

When pg_dump runs, it holds the very lowest kind of lock on the tables being dumped. Those are

designed to prevent DDL from running against the tables while the dump takes place. If a dump

is run at the point that other DDL are already running, then the dump will sit and wait. If you want

to limit the waiting time you can do that by setting the --lock-wait-timeout option.

pg_dump allows you to make a selective backup of tables. The -t option also allows you to

specify views and sequences. There's no way to dump other object types individually using

pg_dump . You can use some supplied functions to extract individual snippets of information

available at the following website:

http://www.postgresql.org/docs/9.0/static/functions-info.

html#FUNCTIONS-INFO-CATALOG-TABLE

pg_dump works against earlier releases of PostgreSQL, so it can be used to migrate data

between releases.

pg_dump doesn't generally handle included modules very well. pg_dump isn't aware of

additional tables that have been installed as part of an additional package, such as PostGIS

or Slony, so it will dump those objects as well. That can cause difficulties if you then try

to restore from the backup, as the additional tables may have been created as part of the

software installation process in an empty server.

There's more...

What time was the pg_dump taken? The snapshot for a pg_dump is taken at the beginning of

a run. The file modification time will tell you when the dump finished. The dump is consistent

at the time of the snapshot, so you may want to know that time. If you are making a script

dump, you can do a dump verbose as follows:

pg_dump -v

which then adds the time to the top of the script. Custom dumps store the start time as well

and that can be accessed using the following:

pg_restore --schema-only -v dumpfile | head | grep Started

-- Started on 2010-06-03 09:05:46 BST