Thread: What is better method to backup postgresql DB.

In response to Steeles <steeles@gmail.com>:

> new to postgresql. need to backup postgresql DB, which way is better to
> backup DB.
>
> from training, I learned that we can backup the whole PGdata and other
> directories to achieve backup goal, originally I was planned to schedule
> jobs to use pgdump to backup all DBs.
>
> so which way is better for backup or there is other way to backup PGDB.

Use pg_dump or pg_dumpall.  If you're at the level that you have to ask
this question, then you'll have nothing but trouble getting reliable
backups by backing up directories.

Also, since you're new to PostgreSQL, I _HIGHLY_ recommend that you don't
assume that you're getting backups until you can demonstrate that you can
restore them.

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

> In response to Steeles <steeles@gmail.com>:
> > new to postgresql. need to backup postgresql DB, which way is better to
> > backup DB.
> >
> > from training, I learned that we can backup the whole PGdata and other
> > directories to achieve backup goal, originally I was planned to schedule
> > jobs to use pgdump to backup all DBs.
> >
> > so which way is better for backup or there is other way to backup PGDB.
>
> Use pg_dump or pg_dumpall.  If you're at the level that you have to ask
> this question, then you'll have nothing but trouble getting reliable
> backups by backing up directories.
>
> Also, since you're new to PostgreSQL, I _HIGHLY_ recommend that you don't
> assume that you're getting backups until you can demonstrate that you can
> restore them.

Good advise! For all my production systems I do a life backup/replication
using slony plus a nightly pg_dump which is then copied to a remote server.
That way, no matter what happens, I have a backup that's a maximum of 1 day
old (if I have to use the pg_dump) or a few seconds behind (if the replicated
database isn't hit - which sure is also remote and not in the same physical
location)

Whatever you use, always make sure you actually can restore the backup.

On Tue, 2010-10-26 at 14:27 -0400, Steeles wrote:

> new to postgresl. need to backup postgresql DB, which way is better to
> backup DB.
>
> from training, I learned that we can backup the whole PGdata and other
> direcotries to acheive backup goal, originally I was plainned to
> schedule jobs to use pgdump to backup all DBs.
>
> so which way is better for backup or there is other way to backup
> PGDB.

Backup is explained in a detailed chapter in the new book:
PostgreSQL Administration Cookbook, available from the link below.

--
 Simon Riggs           http://www.2ndQuadrant.com/books/
 PostgreSQL Development, 24x7 Support, Training and Services