Re: Reliably backing up a live database

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
pg_dump.)

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.
---------------------------------------------------------------