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

Re: Reliably backing up a live database

From: "Daniel Staal" <DStaal(at)usa(dot)net>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: Reliably backing up a live database
Date: 2012-02-24 14:30:34
Message-ID: (view raw or whole thread)
Lists: pgsql-novice
On Fri, February 24, 2012 9:07 am, Tanstaafl wrote:
> Hello,
> I am relatively new to SQL databases in general, and very new to
> postgresql, so please be gentle.
> My question has to do with how to properly perform a dump on a live
> database, that I've sort of inherited. We have some developers that are
> assisting with making some modifications, but I want to make sure that
> something they told me is true...
> 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)?
> Second, can this command be run safely on a running database, or should
> the database be stopped first? If the latter, would someone be so kind
> as to provide an example of the commands necessary to stop this
> database, perform the dump, then restart it, that I could put in a cron
> job?

I might argue about the need/desirability of a couple of the switches, 
(--oids in particular: If you need it, you probably should redesign your
database so you don't) but that depends on your environment.  But yes,
that should preform a full backup on 'dbname' while you are running the
database.  (In fact, I think you have to be running the database to run

Of course, the best way to be sure the above meets your needs is to set up
a test server and restore the database to it: a 'tested to work in
practice' backup/restore process beats a 'works in theory' backup/restore
process any day of the week.

Daniel T. Staal

This email copyright the author.  Unless otherwise noted, you
are expressly allowed to retransmit, quote, or otherwise use
the contents for non-commercial purposes.  This copyright will
expire 5 years after the author's death, or in 30 years,
whichever is longer, unless such a period is in excess of
local copyright law.

In response to

pgsql-novice by date

Next:From: Merlin MoncureDate: 2012-02-24 14:33:50
Subject: Re: Test inserted text in trigger (arrays, custom types) (corrected)
Previous:From: TanstaaflDate: 2012-02-24 14:07:18
Subject: Reliably backing up a live database

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