I would like to receive assistance (instruction based) to create a master to master replication of the PostgreSQL 10 database.
I am looking for something similar like global transaction identifier based replication (this is how it's called in MySQL). I hope there must be something analogous in PostgreSQL.
Currently, I am running a PostgreSQL 10 database server in the cloud (digital ocean) on the top of CentOS 8. 1 CPU, 1GB RAM, 2GB SWAP, 25GB disk. It's not an RDS service.
The content in the database server is tiny (currently less than 1GB of data with the plans to grow up to 9GB). The database server runs multiple databases inside. Each database is a Relational Database.
I would love to have a master to master replication with a "ground" instance. It's a workstation like Linux box. 4 CPUs, 32GB RAM.
Ideally, when a new database (not table) is created (in a cloud or at the "ground") it should be replicated to the other node.
The "groud" instance is running Debian 9 (stretch) based Linux distro. Runs "MX Linux 18" more precisely.
The whole idea of replication is to connect a local application (web-based) to the local database engine.
I guess it's configurable to instruct the database engine at system startup to serve all content in memory. So I can access the data in the database with the very best access time.
The local "ground" database will be used mostly for reads only, but a couple of inserts/deletes may occur sometimes (while foreign keys ON).
The cloud instance is online all the time but the "ground" instance can be offline for 7 days.
The "ground" instance must be capable to keep up with the cloud instance once it's back online. The quality of the network is 200Mbit up/down.
My expectation is: once the workstation has a fresh boot, it will "download" all the updates while sustaining everything in memory.
The architecture should be scalable so if I decide to have 2 different workstations (not online at the same time) it should be capable to replicate the content again from master (cloud) instance.
To complete the project I hope to receive instructions in a pure text document containing comments per command.
Plus some explanation per option installed regarding the database tuning config per machine.
In response please include some keywords from the project description so I will know I'm talking to a real person.
Kindly include a tiny note of how the project will be implemented. Introduce some downsides, if any. What advantages can be used if a more recent version of the PostgreSQL server is used?
Thank you very much for reading so far. Looking forward to hear from you,