Changing postgresl database collation for JIRA and Confluence

Jason Rider_bvmbct

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

teilor_soares

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