On a PostgreSQL v9.0 I've a WAL archiving system working. So WAL files a regulary archived (when 3 WAL are created or if a WAL is older than 15 minutes).

Now I add a binary packup of the PG_DATA directory (excluding the pg_xlog subdirectoy). To do that I perform a pg_start_backup(), the binary copy, and a pg_stop_backup().

I think I understand quite well what the pg_start_backup and pg_stop_backup are doing, the first one make a checkpoint and the last one ensure the last WAL file is archived.

From the official documentation we can see that for the binary data copy we should:

Perform the backup, using any convenient file-system-backup tool such as tar or cpio (not pg_dump or pg_dumpall). It is neither necessary nor desirable to stop normal operation of the database while you do this.

So I'm quite perplex. This mean a Checkpoint could be performed while we make the copy. I saw a lot of documentation stating that the copy command should allow data changes while performing the copy, I'm Ok with that, simplya matter of finding the right tool. But My question is how postgreSQL will handle the recovery with a pg_data content containing some files which are inconsistent (some from before the checkpoint, some from after)?

By replaying the transaction logs Postgresql will be able to put all these files in the right state? I saw that create tables and drop operations are dangerous while the backup is performing, Isn't there some dangerous operations like vacuum commands? Does the pg_backup suspend the vacuum operations? Should I make a copy of the global/pg_control file in the end of in the start of the binary copy process? Should I use a snapshot-enabled filesystem (like with an xfs-freeze) to get a faster restore process?

I saw that a backup script crash will not launch a pg_stop_backup automatically, so there's a chance that my backup state live for a long time (until my nagios rings someone somewhere to fix the pg_stop_backup() ). So if anythingis different in PostgreSQL between these two comands I'd like to know it, to understand what impact it may have.

Enlighten me please.

1 Answer 1


You asked:

how postgreSQL will handle the recovery with a pg_data content containing some files which are inconsistent.

pg_start_backup() ensure the data file is at least as new as the checkpoint. On recovery, the logs are applied.

If the data is old, the log will update it..

If the data is new, the log will have same content. There is no hurt writing it again.

The data are never newer then the log, because the logs are write ahead (WAL).

You asked:

... xfs-freeze ...

xfs-freeze is alike to pg_start_backup(), it don't take a snapshot. You need a volume manager to do that.

You asked:

... why do create tablespace & create database statements are unsupported if the WAL can replay everything?

It is supported, just some little gotcha. See http://www.postgresql.org/docs/8.1/static/backup-online.html :

23.3.5. Caveats

CREATE TABLESPACE commands are WAL-logged with the literal absolute path, and will therefore be replayed as tablespace creations with the same absolute path. This might be undesirable if the log is being replayed on a different machine. It can be dangerous even if the log is being replayed on the same machine, but into a new data directory: the replay will still overwrite the contents of the original tablespace. To avoid potential gotchas of this sort, the best practice is to take a new base backup after creating or dropping tablespaces.

  • about xfs-freeze I know this depends as well on a volume manager, it was just a part of the snapshoting procedure. but are we sure the WAL recovery will handle well the replay of a pre-vacuum table logs on a post-vacuum binary table? and is the content of global/pg_control important? why do create tablespace & create database statements are unsupported if the WAL can replay everything?
    – regilero
    Jul 15, 2011 at 10:35
  • CREATE TABLESPACE works. see updated answer. I am not sure on VACUUM, but I can't imagine why it won't.
    – J-16 SDiZ
    Jul 15, 2011 at 13:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.