dbulashev/postgres-replication repository overview

⁠Docker Swarm Stack for Physical Replication of PostgreSQL and monitoring

Clone github repository⁠:

git clone [email protected]:dbulashev/physical-replication.git

⁠Database Stack

⁠The PostgreSql image⁠ is based on the official Postgres⁠ image with the following additions:

• Configurations for physical replication;
• Extension pg_stat_statements⁠ (dependency of exporter pg_scv⁠);
• Extension pg_buffercache⁠ (dependency of exporter pg_scv);
• Extension pg_profile⁠;
• Extension dblink⁠ (dependency of the extension pg_profile);
• Extension pgstattuple⁠ (dependency of the extension pg_csv);
• Extension pg_stat_kcache⁠ (dependency of the extension pg_profile);
• cron (for periodic sampling by the pg_profile extension, backup target purposes);
• rsyslogd (for collecting logs from cron and exporting to /dev/stdout in the container).

Environment variables of the image:

• DB_ROLE can take values primary or secondary;
• PRIMARY_HOST specifies the service name with the primary role;
• REPLICA_SLOT the name of the replication slot, default replica;
• other variables, from the base image, are described in the documentation⁠.

Scripts for managing the stack:

• db0[12]_replication_status.sh - status of slots and replication;
• exec_db0[12].sh - executing a command in the container of services db01, db02;
• exec_manage_db01.sh - executing a command in the manage_db01 container;
• logs_db0[12].sh - logs of services db01, db02.

Building the image:

• images/postgres - image dbulashev/postgres-replication⁠ for the database stack.

⁠Preparing to run the database stack

Specify the variables POSTGRES_PASSWORD and ICU_LOCALE in the .env file.

Create a swarm on two or three nodes. Add SSH key authorization.

Assign labels db-host-01 and db-host-02 on different nodes:

docker node update --label-add db-host-01=true NODE_1
docker node update --label-add db-host-02=true NODE_2

On the node labeled db-host-01 create the pgdata-db01 directory:

mkdir /docker-compose/pgdata-db01

On the node labeled db-host-02 create the pgdata-db02 directory:

mkdir /docker-compose/pgdata-db02

Deploy the stack with the following command:

./stack-deploy.sh

The node labeled db-host-01 will act as the primary, and the node labeled db-host-02 will act as the secondary.

To switch roles, that is, to make the db02 service primary and the db01 service secondary, execute the following two commands sequentially:

./promote-db02.sh
./swithover_db01.sh

⁠Monitoring Stack

Event notifications are sent to the Telegram chat. Grafana is accessible on port 3999.

• Grafana;
• VictoriaMetrics;
• vmagent (metrics collection);
• Alertmanager (sending notifications to Telegram chat);
• pg_scv agent;
• pg_exporter agent.

Alerts:

• Awesome Prometheus alerts / postgres-exporter⁠;
• a set by VictoriaMetrics (alerts-vmagent⁠, alerts-vmalert⁠, alerts-vmhealth⁠, alerts-vmsingle⁠);
• Other alerts based on postgres-exporter metrics and the book Lesovsky A. V. Monitoring PostgreSQL⁠.

Dashboards:

• Lock tree based on the query postgres.ai Lock tree⁠;
• Consolidated report⁠ by Data Erget based on pg_stat_statements;
• Using pgSCV metrics based on pgSCV: PostgreSQL⁠, postgresql-monitoring-book/unofficial/dashboards⁠;
• PostgreSQL Exporter⁠;
• pg_profile io/summary/visualization/waits dashboards v4.3⁠;
• VictoriaMetrics - vmalert⁠;
• VictoriaMetrics - vmagent⁠;
• VictoriaMetrics - single-node⁠.

⁠Deploying Monitoring Stack

Specify the GRAFANA_PASSWORD variable in the .env file. Specify the bot token (bot_token) and Telegram chat ID (chat_id) in the alertmanager/alertmanager.yml file.

Deploy the stack using the command:

./stack-deploy-monitoring.sh

⁠Cron Stack

Used for sampling by the pg_profile extension (every 30 minutes).

Deploy the stack using the command:

./stack-deploy-cron.sh