Skip site navigation (1) Skip section navigation (2)

Re: Reliably backing up a live database

From: Tanstaafl <tanstaafl(at)libertytrek(dot)org>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Reliably backing up a live database
Date: 2012-02-24 17:37:14
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
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,


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:
> (adjust link depending on which PG version you're running,
> as the facilities vary over time)
> 			regards, tom lane

In response to

pgsql-novice by date

Next:From: TanstaaflDate: 2012-02-24 17:58:13
Subject: Sanitizing text being stored in text fields - some characters cause problems
Previous:From: Tom LaneDate: 2012-02-24 16:31:08
Subject: Re: Reliably backing up a live database

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group