JIRA DB migration from Oracle 12C to PostgreSQL DB 9.4+?

[Jason Liwag]

Anyone tried this migration process? Did you try to use some tools like https://dbconvert.com/oracle/postgresql/ or something else? Thank you! 

XML backup will not work for us since JIRA instance is large 400K+ issues with Tempo plugins. 

[Sam Hall]

I used SQLines Data after the XML backup failed. There were some preparation tasks and trial and error involved with that tool, but eventually it worked well and has a nice GUI to show you what it's doing and the logs will detail anything that goes wrong. Also, it can verify the data after migration.

Rather than having SQLines guess at Postgres data types, allow a fresh install of Jira to create tables within a Postgres database. Shutdown Jira and configure SQLines to truncate each table on the Transfer Options tab.

We found 6 or so missing AO tables but upon inspection they contained either few or no records, nothing of value. We determined that Jira will recreate these Active Object tables as and when required. They were possibly left over from old plugins we no longer use.

SQLines notes:

• Support/documentation is sparse, but it is quite fast and reliable once you figure it out
• Make sure your database drivers are the same architecture as SQLines (32 vs. 64bit)
• Create a script that renames all case sensitive Postgres tables to case insensitive and the corresponding script to rename them back again after migration (SQLines may fail to find case sensitive tables in the target database).
• Likewise, create scripts to drop and recreate constraints
• Column ordering seems to matter to SQLines. I found that the Jira Oracle tables had columns in a different order than that of Postgres. Though SQLines has a config file for mapping columns, explicitly configuring it to map columns to columns with the same name using the documented convention seems to have no effect (perhaps this is a command line feature only). Oracle 12c allows you to reorder columns in-place using the invisible/visible feature). I wrote a script to run on Postgres that outputs the column reordering script for every table on Oracle to ensure no column mapping issues.

Once that was done, migration of 1.4 million rows took just under an hour to transfer. Jira came up like nothing just happened.

[Sam Hall]

I also should have mentioned that we had to create a script to alter all the Postgres sequences with the existing max value plus one before starting Jira.

[Dave Norton]

I'm pleased to report that we were able to get Jason's data migrated from Oracle to Postgres. There were a few gotchas along the way! Here's the outcome of our Support case - hopefully it will help others in the future :)

Missing AO tables

Sometimes, we saw Active Objects tables weren't present at the exact time that the restore happened. This can happen because plugins haven't started yet, or because those plugins aren't actually installed. When restoring to Jira, it's a good idea to ensure that all your plugins are present before attempting the restore.

If you're migrating from one database to another (as Jason was here) then you can simply copy your $jira-home/plugins/installed-plugins directory to the new installation; and then restore from there.

A null character from an Email

Some email clients add the NUL character to the body of email messages. In Oracle (which Jason was coming from) those characters are allowed, but they're not allowed in a Postgres database.

The error was failing with something like this:

Error during ActiveObjects restore com.atlassian.activeobjects.spi.ActiveObjectsImportExportException: There was an error during import/export with plugin Email This Issue(com.metainf.jira.plugin.emailissue) #7.1.1.1 (table AO_544E33_AUDIT_LOG_ENTRY): ... Caused by: java.sql.BatchUpdateException: Batch entry 742 INSERT INTO public."AO_544E33_AUDIT_LOG_ENTRY" (<fields>) VALUES (<values>) was aborted: ERROR: invalid byte sequence for encoding "UTF8": 0x00 Call getNextException to see other errors in the batch.

We used the following SQL to identify those records in the Oracle database:

SELECT * FROM AO_544E33_AUDIT_LOG_ENTRY WHERE instr(EMAIL_BODY, unistr('\0000')) > 0;

Then, once we'd done a database backup, we ran this against the Oracle database to replace those NUL characters with nothing.

UPDATE AO_544E33_AUDIT_LOG_ENTRY SET EMAIL_BODY = replace(EMAIL_BODY, unistr('\0000')) WHERE instr(EMAIL_BODY, unistr('\0000')) > 0;

With those characters zapped, we created a new XML backup from the Oracle instance, which restored correctly to the Postgres instance.

[Vickey Palzor Lepcha]

@Dave Norton Great job guys.

[Dave Norton]

Hi Jason!

We've had some reports of customers who successfully used third party tools to convert the database - either things like the tool you linked or pgloader (for folks moving to Postgres like yourself).

The downside with this approach is that we have no way to verify if the data has gone through successfully. You can spin up a test instance of Jira with the same addons against a fresh postgres database, and compare the database structure between a fresh installation and what you converted - that would at least tell you if the structure is OK. If the structure is identical, then it's likely that the data has come across as well - but you'd want to test a lot in UAT :)

