[Box Backup-dev] Backup of PostgreSQL database

[David Sommerseth]

Hi!

I've been using boxbackup-0.10 for over a year in a production environment. 
  And it really makes me sleep quite well at night.  But there is one thing 
which can cause some unclear nightmares.  Proper backup of PostgreSQL.

Right now, I'm running the worst scenario of DB backup - backing up the raw 
DB files without shutting down the database server.  But I have done 
restores from it this way, but it is far from ideal as the WAL might not be 
consistent.  This can cause troubles if WAL data is not written to the 
table files yet.

PostgreSQL do support some kind of Point-In-Time-Recovery, with special 
commands for preparing files for backup.

http://www.postgresql.org/docs/8.3/static/continuous-archiving.html

Has anyone looked into such support in BoxBackup?  If not, I'd be glad to 
spend some time digging into this.  With proper documentation in BoxBackup 
on how to configure BoxBackup and PostgreSQL, linking in libpq and calling 
pg_start_backup() and pg_stop_backup() SQL functions is actually how I see 
the implementation could be done.  Any thoughts about this?

I do not have plenty of time, but if nobody is looking into it I believe I 
could have some suggestions for solutions ready within some months.

Another aspect which I know some financial industries are interested in as 
well is some kind of stream-backup of all SQL queries which modifies the 
database in which ever way (DELETE/UPDATE/INSERT/GRANT/CREATE/DROP).  So 
each query is then logged and backed up immediately.  There are some 
commercial solutions already for Oracle, where this is done to a tape 
streamer.  Not sure if BoxBackup would be suitable for such an approach.


kind regards,

David Sommerseth