Exporting and importing restrictions

[Peter Jakobsen]

Hi

Our customer has a huge Confluence space which needs to be made searchable again. The root cause for this is unknown as of now. Perhaps a corrupted database?

Currently all pages in the specific space cannot be found in a search by a user. A re-indexing of the space will fix this. This has been tested on a small space. However this cannot be completed due to the existence of various page restrictions on the space. These are custom restrictions setup individually for each page.

The solution roadmap to fix this, is so far:

1. Backup Confluence DB
2. Extract (backup) restrictions
3. Remove all restrictions (using the built in ScriptRunner script "Add/Remove Restrictions to Parent & Child Pages")
4. Perform a re-indexing of space
5. Import (restore) restrictions
(6. Verification)

We need help on
step #2 - on how to backup/export "restrictions" data before we can re-index the pages to make them searchable again. AND
step #5 - on how to import "restrictions" data again.

I have not been able to find a ScriptRunner script that could help with exporting / importing the "restrictions" data.
So I have tried to find a manual solution with some DB sql commands.

To do this I have examined and looked into the Confluence datamodel.
(https://confluence.atlassian.com/doc/files/127369837/958779046/1/1538453845677/H2vis-NoAO-Sep18.png)

So far to my knowledge, I have been succesfull on the steps #1 - #4 ( on our test setup).

I have used the following SQL statements to "Export data" via the SQL Server Management Studio.
The data have been succesfully exported to 2 flat files.

– 1. CONTENT PERM SET
SELECT c.CONTENTID, c.TITLE, s.SPACEKEY, cps.CONT_PERM_TYPE
FROM SPACES s
JOIN CONTENT c ON s.SPACEID = c.SPACEID
JOIN CONTENT_PERM_SET cps ON c.CONTENTID = cps.CONTENT_ID
JOIN CONTENT_PERM cp ON cps.ID = cp.CPS_ID
WHERE s.SPACEKEY = '<space key>'

– 2. CONTENT PERM
select cps.*
from CONTENT_PERM_SET cps
JOIN CONTENT c ON c.CONTENTID = cps.CONTENT_ID
JOIN SPACES s ON c.SPACEID = s.SPACEID
WHERE s.SPACEKEY = '<space key>'

However when proceeding to step #5 (import) things go wrong.
I have the 2 flat DB files and via the SQL Server Managment Studio, I have succesfully imported the 2 flat files. Seemingly with no problems.
But when revistiing the specific page (content) in Confluence, the restrictions are not there after the import. They are there in the DB tables though.

Am I missing some part of the Datamodel in my export?
Or can anyone tell me what I am doing wrong.
Or maybe an alternative solution perhaps ?

On a side note it would be interesting to know what DB tables the "Built in script" modifies when removing restritions "Add/Remove Restrictions to Parent & Child Pages".
Anyone ?

Thank you. Any help would be much appreciated.

Kind regards
Peter Jakobsen

[Paul Marachlian]

Hello Peter,

Before you continue reading : I don't have the answer. Good luck

In what you have described, you have 2 risky points:
- Huge space
- SQL Server Management Studio, so your DB is on MicrosoftSQL

Lately I had to migrate a "simple" huge space migration from one instance to another (entities.xml 2GB + attachments 34GB), from MicrosoftSQL to PostgreSQL. Whatever I tried the export/import wasn't always complete although Confluence didn't report issues, but the content_properties table didn't match between source and destination.
Although MsSQL is supported, it's not the most recommended DB to have.

So you may be doing nothing wrong, it's just the fact that the space is huge.

As for your "Built in script" question, contact Adaptavist directly.

Good luck

[Peter Jakobsen]

Hi Paul

Thanks for your reply. 
It's good to know (MS SQL server). Unfortunately it's out of my hands (DB  choice).

Yeah I will contact Adaptavist regarding the "built in script". Thanks.

Kind regards
Peter