#1224: How to configure Swarmica database replication?

Отредактирована: сегодня

Symptoms

Is it possible to configure Swarmica database replication for analytics or backup?

Solution

Yes, Swarmica uses Postgres as its DBMS, which supports replication.

To enable replication, you must follow the steps on the primary server and the replica, described below.

Configuring the Primary Swarmica Server

Create a file /root/swarmica/pg_hba.conf with the following content (replace REPLICA_IP with the IP address of the replica server):

local   all             all                                     trust
host    all             all             127.0.0.1/32            trust
host    all             all             ::1/128                 trust
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust
host    replication     all             REPLICA_IP/32           scram-sha-256
host all all all scram-sha-256

Edit /root/swarmica/docker-compose.yml, adding the mounting of pg_hba.conf in the volumes section and passing the necessary postgres server settings via -c commands:

postgres:
  image: postgres:14.2-bullseye
  container_name: swarmica-postgres
  shm_size: 8gb
  command: postgres -N 300 -c shared_preload_libraries=pg_stat_statements -c pg_stat_statements.track=all -c wal_keep_size=512MB -c work_mem=8MB -c temp_buffers=16MB -c shared_buffers=8GB -c track_activity_query_size=16384 -c max_wal_size=4GB -c checkpoint_timeout=10min
  restart: always
  volumes:
    - swarmica_postgres_data:/var/lib/postgresql/data:Z
    - swarmica_postgres_data_backups:/backups:z
    - ./pg_hba.conf:/var/lib/postgresql/data/pg_hba.conf
  env_file:
    - .env
  ports:
    - "54321:5432"

ATTENTION! For replication on another server, you need to make the port 5432 of the postgres container accessible on the server. This increases the server's vulnerability to external attacks, so it is recommended to take all possible security measures, such as:

Changing the port address (in the example above - 54321)
Limiting access to the port using the server's firewall
Limiting access to the port using an external firewall / NAT / WAF / NGFW

Restart the services:

cd /root/swarmica
docker compose down
docker compose up -d

Enable replication for the account you will use on the replica:

docker exec -it swarmica-postgres psql -U ... ...
CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'replicator_insecure_password';
SELECT pg_create_physical_replication_slot('replication_slot');

ATTENTION! Be sure to replace replicator and replicator_insecure_password with your own, more secure username and password

Configuring the Replica

Here is an example of configuring a replica inside a Docker container on another physical / virtual server; you can apply similar settings in any Postgres installation.

Install docker.io and docker-compose-v2 packages:

apt-get update
apt-get install docker.io docker-compose-v2

Create a folder for configuration files:

mkdir replica
cd replica

Create a .env file with Postgres connection parameters:

# Master host connection configuration
POSTGRES_MASTER_HOST=my-swarmica-host.tld
POSTGRES_MASTER_PORT=54321
POSTGRES_MASTER_DB=swarmica_app
POSTGRES_MASTER_USER=replicator
POSTGRES_MASTER_PASSWORD=replicator_insecure_password

# Replica host configuration
POSTGRES_HOST=postgres
POSTGRES_USER=replicator
POSTGRES_PASS=repplicator_insecure_password
POSTGRES_PORT=5432

Create a docker-compose.yml file with the following content:

vou