-3

I just installed postgresql 16 on my Kali Linux. It's working perfectly but when I try to create a database (using the correct command line), it gives me this feedback :

postgres=# create database Danny;
ERROR:  template database "template1" has a collation version mismatch
DETAIL:  The template database was created using collation version 2.37, but the operating system provides version 2.38.
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.

I want to know how to solve this error and continue with database creation in postgre.

How can I rectify this problem?

Improve this question
7
  • 8
    Did you try anything so far? The error itself gives you suggestions and googling the error returns very many hits. Did none of those help? Please show us what you have tried so we don't waste your time with solutions you have already attempted. Also, are you sure you want to do this on Kali? Commented Jul 15, 2024 at 10:04
  • 5
    and what is the correct command line? According to whom? For which usage? Commented Jul 15, 2024 at 10:31
  • 1
    @MarcusMüller presumably the create database Danny;, which does seem like a correct command. Commented Jul 15, 2024 at 12:17
  • 1
    agreed, but there's a template database from another version, so I'm guessing there's more than that. I'd assume "the correct command line" is the invocation of the DB client, but I'm not sure! I wish Daniel would clarify that, so that's where my comment comes from! Commented Jul 15, 2024 at 12:18
  • 1
    @PhilipCouling yes, that was my understanding. So, either this is an downgrade from an existing database installation (on Kali?!) or something strange is going on, and us guessing all the steps that lead to where Daniel is will not work. Commented Jul 15, 2024 at 15:00

1 Answer 1

Reset to default
1

I suspect this has something to do with Kali's choice of using a Rolling release strategy. Version mismatches are much more likely this way that with other distributions like Ubuntu or Debian. Kali is, of course designed for pen-testing and not a general use distribution.

You've most likely triggered with a system upgrade:

apt-get upgrade

The error is telling you that the template database (which was created duing the inintial apt-get install postgresql does not now match the installed core library responsible for collation.

It's effectivly this one: https://dba.stackexchange.com/q/324649/7472. For sorting ("Collating") postgresql uses the operating system's own library. This means that if the OS sort order changes, postgressql might accidently corrupt an index. (immagine putting someone's name in the wrong place in a phone book... you'd never find it!)

So the error you are seeing is that the template database template1, which was created when you installed postgresql, expects version 2.37 but the installed core system library is now 2.38.

The answer here: https://dba.stackexchange.com/a/330184/7472 should help you. I believe, you need to use the interactive commandline tool psql to issue the following instructions to postgresql:

REINDEX DATABASE template0;

ALTER DATABASE template0 REFRESH COLLATION VERSION;

I have no way to test this for myself, so I can't garentee the above will work.

Improve this answer
4

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.