Confluence Postgres DB backup and restoration

[Niranjan]

Hi All,

I have a question on postgres confluence DB backup and restoration process. We tried to backup our confluence PROD DB and restore them in Dev instance by

Stopping confluence
Updating the DB details in confluence.cfg.xml file
Restarting confluence and performing a content indexing.

We do not need the attachments and hence we have not restored the confluence home directory.

The system info page of our Dev instance is showing number of spaces, contents, site spaces, user groups, personal spaces matching our PROD instance, however the DB size of prod confluence and dev confluence doesn't match. Unable to find any missing data. Import of PROD data to a new database completed without any errors.

We ran this query to check the DB size

SELECT pg_size_pretty(pg_database_size(current_database())) as size;

A diff of 13G is seen between PROD and Dev instance databases

Any thoughts?

[Daniel Ebers · Accepted solution]

Hi Niranjan,

I assume you used the pg_dump command and restored via psql like described by Stephen?

If so, the diff in size is most likely just normal.
I found a explanation on the "why" here - in case you want to read through the backgrounds.

Cheers,
Daniel

[Stephen Garber]

It's possible that you are missing data, or you could just me missing the page history. Either way you'll probably want to do a postgres dump and restore to make sure you have all the data. The process looks like this:

1) Stop production confluence 

2) Take a database dump

pg_dump dbname > dumpfile

3) Move the dump file to new confluence db server 

4) Stop Dev confluence

5) Restore database from db dump

psql dbname < dumpfile

 6) Start Dev Confluence

A postgres dump drops all the data into a single file, which can be transferred and reimported.

You can read more about this here: https://www.postgresql.org/docs/9.5/backup-dump.html