Database Restore - what directories are required

[Jiri Pik]

The XML backup / restore for me reached the size where it is no longer usable. 

So, I am thinking of backing up Confluence by backing up the attachments directory and database.

Some docs on Atlassian site requires a backup of a confluence home directory - which one is it? the /opt or the /var? And would the backup of the home directory be satisfied by installing from the installer?

What would then be a restore process?

1. I install the latest version of confluence but do not let it start
2. I restore the database 
3. I restore the attachments directory
4. I restore the confluence.cfg.xml and point it to the restored database

Is that all? I tried these but then there were missing spaces etc. 

[Nic Brough -Adaptavist- · Accepted solution]

Depends on how you installed it, but the way Atlassian defaults it to is to use those directories as Linux tends to define them - opt will probably contain the installation, and var will have the home directory.  

Your process won't really work - you'll be missing too much of a sound home directory.  You are not far off though, I would have a spare and empty database, and run the install through to completion - start Confluence pointed to that empty database and run the install process through to the point where you can log into the local account you set up. 

Then stop it, do the rest of the steps 2-4, start it up and log in again.  You will need to do a full re-index as well, from the UI, that will make all your spaces and pages reappear.

[Jiri Pik]

THANK YOU.

The reason I am asking is that the XML backup/restore is not really robust enough. Yesterday on restore I lost all emoticons

com.atlassian.confluence.importexport.ImportExportException: Unable to complete import: Invalid byte 2 of 4-byte UTF-8 sequence.

The database backup looks like the way forward for huge confluence installations but would not like to hack it as we suggested.

Is there any plan to make the backup / restore more robust?

[Nic Brough -Adaptavist-]

That actually suggests that your database is not configured to support 4 byte utf.

Is it MySQL by any chance?

[Jiri Pik]

THANK YOU.

It's PostgreSQL, with the database set up as follows

CREATE DATABASE confluence WITH ENCODING 'UTF-8' LC_COLLATE='en_US.UTF-8' LC_CTYPE='en_US.UTF-8' TEMPLATE template0

I use PostgreSQL since I remember seeing some performance benchmarks for Confluence and PostgreSQL was the best backend. Maybe it has changed since then?

I do not think it has anything to do with database but with the size of the xml - 

https://confluence.atlassian.com/bitbucketserverkb/database-restore-migration-fails-saxparseexception-invalid-byte-2-of-4-byte-utf-8-sequence-1014276836.html

Actually, it's intriguing. On Oct 9, I migrated the entire confluence (new EC2 and new RDS) from PostgreSQL 9.6 to 10.x and no problem with restore from XML.

Then, on Oct 13 there was a minor accident and I had to reinstall everything on PostgreSQL 10.x and was unable to do so without cleaning the emoticons. I did have added some new content between Oct 9 and 13 so the XML got bigger. 

I have tested all possible solutions - have created like 10 different Ec2s with different versions of Confluence and JVM settings but no help.

So the questions:

1. Is PostgreSQL still the most performant backend for Confluence / JIRA/
2. If the XML restore on large backups has problems, what is the recommend restore - the database restore is more of a hack than recommended solution?

[Daniel Ebers]

PostgreSQL is still a good choice. As of your described process I'd recommend to review and probably rework the backup/restore process.

From the Backup & Restore documentation (https://confluence.atlassian.com/doc/site-backup-and-restore-163578.html) the recommendation is to use the databases "own" backup tools to do backup/restore.

As for the XML backup it reads:

The XML backups are considered as a (...)method (...) useful for small sites, test sites, or in addition to your database and directory backups. We don't recommend you rely solely on this backup method for your production site.

So my suggestion at this step would be to consider if the stock tools (PostgreSQL-related) would not be a better choice for the backup. From your description the XML backups seem not to be stable in your environment anyway.

Like Nic already outlined very detailed you would need to have all needed directories/files in the backup + the database (of course).
The following guide should get you a first idea:
https://confluence.atlassian.com/doc/production-backup-strategy-38797389.html

After you are familiar with the tooling and some tests I'd propose to restore a backup to a non-production environment to test for data consistency.

Cheers,
Daniel

[Jiri Pik]

@Daniel Ebers - This is excellent. THANK YOU.