Re: Reliably backing up a live database

Thanks very much for your responses Tom (and Daniel)... I will get with
our developers on this. I believe that the --oids switch is being used
because this is a very old database that has a web front end and
originally used postgreSQL 7.x.

We will doing a full rewrite of it soon, so unless the requirement to
use --oids can be fixed easily, we probably won't worry about that for now.

Thanks again,

Simon

On 2012-02-24 11:31 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Tanstaafl<tanstaafl(at)libertytrek(dot)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
>