search

fly-apps / postgres-flex

Postgres HA setup using repmgr
67 stars 32 forks source link

"database "repmgr" has a collation version mismatch" #208

Open bcomnes opened 10 months ago

bcomnes commented 10 months ago

After updating from v0.0.42 to v0.0.43 with fly image update, my database started spamming the following warning:

repmgrd | WARNING: database "repmgr" has a collation version mismatch
2023-08-21T22:17:51.251 app[32874599c655d8] lax [info] repmgrd | DETAIL: The database was created using collation version 2.31, but the operating system provides version 2.36.
2023-08-21T22:17:51.251 app[32874599c655d8] lax [info] repmgrd | HINT: Rebuild all objects in this database that use the default collation and run ALTER DATABASE repmgr REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.
2023-08-21T22:17:52.567 app[32874599c655d8] lax [info] postgres | 2023-08-21 22:17:52.567 UTC [17285] WARNING: database "postgres" has a collation version mismatch
2023-08-21T22:17:52.567 app[32874599c655d8] lax [info] postgres | 2023-08-21 22:17:52.567 UTC [17285] DETAIL: The database was created using collation version 2.31, but the operating system provides version 2.36.
2023-08-21T22:17:52.567 app[32874599c655d8] lax [info] postgres | 2023-08-21 22:17:52.567 UTC [17285] HINT: Rebuild all objects in this database that use the default collation and run ALTER DATABASE postgres REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.
2023-08-21T22:17:52.570 app[32874599c655d8] lax [info] postgres | 2023-08-21 22:17:52.570 UTC [17287] WARNING: database "repmgr" has a collation version mismatch
2023-08-21T22:17:52.570 app[32874599c655d8] lax [info] postgres | 2023-08-21 22:17:52.570 UTC [17287] DETAIL: The database was created using collation version 2.31, but the operating system provides version 2.36.
2023-08-21T22:17:52.570 app[32874599c655d8] lax [info] postgres | 2023-08-21 22:17:52.570 UTC [17287] HINT: Rebuild all objects in this database that use the default collation and run ALTER DATABASE repmgr REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.
2023-08-21T22:17:52.810 app[32874599c655d8] lax [info] postgres | 2023-08-21 22:17:52.810 UTC [17289] WARNING: database "repmgr" has a collation version mismatch
2023-08-21T22:17:52.810 app[32874599c655d8] lax [info] postgres | 2023-08-21 22:17:52.810 UTC [17289] DETAIL: The database was created using collation version 2.31, but the operating system provides version 2.36.
2023-08-21T22:17:52.810 app[32874599c655d8] lax [info] postgres | 2023-08-21 22:17:52.810 UTC [17289] HINT: Rebuild all objects in this database that use the default collation and run ALTER DATABASE repmgr REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.
2023-08-21T22:17:52.816 app[32874599c655d8] lax [info] postgres | 2023-08-21 22:17:52.815 UTC [17291] WARNING: database "repmgr" has a collation version mismatch
2023-08-21T22:17:52.816 app[32874599c655d8] lax [info] postgres | 2023-08-21 22:17:52.815 UTC [17291] DETAIL: The database was created using collation version 2.31, but the operating system provides version 2.36.
2023-08-21T22:17:52.816 app[32874599c655d8] lax [info] postgres | 2023-08-21 22:17:52.815 UTC [17291] HINT: Rebuild all objects in this database that use the default collation and run ALTER DATABASE repmgr REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.
2023-08-21T22:17:53.254 app[32874599c655d8] lax [info] postgres | 2023-08-21 22:17:53.253 UTC [17292] WARNING: database "repmgr" has a collation version mismatch
2023-08-21T22:17:53.254 app[32874599c655d8] lax [info] postgres | 2023-08-21 22:17:53.253 UTC [17292] DETAIL: The database was created using collation version 2.31, but the operating system provides version 2.36.
2023-08-21T22:17:53.254 app[32874599c655d8] lax [info] postgres | 2023-08-21 22:17:53.253 UTC [17292] HINT: Rebuild all objects in this database that use the default collation and run ALTER DATABASE repmgr REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.

Any suggestions on how to address this?

bcomnes commented 10 months ago

I followed the suggestions here: https://dba.stackexchange.com/questions/324649/collation-version-mismatch

davissp14 commented 2 weeks ago

This is pretty unfortunate and has to do with the transition to bookworm: https://github.com/fly-apps/postgres-flex/pull/205

We should be able to detect and address collation issues on boot then work to reindex things concurrently. Given how we roll things out though, the primary would be the last node to be upgraded so we wouldn't be able to avoid these errors completely. However, they would be cleared by the end of the upgrade process. Not deal, but it's better than leaving the collation issues for the user to address.