How to fix the collation of a Postgres Confluence database
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
If your Postgres database doesn't use the recommended collation you can run this procedure to bring them into line with our documentation: Database Setup for PostgreSQL
The following method may also be used to move a Postgres Database between two servers while ensuring the collation is correct.
Solution
Before you begin
Always back up your data before making any database modifications. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.
If your instance is using non-Latin characters, please be mindful of the issue described below and follow the suggested remediation:
Does this apply to you?
You may see some of the following issues in Confluence:
Run the following query against the Confluence database. The collation will need to be updated if it returns any results other than "UTF-8":
1
SELECT datcollate FROM pg_database WHERE datname='<Confluence database name>';How to Change the Collation?
METHOD 1: Using the site XML export/import
Make sure you have enough disk space on your server before starting this section as we will have two databases with the same size once the export wizard is done.
Create an XML backup.
Create a new database as per our guidelines. Please refer to Database Setup for PostgreSQL.
Shut down the existing Confluence instance.
Install a fresh Confluence installation with the same version as the current version, and point to the new database when prompted.
Once the setup has completed, restore the site XML backup.
If the XML backup did not include attachments, copy the attachments folder from the original Confluence instance to the new instance. ℹ️ Be sure that the ID used to run Confluence has full permissions over the attachments folder.
Rebuild the content indexes and perform a checkout.
METHOD 2: Using PGDUMP
Create new database with the correct Collation and CType. Please refer to Database Setup for PostgreSQL
Shut down the Confluence instance.
Migrate the data to the new database with Pg_dump.
Update the value for the hibernate.connection.url property within the <confluence-home>/confluence.cfg.xml file to point to the new database.
Restart Confluence and perform a checkout.
Having problems?
Contact Atlassian Support and provide the following information:
Any error messages from the transfer process, if any
The most recent Confluence logs from the Home Directory (if you're having problems starting Confluence).
We'll be happy to help you troubleshoot problems fixing the collation.
Was this helpful?