Andreas Pflug <pgadmin@pse-consulting.de> writes:
> When I'm doing a file level hot backup, I can't be sure about the backup
> order. To be sure the cluster is in a consistent state regarding
> checkpoints, pg_clog must be the first directory backed up.
You are going off in the wrong direction entirely.
Any hot-backup design that thinks safety can be ensured by "back up file A
before file B" considerations is wrong. That's because Postgres doesn't
necessarily dump dirty blocks into the data area (or clog area) at any
particular time. Therefore, a filesystem-level backup taken while the
postmaster is running is basically certain to be inconsistent. You can
*not* avoid this by being careful about the order you dump files in.
Heck, you can't even be certain that a file you dump is internally
consistent.
The only way we can support file-level hot backup is in conjunction with
PITR-style WAL log archiving. It is okay for the data area dump to be
inconsistent, so long as your recovery process includes replay of WAL
starting at some checkpoint before the filesystem dump started, and
extending to some point after the filesystem dump finished. Replaying
WAL will correct the inconsistencies.
regards, tom lane