Postgresql Replication and Load Balancing with Docker
Tools
- Postgres 13
- HAProxy
Design
Manual Installation
-
Run Postgres
docker-compose up -d
-
Create User For Replication
# 1. Enter the container docker exec -it database-master /bin/sh # 2. Connect to PostgreSQL psql -U postgres # 3. Create user replica postgres-# CREATE ROLE repuser1 WITH LOGIN REPLICATION CONNECTION LIMIT 5 PASSWORD '123456'; Username repuser1; Maximum number of links: 5; Password: 123456 postgres-# CREATE ROLE repuser1 WITH LOGIN REPLICATION CONNECTION LIMIT 5 PASSWORD '123456'; Username repuser1; Maximum number of links: 5; Password: 123456 # 4. View rules postgres-# \du List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} repuser1 | Replication +| {} | 5 connections | repuser2 | Replication +| {} | 5 connections |
-
Modify the master configuration file
pg_hba.conf
host replication repuser1 10.0.3.201/24 md5 host replication repuser2 10.0.3.202/24 md5
postgresql.conf
archive_mode = on # Enable Archive Mode archive_command = '/bin/date' # Set archiving behavior # The sum of the number of concurrent connections from the slave to the host max_wal_senders = 10 # Specifies that if the backup server needs to obtain log segment files for stream replication, pg_ The minimum size of past log file segments that can be retained in the wal directory wal_keep_size = 16 # Specify a list of backup servers that support synchronous replication synchronous_standby_names = '*'
-
Craete basebackup on slave
Slave 1
# 1. Enter the container slave 1 docker exec -it database-slave-1 /bin/sh # 2. Back up the host data to the repl folder. Here, enter the password set above: 123456 pg_basebackup -R -D /var/lib/postgresql/repl -Fp -Xs -v -P -h 10.0.3.200 -p 5432 -U repuser1
Slave 2
# 1. Enter the container slave 2 docker exec -it database-slave-2 /bin/sh # 2. Back up the host data to the repl folder. Here, enter the password set above: 123456 pg_basebackup -R -D /var/lib/postgresql/repl -Fp -Xs -v -P -h 10.0.3.200 -p 5432 -U repuser2
-
Reconfig docker volumes container slave
Slave 1
volumes: - ./data/slave1:/var/lib/postgresql/data - ./data/repl1:/var/lib/postgresql/repl
volumes: - ./data/repl1:/var/lib/postgresql/data
Slave 2
volumes: - ./data/slave2:/var/lib/postgresql/data - ./data/repl2:/var/lib/postgresql/repl
volumes: - ./data/repl2:/var/lib/postgresql/data
After modif run command
docker-compose up -d
, if not work restart container slave manual -
Status database replication
docker exec -it database-master /bin/sh # su postgres [email protected]:/$ psql psql (13.8 (Debian 13.8-1.pgdg110+1)) Type "help" for help. postgres=# SELECT * FROM pg_stat_replication; pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_lsn | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state | reply_time -----+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+--------------+-----------+-----------+-----------+-----------+------------+-----------+-----------+------------+---------------+------------+------------------------------- 36 | 16385 | repuser | walreceiver | 10.0.3.201 | | 34324 | 2022-08-17 11:41:19.455511+00 | | streaming | 0/A0000D8 | 0/A0000D8 | 0/A0000D8 | 0/A0000D8 | | | | 1 | sync | 2022-08-17 11:42:14.418949+00 37 | 16392 | repuser2 | walreceiver | 10.0.3.202 | | 60442 | 2022-08-17 11:41:24.210798+00 | | streaming | 0/A0000D8 | 0/A0000D8 | 0/A0000D8 | 0/A0000D8 | | | | 1 | potential | 2022-08-17 11:42:14.418667+00 (2 rows) postgres=#
-
Example testing database with Golang
Setup
# 1. Install Golang https://go.dev/doc/install # 2. Init module go mod tidy
Try-1 Run apps
go run example_apps.go Create Data : {2 Test} IP DB Master: 10.0.3.200 Read Data : {2 Test} IP DB Slave : 10.0.3.201
Try-2 Run apps
go run example_apps.go Create Data : {3 Test} IP DB Master: 10.0.3.200 Read Data : {3 Test} IP DB Slave : 10.0.3.202
Slave database load balancing is working.
Contact
https://www.linkedin.com/in/aji-indra-jaya
License
MIT