Category: Technology

When I moved this website from WordPress to Ghost earlier this year, I used their tool to import the posts in.

What it did, it created a 2nd user and associated the posts with that user while keeping the Ghost author (The main one that I would use) as a separate user. I didn’t want this, so I decided to tinker with the SQLite database and change that.

Note: If you decide to follow my instructions, I am not responsible for any data loss or breakages to your site, ok? Good! 🙂

Just for a bit of context, I am using Ghost on Docker via Plesk, (click here for instructions on how to set that up).

To get into the terminal for your docker instance, run docker ps first and get the name of your docker container and then run docker exec -t -i container_name bash (replacing container_name with your own)

You will need to ensure that you have sqlite3 installed. On Debian/Ubuntu, you can do this by running apt update && apt install -y sqlite3.

• BACK UP YOUR DATABASE. This should be obvious. Back up your databases before messing with it.

  * Navigate to /var/lib/ghost/content/data and run cp ghost.db ghostdb-backup2021-10-11

• While in /var/lib/ghost/content/data run sqlite3 ghost.db
• You will need to first find out the author_id of your users, to do this, run the command SELECT id, name, slug FROM users;

This will return the following:

1|Padraig Fahy|padraig8df7s89f7sd98f7sd|Ghost|ghost7vc89x675x6c78x9c|Padraig Fahy|padraig-2(Note: The id's are different for every instance and are randomised here)

From this, you might be able to determine which user is your WordPress user. But to be sure, go and view one of the posts that you would have created on the site and click on the link to the author, whether that would be a ‘See more from this author’ or similar link.  

Then check the URL in the address bar to see what the ‘slug’ is. For me, all my WordPress posts where imported as ‘padraig-2’

So the ID for ‘padraig-2’ would be 7vc89x675x6c78x9c and now we want to change that to 1 so that it is the Ghost user ‘padraig’

• UPDATE posts SET author_id = REPLACE(author_id,'7vc89x675x6c78x9c','1');
• Press enter. Then run:
• UPDATE posts_authors SET author_id = REPLACE(author_id,'7vc89x675x6c78x9c','1');
• And press enter again.

This will update the posts themselves to be the correct user and then update the ‘authors’ page to point it to the correct user.

When you try to go to the WordPress author page, it should return ‘0 Results’

You can verify your work by going to your proper author page https://padraig.blog/author/padraig/ and seeing your posts.

Repeat the process for the different authors if you have them.

Exit sqlite3 by running .quit and pressing enter.

This is a follow-on to my previous post about installing Ghost 4 on Plesk Obsidian.

As with the previous post:

I am not an expert in Docker, npm, node.js or related technologies. What I am documenting here is what worked for myself.

(more…)