搭建流复制集群
目录
- 获取镜像
- 主节点配置
- 从节点配置
- 测试
- 开始插件
获取镜像
拉取镜像：docker pull timescale/timescaledb-ha:pg15-all
配置 docker-compose.yaml 文件：
version: '3.9'
services:
# 写节点
postgres-01:
container_name: postgres-01
hostname: postgres-01
image: timescale/timescaledb-ha:pg15-all
privileged: true
environment:
POSTGRES_USER: postgres
POSTGRES_PASSWORD: postgres
POSTGRES_DB: postgres
volumes:
- ./postgres-01:/home/postgres
ports:
- "5001:5432"
networks:
pg-net:
ipv4_address: 10.20.0.5
# 读节点一
postgres-02:
container_name: postgres-02
hostname: postgres-02
image: timescale/timescaledb-ha:pg15-all
privileged: true
environment:
PGDATA: /home/postgres/db
POSTGRES_USER: postgres
POSTGRES_PASSWORD: postgres
POSTGRES_DB: postgres
volumes:
- ./postgres-02:/home/postgres
ports:
- "5002:5432"
networks:
pg-net:
ipv4_address: 10.20.0.6
# 读节点二
postgres-03:
container_name: postgres-03
hostname: postgres-03
image: timescale/timescaledb-ha:pg15-all
privileged: true
environment:
PGDATA: /home/postgres/db
POSTGRES_USER: postgres
POSTGRES_PASSWORD: postgres
POSTGRES_DB: postgres
volumes:
- ./postgres-03:/home/postgres
ports:
- "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-02
hostname: postgres-02
image: timescale/timescaledb-ha:pg15-all
privileged: true
environment:
# 配置数据目录
PGDATA: /home/postgres/db
POSTGRES_USER: postgres
POSTGRES_PASSWORD: postgres
POSTGRES_DB: postgres
volumes:
- ./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-02
hostname: postgres-02
image: timescale/timescaledb-ha:pg15-all
privileged: true
environment:
# 配置数据目录
#PGDATA: /home/postgres/db
POSTGRES_USER: postgres
POSTGRES_PASSWORD: postgres
POSTGRES_DB: postgres
volumes:
- ./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+00
469 | 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;
