Writing and reading from two databases

Developer help
1

Hello,
Is it possible to allow Ghost to write simultaneously in two different databases?

Something like it:

{
  "url": "https://domain.it",
  "server": {
    "port": 2368,
    "host": "127.0.0.1"
  },
  "database": {
    "client": "mysql",
    "connection": {
      "host": "localhost",
      "user": "ghost-___",
      "password": "password__",
      "database": "ghost_prod"
    },
  "database_2": {
    "client": "mysql",
    "connection": {
      "host": "ADDRESS_IP",
      "user": "ghost-___",
      "password": "password__",
      "database": "ghost_prod_2"
    }
  },

...

To prevent downtime I’m trying to run a 2nd server and use a fail-over IP to switch if one of the servers fail.

2

No. The correct way to do this would be to set up replication at the database level rather than pushing infrastructure concerns into application code.

However, I don’t know how well this will work with Ghost for your intended use-case. Ghost does not currently support running multiple instances so the failover would be buggy unless you were able to boot Ghost from scratch. You’d probably be better off having a cache/proxy in front of your Ghost instance that can detect upstream status and serve content from the cache if the upstream is unreachable.

3

I don’t know if I understand exactly what you mean.

My intent is to replicate my own site on another server.
I have a monitoring system that when server1 is no longer reachable a redirect is made to server2.

My problem is the database. How can I have both databases synchronized? If a user is registered on server1 and then crashes. How does the user log into server2?

I need to have a way to sync Ghost databases.

4

This is a standard database setup, it’s called replication. You can read about MySQL built-in options here https://dev.mysql.com/doc/refman/8.0/en/replication.html. Just bear in mind that it’s a pretty advanced topic and it can be easy to get yourself into a mess.

However, just because a database is synced does not mean that your Ghost instances will be synced. As I said before Ghost does not support running multiple instances. If you have your “failover” instance running, even if it’s not being accessed it’s still a second instance and will not have the same in-memory caches. You would need to detect failover and then boot your second Ghost instance so that it can do it’s boot-time startup and build it’s in-memory routing maps etc from the database.

You’d have a much simpler and probably more resilient setup if you went with the caching approach and served content directly from a cache/CDN any time your upstream Ghost instance is unavailable.

5

Thanks for this clarification.

If you recommend this approach, I inquire about how to proceed.
Can you give me some advice or something to read to setup this configuration?
Thank you

6

It depends a lot on the software you are using for your proxy and whether you’re using a CDN as they tend to have their own setups for handling this sort of thing.

If you’re using nginx then the docs may be a good place to start https://www.nginx.com/blog/nginx-caching-guide/#stale

There are many ways to set this type of system up and to customise it. There’s no magic bullet when self-hosting as every site, it’s requirements, and it’s setup/maintenance/on-going cost budgets are different. Of course if you want to avoid the headaches there’s always Ghost(Pro) where all of this is managed for you.

7

I use the default configuration, I only added cloudflare.
Anyway thanks for this information. I will evaluate the best solution for my use case.

I would have opened another topic to ask for information on the cache, but I believe that this topic is also fine, since we talk about cache.

I noticed that the cache on my VPS often almost occupies a large part of the RAM.

        total   used   free   shared   buff/cache   available
 Mem:    3.8G   685M   1.5G      1.1M   1.6G         2.8G

I often give this command when RAM is almost totally occupied.

sync; echo 3 > /proc/sys/vm/drop_caches

Is it wrong for connected users or for other reasons?

8

You don’t need to do that, it’s perfectly normal for the “buffer/cache” memory to fill any/all available RAM. The OS will manage it automatically and it won’t affect any other users on the same physical hardware. There’s a good SO answer here with links to further reading linux - Meaning of the buffers/cache line in the output of free - Server Fault

If you are looking to get into advanced topics such as db replication, failover, proxy/cache management, etc that you want to handle yourself I would suggest covering some of the basics of server management first. Otherwise you can end up with a complex system where if (when!) anything goes wrong you won’t have the supporting knowledge and monitoring in place to diagnose the underlying causes and fix things quickly.

There’s a lot to be said for keeping your infrastructure as simple and bare bones as possible or outsourcing that headache, that way when something goes down it’s quick to get back up and running or it’s someone else’s problem. Unless you’re doing it for the learning experience with personal sites where downtime is not an issue (in which case, go for it and learn as much as possible! :smile:) leave the complex infrastructure for mission critical services, and even then only once you’ve outgrown the simple infrastructure. Infrastructure complexity cascades - if you add db replication you now have two servers to manage and go wrong, plus a more complex backup solution, plus a requirement for a post-failover recovery plan, plus a test environment to make sure that your failover works and won’t destroy data, and so on, and so on.

2 Likes
9

Thank you, you have been very clear.
I am interested in studying these dynamics for my personal knowledge, and I would like to apply them to my projects, I am interested in never going offline so I was looking for solutions to achieve this result.
It is certainly more complex than I thought, I will read and study further. Thank you!