I recently had a unique index failure on PostgreSQL. To be clear it wasn't Postgres' fault - it was mine.

Thankfully it reared it's head on Staging not Live, but here's something other PostgreSQL users might find interesting.

In between writing one set of data, and the next which relies on ON CONFLICT to update/insert rows, thus fully relying on the unique index holding the integrity, I switched docker image, to get some tooling built in.

The original was alpine/musl based. The subsequent was Debian/Glib based.

As far as I can tell the byte collation is different and the index operations just didn't find one set of bytes where it ought to be.

If I had dumped/restored - it wouldn't have been a problem, but I just flipped the image on the existing storage volume. oops.

On the surface, the index was active, and in an enforcing state.

a REINDEX failed however, there were dupes!

For me, I fixed the dupes, REINDEXed, then dump-restored.

But worth bearing in mind given how many backup mechanisms rely on storage volume snapshots, and a global reindex after restore will catch these types of issues if that's a potential for anyone.

I hereby invite the forthcoming tsunami of criticism, that I will ignore, purely so i can post this for others to find interest, and who might have done the same thing as me.