Unsupported collation error thrown in Jira server using PostgreSQL

Still need help?

The Atlassian Community is here for you.

Ask the community

Platform notice: Server and Data Center only. This article only applies to Atlassian products on the Server and Data Center platforms.

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

Purpose

If your Postgres database doesn't use the recommended collation you can run this procedure to bring them into line with our documentation: Connecting Jira applications to 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 performing any modifications to the database. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.

Does this apply to you?

The following line is being thrown in atlassian-jira.log:

****************************************************************************************************
You are using an unsupported postgres72 collation: ja_JP.UTF-8. This may cause some functionality to not work.
Please use POSIX.UTF-8 or C.UTF-8 or C or POSIX as the collation instead.
****************************************************************************************************

Run the following query against your old database. You will need to adjust the collation if it returns any results other than "POSIX.UTF-8", "C.UTF-8", "C", or "POSIX".

SELECT datcollate FROM pg_database WHERE datname='<Jira database name>';

How to Change the Collation?

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.

  1. Create an XML backup
  2. Stop Jira
  3. Create a new database as per our guidelines and adjust the $JIRAHOME/dbconfig.xml file to change Jira database connection to point to the newly created database (You can also use the Jira Config tool to configure the dbconfig.xml file)

    1. Please refer to Setting up a PostgreSQL Database for Jira. for more information on how to create the new database and how to configure the dbconfig.xml file.
  4. Copy or move the backup .zip file created in step 1 into the $JIRAHOME/import/ path
  5. Start Jira again (when Jira starts connected to an empty database, the setup wizard will start)
  6. In the setup wizard, choose the option for "Import existing data" (if you reach a screen to create an admin account in the setup wizard, you have gone too far, go back)
  7. Select the filename.zip located in the $JIRAHOME/import/ which you copied in step 5
  8. Complete import and ensure that everything is working correctly
  9. You may want to run Using the database integrity checker to verify your data

Having problems?

Contact Atlassian Support and provide the following information:

  1. Any error messages from the transfer process, if any
  2. The most recent Jira logs from the Home Directory (if you're having problems starting Jira).

We'll be happy to help you troubleshoot problems fixing the collation.


Description

If your Postgres database doesn't use the recommended collation you can run this procedure to bring them into line with our documentation: Connecting JIRA to PostgreSQL

The following method may also be used to move a Postgres Database between two servers while ensuring the collation is correct.

ProductJira
PlatformServer
Last modified on Jun 27, 2022

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.