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?
Best Answer
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.