postgres fails to start when template database with collation version mismatch is needed at startup #318777
Description
Describe the bug
postgresql.service fails to start when databases are created during startup while template1's has a collation version mismatch. Observed with postgresql 15.7.
Steps To Reproduce
Steps to reproduce the behavior:
- Install postgresql on an older nixos version, e.g. 23.11
- Update to 24.05
- Install a service that will cause a postgres database to be created during postgres startup, e.g. by
services.roundcube.enable = true.
Expected behavior
Postgresql runs and roundcube database gets created
Additional context
Jun 10 11:41:31 rack systemd[1]: Starting PostgreSQL Server...
Jun 10 11:41:31 rack postgres[80547]: [80547] LOG: starting PostgreSQL 15.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 13.2.0, 64-bit
Jun 10 11:41:31 rack postgres[80547]: [80547] LOG: listening on IPv6 address "::1", port 5432
Jun 10 11:41:31 rack postgres[80547]: [80547] LOG: listening on IPv4 address "127.0.0.1", port 5432
Jun 10 11:41:31 rack postgres[80547]: [80547] LOG: listening on Unix socket "/run/postgresql/.s.PGSQL.5432"
Jun 10 11:41:31 rack postgres[80550]: [80550] LOG: database system was shut down at 2024-06-10 11:27:18 GMT
Jun 10 11:41:31 rack postgres[80547]: [80547] LOG: database system is ready to accept connections
Jun 10 11:41:31 rack postgres[80564]: [80564] ERROR: template database "template1" has a collation version mismatch
Jun 10 11:41:31 rack postgres[80564]: [80564] DETAIL: The template database was created using collation version 2.38, but the operating system provides version 2.39.
Jun 10 11:41:31 rack postgres[80564]: [80564] 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.
Jun 10 11:41:31 rack postgres[80564]: [80564] STATEMENT: CREATE DATABASE "roundcube"
Jun 10 11:41:31 rack postgresql-post-start[80563]: ERROR: template database "template1" has a collation version mismatch
Jun 10 11:41:31 rack postgresql-post-start[80563]: DETAIL: The template database was created using collation version 2.38, but the operating system provides version 2.39.
Jun 10 11:41:31 rack postgresql-post-start[80563]: 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.
Jun 10 11:41:31 rack systemd[1]: postgresql.service: Control process exited, code=exited, status=1/FAILURE
Workaround:
- Disable service that causes database creation
nixos-rebuild switch, allowing postgres database to start- Run
ALTER DATABASE template1 REFRESH COLLATION VERSIONviapsql - Reenable service
Notify maintainers
@thoughtpolice @danbst @globin @ivan @Ma27 @wolfgangwalther
Metadata
- system: `"x86_64-linux"`
- host os: `Linux 6.6.32, NixOS, 24.05 (Uakari), 24.05.1135.9b5328b7f761`
- multi-user?: `yes`
- sandbox: `yes`
- version: `nix-env (Nix) 2.18.2`
- channels(root): `"nixos-24.05"`
- nixpkgs: `/nix/var/nix/profiles/per-user/root/channels/nixos`