Ghost Blogging: Move from SQLite to Postgres or MySQL

After moving to the ghost blogging platform, I started running the system with SQLite as backend.

Now I wanted to move from SQLite to Postgres. A Google search brought up articles like this one, which try to migrate the content of the database. Scripts are used to dump the SQLite content to an export file format, to change the SQL syntax from SQLite to Postgres and to import it to the database. This has the advantage that all your settings, especially the global ones, will be converted. If that is not necessary for you, then you can use the export/import function of Ghost.

This function allows to migrate the content without having to touch the database. However, this way will loose your global settings.

Export your Blog data

Ghost ships a way to export your data. In the management interface, navigate to Settings -> Lab. There you can find the Export button:

With this function, you can export your blog content into a JSON file. This file can later be imported to the system, after you reconfigured it to use a Postgres backend.

Reconfigure the backend

At first, you need to setup your postgres database. Consult your favourite source of documentation for how to do this.

Now create a user for your blog inside the psql command line:

CREATE USER <blog_user> WITH PASSWORD '<blog_password>'; 

now create your database:

CREATE DATABASE <your_db_name> OWNER <blog_user>;

You can also give all privileges to this database to an existing user:

GRANT ALL PRIVILEGES ON <your_db_name> TO <blog_user>;

Reconfigure the Ghost backend

Go to your blog directory and edit the file config.js, according to the documentation. Remove your old db confug and import the new one:

database: {
    client: 'postgres',
    connection: {
      host: '',
      user: 'username',
      password: 'password',
      database: 'databasename',
      port: '5432'

Now you can restart ghost so that it uses the new backend.

Import your blog data

You are asked to setup your blog. Enter your blog description. After entering the right information, you can then again navigate to Settings -> Lab and import the blog data that you exported in the previous step.

Unfortunately, you have to recreate your global settings with this way to migrate your DB.