CODEWITHSUNDEEP
databasepostgresqlcollationpostgresql-12postgresql-15
buddy2891
buddy2891

Reputation: 427

template database "template1" has a collation version, but no actual collation version could be determined

I just upgraded my Postgres from 12 to 15 and I keep getting this error: template database "template1" has a collation version, but no actual collation version could be determined

Here's the template1 collation info in Postgres 15: enter image description here

Any ideas on how to fix this? Please let me know if any more information is required.

Thanks!

UPDATE:

Here's the OS information:
Distributor ID: Debian.
Description: Debian GNU/Linux 10 (buster).
Release: 10.
Codename: buster.

I have a Postgres docker container so I used this to upgrade: https://github.com/tianon/docker-postgres-upgrade/tree/master/12-to-15

I did not change machines or do an OS upgrade. Here's the template1 collation info on PG 12:

enter image description here

UPDATE 2:

So I did some digging and it turns out that the en_US.utf8 collation does not exist in the pg_collation table on Postgres 12. However on Postgres 15, it does exist in that table and has a version of 2.31:

enter image description here

Is there any way to fix this?

Upvotes: 20

Views: 20788

Answers (3)

Capripot
Capripot

Reputation: 1509

If you follow this

ALTER DATABASE template1 REFRESH COLLATION VERSION;

and you get the following error:

invalid collation version change

It is probably because you upgraded your Postgres data directory with a different architecture to which you are running it now. See post from Daniel Nachtrub.

If that's for a very large database, try to make sure to use the same architecture in old and new binaries with Docker. If on Mac installed with Homebrew, best would be to upgrade ASAP when a new version is released so both binaries are available.

Tho the question is for Debian, if you find this post and that you are on macOS, then most likely your data dir is small, and set for dev/testing for instance. If that's the case, there is another way to that: you can export all databases and then import in a clean new data dir.

For instance, on a Mac, with PostgreSQL 15 installed with brew, assuming you are in broken state, the following should work:

pg_dumpall > all_dbs.out
brew services stop postgresql@15
mv $(brew --prefix)/var/postgresql@15 $(brew --prefix)/var/[email protected]_collation
brew reinstall postgresql@15 # that should recreate a new data dir
brew services start postgresql@15
psql -f all_dbs.out postgres

Dev-containers is a good alternative to installing PostgreSQL in your system to dev. Tho I found containers to be a bit slow at times.

Upvotes: 4

Jackkobec
Jackkobec

Reputation: 6745

The simplest working solution is to update collation version of template1 data base.

Login into postgres:

sudo -u postgres psql

Look list of databases:

\l

If you see your template1 data base. Upgrade collation version:

ALTER DATABASE template1 REFRESH COLLATION VERSION;

Quite postgres cli:

\q

Reload postgres service(for Ubuntu Linux etc):

sudo service postgresql restart

Check postgres service status:

sudo service postgresql status

Maybe you will need to update collation version of postgres databse. Use the same steps with command:

ALTER DATABASE postgres REFRESH COLLATION VERSION;

Upvotes: 26

Hartmut Pfarr
Hartmut Pfarr

Reputation: 6139

  1. remove indexes

  2. alter database template1 refresh collation version;

  3. add/rebuild indexes

Upvotes: 5

Related Questions