Re: Using Tarsnap for PostgreSQL continuous archiving

[Gleb Arshinov <gleb.lists@pluron.com>]

Hi George,

What are you trying to achieve with this setup?  Unless the goal is
strictly to fund tarsnap, using tarsnap as archive_command  seems like
the wrong approach ;-)

We use tarsnap to backup Postgres database for Acunote simply by
archivining pg_dump's.  For our particular database doing it montly
uses about as much tarsnap space as full bz2 archive, doing it daily
uses 3 times more space (meaning you get 30 daily backups for the
price of 3 full backups), and doing it hourly uses 5 times more space
than daily.

This is using standard pg_dump format.  It would useful to test if
pg_dump's custom format would be better, but existing setup is cheap
enough that we won't bother.  Also, I suspect much of the space goes
towards some volatile data we could exclude from backup altogether.

Doing binary backups of data files + wals (using pg_start_backup)
would be another thing to test.  It may or may not work well with
tarsnap's deduplication algorithm.  In any case, if you DB is big
enough pg_dump is not an option, this would be the alternative.  If
that's the path you want to take check out Skype's walmgr.  It has
good functionality to create on-filesystem backups.  Once these work
for your, all you'd need to do is to tarsnap them after/rather than
copying them.

Best regards,

Gleb


On Fri, May 28, 2010 at 6:04 AM, George MacKerron
<george@mackerron.co.uk> wrote:
> Hi all,
>
> I'm considering using Tarsnap for backup of a PostgreSQL database, using tarsnap both for Write Ahead Log archiving (as my 'archive_command') and for the base backup (performed between SELECT pg_start_backup('label'); and SELECT pg_stop_backup(); commands). (These tasks are described at http://www.postgresql.org/docs/8.4/static/continuous-archiving.html).
>
> I'm also considering adding pg_compresslog/pg_decompresslog to this arrangement (though I'm a bit wary of recent bugs with Postgres 8.4).
>
> Does anyone on the list have experience with this Tarsnap use case? Any advice? Any pitfalls? And/or any feel for what level of compression is achieved with both full and non-full ('archive_timeout'-switched) WAL files?
>
> Many thanks,
> George