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