Avoiding Logical Dump Pitfalls

Postgres has three backup methods: logical,
file system, and
continuous archiving. The last two rely on copying the file
system (with the possible assistance of the write-ahead log). The first
method, logical backups, is performed via pg_dump or
pg_dumpall, and has two pitfalls I would like to mention.

First, many people think that running pg_dumpall and running pg_dump on each database individually are equivalent — they are
not. While pg_dumpall does backup each database individually, it also dumps out
global objects that exist outside any specific database, e.g. roles,
tablespaces. If you prefer to use pg_dump to dump each database individually, be sure to run
pg_dumpall -globals-only as well. Failure to restore global
objects before per-database objects causes all restored data to be owned by the restore user and be in a single tablespace.

Continue Reading »