Switching from MariaDB to Postgres

[Ziad Qadora]

Hello,

We are upgrading from Jira DC 8.5.1(using MariaDB) to 8.13.7.  (using Postgres 11) and when I tried to import the XML backup taken before the upgrade, I get the following error 

ERROR: invalid byte sequence for encoding "UTF8": 0x00)) java.lang.Exception: com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericEntityException: 

Attached is the screenshot of the full error

Postgres was setup per the instruction in the link below 

Connecting Jira applications to PostgreSQL | Administering Jira applications Data Center and Server 8.5 | Atlassian Documentation

What could be causing the issue, is it that MariaDB was setup with UTF8 and Postgres was setup with UNICODE? How can we resolve the issue at hand?

[Nic Brough -Adaptavist-]

I would want to simplify this process.  At the moment you're making two major structural changes at the same time, and it's impossible to know which one of them the actual problem is with.

I'm curious to know why you put a known-broken (and unsupported) system in place, as that's one of the possible problems, but that's not really relevant.

Your two processes are "change database" and "upgrade".   The complexities of those two don't add together, they multiply any problem, and make it pretty much impossible for us to tell you where the problem really is without asking you to do multiple runs with large amounts of expensive logging.

What you should do is split this into two parts.

First, take your 8.5.1 installation and move it from a broken database to a supported one.  The recommended way to do this would be to take the backup of the current system, reconnect it to an empty Postgres 9.6 database, and restore the backup into there.

If that errors, we can be 99% sure that the problem is in the export from your unsupported database, and we can take it to Atlassian (technically, they don't have to help, you're unsupported, but they will try)

After that, upgrade your 8.5 to 8.13, and then postgres to 9.11 (Jira 8.13 supports 9.6 through to 11)

I suspect if you take this route, you may find no problems.

[Ziad Qadora]

Hi,

I started doing that way, and the health check did find Postgres 11 as un-supported for 8.5.1. 

I did find this documentation of the issue, so this seems to be a known issue when switching from MySQL to Postgres

Removing invalid characters from XML backups | Atlassian Support | Atlassian Documentation

[Nic Brough -Adaptavist-]

I don't think you started to do it quite the way I described.  I said you need to move to a supported database (Postgres 9.6) first, not try to take a Jira 8.5 from an unsupported database (maria) to another unsupported database (postgres 11).  MySQL to Postgres has similar problems, but you're not using MySQL, you said you're on Maria.

You are looking too far into the later releases and trying to do too much in one go.  You need to start by moving from an unsupported database (maria) on Jira 8.5.1 to a supported database (postgres 9.6) on Jira 8.5.1

Once you've achieved that, the rest of your upgrades (Jira 8.5.1 -> Jira 8.13.latest, then postgres 9.6 -> 11) will be very smooth.

[Ziad Qadora]

Hi Nic,

Thank you very much. 

Will get 9.6 installed and follow the recommended advice.

Regards,

Ziad