Consistent file-level backup of pg data directory

From: gator_ml(at)yahoo(dot)de
To: pgsql-general(at)postgresql(dot)org
Subject: Consistent file-level backup of pg data directory
Date: 2014-01-07 18:58:06
Message-ID: lahini$amt$1@ger.gmane.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

On my site, we run rsync-based backups of all servers to a central
backup server every night. Unfortunately, reading the postgres
documentation I could not find any direct way how to ensure that the
state of the postgres data directory stored on the backup server is in a
consistent state.

It seems to me, that using (abusing;-) the "Continuous Archiving" it
should be possible to get what i need. Specifically, I came up with
the following strategy:
- set wal_level=archive
- call pg_start_backup()
- keep a copy "backup_label" under a different name
- call pg_stop_backup()
- the run the regular backup procedure

As far as I can see, the result of this should be that I get a
consistent snapshot of the data directory at the time of the
pg_stop_backup call. After restoring the backup somewhere,
I only need to create a "recovery.conf" file (containing
restore_command='/bin/true' because restore_command is
mandatory) and everything should be fine.

In practice, this procedure also seems to work as expected.
Is there any hidden pitfall I overlooked? Or is there maybe
a better way not mentioned in the postgres documentation how to
get the data into a consistent state for a file level backup?

Any Feedback would be appreciated!

Regards,
Peter

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Susan Cassidy 2014-01-07 19:47:36 When starting postgres, it hangs like it is still connected to stdout
Previous Message ChoonSoo Park 2014-01-07 18:41:10 Re: Is there a way to return "true"/"false" string for boolean type?