Migrating Large Confluence DC Instance From MSSQL to PostgreSQL

[AJITH PG]

Hello All,

I'm planning to migrate Confluence DC version 9.2.15 from from MSSQL to PostgreSQL. I have a pretty large instance with big DB and lots of attachments. So, Site export including attachments is not an option for me. DB conversion is also not an easy task considering the size of the instance. Space level migration is not an option as the Page IDs will change and that impacts our user base. 

So, my plan is to do a Site XML export by excluding attachments and restore the same into the new instance and use the old attachment folder to point the new instance to the attachments. There won't be any change in the attachment folder hierarchy and XML exports will be having the same I guess. Anyone ever tried a similar thing? Would like to get some recommendations. 

[Tomislav Tobijas]

@AJITH PG I think one of our clients recently did this (or potentially something similar), and that they followed this article Alexander shared (for migrating databases).
Although, if I remember correctly, they did point out that it is/would be easier to migrate attachments as well. Basically, copy everything to the test server and test migration there. 🧪

We haven't handled this part, so there are no learnings from our side, but you could also try using the Confluence Setup Wizard to migrate databases. You would typically connect your existing Confluence instance to the new PostgreSQL database and let Confluence handle the schema creation and data transfer, which is often more reliable than a manual XML site restore for very large datasets. But again... I haven't tested this myself.

Best bet would also be to reach out to Atlassian Support (if you have a valid DC license) and ask DC support team what they would recommend in this scenario 👀

[AJITH PG]

Hello Tomislav, 

Thanks for the suggestions. I did reach out to Atlassian about the same but didn't get much help as their recommendation is to use third party tools. However, when I asked about the attachment copy method they informed me to test it out. I will keep you guys updated. 

-Ajith

[Alexander Nilsson]

Hello Ajith,

your plan is solid and aligns almost exactly with what Atlassian documents as "Method Two" for instances with a high volume of attachments. This is a well-established path, not something you'd be improvising.

The documented method is: take the site XML export with attachments excluded, make a separate copy of the <confluence-home>/attachments directory, stand up a fresh Confluence on the new PostgreSQL database (same version, empty site), restore the XML, then copy the attachments directory contents from the old home into the new home. The folder hierarchy and the references in the XML line up, exactly as you expected, so the attachments reattach without any remapping. Both methods are on this page:

https://confluence.atlassian.com/conf92/migrating-to-another-database-1477577060.html

Your reasoning about page IDs is also right. A full site export and restore brings the whole site into an empty instance as-is, so it does not regenerate IDs the way a space import does. That is the correct call for protecting existing links and bookmarks.

The one thing I would push back on is the word "large". Method two only solves the attachment size problem. The XML export, and especially the restore of the database content itself (pages, versions, comments), is the part Atlassian explicitly flags as not suited for large data sets. That is where these migrations tend to run for many hours or fail on restore, not on the attachments. And since MSSQL to PostgreSQL is a cross-engine move, a native dump and restore was never on the table anyway, so you are realistically choosing between the XML route and a third party cross-engine migration tool. For a genuinely big DB, run the full export and import on a clone first and time the restore. That tells you whether XML is viable before you go near production.

Also a few thing which you have take care of:

• User-installed apps are not included in the XML backup, only their data is. You have to re-install every non-bundled app on the new instance at the same version, or the app data sits there orphaned.
• Use case sensitive collation on the new PostgreSQL database. Case insensitive collation can cause data integrity problems after an XML import.

If the restore keeps failing on the content size, that is the point to open a support ticket via Atlassian: https://support.atlassian.com/contact/#/

I am looking forward to your feedback.

Greetings,

Alex

[AJITH PG]

Hello Alexander, 

Thanks for the input and recommendations. I did try a test migration using a smaller set of spaces with site export/import and it worked as expected. Now I need to do the same test with the actual data which is kind of huge. I will update here of my learnings. 

Also, I did come across the article you have shared but it mentioned "Large data sets will require third party database migration tools" and that's the reason we initially didn't pursue this option. Now, we don't really have a choice as we need to keep the CONTENTIDs(Page and Attachment IDs) same after the migration also. So, let me try this option and see how it goes. 

-Ajith

[AJITH PG]

Hello Alexander, 

Thanks for the input and recommendations. I did try a test migration using a smaller set of spaces with site export/import and it worked as expected. Now I need to do the same test with the actual data which is kind of huge. I will update here of my learnings. 

Also, I did come across the article you have shared but it mentioned "Large data sets will require third party database migration tools" and that's the reason we initially didn't pursue this option. Now, we don't really have a choice as we need to keep the CONTENTIDs(Page and Attachment IDs) same after the migration also. So, let me try this option and see how it goes. 

-Ajith