How to bulk update Confluence content through the database to replace old macros with new ones
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Summary
There might be cases when the Confluence administrator may need to replace a specific macro with a new one.
Currently, Confluence doesn't support bulk update of pages from the UI, as highlighted in the following feature request: CONFSERVER-56085 - Add bulk search and replace.
The following procedure provides the steps to bulk update content directly on the database, replacing the legacy macro with the new one.
This procedure only works if the old and new macros share the same attributes and configuration, having only the name changed.
If there's a major change on the macro attributes, then you may use this as a starting point.
This KB article provides changes that are out of the scope of the Atlassian Support and, therefore, is provided as-is.
Since it suggests changes on the database, it is strongly recommended to validate it on a test environment before applying it on production.
Make sure to have a backup of the database before applying changes on production.
Solution
Preparation
The first thing you need to do is to determine the name of the macro you want to replace on the target pages. You will also need the name of the new macro.
You can get this information by accessing the Confluence Storage Format of pages using the target macros.
The image below shows an example with the Info, Tip, Note, and Warning Macros.
You may also need to get a report on the pages using the target macro. The following SQL query provides this information from latest (current) version of pages using the old macro. This is targeting pages, blogposts and page comments.
Change <OLD MACRO NAME> for your target macro as retrieved from the storage format.
PostgreSQL
1
2
3
4
5
6
7
8
9
SELECT c.contentid, c.contenttype, c.title, s.spacekey
FROM CONTENT c
JOIN BODYCONTENT bc
ON c.contentid = bc.contentid
JOIN SPACES s
ON c.spaceid = s.spaceid
WHERE c.prevver IS NULL
AND c.contenttype IN ('PAGE', 'BLOGPOST', 'COMMENT')
AND bc.body LIKE '%ac:name="<OLD MACRO NAME>"%';If Collaborative editing is enabled, we also need to touch the shared draft of target pages.
The following SQL query provides information on shared drafts (pages and blogposts) using the old macro.
1
2
3
4
5
6
7
8
9
SELECT c.contentid, c.contenttype, c.title, s.spacekey
FROM CONTENT c
JOIN BODYCONTENT bc
ON c.contentid = bc.contentid
JOIN SPACES s
ON c.spaceid = s.spaceid
WHERE c.content_status = 'draft'
AND c.contenttype IN ('PAGE', 'BLOGPOST')
AND bc.body LIKE '%ac:name="<OLD MACRO NAME>"%';Change Procedure
Stop Confluence following your standard procedure.
If running Confluence Data Center, stop Confluence on all nodes.
If Collaborative Editing is enabled and Synchrony is running on a Set up a Synchrony cluster for Confluence Data Center, stop the Synchrony process on each node.
If Collaborative Editing is enabled, change the properties of target shared drafts so that Synchrony updates its cache with the modified content.
PostgreSQL
1 2 3 4 5 6 7 8 9 10 11 12 13 14update CONTENTPROPERTIES set stringval = 'synchrony-recovery' where CONTENTID IN (SELECT c.contentid FROM CONTENT c JOIN BODYCONTENT bc ON c.contentid = bc.contentid JOIN SPACES s ON c.spaceid = s.spaceid WHERE c.content_status = 'draft' AND c.contenttype IN ('PAGE', 'BLOGPOST') AND bc.body LIKE '%ac:name="<OLD MACRO NAME>"%' ) AND propertyname = 'sync-rev-source' ;Run the following SQL query to update the content of Pages, Blogposts and Page Comments.
To update only the latest version of target content
PostgreSQL
1 2 3 4 5 6 7 8 9 10 11 12UPDATE bodycontent SET body = replace(body,'ac:name="<OLD MACRO NAME>"','ac:name="<NEW MACRO NAME>"') WHERE bodycontentid in ( SELECT bc.bodycontentid FROM CONTENT c JOIN BODYCONTENT bc ON c.contentid = bc.contentid WHERE c.prevver IS NULL AND c.contenttype IN ('PAGE', 'BLOGPOST', 'COMMENT') AND bc.body LIKE '%ac:name="<OLD MACRO NAME>"%' ) ;To update all versions of target content
PostgreSQL
1 2 3 4 5 6 7 8 9 10 11UPDATE bodycontent SET body = replace(body,'ac:name="<OLD MACRO NAME>"','ac:name="<NEW MACRO NAME>"') WHERE bodycontentid in ( SELECT bc.bodycontentid FROM CONTENT c JOIN BODYCONTENT bc ON c.contentid = bc.contentid WHERE c.contenttype IN ('PAGE', 'BLOGPOST', 'COMMENT') AND bc.body LIKE '%ac:name="<OLD MACRO NAME>"%' ) ;If Collaborative Editing is enabled, update the body of shared drafts.
PostgreSQL
1 2 3 4 5 6 7 8 9 10 11 12UPDATE bodycontent SET body = replace(body,'ac:name="<OLD MACRO NAME>"','ac:name="<NEW MACRO NAME>"') WHERE bodycontentid in ( SELECT bc.bodycontentid FROM CONTENT c JOIN BODYCONTENT bc ON c.contentid = bc.contentid WHERE c.content_status = 'draft' AND c.contenttype IN ('PAGE', 'BLOGPOST') AND bc.body LIKE '%ac:name="<OLD MACRO NAME>"%' ) ;If Collaborative Editing is enabled and Synchrony is running on a Set up a Synchrony cluster for Confluence Data Center, start the Synchrony process on each node following your standard procedure.
Start Confluence following your standard procedure.
If running Confluence Data Center, start Confluence on each node at a time.
See Also
Was this helpful?