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