Understanding Self-Hosted Ghost Data Structure

Published: 24 Jul, 2018
Reading Time: 6 minutes.
Tags: #Ghost

Warning: Ghost was updated to 2.0. The content of this blog is not tested on 2.X releases.

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

Preface

Before backing up your Ghost instance, it’s important to understand how your data is handled and where it 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):

cd /var/www/ghost/
cat config.production.json
# Output
{
  "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 the same host and on port 2368).
database - Contains info about Ghost’s database and how the client accesses it.
mail - How Ghost handles mail.
logging - How Ghost will log information.
process - Ghost processes.
paths - Contain paths that Ghost will use.

Nginx / systemd Config Files

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

Ghost, by default, uses systemd to handle its lifecycle since Ghost is planned only to be supported officially on Ubuntu Xenial (16.04) which uses systemd to handle most of the services on the host:

ls -ltr
# Output
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 was in the past), another directory is used, which Nginx uses as its root directory:

cd /var/www/ghost/system/nginx-root/
ls -ltr
# Output
total 4
-rw-rw-r-- 1 root root  628 Jul 14 16:59 index.html

Blog Content On The Filesystem

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

cd /var/www/ghost/
ls -ltr content/
# Output
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 that log the activities of Ghost.

Blog 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 who can access the ghost database.

mysql -u ghost_user -p
# Prompt
Enter password:
# Logged in
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 the 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.

vkhitrin.com

Technology And Ramblings