Come for the products,
stay for the community

The Atlassian Community can help you and your team get more value out of Atlassian products and practices.

Atlassian Community about banner
4,297,452
Community Members
 
Community Events
165
Community Groups

Exporting and importing restrictions

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

1 answer

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

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

Suggest an answer

Log in or Sign up to answer
TAGS
Community showcase
Published in Confluence

An update on Confluence Cloud customer feedback – June 2022

Hi everyone, We’re always looking at how to improve Confluence and customer feedback plays an important role in making sure we're investing in the areas that will bring the most value to the most c...

55 views 0 0
Read article

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you