Docker configures the master-slave environment of PostgreSQL13
preface
PostgreSQL database supports a variety of replication solutions to build high availability, scalable and fault-tolerant applications, one of which is pre write log (WAL) delivery. This solution allows the use of file based log shipping or streaming replication, or, where possible, a combination of the two methods to implement a standby server.
By default, stream replication is asynchronous, where data is written to the standby server after the transaction is committed to the primary server. This means that there is a small delay between committing transactions on the primary server and making changes visible on the standby server. One disadvantage of this approach is that if the primary server crashes, any uncommitted transactions may not be replicated, which may lead to data loss.
This experiment will install PostgreSQL13 on Docker and configure the master-slave environment. In order to simplify the demonstration environment, only one server is used to demonstrate, which is distinguished by different ports.
Installation configuration
1. Create test network
Create a docker bridge network for testing:
# 1. Create test network docker network create --subnet=172.18.0.0/24 dockernetwork # 2. View the network docker network ls NETWORK ID NAME DRIVER SCOPE 8c8a87e2c6e0 bridge bridge local a8e4916d92c2 dockernetwork bridge local 92951335914e host host local 2e991e7fd5a3 none null local
The planned IP ports of the master-slave library are as follows:
Main library: 172.18.0.101:5432
Slave Library: 172.18.0.102:5433
2. Pull the postgres13 image
docker pull postgres
3. Create data directory
mkdir -p /data/psql/master mkdir -p /data/psql/slave mkdir -p /data/psql/repl chown 999:999 /data/psql/master chown 999:999 /data/psql/slave chown 999:999 /data/psql/repl
4. Run the master container
docker run -d \ --network dockernetwork --ip 172.18.0.101 -p 5432:5432 \ --name master -h master \ -e "POSTGRES_DB=postgres" \ -e "POSTGRES_USER=postgres" \ -e "POSTGRES_PASSWORD=postgres" \ -v /data/psql/master:/var/lib/postgresql/data \ postgres
View container:
docker ps -a -f network=dockernetwork --format "table {{.Names}}\t{{.Image}}\t{{.RunningFor}}\t{{.Status}}\t{{.Networks}}\t{{.Ports}}"
NAMES IMAGE CREATED STATUS NETWORKS PORTS
master postgres 48 seconds ago Up 46 seconds dockernetwork 0.0.0.0:5432->5432/tcp, :::5432->5432/tcp
5. Create a special account for master-slave stream replication
# 1. Enter the container docker exec -it master bash # 2. Connect to PostgreSQL psql -U postgres # 3. Create user rules CREATE ROLE repuser WITH LOGIN REPLICATION CONNECTION LIMIT 5 PASSWORD '123456'; # Username repuser; Maximum number of links: 5; Password: 123456 # 4. View rules \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
repuser | Replication +| {}
| 5 connections |
6. Modify the master configuration file
# 1. Enter the master folder cd /data/psql/master # 2. Add rules at the end echo "host replication repuser 172.18.0.102/24 md5" >> pg_hba.conf
Modify the postgresql.conf configuration file, find the following lines, uncomment and modify the configuration:
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 = '*'
For more details of parameters, please refer to: 19.6. Copy (postgres.cn)
7. Restart the master container
#Using pg_ctl stop stops the database safely docker exec -it -u postgres master pg_ctl stop docker start master
8. Create a slave container
docker run -d \ --network dockernetwork --ip 172.18.0.102 -p 5433:5432 \ --name slave -h slave \ -e "POSTGRES_DB=postgres" \ -e "POSTGRES_USER=postgres" \ -e "POSTGRES_PASSWORD=postgres" \ -v /data/psql/slave:/var/lib/postgresql/data \ -v /data/psql/repl:/var/lib/postgresql/repl \ postgres
# View container
docker ps -a -f network=dockernetwork --format "table {{.Names}}\t{{.Image}}\t{{.RunningFor}}\t{{.Status}}\t{{.Networks}}\t{{.Ports}}"
NAMES IMAGE CREATED STATUS NETWORKS PORTS
slave postgres 18 seconds ago Up 15 seconds dockernetwork 0.0.0.0:5433->5432/tcp, :::5433->5432/tcp
master postgres 2 hours ago Up 2 hours dockernetwork 0.0.0.0:5432->5432/tcp, :::5432->5432/tcp
9. Synchronize data
# 1. Enter the container docker exec -it -u postgres slave /bin/bash # 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 172.18.0.101 -p 5432 -U repuser pg_basebackup: initiating base backup, waiting for checkpoint to complete pg_basebackup: checkpoint completed pg_basebackup: write-ahead log start point: 0/2000028 on timeline 1 pg_basebackup: starting background WAL receiver pg_basebackup: created temporary replication slot "pg_basebackup_154" 24264/24264 kB (100%), 1/1 tablespace pg_basebackup: write-ahead log end point: 0/2000138 pg_basebackup: waiting for background process to finish streaming ... pg_basebackup: syncing data to disk ... pg_basebackup: renaming backup_manifest.tmp to backup_manifest pg_basebackup: base backup completed # 3. Exit the container after the backup is completed exit
10. Rebuild the slave container
Through the initial backup in the previous step, you can now rebuild the slave container using the data in / data/psql/repl. First delete the slave directory, and then change the repl directory to slave, which is the data directory of the slave Library:
# 1. Delete container docker rm -f slave # 2. Delete the original folder and rename repl to slave cd /data/psql/ rm -rf slave mv repl slave cd /data/psql/slave # 3. View configuration information # postgresql.auto.conf will contain the information required for replication cat postgresql.auto.conf primary_conninfo = 'user=repuser password=123456 channel_binding=prefer host=172.18.0.101 port=5432 sslmode=prefer sslcompression=0 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any'
Rebuild the slave container:
docker run -d \ --network dockernetwork --ip 172.18.0.102 -p 5433:5432 \ --name slave -h slave \ -e "POSTGRES_DB=postgres" \ -e "POSTGRES_USER=postgres" \ -e "POSTGRES_PASSWORD=postgres" \ -v /data/psql/slave:/var/lib/postgresql/data \ postgres
# View container
docker ps -a -f network=dockernetwork --format "table {{.Names}}\t{{.Image}}\t{{.RunningFor}}\t{{.Status}}\t{{.Networks}}\t{{.Ports}}"
NAMES IMAGE CREATED STATUS NETWORKS PORTS
slave postgres 23 seconds ago Up 21 seconds dockernetwork 0.0.0.0:5433->5432/tcp, :::5433->5432/tcp
master postgres 2 hours ago Up 2 hours dockernetwork 0.0.0.0:5432->5432/tcp, :::5432->5432/tcp
11. View master-slave replication information
ps -aux | grep postgres Main library process: postgres: walsender repuser 172.18.0.1(52678) streaming 0/3000148 Process from library: postgres: walreceiver streaming 0/3000148
Verify master-slave configuration
Host generated data
# Enter the master container and switch to the postgres user docker exec -it master bash psql -U postgres
-- Query replication information select * from pg_stat_replication; pid | usesysid | usename | application_name | client_addr | client_hostname... 170 16384 repuser walreceiver 172.18.0.1 52678 2021-09-29 05:57:30.471391+00...
-- Create test database
CREATE DATABASE test;
-- View all databases
\list
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+------------+------------+-----------------------
postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
test | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
(4 rows)
-- Switch database
\c test
-- Create test table
CREATE TABLE test (
"id" int4 NOT NULL,
"value" varchar(255),
PRIMARY KEY ("id")
);
-- View created tables
\dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | test | table | postgres
(1 row)
-- Insert ten pieces of data into the table insert into test select generate_series(1,10),md5(random()); -- View all data select * from test; id | value ----+---------------------------------- 1 | cfcd208495d565ef66e7dff9f98764da 2 | cfcd208495d565ef66e7dff9f98764da 3 | cfcd208495d565ef66e7dff9f98764da 4 | cfcd208495d565ef66e7dff9f98764da 5 | cfcd208495d565ef66e7dff9f98764da 6 | cfcd208495d565ef66e7dff9f98764da 7 | cfcd208495d565ef66e7dff9f98764da 8 | cfcd208495d565ef66e7dff9f98764da 9 | cfcd208495d565ef66e7dff9f98764da 10 | cfcd208495d565ef66e7dff9f98764da (10 rows)
Slave view data
# Enter slave container docker exec -it slave bash psql -U postgres
-- view the database
\d
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+------------+------------+-----------------------
postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
test | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
(4 rows)
-- View table
\c test
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | test | table | postgres
(1 row)
-- View all data select * from test; id | value ----+---------------------------------- 1 | cfcd208495d565ef66e7dff9f98764da 2 | cfcd208495d565ef66e7dff9f98764da 3 | cfcd208495d565ef66e7dff9f98764da 4 | cfcd208495d565ef66e7dff9f98764da 5 | cfcd208495d565ef66e7dff9f98764da 6 | cfcd208495d565ef66e7dff9f98764da 7 | cfcd208495d565ef66e7dff9f98764da 8 | cfcd208495d565ef66e7dff9f98764da 9 | cfcd208495d565ef66e7dff9f98764da 10 | cfcd208495d565ef66e7dff9f98764da (10 rows)
It can be found that the master-slave data always indicates that our master-slave configuration is successful!