Reverting to previous post revision in Ghost using SQL

Ghost 5.2 stores the last 10 revisions of blog posts in the database, but currently there's no interface to revert to a prior version using the web interface. Here's how you can restore a previous revision if it's not too later.

What to do if you need to revert to a past version of your Ghost blog post

First, stop making any changes to the post. If you make any change it all, it may be shortly saved as a new revision. Once you've made 10 more changes of any type, older revisions are permanently lost.

Steps to restore an old Ghost blog post revision using MySQL

First, grab the Post ID from the URL in the admin area:

https://example.com/ghost/#/editor/post/62c85522b917641e829521d5

That last string of characters is the post ID.

This post assumes you have found your database connection details (check config.production.json) and connected to the MySQL database for your site. The same SQL syntax likely works with SQLite as well, but isn't tested.

You can confirm you have the right post ID:

mysql> select id, title from posts where id = '62c85522b917641e829521d5';
+--------------------------+--------------------------------------------------------+
| id                       | title                                                  |
+--------------------------+--------------------------------------------------------+
| 62c85522b917641e829521d5 | Reverting to previous post revision in Ghost using SQL |
+--------------------------+--------------------------------------------------------+

If you don't have the Post ID, you can look at the IDs for the 10 most recent posts:

 select id, title from posts order by created_at desc limit 10;

Once you have the Post ID, it's time to find the ID of the revision you want to restore. Using the Post ID, you can query the 10 most recent revisions of your post:

select id, mobiledoc from mobiledoc_revisions where post_id  = 'YOUR_POST_ID' order by created_at desc;

The output will be hard to read to for long posts, but output is two columns per line: the first column has the revision ID you need and the second column has the entire post in MobileDoc format.

Once you the Post Id and the MobileDoc, it's time to put them together and restore your post! Here's the SQL query for that:

UPDATE posts SET mobiledoc = (SELECT mobiledoc from mobiledoc_revisions where id = 'YOUR_MOBILEDOC_REVISION_ID') where id = 'YOUR_POST_ID';

The SQL says to update the post that matches your Post ID with the MobileDoc found in the revision matching your revision ID.

Now if you re-open the Ghost admin editor, your restored content should be there.

How to reduce the risk of losing work with Ghost

As of version 5.2, using an "Undo" keystroke in a Markdown block will delete all the content in the block. And there is no redo feature. For now it's saver to avoid lengthy Markdown blocks. Instead, the standard block editor means a new "block" will be created for each element or paragraph, and an accidental "undo" will only one block at a time.

Some journalists compose drafts in Google Docs or other tools and use Ghost primarily for publishing. Creating your content first with another tool is another way to have a backup option.

Hopefully Ghost will implement "Redo" and "Restore revision".

Other tutorials for restoring Ghost blog revisions

• Ghost-o-matic's Ghost Revisions covers the same topic, using graphical tools.