We have postgresql 9.1 configured for streaming replication with a WAL archive, and this works great. We would like to use a shared WAL archive so we can restore from periodic base backups. The postgresql docs refer to this as a "long-term WAL archive area."
However, we archive to a shared folder (using archive_command), and after a failover, the new master tries to archive the same WAL segment filename as the old master. The contents are different. What is the right way to handle this?
We have tried to allow the new master to overwrite the old file, but then when we test restore from the base backup with a configured restore_command that restores from the shared WAL archive, we get various errors when the restoring server hits the overwritten log files. (errors like "record with zero length at 0/E5003128") So it looks like overwriting is the wrong approach. What is the right approach?
In a restore situation, there is no master and no slave. There is a fresh clean database that is restoring from a basebackup. It also has a restore_command. How are the archive_command and restore_command supposed to work together to let the restore work? (i.e. where does the archive command write after a failover, and where does the restore_command read from?)
A bit more information so this is perfectly clear:
Let's say we have 4 servers. A, B, X, and R. A and B are identical, and are a master/slave pair using streaming replication. They have the same configuration, except as far as recovery.conf v. recovery.done on the slave. X is a separate reliable host where we store backups and archived WAL files. archive_command (on either A or B, whoever is master) archives WAL files to a shared folder on X. restore_command (on either A or B, whoever is slave) restores from that shared folder on X. Periodically, we take a basebackup of the master and store it on X. This works, with one caveat below.
In a disaster situation (or for testing) we want to restore a full copy of the database onto R. We restore one of the basebackups from X on R. R now has the restore_command to read from the shared folder on X. It can easily restore all the WAL files made from the same source as the basebackup, but it fails as soon as it hits a WAL file from the new master after a failover. (see Caveat).
Caveat: if archive_command does as the specs says, and refuses to overwrite a file with the same name, then the new master will fail to archive any WAL file segments. On promotion, it tries to archive everything it has locally, including segments from the old timeline. In fact, it has segments that were never archived by the old master, because they were streamed before the xlog segment was closed. So currently, we allow the archive command to overwrite archived WAL files in this situation. It's these WAL files that fail to restore in a recovery. We know this is wrong, somehow, but don't know what the right behavior should be. With this behavior, though, failover and later fail back work.