How to fix the collation of a Postgres JIRA database

Still need help?

The Atlassian Community is here for you.

Ask the community

Purpose

If your Postgres database doesn't use the recommended collation you can run this procedure to bring them into line with the

This 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. Create a new database as per our guidelines. Please refer to Setting up a PostgreSQL Database for JIRA if you are using Linux.

  3. Adjust your JIRA database connection to point to the new database.
  4. Restore your data to the new database using Restoring Data.
  5. Start JIRA and ensure that everything is working correctly
  6. You may want to run Database Integrity Checker to verify you 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.

 

 

Last modified on Feb 26, 2016

Was this helpful?

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