Thread: Backup Database Question

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

Carlos Mennens <carlos.mennens@gmail.com> writes:
> 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?

Use pg_dumpall.  The extra time to dump the user and database
definitions is unlikely to be noticeable, and if push comes to shove
you'll be glad you had them.

            regards, tom lane

On Thu, Oct 6, 2011 at 11:19 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Use pg_dumpall.  The extra time to dump the user and database
> definitions is unlikely to be noticeable, and if push comes to shove
> you'll be glad you had them.

Yes I agree but I didn't know enough about PostgreSQL to make that
determination. Seems very logical however. Does anyone know of a
PostgreSQL backup script floating around the Internet for Linux
systems? I found a great one for MySQL but sadly that doesn't do me
any good.

On 10/06/11 8:26 AM, Carlos Mennens wrote:
> Yes I agree but I didn't know enough about PostgreSQL to make that
> determination. Seems very logical however. Does anyone know of a
> PostgreSQL backup script floating around the Internet for Linux
> systems? I found a great one for MySQL but sadly that doesn't do me
> any good.


    /path/to/pg_dumpall | gzip > /path/to/pgbackup-$(date -I).sql.gz

put this in a script accessible by the postgres user, and in the
postgres user's crontab, invoke it something like..

30 1 * * * /path/to/backupscript

to run it every night at 1:30am.  If you only want to run it once a week...

30 1 * * 6 /path/to/backupscript

which will run it on Saturday morning at 1:30am (day 6)


--
john r pierce                            N 37, W 122
santa cruz ca                         mid-left coast

On Thu, Oct 6, 2011 at 3:12 PM, John R Pierce <pierce@hogranch.com> wrote:
>   /path/to/pg_dumpall | gzip > /path/to/pgbackup-$(date -I).sql.gz

Thanks John. I've never written a script so do I just use 'Vim' to
open a new file and just paste the following line?

#!/bin/bash
/usr/bin/pg_dumpall | gzip > /var/db_backup/pg_backup-$(date -I).sql.gz

Is that all I need to do or is there more steps / data involved?

On Thu, Oct 6, 2011 at 1:18 PM, Carlos Mennens <carlos.mennens@gmail.com> wrote:
> On Thu, Oct 6, 2011 at 3:12 PM, John R Pierce <pierce@hogranch.com> wrote:
>>   /path/to/pg_dumpall | gzip > /path/to/pgbackup-$(date -I).sql.gz
>
> Thanks John. I've never written a script so do I just use 'Vim' to
> open a new file and just paste the following line?
>
> #!/bin/bash
> /usr/bin/pg_dumpall | gzip > /var/db_backup/pg_backup-$(date -I).sql.gz
>
> Is that all I need to do or is there more steps / data involved?

You could check if it executed and send an email when it fails.

exec_status=(/usr/bin/pg_dumpall | gzip >
/var/db_backup/pg_backup-$(date -I).sql.gz);
if [[ exec_status -ne 0 ]] ;then
   //Send yourself an email here
fi;

On 06/10/2011 20:18, Carlos Mennens wrote:
> On Thu, Oct 6, 2011 at 3:12 PM, John R Pierce <pierce@hogranch.com> wrote:
>>   /path/to/pg_dumpall | gzip > /path/to/pgbackup-$(date -I).sql.gz
>
> Thanks John. I've never written a script so do I just use 'Vim' to
> open a new file and just paste the following line?
>
> #!/bin/bash
> /usr/bin/pg_dumpall | gzip > /var/db_backup/pg_backup-$(date -I).sql.gz
>
> Is that all I need to do or is there more steps / data involved?

I think you also need to make it executable:

  chmod u+x my_backup_script

...or something like that.

Ray.


--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

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.klaver@gmail.com