Changing postgresl database collation for JIRA and Confluence

Apparently, the Atlassian guide I used to install JIRA was not 100% correct, and in the process, the database was created with the incorrect Collation.

https://confluence.atlassian.com/display/ATLAS/Dragons+Stage+1+-+Install+JIRA#DragonsStage1-InstallJIRA-Step2.InstallyourPostgreSQLDatabaseServer

Following those steps lead to the database with en_US.UTF-8 collation, which is not supported by JIRA (according to JIRA Admin > System > Support Tools > Instance Health).  Confluence seems to be fine and Confluence Support Tools do not give an error.

Also, in my atlassian-jira.log:

****************************************************************************************************
You are using an unsupported postgres72 collation: en_US.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.
****************************************************************************************************

 

But JIRA with PostgreSQL requires POSIX.UTF-8, C.UTF-8, POSIX or C according to 

https://confluence.atlassian.com/display/JIRAKB/How+to+fix+the+collation+of+a+Postgres+JIRA+database

or Unicode according to 

https://confluence.atlassian.com/display/JIRAKB/What+Database+Collation+X+Encoding+Should+be+Used+in+JIRA

So I need to recreate the database with the correct collation.

What are the proper steps to properly change the collation?

I found the steps to do so with PostgreSQL here:

https://confluence.atlassian.com/display/JIRAKB/How+to+fix+the+collation+of+a+Postgres+JIRA+database

But the steps it says to take are a little unclear. 

  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.

I would have to shut down JIRA between steps 2 and 3 in order to point JIRA to the new database.  But then obviously, I cannot do Step 4 and on, because Restoring XML Data Backup requires JIRA to start.  But starting JIRA after step 3 pointing to the new database will give an error, as the new database will not have its database setup to work with JIRA.  

So I am thinking the best way to change collation is to do the following:

  1. Make XML Backup
  2. Stop JIRA
  3. Make Backup of JIRA DB (jiradb).
  4. Create new JIRA DB with correct collation (correctjiradb)
  5. Restore jiradb data into new DB (correctjiradb)
  6. Point JIRA install to point to new database (dbconfig.xml)
  7. Start JIRA
  8. Run Database Integrity Checker
  9. If any errors, Restore XML Backup

Would this be the best way to change the database collation for JIRA?  Am I missing anything?

I also assume the same steps would be taken for Confluence?  (Confluence does not give an error, but should be done also?)

I also should think that the JIRA/Confluence setup/installation process should catch this error during the setup process?

Thank you

1 answer

Hello there!

The instructions are correct. In the step 3 you can stop JIRA, use the JIRA configuration tool to point JIRA to the new database, start JIRA, JIRA will show you the setup Wizard screen, where you have the option to set a new instance or import data from a XML backup from an old one, click in the link to import the data follow the instructions.

Don't forget to backup everything before starting, including the JIRA Home and installation directory, just in case

Cheers!
Teilor

If I am correct, the JIRA configuration tool just modifies dbconfig.xml ? I could not get the config tool to run on Linux (I remote in to the machine). So I could just modify dbconfig.xml accordingly, then run JIRA setup to import the XML backup?

Suggest an answer

Log in or Join to answer
Community showcase
Teodora [Botron]
Published Thursday in Marketplace Apps

Jira Inferno: The Nine Circles of Jira Administration Hell

If you spend enough time as a Jira admin - whether you are managing a single, mid-sized instance, a large enterprise one or juggling multiple instances at once - you will eventually find yourself in ...

823 views 5 17
Read article

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you
Atlassian Team Tour

Join us on the Team Tour

We're bringing product updates and pro tips on teamwork to ten cities around the world.

Save your spot