How to Recover Page Content from the Database and Import Using Source Editor
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
- Connect to the database
From the terminal/command line (if your database is not named confluence, replace confluence with the appropriate name):
$ psql -U username -d confluence
It should prompt you for your database password, after entering that you should see the following:
- 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
confluence=> 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.txt
- You 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:
$ view /path/to/folder/filename.txt
- You 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: