Re: Consistent file-level backup of pg data directory

On 01/08/14 19:55, Jeff Janes wrote:
> I think it would be easier to just exclude the database from the
> system-wide backup and use a different method for it, rather than
> engineer the necessary before/after hooks onto the system-wide backup.

Thanks for your comments!

I really thought, it would be easier (I still have the
impression, that almost everything needed to make a filesystem
backup safe would be available, only the parts don't fit too well
for that purpose). I now came up with a way how to safely get a
postgres database into the general backup-procedure:

- move the postgres data directory to a separate LVM volume
- call pg_start_backup()
- create a LVM snapshot
- call pg_stop_backup() and update the snapshot's pg_xlog from
the "real" volume
- create a recovery.conf on the snapshot
- run our normal backup procedure with the "real" postgres data
volume replaced by the prepared snapshot
- remove the snapshot again
- If the backup is restored to a machine, postgres will find the
recovery.conf file and initiate the recovery

Actually, I wonder if in this scenario, the pg_start/stop_backup
is really necessary - would it be safe to just tell postgres to
create a checkpoint right before the snapshot and directly use
the "frozen" data directory in the backup? (The postgres
documentation suggests this).

Another point in favor of the slightly cumbersome solution above
would be the ability to combine it with Point-in-Time Recovery.
As far as I understood, this only works with a base backup created
using pg_start/stop_backup. Is this right?

Regards,
Peter