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 »

Avoiding Logical Dump Pitfalls
By  , Apr 18, 2012

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 »

Tags:
mailto:?subject= Avoiding Logical Dump Pitfalls &body= Avoiding Logical Dump Pitfalls %20:%20http://www.enterprisedb.com/avoiding-logical-dump-pitfalls