As an alternative, you can migrate the data in chunks. Use something like Project Configurator to import configuration from your Oracle based instance to your Postgres based one; and then something like Issue Sync for Jira. I'm not sure how well this would work at scale; however; you would want to test it out.

That being said, I've seen some larger instances succeed at the XML export - I'd love to know what problems you're specifically running into with your export. Maybe we can suggest some additional tuning to help you get the XML Export succeed (which will give you the best results).

Let us know how you decide to go forward here!

Cheers,
Dave

[Jason Liwag]

Thank you Dave! We tried XML backup but throwing errors. XML backup/restore on the same database works Oracle to Oracle. For Oracle to Postgres getting below error right before it completes the restore and related tempo data not showing as well (Tempo teams, timesheets).

UI ERROR after restore:

There was a problem restoring ActiveObjects data for the plugin Email This Issue(com.metainf.jira.plugin.emailissue) #7.1.1.1 plugin. Importing table AO_544E33_AUDIT_LOG_ENTRY failed. Please check the log for details.

LOGS:

2018-04-26 16:56:34,765 http-nio-8083-exec-21 ERROR ca34134 1016x7241x1 9jpaup 10.14.44.80 /secure/CreateIssueDetails.jspa [c.m.j.p.emailissue.action.EmailBuilderImpl] There was a SQL exception thrown by the Active Objects library:
Database:
- name:PostgreSQL
- version:9.6.8
- minor version:6
- major version:9
Driver:
- name:PostgreSQL Native Driver
- version:PostgreSQL 9.4.1212

org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "AO_544E33_AUDIT_LOG_ENTRY_pkey"
Detail: Key ("ID")=(1) already exists.
com.atlassian.activeobjects.internal.ActiveObjectsSqlException: There was a SQL exception thrown by the Active Objects library:
Database:
- name:PostgreSQL
- version:9.6.8
- minor version:6
- major version:9
Driver:
- name:PostgreSQL Native Driver
- version:PostgreSQL 9.4.1212
org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "AO_544E33_AUDIT_LOG_ENTRY_pkey"
Detail: Key ("ID")=(1) already exists.
at com.atlassian.activeobjects.internal.EntityManagedActiveObjects.create(EntityManagedActiveObjects.java:94)
at com.atlassian.activeobjects.osgi.TenantAwareActiveObjects.create(TenantAwareActiveObjects.java:266)
... 2 filtered
at java.lang.reflect.Method.invoke(Method.java:498)
at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:302)
at org.eclipse.gemini.blueprint.service.importer.support.internal.aop.ServiceInvoker.doInvoke(ServiceInvoker.java:56)
at org.eclipse.gemini.blueprint.service.importer.support.internal.aop.ServiceInvoker.invoke(ServiceInvoker.java:60)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)

...

...

...

Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "AO_544E33_AUDIT_LOG_ENTRY_pkey"
Detail: Key ("ID")=(1) already exists.
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2455)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2155)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:28

Regards,
Jason

[Jason Liwag]

Seems like similar to this case https://community.atlassian.com/t5/Jira-questions/Restoring-jira-from-XML-problem-restoring-ActiveObjects-data-for/qaq-p/459713 but they are migrating from MySQL to PostgreSQL

[Dave Norton]

Hi Jason,

That's a pretty weird error. It might be worth double checking the activeobjects.xml file for a duplicate entry in that table - have a search for the table name (AO_544E33_AUDIT_LOG_ENTRY) and see if there are actually two rows with the same ID there.

Out of curiosity, are you restoring during the setup wizard, or from a dummy installation (that has all your plugins setup)? Can you see if those tables actually have data in them (or if they even exist before you import)? That'll tell us if the problem is "what's already in the database" or "what's attempting to be imported" :)

Let me know what you find!

Cheers,
Dave

[Jason Liwag]

Hi Dave,

I tested this with new JIRA install with all plugins needed and also performed this on the existing instance which I just pointed it to postgres (new empty) db, configure the back end and restore it but didn't work. If I pointed it back to oracle using the same XML backup file and restore it, it will work just fine. If there are two rows with the same ID then it should not work when restoring it to Oracle DB. I can check that activeobjects.xml too. 

Regards,

Jason 

[Dave Norton]

Hi Jason! I've gone ahead and raised a support case for you - you should get an email shortly.

I'd like to take a deeper look into your logs and potentially your back up as well - so the support case will let us do that. Then, when we've found the root cause and fixed it, we'll update this ticket for all to see the resolution that worked for us :)

Cheers,
Dave

[Sachin]

Hi Dave,

I have created Premier Support ticket PS-61176 for the same. and we are facing lots of issue in this. Can you pls help us on this?

[Henry Zhao]

great article, really helps and I am looking for the solution.