Linux

使用Docker部署Postgresql集群

一、系统环境

服务器IP操作系统Docker
192.168.2.105Rocky10已安装
192.168.2.106Rocky10已安装

二、部署Postgresql

2.1、基础环境准备

在两台服务器上创建文件夹，用于存放数据，并下载数据库镜像

1
2
3
4

mkdir -p /opt/postgres/data 
cd /opt/postgres
#下载镜像,因本文为部署zabbix，使用带timescale的数据库
docker pull timescale/timescaledb:latest-pg16

2.2、启动主库

在192.168.2.104的/opt/postgres文件夹中编辑docker-compose.yml

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

services:
  postgres:
    image: timescale/timescaledb:latest-pg16
    container_name: postgres_primary
    restart: always
    network_mode: "host" # 为了极致性能和网络直通，推荐使用 host 模式
    environment:
      - POSTGRES_USER=zabbix
      - POSTGRES_PASSWORD=zabbix_pwd # 请替换为强密码
      - POSTGRES_DB=zabbix
    volumes:
      - /opt/postgres/data:/var/lib/postgresql/data

启动该docker文件,然后检查/opt/postgres/data是否已经写入，docker进程完成执行

1

docker compose up -d

在数据库中创建用于同步的账号

1

docker exec -it postgres_primary psql -U zabbix -d zabbix -c "CREATE ROLE repl_user WITH REPLICATION PASSWORD 'repl_pwd' LOGIN;"

修改配置文件，根据情况进行修改

1
2
3
4

echo "wal_level = replica" >> /opt/postgres/data/postgresql.conf
echo "max_wal_senders = 10" >> /opt/postgres/data/postgresql.conf
echo "wal_keep_size = 1024" >> /opt/postgres/data/postgresql.conf # 防止主库 WAL 清理过快导致从库断开
echo "listen_addresses = '*'" >> /opt/postgres/data/postgresql.conf

编辑pg_hba文件，根据情况允许接入

1
2
3
4
5
6

echo "host replication repl_user 192.168.2.106/32 md5" >> /opt/postgres/data/pg_hba.conf
echo "host    zabbix          zabbix          10.244.0.0/16           scram-sha-256" >> /opt/postgres/data/pg_hba.conf
echo "host    zabbix          zabbix          10.96.0.0/12            scram-sha-256" >> /opt/postgres/data/pg_hba.conf
echo "host    zabbix          zabbix          192.168.2.0/24          scram-sha-256" >> /opt/postgres/data/pg_hba.conf
#重启服务，使其生效
docker restart postgres_primary

2.3、启动从库

在192.168.2.106中执行

1
2
3
4
5

cd /opt/postgres
# 临时放开目录权限，避免 docker 写入失败 
chmod 777 /opt/postgres/data
# 执行备份 (会提示输入密码 repl_pwd) 
docker run --rm -it -v /opt/postgres/data:/var/lib/postgresql/data \ timescale/timescaledb:latest-pg16 \ pg_basebackup -h 192.168.2.105 -U repl_user -D /var/lib/postgresql/data -Fp -Xs -P -R

(注：-R 参数非常重要，它会自动在从库生成 standby.signal 文件，并把主库的连接信息写进配置，标识这是一个从库。)

在192.168.2.105上执行,用来查看文件的uid信息

1

ls -ln /opt/postgres/data

根据返回结果在192.168.2.106上执行

1

chown -R 70:70 /opt/postgres/data # 根据实际 UID 调整

创建docker-compose.yml文件

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

services:
  postgres:
    image: timescale/timescaledb:latest-pg16
    container_name: postgres_replica
    restart: always
    network_mode: "host"
    environment:
      - POSTGRES_USER=zabbix
      - POSTGRES_PASSWORD=zabbix_pwd
      - POSTGRES_DB=zabbix
    volumes:
      - /opt/postgres/data:/var/lib/postgresql/data

启动从库

1

docker-compose up -d

2.4、查询状态

在主库192.168.2.105上执如下命令查看系统状态

1

docker exec -it postgres_primary psql -U zabbix -d postgres -x -c "SELECT * FROM pg_stat_replication;"

查看返回结果

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

-[ RECORD 1 ]----+------------------------------
pid              | 127
usesysid         | 18598
usename          | repl_user
application_name | walreceiver
client_addr      | 192.168.2.106
client_hostname  | 
client_port      | 47026
backend_start    | 2026-03-25 09:44:26.846355+00
backend_xmin     | 
state            | streaming
sent_lsn         | 0/A6C21D0
write_lsn        | 0/A6C21D0
flush_lsn        | 0/A6C21D0
replay_lsn       | 0/A6C21D0
write_lag        | 
flush_lag        | 
replay_lag       | 
sync_priority    | 0
sync_state       | async
reply_time       | 2026-03-25 12:46:15.517058+00

