ARTIFACTORY: How to Rebuild Index and Clear the access_db_check Table After Restoring PostgreSQL DB in Artifactory

Introduction:
Following the restoration of a PostgreSQL database from a dumped SQL file, you might encounter an issue where Access fails to start. An error in the access-service.log may indicate a collation change, and the following message could be observed: "Your collation seems to have changed. To remove this warning: rebuild your index, then empty the table access_db_check."

This situation commonly arises during processes like database migration or when separating a database to different virtual machines (VMs).

To resolve this issue, follow the steps outlined below:

1. Verify Collation Consistency:
Ensure that the collation is consistent between the Source DB and the Target DB.
You can do this by running \list on both databases.

2. Check access_db_check Table:

Run the following query to check the contents of the access_db_check table:sql

select sorting_order, username from access_db_check order by username;

Confirm that the result matches the expected values:

  sorting_order | username 
     ---------------+----------
       1 | 0102 
       2 | "0102"

3. Re-index Artifactory DB:
If the result is not as expected, re-index the Artifactory DB using the following command:
PGPASSWORD=password reindexdb -U artifactory -d artifactory -h localhost -p 5432 -v

4. Empty the access_db_check Table:
Once the index is rebuilt, truncate the access_db_check table:sql

TRUNCATE TABLE access_db_check;

5. Restart Artifactory:
Restart the Artifactory service to apply the changes.

By following these steps, you can address issues related to collation changes after restoring a PostgreSQL database in Artifactory. Ensure to verify the collation, rebuild the index, and clear the access_db_check table for a smooth recovery process.