Postgresql – How does PostgreSQL handle Checkpoints in the middle of a WAL-enabled backup

backuppostgresql

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.

Best Answer

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.