Am 2015-06-08 14:45, schrieb otheus uibk:
The manual and in this mailing list, the claim is made that
consistent, file-level backups may be made by bracketing the
file-copy
operation with the postgresql pg_start_backup and pg_stop_backup
operations. Many people including myself have found that in some
circumstances, using "tar" to copy these files will result in an
error
if one of the data files changes during the tar operation. The
responses to those queries on this mailing list are unsatisfactory
("everything is fine, trust us").
[...]
I decided to test this claim that these messages are "perfectly
harmless" and "can be ignored":
1. I executed pg_start_backup() on server
2. Ran md5sum recursively through PGs data directories
3. waited a split second
4. Ran md5sum recursively through PGs data directories as in step 2
5. Compared output from #2 and #4
As you can see below, there were non-zero changes made to these
files.
< a1a571bfd1e4a98b20245edbdfce6d9a
/var/lib/pgsql/data/base/41514/809275
---
21de5b864019c96c55e81a38fa1c9ccf
/var/lib/pgsql/data/base/41514/809275
1783c1783
< 8eb4a578ecb56667e1698174f89c462c
/var/lib/pgsql/data/base/41514/809280
---
b4c7b4ef30dda9543181465f53a85d72
/var/lib/pgsql/data/base/41514/809280
Such changes occurred EVEN WHEN TAR DID NOT WARN of changed files.
Further, when step 3 involved an actual backup, involving minutes,
not
milliseconds, dozens of differences to files in data/base/... are
reported. To be clear, I excluded from consideration all files in
pg_xlog, pg_clog, pg_subtrans, pg_stat_tmp.
If these files are changing during the pg_start_backup() and
pg_stop_backup, then exactly what is their purpose? Might they be
changing during the tar, as tar thinks? How may an operator be
assured
the snapshot is consistent (unless one stops the databases)? Will
the redo logs restore the files to a consistent state, no matter when
these files are changed? I find it hard to believe that would be the
case.
This test was performed using Postgresql 9.1.8. A scan of the
CHANGELOG since then indicates that if this is a bug, it has not been
reported as fixed.
Still everything is fine here. You need to understand that in between
pg_start_ and pg_stop_backup Postgres continues to operate aus usual -
so files in $PGDATA directory WILL change. That's why it is necessary to
also keep all the WAL segments that where created during _start and
_stop to actually recover to a consistent state. When you recover from a
full (file based) backup the WAL files file be applied, too (that is why
you need a recovery.conf and a restore_command.
You should possibly re-read
http://www.postgresql.org/docs/9.4/static/continuous-archiving.html#BACKUP-PITR-RECOVERY
especially 24.3.3 and 24.3.4.
hth
Jan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
