Changing postgresl database collation for JIRA and Confluence

[Jason Rider]

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

[EL Motasim Gumaa]

Hi,

This assumes you are doing the database switch for the same instance(not a new JIRA installation). As you will need to backup the attachments(data directory) as well in that case.

1. First of all, make sure you have the XML backup and backup the dbconfig.xml file found in Jira home directory (make a copy of the file somewhere else).
2. Copy the backup XML to import folder
3. Shut down Jira
4. Delete dbconfig.xml
5. Start Jira again and this time it will start in with a setup wizard as if it is a new install.
6. Choose "I'll set it up my self"
7. Set Jira to point to new DB with correct collation
8. In "Set up application properties" screen choose "import your data"

More info can be found here:
https://confluence.atlassian.com/adminjiraserver/backing-up-data-938847673.html
https://confluence.atlassian.com/jirakb/importing-data-via-setup-wizard-941589200.html

[Vincent Roujas]

Will the xml import delete my addons and other "foreigns" stuff ? i'm a bit worried about that.

[EL Motasim Gumaa]

Add-ons can be safely installed form Atlassian market place if you don't have any special self-made add-ons.

XML restore will keep most system settings, users, groups, projects and so on.

Attachments, avatars and add-ons should be restored by backing up the "Data directory" and "Home directory".

Please refer to:
https://community.atlassian.com/t5/Jira-questions/XML-Backup/qaq-p/103111
https://confluence.atlassian.com/adminjiraserver/backing-up-data-938847673.html

[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

[Jason Rider]

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?