How to check Database Integrity.

[yvonnearnoldus]

Hi,

We are using Confluence 6.3.2 with Postgresql 9.6.

At the moment I'm using our installation to another location. When I import the zipped backup, I get an error about a duplicate key. Which I have seen for the first time today, other imports with the zipped backups worked fine.

So it seems our database got corrupted at some point in time. I would like to check the Database Integrity but I can't find a admin option for that or a plugin to do so.

How can I check the Integrity of my Confluence DB in Postgresql?

[R. Fuerst]

Hi Yvonne,

Thanks for providing these details! XML backups can fail due to duplicate records which can occur from time to time. One such example is shown in the knowledge base article below:

• https://confluence.atlassian.com/confkb/confluence-site-xml-import-fails-with-duplicate-key-error-438993638.html

Very likely if you had integrity issues with the database, you’d see a lot more broken functionality on the production site. Without knowing the exact error, it’s difficult to say what exactly occurred.  If you’d like, feel free to paste that error in this thread!

Moving forward, you mentioned that you were moving the Confluence instance. Could you describe what exactly you’re moving (filesystem and/or database)? Is this for testing purposes? Unless you’re migrating your database to another DBMS, you shouldn’t need to use the XML backup. Rather, you can follow the guide below to move Confluence:

• https://confluence.atlassian.com/doc/migrating-confluence-between-servers-184150.html

XML backups can be resource intensive and have more room for failure, which is why we suggest manually migrating file system and database.

If you’d like to check your database, there’s a couple resources you could refer to:

• Confirm that you’ve set up your database per Database Setup for PostgreSQL
• Make sure all the tables exist per Confluence Data Model 

That said, based on what you’ve described, it sounds like the issue rests with the XML backup as opposed to the database itself.

[yvonnearnoldus]

Hi Rachel,

Thank you for the reply. It seems there is not a tool to check do this in Confluence so we will investigate this further with a little try and error.

We are moving or installation from our location to cloud hosting a confluence docker image with a cloud db. The process we are using is do a new install of confluence (because it is an image) and import a backup of the current production db. I've tested this multiple times with different exports which worked fine but the latest backup failed due to duplicate keys.

I wanted to check on the current production environment which key that was so we could figure out what happend and fix this. And if there are more problems with the database consistency.

Because now if something happens we need to trust in restoring a Postgresql backup restore, which are created less frequent then the Confluence backups. Also I'm not sure I can import the Postgresql backup into the cloud Postgresql db.

[R. Fuerst]

Hi Yvonne,

Thanks for clarifying your process and end goal! It is possible that you might have imported the XML file and there was already content in the database. It’s difficult to know without taking a look first-hand. That said, I believe it’s worth trying a new/fresh start to ensure everything is set up properly.

Since you are migrating a new environment, I suggest starting the migration process over and follow the steps below:

1. Create your new PostgreSQL database according to the guidelines outlined in Database Setup for PostgreSQL.
2. Install Confluence 6.3.2 on the new environment and point it to the new database created in step 1.
3. Export your site backup from your production instance.
4. Import the backup to the new install.

This should ensure that your database is set up properly and that there is no remnant data that might be causing the issue. If you’re interested, here are the full steps for migrating databases in the article below:

• https://confluence.atlassian.com/doc/migrating-to-another-database-148867.html

If the above process fails, that might indicate an issue with the source instance, as opposed to the destination instance and you'd want to investigate the error messages in more detail.

I hope it helps!