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: | 030c50da47c624ab9311020e94d93542.squirrel@www.magehandbook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
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.
---------------------------------------------------------------
From | Date | Subject | |
---|---|---|---|
Next Message | Merlin Moncure | 2012-02-24 14:33:50 | Re: Test inserted text in trigger (arrays, custom types) (corrected) |
Previous Message | Tanstaafl | 2012-02-24 14:07:18 | Reliably backing up a live database |