View Issue Details

IDProjectCategoryView StatusLast Update
0008428Kali LinuxKali Package Bugpublic2024-06-14 15:14
Reportermartinsmikelsteins Assigned To 
PriorityhighSeveritymajorReproducibilityunable to reproduce
Status closedResolutionopen 
Product Version2023.2 
Summary0008428: GVM - database "postgres" has a collation version mismatch
Description

Hi,

I have updated the Kali today and received the message:
ARNING: database "postgres" has a collation version mismatch
DETAIL: The database was created using collation version 2.36, but the operating system provides version 2.37.
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.

Steps To Reproduce

I tried to run
ALTER DATABASE postgres REFRESH COLLATION VERSION
It doesn't work.

I have tried to run:
sudo runuser -u _gvm -- gvmd --migrate
Gives same error:
WARNING: database "gvmd" has a collation version mismatch
DETAIL: The database was created using collation version 2.36, but the operating system provides version 2.37.
HINT: Rebuild all objects in this database that use the default collation and run ALTER DATABASE gvmd REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.

Additional Information

Is there anyone that can help me?

Activities

martinsmikelsteins

martinsmikelsteins

2023-08-17 13:18

reporter   ~0018344

I found a solution:

Step-by-step Guide to Resolve PostgreSQL Collation Mismatch

  1. Backup the "gvmd" database
    Ensure you're in a directory with write permissions for the postgres user, like /tmp:
    cd /tmp

Attempt to backup the "gvmd" database:
pg_dump gvmd > gvmd_backup.sql

If you encounter a collation version mismatch warning, you can proceed. This warning is expected given your situation.

  1. Refresh the collation for template1 database
    As many operations depend on template1, ensure it has the right collation version:
    psql -c "UPDATE pg_database SET datallowconn = TRUE WHERE datname = 'template1';"
    psql -d template1 -c "ALTER DATABASE template1 REFRESH COLLATION VERSION;"

  2. Refresh the collation for postgres database
    psql -d postgres -c "ALTER DATABASE postgres REFRESH COLLATION VERSION;"

  3. Create a new database
    Attempt to create a new database named "gvmd_new":
    createdb gvmd_new
    If you encounter a collation version mismatch error related to template1, ensure you've followed Step 2 correctly.

  4. Refresh the collation for gvmd_new database
    psql -d gvmd_new -c "ALTER DATABASE gvmd_new REFRESH COLLATION VERSION;"

  5. Dump and restore the "gvmd" database into "gvmd_new"
    pg_dump gvmd | psql gvmd_new

  6. Terminate active connections to "gvmd"
    Before dropping the original "gvmd" database, ensure no active connections are using it:
    psql -d postgres -c "SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = 'gvmd' AND pid <> pg_backend_pid();"

  7. Drop the "gvmd" database
    dropdb gvmd
    If you encounter an error indicating active connections, ensure you've followed Step 7 correctly.

  8. Rename "gvmd_new" to "gvmd"
    createdb gvmd --template=gvmd_new

  9. Cleanup (Optional)
    If everything is working as expected with the new "gvmd" database:
    dropdb gvmd_new

g0tmi1k

g0tmi1k

2024-06-14 15:14

administrator   ~0019444

This report has been filed against an old version of Kali. We will be closing this ticket due to inactivity.

Please could you see if you are able to replicate this issue with the latest version of Kali Linux (https://www.kali.org/get-kali/)?

If you are still facing the same problem, feel free to re-open the ticket. If you choose to do this, could you provide more information to the issue you are facing, and also give information about your setup?
For more information, please read: https://www.kali.org/docs/community/submitting-issues-kali-bug-tracker/

Issue History

Date Modified Username Field Change
2023-08-17 10:45 martinsmikelsteins New Issue
2023-08-17 13:18 martinsmikelsteins Note Added: 0018344
2024-06-14 15:14 g0tmi1k Note Added: 0019444
2024-06-14 15:14 g0tmi1k Status new => closed