Re: How to increace nightly backup speed

On 11/28/06, Andrus <eetasoft(at)online(dot)ee> wrote:
>
>
> My goal is to create ERP system which creates backups without any
> attendance.
> I don'nt know how to automate this 100% and havent found any such sample.
>
> Depending on what you plan to do with the backups (like create a fallover
server), I don't know that you'll find a fully automated solution without
going to very expensive high end products like Oracle, and quite possibly
not even then.

I have a three part approach to backups, all implemented via cron (on a
Linux server):

1. On a daily basis I back up the key tables using pgdump.

2. On a weekly basis I also backup the two 'productiion' databases using
pgdump.

3. Also on a weekly basis, I do a full backup (dumpall) of the entire
PostgreSQL database.

The weekly backup of the larger of the two databases produces a file that is
about 20GB and takes about an hour and 15 minutes. I then compress it down
to about 4 GB, which takes another hour. However, because that's a separate
task, it doesn't impact the database server as much. (I suspect all that
I/O slows things down a bit, but I haven't noticed any significant effect in
my transaction time reports. That task is run during the slowest 4 hour
period of the week, though).

A 'restore' of that database on a different server takes somewhere between 4
and 6 hours.

BTW, if you've never actually tested your recovery capabilities, can you be
sure they work?
I did a full-blown test in February or March and found a few loose ends.
And when we had to do the real thing in May (due to a power supply failure),
there were STILL a few loose ends, but we were back online within 12 hours
of when I started the recovery process, and half of that time was spent
completing the setup of the 'backup' server, which I had been rebuilding.
I'm working to lower that downtime and will be doing another full-blown test
in January or February.