I recently experienced a filename collision with PostgreSQL's wal archives.

This is with Postgresql 11.2, containerized, on Ubuntu 18.04.

I also noticed the container itself was restarted some 4 weeks ago.

The archive command starts with test, as is recommended on the documentation page: https://www.postgresql.org/docs/11/continuous-archiving.html

And it turned out that file that postgresql was trying to create, already existed.

So, a name collision.

What I'd like to know is how this is possible. I was expecting postgresql to name its wal_archives continously, not restart its naming scheme upon a restart of the process.

Or am I wrong in my analysis and is something else going on here?

  • 1
    I suspect that something else is going on here. Particularly, two clusters (or both a primary and a standby server) archive to the same location. Do you have any knowledge uf unusual occurrences (switchover, ...) around the same time? – Laurenz Albe Nov 25 '19 at 17:04
  • @LaurenzAlbe Not to my knowledge. Perhaps a restart of the docker container? But I have no logs showing this. That being said, primary and standby are not on the same filesystem, so that's not gonna have happened. – KdgDev Nov 25 '19 at 22:03

The archive command starts with test, as is recommended on the documentation page: https://www.postgresql.org/docs/11/continuous-archiving.html

To quote the following sentence from that page "This is an example, not a recommendation"

Most likely the file had been copied, and it had been (unknown to PostgreSQL) persisted to disk, but for some reason the copy command returned an error, or didn't return anything at all, because of a crash or an unclean shutdown, or storage was detached unexpectedly from underneath it. So as far as PostgreSQL is concerned, the archival was not successful and so needed to be repeated. But when it tried to repeat it, it finds a file with same name already exists. You could move the existing file to new name, let the archival proceed, then compare the two files to see if they are identical and if so delete the first one. There is no way to atomically coordinate the filesystem with PostgreSQL, so these things do happen. If you used a dedicated product to do the archiving rather than rolling your own script, it might take these steps for you.

If you have two clusters archiving to the same place, you will find the files are not identical in their headers. You will have to figure out whether the other cluster is still valid, and if so where it is supposed to be archiving, fix it going forward, and move the old version of the file to the correct location.

If you are absolutely certain you can't have two instances trying to archive to the same place, then you can remove the 'test' from the archive_command. Its only useful function is guard against that eventuality. And as you see, it complicates the case when a archive operation was unsuccessful (either actually unsuccessful, or was not reported up to PostgreSQL as being successful) but did create the named file before failing.

| improve this answer | |
  • From reading this, I'm starting to think the docker container was maybe restarted at a point where the archive command was still happening, but not done. And then started again once the docker container was up and running again. Not sure if there's a safeguard against that. As for the "not a recommendation" bit, it sounds to me like that isn't so much saying "we don't recommend you do this" but rather "we don't recommend anything at all, as a policy". The 'test' command is still a good command and very fast. And it did point out a problem. – KdgDev Nov 25 '19 at 22:01
  • If it was two servers trying to archive to the same place the "test" command did point out the problem and (possibly) prevent a disaster. However, if it was the first issue of an interrupted/unconfirmed archive that was then attempted to be repeated, then "test" is only pointing out a problem that it itself had created. Silently overwriting the old file is the correct thing to do in that situation. Of course knowing for sure which of those is the case is the problem, and including the "test" command is probably the more conservative option. – jjanes Nov 26 '19 at 3:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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