Collation version mismatch when trying to restore Postgres snapshot

Questions / Help
1

Hi there,

I’m trying to restore a postgres snapshot but I’m getting collations mismatch issues:

2024-05-31T23:11:31.072 app[e2867512c76618] sjc [info] postgres | 2024-05-31 23:11:31.072 UTC [594] ERROR: template database "template1" has a collation version mismatch
2024-05-31T23:11:31.072 app[e2867512c76618] sjc [info] postgres | 2024-05-31 23:11:31.072 UTC [594] DETAIL: The template database was created using collation version 2.31, but the operating system provides version 2.36.
2024-05-31T23:11:31.072 app[e2867512c76618] sjc [info] postgres | 2024-05-31 23:11:31.072 UTC [594] HINT: Rebuild all objects in the template database that use the default collation and run ALTER DATABASE template1 REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.
2024-05-31T23:11:31.072 app[e2867512c76618] sjc [info] postgres | 2024-05-31 23:11:31.072 UTC [594] STATEMENT: CREATE DATABASE repmgr OWNER repmgr;
2024-05-31T23:11:31.073 app[e2867512c76618] sjc [info] failed post-init: failed to enable repmgr: failed to create repmgr database: ERROR: template database "template1" has a collation version mismatch (SQLSTATE XX000). Retrying...

I’ve tried different images with no success.

The steps described at postgres-flex: database "postgres" has a collation version mismatch do not help since I cannot connect to the database.

How can I properly use a postgres snapshot?

2

For anyone having this issue, this is how I was able to connect to the machine and fix it:

  • Since fly postgres connect will not work (it will throw “no active leader found” error), use fly ssh console to ssh into a machine
  • Once into the machine, switch to postgres user: su postgres
  • Connect to postgres: psql -p 5433
  • Connect to the problematic database (named template1 in my case): \c template1
  • Run the SQL statement described in the linked post: REINDEX DATABASE template1; ALTER DATABASE template1 REFRESH COLLATION VERSION;

These steps fixed the issue for me.

2 Likes
3

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.