Published: July 24, 2018
Updated: January 29, 2019
Tags: Ghost
5 min read

Understanding Self-Hosted Ghost Data Structure

Ghost was updated to 2.0, the content of this blog is not tested

(A follow-up to this blog post Backing Up/Restoring Self-Hosted Ghost Instance)

Relevant for self-hosted non-containerized Ghost 1.X, might not be suited in the future Ghost 2.0 release

Before backing up your Ghost instance it's important to understand how your data is handled and where your data is stored.

Ghost's data is stored in two places, on the filesystem and in a database(MySQL).

Ghost config files

Ghost stores general info about itself in a file named config.production.json in the ghost installation directory (usually /var/www/ghost):

[11:16:20] ubuntu@website ~
$ cd /var/www/ghost/
[11:16:23] ubuntu@website /var/www/ghost
$ cat config.production.json
{
  "url": "https://example.com",
  "server": {
    "port": 2368,
    "host": "127.0.0.1"
  },
  "database": {
    "client": "mysql",
    "connection": {
      "host": "localhost",
      "user": "ghost_user",
      "password": "CXA$_EncryptedPass",
      "database": "db_name"
    }
  },
  "mail": {
    "transport": "Direct"
  },
  "logging": {
    "transports": [
      "file",
      "stdout"
    ]
  },
  "process": "systemd",
  "paths": {
    "contentPath": "/var/www/ghost/content"
  }
}

url - URL of Ghost's blog.
server - Contains the IP and Port of where your Ghost Instance is hosted (by default on same host and on port 2368)
database - Contains info about Ghost's database and how the client accesses it.
mail - How Ghost handles mail[1].
logging - How ghost will log information.
process - Which process manages Ghost processes.
paths - Contain paths that Ghost will use.

Nginx / systemd config files

Ghost by default uses Nginx as it's web and proxy server to expose its content on the web.

Ghost by default uses systemd to handle it's 'lifecycle' since Ghost is planned to only be supported officially on Ubuntu Xenial(16.04)[2] which uses systemd to handle most of the services on the host:

[11:30:12] ubuntu@website /var/www/ghost/system/files
$ ls -ltr
total 12
-rw-rw-r-- 1 ubuntu ubuntu  299 Jul 14 16:49 ghost_www-example-com.service
-rw-rw-r-- 1 ubuntu ubuntu  596 Jul 18 09:05 www.example.com.conf
-rw-rw-r-- 1 ubuntu ubuntu 1080 Jul 18 15:08 www.example.com-ssl.conf

'ghost_www-example-com.service' - Systemd service unit file used to control the ghost service.
'www.example.com.conf' - Nginx config file.
'www.example.com-ssl.conf' - Nginx config file which contains SSL configuration.

If your blog is not deployed on the root (/) of your web server (like this blog), another directory is used which Nginx uses as its root directory:

[11:39:16] ubuntu@website /var/www/ghost/system/files
$ cd /var/www/ghost/system/nginx-root/
[11:39:25] ubuntu@website /var/www/ghost/system/nginx-root
$ ls -ltr
total 4
-rw-rw-r-- 1 root root  628 Jul 14 16:59 index.html

Blog's content on the filesystem

Ghost stores static content (such as logs, some settings, images, apps, themes and such) on the filesystem.

[10:57:34] ubuntu@website ~
$ cd /var/www/ghost/
[10:57:38] ubuntu@website /var/www/ghost
$ ls -ltr content/
total 24
drwxrwxr-x 2 ghost ghost 4096 Jul 14 16:48 images
drwxrwxr-x 2 ghost ghost 4096 Jul 14 16:48 data
drwxrwxr-x 2 ghost ghost 4096 Jul 14 16:48 apps
drwxrwxr-x 2 ghost ghost 4096 Jul 14 16:50 settings
drwxrwxr-x 3 ghost ghost 4096 Jul 18 21:34 themes
drwxrwxr-x 2 ghost ghost 4096 Jul 21 00:00 logs

images - Images that your website hosts (and accessible with a URL)
data
data - Used to be the location of Ghost's DB files.
apps - Used to be called plugins, extends Ghost's capabilities.
settings - Contains additional settings that ghost uses (by default the way content is routed with ghost exists in a file in this directory).
themes - Contains all the themes installed.
logs - Contains log files which log activities of Ghost.

