Re: backup strategies

"Richard P. Welty" <rwelty@averillpark.net> wrote:
>
> so the outfit i'm currently working for on a quasi-full time
> basis has what amounts to an OLTP database server in colocation.
> the footprint in the rack is very small, that is, there's no
> DLT autoloader or anything of that sort in the rack.
>
> the temporary backup solution was to do full dumps in cron
> every night and use scp to move them to the office server.
> this has clear scaling problems, but was an ok quick
> hack back in march.
>
> well, now it's may and the magnitude of the scaling problems
> are becoming very obvious. the scp is taking an absurd
> amount of time (the backup times themselves aren't all that
> bad, but the office is behind a business road runner connection
> and the scp is severely bandwidth limited.)

Have you looked into rsync?  I would think rsync could copy your
pg_dump very efficiently, since it should be able to skip over
parts that haven't changed since the previous run.  Make sure _not_
to compress the dump if you use rsync, to allow it to take
the most advantage of unchanged data.

> so i'm outlining a longer term solution, and would be
> interested in suggestions/comments. part 1 is to reduce the
> frequency of the full dumps, and start using a WAL based
> incremental solution such as is outlined here:
>
>   http://www.postgresql.org/docs/8.1/static/backup-online.html
>
> part 2 is going to be to set up amanda to roll this stuff to
> a DLT drive in the office. i figure that i probably want to do
> full backups of the incremental WAL files each time so i'm not
> rummaging around the tape library trying to find all of them
> should the worst case happen.
>
> but what are the consequences of backing up a WAL file
> if the archive process (probably scp in this case) is running
> when the backup copy is made? the whole thing won't make it onto
> tape, are there any downsides to running a recover with
> an incomplete WAL file?

Make sure you do a "SELECT pg_start_backup()" before doing the copy
(don't forget the "SELECT pg_stop_backup()" when you're done)  That
tells PG that data after that point might change, and gives it
the information it needs to ensure it can use the WAL files no
matter what happens during.

As an aside, you can only fit so many gallons into a 10 gallon
container.  You might simply have to accept that your requirements
now exceed the capacity of the RR connection and upgrade.

--
Bill Moran
http://www.potentialtech.com