After taking a backup of a SOAR instance running on one operating system and restoring it to a SOAR instance running on a different operating system -- for example, from CentOS 7 to RHEL 8 as documented here -- PostgreSQL and its related utilities, such as the psql command, may display a warning that there is a collation version mismatch.
For example:
$ psql
WARNING: database "phantom" has a collation version mismatch
DETAIL: The database was created using collation version 2.17, but the operating system provides version 2.28.
HINT: Rebuild all objects in this database that use the default collation and run ALTER DATABASE phantom REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.
psql (15.6)
Type "help" for help.
phantom=#
Per the PostgreSQL documentation:
A change in collation definitions can lead to corrupt indexes and other problems because the database system relies on stored objects having a certain sort order. Generally, this should be avoided, but it can happen in legitimate circumstances, such as when upgrading the operating system to a new major version [ . . . ]
After the restore has completed you can determine if your SOAR database is experiencing this issue by invoking the psql command and observing whether the warning is displayed, or by reviewing the PostgreSQL logs in <PHANTOM_HOME>/data/db/pg_logs to see if the warning is logged during database startup or shutdown.
The PostgreSQL logs in <PHANTOM_HOME>/data/db/pg_logs may show a warning similar to this:
WARNING: database "phantom" has a collation version mismatch
DETAIL: The database was created using collation version 2.17, but the operating system provides version 2.28.
HINT: Rebuild all objects in this database that use the default collation and run ALTER DATABASE phantom REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.
Or invoking the psql command may result in a similar warning:
$ psql
WARNING: database "phantom" has a collation version mismatch
DETAIL: The database was created using collation version 2.17, but the operating system provides version 2.28.
HINT: Rebuild all objects in this database that use the default collation and run ALTER DATABASE phantom REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.
psql (15.6)
Type "help" for help.phantom=#
If the warning is present then the collation version should be refreshed.
First, run this SQL query to determine if there are any objects that need to be rebuilt. There will generally be none.
SELECT pg_describe_object(refclassid, refobjid, refobjsubid) AS "Collation",
pg_describe_object(classid, objid, objsubid) AS "Object"
FROM pg_depend d JOIN pg_collation c
ON refclassid = 'pg_collation'::regclass AND refobjid = c.oid
WHERE c.collversion <> pg_collation_actual_version(c.oid)
ORDER BY 1, 2;
Then run this ALTER DATABASE SQL statement to refresh the collation version:
$ psql
WARNING: database "phantom" has a collation version mismatch
DETAIL: The database was created using collation version 2.17, but the operating system provides version 2.28.
HINT: Rebuild all objects in this database that use the default collation and run ALTER DATABASE phantom REFRESH COLLATION VERSION, or build PostgreSQL with the right library version.
psql (15.6)
Type "help" for help.phantom=# ALTER DATABASE phantom REFRESH COLLATION VERSION;
NOTICE: changing version from 2.17 to 2.28
ALTER DATABASE
phantom=#
Then restart Postgres, and review the current PostgreSQL log in <PHANTOM_HOME>/data/db/pg_logs to ensure the warning is no longer present there.
$ phsvc restart postgresql
Stopping postgresql-15: [ OK ]
Starting postgresql-15: [ OK ]
Then run the psql command to ensure that the warning is no longer displayed.
$ psql
psql (15.6)
Type "help" for help.phantom=#
| Revision | Publish Date | Comments |
|---|---|---|
1.0 |
29-Sep-2026
|
Initial Release |