It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

Changing postgresl database collation for JIRA and Confluence

Jason Rider Dec 12, 2015

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

2 answers

0 votes
Teilor Soares Dec 13, 2015

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 Dec 13, 2015

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?

0 votes
EL Motasim Gumaa Jan 15, 2019 • edited Jan 17, 2019

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

VR I'm New Here Jan 17, 2019

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

EL Motasim Gumaa Jan 18, 2019

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

Suggest an answer

Log in or Sign up to answer
This widget could not be displayed.
This widget could not be displayed.
Community showcase
Posted in Jira Core

How to manage many similar workflows?

I have multiple projects that use variations of the same base workflow. The variations depend on the requirements of the project or issue type. The variations mostly come in the form of new statuses ...

682 views 7 0
Join discussion

Community Events

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

Find an event

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

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you