Restore deleted page in Wagtail database

After deleting a page from the Wagtail application, you can only restore it by doing inserts into the Wagtail database of the page ID that was lost.

Page models

Wagtail has many different page types based on our defined models. Each of these page models are represented in a different table within the Wagtail database schema.

A listing of our home grown page templates include:

• AboutLandingPage
• AlertForEmergencyUseOnly
• Author
• CollectionPage
• CommissionerPage
• ContactPage
• CustomPage
• DigestPage
• DigestPageAuthors
• DocumentFeedPage
• DocumentPage
• HomePage
• HomePageBannerAnnouncement
• LegalResourcesLandingPage
• MeetingPage
• PressLandingPage
• PressReleasePage
• PressReleasePageAuthors
• RecordPage
• RecordPageAuthors
• RecordPageTag
• ReportingExamplePage
• ReportsLandingPage
• ResourcePage
• ServicesLandingPage
• TipsForTreasurersPage

Database tables

Generally each page template is tied to the following tables:

1. wagtailcore_page
2. Corresponding page model table (see list above)
3. wagtailredirects_redirect (not always referenced)
4. wagtailcore_pagerevision (An insert of these records are not performed since there is too much data to need to manipulate to insert cleanly into this table)

How to restore deleted record into database

1. Restore a recent copy of the production database dump onto your local machine.

2. Try to find the page ID that was deleted on your local. This page ID can be found in the url, for example: /admin/pages/119/edit/

3. Find that corresponding ID in all database tables referencing that ID. Do a copy of the entire row. Paste that into an editor. If using PG Admin, it can copy an entire row into a comma separated format, which is useful when needing to add to the VALUES() portion of the insert statement.

4. Make sure to escape apostrophes ', by adding another apostrophe in front. Example: That''s how to escape. The SQL needs to be escaped in order to do a clean insert.

5. Generate database insert statements. The tables that need inserts include the wagtailcore_page table and the corresponding page model table. Within the VALUES() insert the copied row from step 3.

Insert statements for these 2 tables would look something like this:

Every page ID is stored within Wagtailcore page

INSERT INTO public.wagtailcore_page(
	id, path, depth, numchild, title, slug, live, has_unpublished_changes, url_path, seo_title, show_in_menus, search_description, go_live_at, expire_at, expired, content_type_id, owner_id, locked, latest_revision_created_at, first_published_at, live_revision_id, last_published_at, draft_title)
	VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);

This is an example of inserting an ID back into the home_custompage table.

INSERT INTO public.home_custompage(
	page_ptr_id, author, date, body, sidebar, legal_resource, related_items, citations, continue_learning, record_articles, show_contact_link, menu_title, related_topics)
	VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);

6. Check to see if this ID is referenced within the wagtailredirects_redirect. This is not always the case because not all pages have a redirect referencing it. If there is a redirect referenced, the insert statement would look like this:

INSERT INTO public.wagtailredirects_redirect(
	id, old_path, is_permanent, redirect_link, redirect_page_id, site_id)
	VALUES (?, ?, ?, ?, ?, ?);

7. Once all the insert statements are created, open up your terminal to run psql to connect to your local DB.

psql -d cfdm_cms_test

8. Perform a transaction command, this allows you to perform multiple sql statements at once to make sure they execute correctly before actually committing the action.

BEGIN TRANSACTION;
<insert_sql_statements_here>

9. Make sure that each insert worked and inserted the correct amount of rows into each table. If everything looks correct, type COMMIT TRANSACTION;. If something went wrong and you want to back out of the transaction, type ROLLBACK TRANSACTION;.

10. Take a look at your local Wagtail application to see if the record was restored.