如果下列状态正常，证明复制没有问题

  • state | streaming：流复制状态正常建立，正在实时同步。
  • sent_lsn 到 replay_lsn：这四个 LSN（日志序列号）的值完全一致（都是 0/A6C21D0），这意味着主从之间当前是零延迟 (Zero Lag)，数据完全一致。
  • sync_state | async：异步复制，这是局域网内性价比最高的模式，不会因为从库的网络抖动阻塞主库的写入。

三、导入Zabbix初始数据

3.1、导入数据

我们采用的是zabbix/zabbix-server-pgsql:alpine-7.0-latestj镜像，在主库192.168.2.105执行

1
2
3
4
5
6
7
8
9

docker run -d --name temp_zabbix zabbix/zabbix-server-pgsql:alpine-7.0-latest
mkdir /opt/postgres/zabbix-sql
docker cp temp_zabbix:/usr/share/doc/zabbix-server-postgresql/create.sql.gz  /opt/postgres/zabbix-sql
docker cp temp_zabbix:/usr/share/doc/zabbix-server-postgresql/timescaledb.sql /opt/postgres/zabbix-sql
dnf install gunzip
cd /opt/postgres/zabbix-sql
gunzip create.sql.tz
docker stop temp_zabbix
docker rm temp_zabbix

我们需要先在数据库中启动timescale

1
2
3
4

docker exec -it postgres_primary psql -U zabbix -d zabbix -c "CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;"
docker exec -i postgres_primary psql -U zabbix -d zabbix < create.sql
#执行之后，会看到在创建表格，预计1-3分钟
 docker exec -i postgres_primary psql -U zabbix -d zabbix < timescaledb.sql

执行之后大概有如下输出,表示启动完成

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

CREATE FUNCTION
NOTICE:  function base36_decode(pg_catalog.varchar) does not exist, skipping
DROP FUNCTION
NOTICE:  PostgreSQL version 16.11 is valid
NOTICE:  TimescaleDB extension is detected
NOTICE:  TimescaleDB version 2.25.2 is valid
WARNING:  column type "character varying" used for "source" does not follow best practices
HINT:  Use datatype TEXT instead.
WARNING:  column type "character varying" used for "value" does not follow best practices
HINT:  Use datatype TEXT instead.
WARNING:  column type "character varying" used for "auditid" does not follow best practices
HINT:  Use datatype TEXT instead.
WARNING:  column type "character varying" used for "username" does not follow best practices
HINT:  Use datatype TEXT instead.
WARNING:  column type "character varying" used for "ip" does not follow best practices
HINT:  Use datatype TEXT instead.
WARNING:  column type "character varying" used for "resource_cuid" does not follow best practices
HINT:  Use datatype TEXT instead.
WARNING:  column type "character varying" used for "resourcename" does not follow best practices
HINT:  Use datatype TEXT instead.
WARNING:  column type "character varying" used for "recordsetid" does not follow best practices
HINT:  Use datatype TEXT instead.
NOTICE:  TimescaleDB is configured successfully
DO

3.2、检查同步情况

在主库执行

1

docker exec -it postgres_primary psql -U zabbix -d zabbix -c "\dt"

看到输出结果

1
2
3
4
5
6
7
8

 public | users                      | table | zabbix
 public | users_groups               | table | zabbix
 public | usrgrp                     | table | zabbix
 public | valuemap                   | table | zabbix
 public | valuemap_mapping           | table | zabbix
 public | widget                     | table | zabbix
 public | widget_field               | table | zabbix
(203 rows)

在到从库执行

1

docker exec -it postgres_replica psql -U zabbix -d zabbix -c "\dt"

查看输出结果

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

 public | userdirectory_ldap         | table | zabbix
public | userdirectory_media        | table | zabbix
public | userdirectory_saml         | table | zabbix
public | userdirectory_usrgrp       | table | zabbix
public | users                      | table | zabbix
public | users_groups               | table | zabbix
public | usrgrp                     | table | zabbix
public | valuemap                   | table | zabbix
public | valuemap_mapping           | table | zabbix
public | widget                     | table | zabbix
public | widget_field               | table | zabbix
(203 rows)

输出结果一致，表明复制成功，等待下一步在k8s集群安装zabbix应用。

comments powered by Disqus