搭建流复制集群
目录
- 获取镜像
- 主节点配置
- 从节点配置
- 测试
- 开始插件
获取镜像
拉取镜像：docker pull timescale/timescaledb-ha:pg15-all
配置 docker-compose.yaml 文件：
version: '3.9'
services:# 写节点postgres-01:container_name: postgres-01hostname: postgres-01image: timescale/timescaledb-ha:pg15-allprivileged: trueenvironment:POSTGRES_USER: postgresPOSTGRES_PASSWORD: postgresPOSTGRES_DB: postgresvolumes:- ./postgres-01:/home/postgresports:- "5001:5432"networks:pg-net:ipv4_address: 10.20.0.5# 读节点一 postgres-02:container_name: postgres-02hostname: postgres-02image: timescale/timescaledb-ha:pg15-allprivileged: trueenvironment:PGDATA: /home/postgres/dbPOSTGRES_USER: postgresPOSTGRES_PASSWORD: postgresPOSTGRES_DB: postgresvolumes:- ./postgres-02:/home/postgresports:- "5002:5432"networks:pg-net:ipv4_address: 10.20.0.6# 读节点二 postgres-03:container_name: postgres-03hostname: postgres-03image: timescale/timescaledb-ha:pg15-allprivileged: trueenvironment:PGDATA: /home/postgres/dbPOSTGRES_USER: postgresPOSTGRES_PASSWORD: postgresPOSTGRES_DB: postgresvolumes:- ./postgres-03:/home/postgresports:- "5003:5432"networks:pg-net:ipv4_address: 10.20.0.7
networks:pg-net:ipam:config:- subnet: 10.20.0.0/16
目录结构如下：
➜ postgres ✗ ls -lh
total 8
-rw-r--r--@ 1 yuelong staff 1.3K Sep 23 17:03 docker-compose.yaml
drwxr-xr-x@ 4 yuelong staff 128B Sep 23 14:34 postgres-01
drwxr-xr-x@ 5 yuelong staff 160B Sep 23 14:52 postgres-02
drwxr-xr-x@ 3 yuelong staff 96B Sep 23 14:06 postgres-03
这里最好将 postgres-01、postgres-02和 postgres-03 设置为：chmod 777 -R postgres-01。接着启动容器：docker compose up -d。
主节点配置
进入主节点的容器内 ：docker exec -it postgres-01 sh
如果需要 root 的权限可以执行：
docker exec -it -u root postgres-01 sh
进入容器内的/home/postgres/pgdata/data目录，修改 pg_hba.conf：
# TYPE DATABASE USER ADDRESS METHOD# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all trust
host replication all 127.0.0.1/32 trust
host replication all ::1/128 trust
# 配置允许复制客户端
host replication all 0.0.0.0/0 md5
host all all all scram-sha-256接着配置 postgresql.conf：
#设置以下
listen_addresses = '*' # 允许远程连接
hot_standby = on # 打开热备
wal_level = replica # 设置 WAL 日志级别为 replica
max_wal_senders = 3 # 允许的 WAL 发送者数量，根据需要进行调整接着退出容器，重启容器：docker compose restart postgres-01。
然后在进入容器内，进入 psql，创建流复制的账号：
$ psql
psql (15.8 (Ubuntu 15.8-1.pgdg22.04+1))
Type "help" for help.postgres=# create role replica with login replication encrypted password 'replica';
create role好了，这样主节点就配置好了。
从节点配置
接下来配置从节点，这里我们先设置数据目录为：
version: '3.9'
services:# 写节点......# 读节点一 postgres-02:container_name: postgres-02hostname: postgres-02image: timescale/timescaledb-ha:pg15-allprivileged: trueenvironment:# 配置数据目录PGDATA: /home/postgres/dbPOSTGRES_USER: postgresPOSTGRES_PASSWORD: postgresPOSTGRES_DB: postgresvolumes:- ./postgres-02:/home/postgres这个时候我们需要进入 postgres-02 的容器内：docker exec -it postgres-02 sh，同步主节点的数据。
pg_basebackup -Fp --progress -D /home/postgres/pgdata/data -R -h 10.20.0.5 -p 5432 -U replica --password
这里的/home/postgres/pgdata/data是默认数据目录，回车输入 replica 的密码。
Password:
28692/28692 kB (100%), 1/1 tablespace
同步完成之后在/home/postgres/pgdata/data中会出现主节点的数据，这里需要注意postgresql.auto.conf和standby.signal。这里的postgresql.auto.conf会记录主节点的信息：
$ cat postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
primary_conninfo = 'user=replica password=replica channel_binding=prefer host=10.20.0.5 port=5432 sslmode=prefer sslcompression=0 sslcertmode=allow sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable'
接着退出容器，修改 docker-compose.yaml的 postgres-02容器的数据目录注释掉：
version: '3.9'
services:# 写节点......# 读节点一 postgres-02:container_name: postgres-02hostname: postgres-02image: timescale/timescaledb-ha:pg15-allprivileged: trueenvironment:# 配置数据目录#PGDATA: /home/postgres/dbPOSTGRES_USER: postgresPOSTGRES_PASSWORD: postgresPOSTGRES_DB: postgresvolumes:- ./postgres-02:/home/postgres
然后删除掉 postgres-02容器，重启启动：
docker compose down postgres-02
docekr compose up -d postgres-02
另一个节点也是一样操作。
测试
在主节点的 psql 中执行：SELECT * FROM pg_stat_replication;
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
-----+----------+---------+------------------+--------------+-----------------+-------------+-------------------------------+--------------+-----------+-----------+-----------+-----------+------------+-----------+-----------+------------+---------------+------------+-------------------------------400 | 20845 | replica | walreceiver | 10.20.0.6 | | 58166 | 2024-09-23 10:36:09.844301+00 | | streaming | 0/7018420 | 0/7018420 | 0/7018420 | 0/7018420 | | | | 0 | async | 2024-09-23 11:57:15.271431+00469 | 20845 | replica | walreceiver | 10.20.0.7 | | 42680 | 2024-09-23 10:41:39.307304+00 | | streaming | 0/7018420 | 0/7018420 | 0/7018420 | 0/7018420 | | | | 0 | async | 2024-09-23 11:57:15.29262+00
(2 rows)
这就配置成功了。
开始插件
我们这里使用的timescale/timescaledb-ha:pg15-all镜像里面包含了，时序和空间插件，这里连接是数据库，执行SELECT * FROM pg_extension;查看开启的插件。
postgres=# SELECT * FROM pg_extension;oid | extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
-------+---------------------+----------+--------------+----------------+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------13542 | plpgsql | 10 | 11 | f | 1.0 | | 17830 | timescaledb_toolkit | 10 | 2200 | f | 1.18.0 | | 16384 | timescaledb | 10 | 2200 | f | 2.16.1 | {16403,16404,16426,16440,16439,16459,16458,16474,16473,16499,16515,16516,16533,16532,16552,16553,16609,16622,16649,16662,16672,16682,16686,16702,16712,16727,16736,16735} | {"","WHERE id >= 1","","","","","","","","","","","","","","WHERE id >= 1000"," WHERE key <> 'uuid' ","","","","","","","","","","",""}
(3 rows)
这里需要我们先创建一个数据库：
create database db1; # 创建库
\c db1; # 连接库
CREATE EXTENSION postgis; # 开启插件
CREATE EXTENSION postgis_topology;
CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION postgis_tiger_geocoder;
