Re: Reliably backing up a live database

Tanstaafl <tanstaafl@libertytrek.org> writes:
> I was told by one of the developers (who admits that he is still
> learning postgres) that I could get a successful dump of the live
> database using the following command, and more importantly, that I could
> do this safely without stopping the database first:

> ./pg_dump -U postgres  -Z --blobs --oids --encoding=UTF-8 dbname >
> dbname.sql.gz

> First, will that command result in a dump file that can be used to
> perform a full restore in event of a catastrophe (these will be uploaded
> to off-site storage)?

This will only get you the content of the single database "dbname";
a Postgres installation ("cluster") can contain multiple databases.
Also, cluster-wide entities such as role definitions don't get dumped
this way.  For most purposes you want to use pg_dumpall for routine
backup purposes, as that fixes both these issues.

Also, in any modern version of PG, --blobs is a no-op (it's on by
default) and --oids is deprecated.

> Second, can this command be run safely on a running database, or should
> the database be stopped first?

Nobody stops the database for this.  pg_dump is built to get a
consistent snapshot despite concurrent updates.  There are some
limitations as to doing schema changes (DDL) concurrently, but ordinary
applications don't often fall foul of that.

Depending on what you're doing, there are other backup methods besides
periodic pg_dump that might be superior.  It'd be worth your while to
read the fine manual:
http://www.postgresql.org/docs/9.1/static/backup.html
(adjust link depending on which PG version you're running,
as the facilities vary over time)

            regards, tom lane