Blog's content in the database

Ghost stores dynamic content (such as posts, authors, tags and much more) inside a database.

Connect to the database with a ghost / any other privileged user which can access the ghost database.

[11:46:57] ubuntu@website ~
$ mysql -u ghost_user -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 30
Server version: 5.7.22-0ubuntu0.16.04.1 (Ubuntu)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Find your Ghost database

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| ghost_db           |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

View the tables that exist in the database:

mysql> use ghost_db;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+------------------------+
| Tables_in_ghost_db     |
+------------------------+
| accesstokens           |
| app_fields             |
| app_settings           |
| apps                   |
| brute                  |
| client_trusted_domains |
| clients                |
| invites                |
| migrations             |
| migrations_lock        |
| permissions            |
| permissions_apps       |
| permissions_roles      |
| permissions_users      |
| posts                  |
| posts_authors          |
| posts_tags             |
| refreshtokens          |
| roles                  |
| roles_users            |
| settings               |
| subscribers            |
| tags                   |
| users                  |
| webhooks               |
+------------------------+
25 rows in set (0.00 sec)

For example, view from which fields that posts table consists of:

mysql> desc posts;
+---------------------+---------------+------+-----+---------+-------+
| Field               | Type          | Null | Key | Default | Extra |
+---------------------+---------------+------+-----+---------+-------+
| id                  | varchar(24)   | NO   | PRI | NULL    |       |
| uuid                | varchar(36)   | NO   |     | NULL    |       |
| title               | varchar(2000) | NO   |     | NULL    |       |
| slug                | varchar(191)  | NO   | UNI | NULL    |       |
| mobiledoc           | longtext      | YES  |     | NULL    |       |
| html                | longtext      | YES  |     | NULL    |       |
| amp                 | longtext      | YES  |     | NULL    |       |
| plaintext           | longtext      | YES  |     | NULL    |       |
| feature_image       | varchar(2000) | YES  |     | NULL    |       |
| featured            | tinyint(1)    | NO   |     | 0       |       |
| page                | tinyint(1)    | NO   |     | 0       |       |
| status              | varchar(50)   | NO   |     | draft   |       |
| locale              | varchar(6)    | YES  |     | NULL    |       |
| visibility          | varchar(50)   | NO   |     | public  |       |
| meta_title          | varchar(2000) | YES  |     | NULL    |       |
| meta_description    | varchar(2000) | YES  |     | NULL    |       |
| author_id           | varchar(24)   | NO   |     | NULL    |       |
| created_at          | datetime      | NO   |     | NULL    |       |
| created_by          | varchar(24)   | NO   |     | NULL    |       |
| updated_at          | datetime      | YES  |     | NULL    |       |
| updated_by          | varchar(24)   | YES  |     | NULL    |       |
| published_at        | datetime      | YES  |     | NULL    |       |
| published_by        | varchar(24)   | YES  |     | NULL    |       |
| custom_excerpt      | varchar(2000) | YES  |     | NULL    |       |
| codeinjection_head  | text          | YES  |     | NULL    |       |
| codeinjection_foot  | text          | YES  |     | NULL    |       |
| og_image            | varchar(2000) | YES  |     | NULL    |       |
| og_title            | varchar(300)  | YES  |     | NULL    |       |
| og_description      | varchar(500)  | YES  |     | NULL    |       |
| twitter_image       | varchar(2000) | YES  |     | NULL    |       |
| twitter_title       | varchar(300)  | YES  |     | NULL    |       |
| twitter_description | varchar(500)  | YES  |     | NULL    |       |
| custom_template     | varchar(100)  | YES  |     | NULL    |       |
+---------------------+---------------+------+-----+---------+-------+
33 rows in set (0.00 sec)

Execute a simple query to understand the content of posts:

mysql> select title,plaintext from posts;
+---------------------+--------------------+
| Title               | plaintext          |
+---------------------+--------------------+
| Blog Post           | Hello World!       |
+---------------------+--------------------+
1 rows in set (0.00 sec)

Summary

  • Ghost keeps some of its settings on a filesystem (mostly Nginx related) and some of it inside a database.
  • Static content is stored on a filesystem.
  • Dynamic content is stored inside of a database.

  1. Ghost mail backend official documentation ↩︎

  2. Ghost official support 'matrix' ↩︎