Re: Consistent file-level backup of pg data directory

On 08 January 2014, Gator wrote:

> 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
>
>

You can better try to use pg_basebackup (if you are not very particular about rsync), which will be
easy to use and will give you consistent backup.
You will be required to make configuration changes of all servers.
Check for PostgreSQL documentation for more detail.

Thanks and Regards,
Kumar Rajeev Rastogi