Re: Backup Database Question

On Thursday, October 06, 2011 7:49:38 am Carlos Mennens wrote:
> So I'm looking to start regularly backing up my production database at
> work. I'm tired of doing it manually every day before I go home. I use
> the built in 'pg_dump' or 'pg_dumpall' utilities however I don't know
> which is more beneficial for a nightly backup. Perhaps I should be
> using the 'pg_dumpall' as a weekly / full backup only and not perform
> this nightly but honestly I have no idea so I'm asking the experts
> here. When should I use 'pg_dump' & 'pg_dumpall'? Is there a downside
> to just backing up the entire database cluster nightly besides I/O
> load and sacrificing system performance?

My 2 cents:

Use pg_dumpall -g to dump only the global objects

Use pg_dump -Fc to dump the individual databases in the cluster.
This gives you a compressed dump file. Furthermore it allows you to restore some
subset of the database fairly easily should the need arise (see another recent
thread where the OP wanted to restore only two tables out of a pg_dumpall file).

>
> My last question is does anyone know how I can easily automate my
> backups for PostgreSQL in Linux using Cron or some well written script
> someone has on the web? I'm looking for anything that can simplify
> and automate my backups for me so I don't have to do them manually by
> hand before I leave the office.
>
> Thanks for any help in this area!

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com