How to Recover Page Content from the Database and Import Using Source Editor
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
Use Case
If you have a page that will not display in the Confluence UI, for example due to database server issues, but the data in the Confluence database is intact, you can use the following to recover the content and add it to a new page. This article assumes you have already created a new Confluence instance from a backup and have pages that were not included in backup that need to be restored. If you have not setup the new Confluence instance, you can refer to Migrating Confluence between servers for directions on this.
This article also assumes you have already installed the Source Editor Plugin.
This was tested on PostgreSQL, and some steps might differ for MySQL, Oracle, and SQL Server
Resolution
Connect to the database
From the terminal/command line (if your database is not named confluence, replace confluence with the appropriate name):
1$ psql -U username -d confluenceIt should prompt you for your database password, after entering that you should see the following:
1confluence=>At this point you are connected to the Confluence database, and can use the following SQL query to export the content of the page to a text file:
Replace <page_title> with the exact title of the page you want to import, and replace filename with whatever you want to call the file
1confluence=> SELECT bc.body FROM bodycontent bc JOIN content c ON bc.contentid = c.contentid WHERE c.title = '<page_title>' AND c.prevver IS NULL \g /path/to/folder/filename.txtYou should now have a text file with the contents of the page you wanted to export. You can use \q to exit the database
Open the text file:
1$ view /path/to/folder/filename.txtYou should see the following:
You will want to select everything under the dashes (------) and before the (1 row), and copy
Create a new page in Confluence, give it a title and save it (you will want it to be blank). Save this blank page.
Edit the page you just created, you should now see the Source Editor button (highlighted in red):
This will open the Source Editor UI:
Paste the text you copied earlier, hit apply.
Save the page in Confluence, and you should now have a replica of the original page:
Was this helpful?