一、实验环境
| 虚拟机名 | IP | 身份 | 简称 |
|---|---|---|---|
| keep-postgres12-node1 | 192.168.122.87 | 主节点 | node1 |
| keep-postgres12-node2 | 192.168.122.89 | 备节点 | node2 |
| keep-postgres12-node3 | 192.168.122.90 | 备节点 | node3 |
二、安装数据库
源码包方式（主）
1、创建用户
[root@keep-postgres12-node1 ~]# groupadd postgres
[root@keep-postgres12-node1 ~]# useradd -g postgres postgres -m -s /bin/bash
[root@keep-postgres12-node1 ~]# echo "Database@123" | passwd --stdin postgres
2、修改服务器内核信息
[root@keep-postgres12-node1 ~]# echo '# 最大共享内存段大小 (默认值68719476736)
kernel.shmmax = 68719476736
# 可以使用的共享内存的总量 (默认值4294967296)
kernel.shmall = 4294967296
# 整个系统共享内存段的最大数目
kernel.shmmni = 4096
# 每个信号对象集的最大信号对象数
kernel.sem = 50100 64128000 50100 1280
# 文件句柄的最大数量
fs.file-max = 7672460
# 应用程序可使用的IPv4端口范围
net.ipv4.ip_local_port_range = 9000 65000
# 套接字接收缓冲区大小的缺省值
net.core.rmem_default = 1048576
# 套接字发送缓冲区大小的缺省值
net.core.wmem_default = 262144
# 套接字发送缓冲区大小的最大值
net.core.wmem_max = 1048576' >> /etc/sysctl.conf# 使参数生效
[root@keep-postgres12-node1 ~]# sysctl -p
3、安装依赖包
[root@keep-postgres12-node1 ~]# yum install gcc gcc-c++ zlib-devel readline-devel perl-ExtUtils-Embed pam-devel openssl openssl-devel cmake libxslt-devel libxml2-devel openldap-devel python-devel tcl tcl-devel bison flex xmlto -y
4、创建数据库目录
[root@keep-postgres12-node1 ~]# mkdir /data
[root@keep-postgres12-node1 ~]# mkdir /data/postgres12.2
[root@keep-postgres12-node1 ~]# chown -R postgres: /data
5、创建环境变量
[root@keep-postgres12-node1 ~]# su - postgres
[postgres@keep-postgres12-node1 ~]$ echo 'export PGPORT=5432
export PG_HOME=/data/postgres12.2
export PATH=$PG_HOME/bin:$PATH
export PGDATA=$PG_HOME/data
export LD_LIBRARY_PATH=$PG_HOME/lib
export LANG=en_US.utf8' >> ~/.bash_profile && source ~/.bash_profile
6、下载源码包，并解压
链接1：https://ftp.postgresql.org/pub/source
链接2：https://www.postgresql.org/ftp/source/
# 安装postgresql-12.2版本
# 下载postgresql-12.2源码包
[postgres@keep-postgres12-node1 ~]$ cd /data
[postgres@keep-postgres12-node1 data]$ wget https://ftp.postgresql.org/pub/source/v12.2/postgresql-12.2.tar.bz2
# 解压源码包
[postgres@keep-postgres12-node1 data]$ tar -xf postgresql-12.2.tar.bz2
7、编译源码包
[postgres@keep-postgres12-node1 postgresql-12.2]$ ./configure --prefix=/data/postgres12.2 --with-pgport=5432 --with-openssl --with-perl \
--with-tcl --with-python --with-pam --without-ldap --with-libxml --with-libxslt \
--enable-thread-safety --with-wal-blocksize=16 --with-blocksize=8
# 包括第三方插件全部编译，包含文档和所有的contirb
[postgres@keep-postgres12-node1 postgresql-12.2]$ gmake world
8、安装数据库
[postgres@keep-postgres12-node1 postgresql-12.2]$ make && make install
9、配置wal日志目录
[postgres@keep-postgres12-node1 postgresql-12.2]$ # 用于保存wal日志
[postgres@keep-postgres12-node1 postgresql-12.2]$ mkdir $PG_HOME/arch
10、初始化数据库
[postgres@keep-postgres12-node1 postgresql-12.2]$ # 创建目录
[postgres@keep-postgres12-node1 postgresql-12.2]$ mkdir $PG_HOME/data
[postgres@keep-postgres12-node1 postgresql-12.2]$ # 初始化数据库集簇
[postgres@keep-postgres12-node1 postgresql-12.2]$ # --data-checksums 主从复制时需要
[postgres@keep-postgres12-node1 postgresql-12.2]$ initdb -D $PGDATA -W --data-checksums
11、启动数据库
[postgres@keep-postgres12-node1 postgresql-12.2]$ pg_ctl -D $PGDATA start
12、创建数据库用户和数据库
[postgres@keep-postgres12-node1 data]$ psql -d postgres -p 5432
postgres=# create database testdb;
postgres=# create user keep with Superuser password 'keep';
13、配置远程连接
# 配置连接
[postgres@keep-postgres12-node1 data]$ cd $PGDATA
[postgres@keep-postgres12-node1 data]$ sed -i "s/#listen_addresses = 'localhost'/listen_addresses = '*'/g" postgresql.conf
[postgres@keep-postgres12-node1 data]$ echo 'host all all 0.0.0.0/0 md5' >> pg_hba.conf
# 重启数据库
[postgres@keep-postgres12-node1 data]$ pg_ctl stop
[postgres@keep-postgres12-node1 data]$ pg_ctl -D $PGDATA start
# 验证远程连接
[postgres@keep-postgres12-node1 data]$ psql -d testdb -U keep -p 5432 -h 192.168.140.96
14、配置数据库日志
[postgres@keep-postgres12-node1 data]$ echo "# Add settings for extensions here
log_destination='csvlog'
logging_collector=on
log_directory='pg_log'
log_filename='postgresql-%Y-%m-%d.log'
log_truncate_on_rotation=off
log_rotation_age=1d
log_rotation_size=0
log_error_verbosity=verbose" >> $PGDATA/postgresql.conf
源码包方式（备 node2/node3）
1、创建用户
[root@keep-postgres12-node2 ~]# groupadd postgres
[root@keep-postgres12-node2 ~]# useradd -g postgres postgres -m -s /bin/bash
[root@keep-postgres12-node2 ~]# echo "Database@123" | passwd --stdin postgres
2、修改服务器内核信息
[root@keep-postgres12-node2 ~]# echo '# 最大共享内存段大小 (默认值68719476736)
kernel.shmmax = 68719476736
# 可以使用的共享内存的总量 (默认值4294967296)
kernel.shmall = 4294967296
# 整个系统共享内存段的最大数目
kernel.shmmni = 4096
# 每个信号对象集的最大信号对象数
kernel.sem = 50100 64128000 50100 1280
# 文件句柄的最大数量
fs.file-max = 7672460
# 应用程序可使用的IPv4端口范围
net.ipv4.ip_local_port_range = 9000 65000
# 套接字接收缓冲区大小的缺省值
net.core.rmem_default = 1048576
# 套接字发送缓冲区大小的缺省值
net.core.wmem_default = 262144
# 套接字发送缓冲区大小的最大值
net.core.wmem_max = 1048576' >> /etc/sysctl.conf# 使参数生效
[root@keep-postgres12-node2 ~]# sysctl -p
3、安装依赖包
[root@keep-postgres12-node2 ~]# yum install gcc gcc-c++ zlib-devel readline-devel perl-ExtUtils-Embed pam-devel openssl openssl-devel cmake libxslt-devel libxml2-devel openldap-devel python-devel tcl tcl-devel bison flex xmlto -y
4、创建数据库目录
[root@keep-postgres12-node2 ~]# mkdir /data
[root@keep-postgres12-node2 ~]# mkdir /data/postgres12.2
[root@keep-postgres12-node2 ~]# chown -R postgres: /data
5、创建环境变量
[root@keep-postgres12-node2 ~]# su - postgres
[postgres@keep-postgres12-node2 ~]$ echo 'export PGPORT=5432
export PG_HOME=/data/postgres12.2
export PATH=$PG_HOME/bin:$PATH
export PGDATA=$PG_HOME/data
export LD_LIBRARY_PATH=$PG_HOME/lib
export LANG=en_US.utf8' >> ~/.bash_profile && source ~/.bash_profile
6、下载源码包，并解压
链接1：https://ftp.postgresql.org/pub/source
链接2：https://www.postgresql.org/ftp/source/
# 安装postgresql-12.2版本
# 下载postgresql-12.2源码包
[postgres@keep-postgres12-node2 ~]$ cd /data
[postgres@keep-postgres12-node2 data]$ wget https://ftp.postgresql.org/pub/source/v12.2/postgresql-12.2.tar.bz2
# 解压源码包
[postgres@keep-postgres12-node2 data]$ tar -xf postgresql-12.2.tar.bz2
7、编译源码包
[postgres@keep-postgres12-node2 postgresql-12.2]$ ./configure --prefix=/data/postgres12.2 --with-pgport=5432 --with-openssl --with-perl \
--with-tcl --with-python --with-pam --without-ldap --with-libxml --with-libxslt \
--enable-thread-safety --with-wal-blocksize=16 --with-blocksize=8
# 包括第三方插件全部编译，包含文档和所有的contirb
[postgres@keep-postgres12-node2 postgresql-12.2]$ gmake world
8、安装数据库
[postgres@keep-postgres12-node2 postgresql-12.2]$ make && make install
9、配置wal日志目录
[postgres@keep-postgres12-node1 postgresql-12.2]$ # 用于保存wal日志
[postgres@keep-postgres12-node1 postgresql-12.2]$ mkdir $PG_HOME/arch
配置互信
1、配置主机名
# keep-postgres12-node1
[root@keep-postgres12-node1 ~]# echo '192.168.122.87 keep-postgres12-node1
192.168.122.88 keep-postgres12-node2
192.168.122.90 keep-postgres12-node3' >> /etc/hosts# keep-postgres12-node2
[root@keep-postgres12-node2 ~]# echo '192.168.122.87 keep-postgres12-node1
192.168.122.88 keep-postgres12-node2
192.168.122.90 keep-postgres12-node3' >> /etc/hosts# keep-postgres12-node3
[root@keep-postgres12-node3 ~]# echo '192.168.122.87 keep-postgres12-node1
192.168.122.88 keep-postgres12-node2
192.168.122.90 keep-postgres12-node3' >> /etc/hosts
2、配置postgres用户互信
# keep-postgres12-node1
[postgres@keep-postgres12-node1 ~]$ ssh-keygen
[postgres@keep-postgres12-node1 ~]$ ssh-copy-id keep-postgres12-node1
[postgres@keep-postgres12-node1 ~]$ ssh-copy-id keep-postgres12-node2
[postgres@keep-postgres12-node1 ~]$ ssh-copy-id keep-postgres12-node3# 验证
[postgres@keep-postgres12-node1 ~]$ ssh keep-postgres12-node1
[postgres@keep-postgres12-node1 ~]$ ssh keep-postgres12-node2
[postgres@keep-postgres12-node1 ~]$ ssh keep-postgres12-node3
# keep-postgres12-node2
[postgres@keep-postgres12-node2 ~]$ ssh-keygen
[postgres@keep-postgres12-node2 ~]$ ssh-copy-id keep-postgres12-node1
[postgres@keep-postgres12-node2 ~]$ ssh-copy-id keep-postgres12-node2
[postgres@keep-postgres12-node2 ~]$ ssh-copy-id keep-postgres12-node3# 验证
[postgres@keep-postgres12-node2 ~]$ ssh keep-postgres12-node1
[postgres@keep-postgres12-node2 ~]$ ssh keep-postgres12-node2
[postgres@keep-postgres12-node2 ~]$ ssh keep-postgres12-node3
# keep-postgres12-node3
[postgres@keep-postgres12-node3 ~]$ ssh-keygen
[postgres@keep-postgres12-node3 ~]$ ssh-copy-id keep-postgres12-node1
[postgres@keep-postgres12-node3 ~]$ ssh-copy-id keep-postgres12-node2
[postgres@keep-postgres12-node3 ~]$ ssh-copy-id keep-postgres12-node3# 验证
[postgres@keep-postgres12-node3 ~]$ ssh keep-postgres12-node1
[postgres@keep-postgres12-node3 ~]$ ssh keep-postgres12-node2
[postgres@keep-postgres12-node3 ~]$ ssh keep-postgres12-node3
三、修改主节点配置
1、创建同步用户
# 数据库需要在线状态
[postgres@keep-postgres12-node1 ~]$ psql -c "create role replrole login replication encrypted password 'ReplRole@123';"
2、配置$PGDATA/pg_hba.conf
[postgres@keep-postgres12-node1 ~]$ echo 'host replication replrole keep-postgres12-node1 trust
host replication replrole keep-postgres12-node2 trust
host replication replrole keep-postgres12-node3 trust
' >> $PGDATA/pg_hba.conf
3、修改数据库参数
[postgres@keep-postgres12-node1 ~]$ echo "wal_level = replica
max_wal_senders=20
wal_keep_segments =64
# 开启归档
archive_mode = on
archive_command = 'cp %p /data/postgres12.2/arch/%f'
restore_command = 'cp /data/postgres12.2/arch/%f %p'
recovery_target_timeline = 'latest'
# full_page_writes是控制是否开启全页写入
full_page_writes = on
# 将每个磁盘页的全部内容写入到WAL
wal_log_hints = on
# 配置同步,此时pg2的优先级比pg3的要高
# 一个节点的sync_state为sync，剩下的是potential
synchronous_standby_names = 'standby_pg2,standby_pg3'
# 默认值为 on
# 可以设置为remote_write，对主库性能有利
# 开启同步模式，此刻需要强同步，数据无法入库
synchronous_commit = on
" >> $PGDATA/postgresql.conf
4、重启数据库
[postgres@keep-postgres12-node1 ~]$ pg_ctl restart
5、synchronous_standby_names参数详解
# 该参数指定流复制中需要同步复制的服务器列表，需要配置standby服务器的名字
# 情况一：（无顺序要求）
# 只有这三台备机会被选为同步备机，即其中一个会被选为活动的同步备机，其余两台将处于备用状态。
synchronous_standby_names = 'standby_pg1,standby_pg2,standby_pg3'# 设置WAL日志强同步至N个节点中的某M个节点
# 情况二：（无顺序要求）
# 只有这三台备机会被选为同步备机，即其中一个会被选为活动的同步备机，其余两台将处于备用状态。
synchronous_standby_names = 'ANY 1 (standby_pg1,standby_pg2,standby_pg3)'# 情况三：（无顺序要求）
# 只有这三台备机会被选为同步备机，即其中两个会被选为活动的同步备机，其余一台将处于备用状态。
synchronous_standby_names = 'ANY 2 (standby_pg1,standby_pg2,standby_pg3)'# 情况四：（按照指定节点的顺序）
# 从括号中列出的备机中按顺序选择前两台作为同步备机，选择不了两台节点时，则会无法操作。
synchronous_standby_names = 'FIRST 2 (standby_pg1,standby_pg2,standby_pg3)'
四、修改备节点配置
node2 配置
1、进行数据恢复
[postgres@keep-postgres12-node2 ~]$ pg_basebackup -h keep-postgres12-node1 -p 5432 -U replrole -R -F p -P -D $PG_HOME/data
2、修改standby.signal配置
[postgres@keep-postgres12-node2 data]$ cd $PGDATA
[postgres@keep-postgres12-node2 data]$ cat standby.signal
primary_conninfo = 'host=keep-postgres12-node1 port=5432 user=replrole password=ReplRole@123 application_name=standby_pg2 options=''-c wal_sender_timeout=5000'''
restore_command = 'cp /data/postgres12.2/arch/%f %p'
archive_cleanup_command = 'pg_archivecleanup /data/postgres12.2/arch %r'
standby_mode = on
3、修改postgresql.auto.conf配置
# 注意：强同步需要注意在postgresql.auto.conf中加入application_name=standby_pg2
[postgres@keep-postgres12-node2 data]$ cat postgresql.auto.conf
primary_conninfo = 'user=replrole passfile=''/home/postgres/.pgpass'' host=''keep-postgres12-node1'' application_name=standby_pg2 port=5432 sslmode=prefer sslcompression=0 gssencmode=disable krbsrvname=postgres target_session_attrs=any'
node3 配置
1、进行数据恢复
[postgres@keep-postgres12-node3 ~]$ pg_basebackup -h keep-postgres12-node1 -p 5432 -U replrole -R -F p -P -D $PG_HOME/data
2、修改standby.signal配置
[postgres@keep-postgres12-node3 data]$ cd $PGDATA
[postgres@keep-postgres12-node3 data]$ cat standby.signal
primary_conninfo = 'host=keep-postgres12-node1 port=5432 user=replrole password=ReplRole@123 application_name=standby_pg3 options=''-c wal_sender_timeout=5000'''
restore_command = 'cp /data/postgres12.2/arch/%f %p'
archive_cleanup_command = 'pg_archivecleanup /data/postgres12.2/arch %r'
standby_mode = on
3、修改postgresql.auto.conf配置
# 注意：强同步需要注意在postgresql.auto.conf中加入application_name=standby_pg3
[postgres@keep-postgres12-node3 data]$ cat postgresql.auto.conf
primary_conninfo = 'user=replrole passfile=''/home/postgres/.pgpass'' host=''keep-postgres12-node1'' application_name=standby_pg3 port=5432 sslmode=prefer sslcompression=0 gssencmode=disable krbsrvname=postgres target_session_attrs=any'
五、启动数据同步
# 注意：主库修改synchronous_standby_names = ‘standby_pg2’ 后，需等待备库接收主库发送的WAL日志流并写入WAL文件，之后才向客户端返回成功。
1、主库检查点
[postgres@keep-postgres12-node1 ~]$ pg_controldata
pg_control version number: 1201
Catalog version number: 201909212
Database system identifier: 7435168474920575893
Database cluster state: `in production`
pg_control last modified: Tue 10 Dec 2024 04:23:13 PM CST
Latest checkpoint location: 0/2E000060
Latest checkpoint's REDO location: 0/2E000028
Latest checkpoint's REDO WAL file: 00000007000000000000002E
Latest checkpoint's TimeLineID: 7
Latest checkpoint's PrevTimeLineID: 7
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID: 0:640
Latest checkpoint's NextOID: 24913
Latest checkpoint's NextMultiXactId: 1
Latest checkpoint's NextMultiOffset: 0
Latest checkpoint's oldestXID: 480
Latest checkpoint's oldestXID's DB: 1
Latest checkpoint's oldestActiveXID: 640
Latest checkpoint's oldestMultiXid: 1
Latest checkpoint's oldestMulti's DB: 1
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Time of latest checkpoint: Tue 10 Dec 2024 04:23:13 PM CST
Fake LSN counter for unlogged rels: 0/3E8
Minimum recovery ending location: 0/0
Min recovery ending loc's timeline: 0
Backup start location: 0/0
Backup end location: 0/0
End-of-backup record required: no
wal_level setting: replica
wal_log_hints setting: on
max_connections setting: 100
max_worker_processes setting: 8
max_wal_senders setting: 20
max_prepared_xacts setting: 0
max_locks_per_xact setting: 64
track_commit_timestamp setting: off
Maximum data alignment: 8
Database block size: 8192
Blocks per segment of large relation: 131072
WAL block size: 16384
Bytes per WAL segment: 16777216
Maximum length of identifiers: 64
Maximum columns in an index: 32
Maximum size of a TOAST chunk: 1996
Size of a large-object chunk: 2048
Date/time type storage: 64-bit integers
Float4 argument passing: by value
Float8 argument passing: by value
Data page checksum version: 1
Mock authentication nonce: 2fd610c9c82cf604c47448c0e7c842aa9ac4944e8fea828fcc0d2425b6f3a6e4
2、启动备库
node2
[postgres@keep-postgres12-node2 data]$ pg_ctl start
node3
[postgres@keep-postgres12-node3 data]$ pg_ctl start
3、查看备库日志
node2
[postgres@keep-postgres12-node2 pg_log]$ tail -f postgresql-2024-12-10.csv2024-12-10 16:20:45.667 CST,,,24030,,6757f9dd.5dde,1,,2024-12-10 16:20:45 CST,,0,LOG,00000,"ending log output to stderr",,"Future log output will go to log destination ""csvlog"".",,,,,,"PostmasterMain, postmaster.c:1300",""
2024-12-10 16:20:45.672 CST,,,24032,,6757f9dd.5de0,1,,2024-12-10 16:20:45 CST,,0,LOG,00000,"database system was interrupted; last known up at 2024-12-10 16:13:44 CST",,,,,,,,"StartupXLOG, xlog.c:6269",""
2024-12-10 16:20:45.894 CST,,,24032,,6757f9dd.5de0,2,,2024-12-10 16:20:45 CST,,0,LOG,00000,"entering standby mode",,,,,,,,"StartupXLOG, xlog.c:6324",""
2024-12-10 16:20:45.906 CST,,,24032,,6757f9dd.5de0,3,,2024-12-10 16:20:45 CST,1/0,0,LOG,00000,"redo starts at 0/2B000060",,,,,,,,"StartupXLOG, xlog.c:7037",""
2024-12-10 16:20:45.907 CST,,,24032,,6757f9dd.5de0,4,,2024-12-10 16:20:45 CST,1/0,0,LOG,00000,"consistent recovery state reached at 0/2B000138",,,,,,,,"CheckRecoveryConsistency, xlog.c:7880",""
2024-12-10 16:20:45.908 CST,,,24030,,6757f9dd.5dde,2,,2024-12-10 16:20:45 CST,,0,LOG,00000,"database system is ready to accept read only connections",,,,,,,,"sigusr1_handler, postmaster.c:5153",""
2024-12-10 16:20:45.923 CST,,,24046,,6757f9dd.5dee,1,,2024-12-10 16:20:45 CST,,0,LOG,00000,"started streaming WAL from primary at 0/2C000000 on timeline 7",,,,,,,,"WalReceiverMain, walreceiver.c:371",""
node3
[postgres@keep-postgres12-node3 pg_log]$ tail -f postgresql-2024-12-10.csv2024-12-10 16:24:45.339 CST,,,25094,,6757facd.6206,1,,2024-12-10 16:24:45 CST,,0,LOG,00000,"ending log output to stderr",,"Future log output will go to log destination ""csvlog"".",,,,,,"PostmasterMain, postmaster.c:1300",""
2024-12-10 16:24:45.344 CST,,,25096,,6757facd.6208,1,,2024-12-10 16:24:45 CST,,0,LOG,00000,"database system was interrupted; last known up at 2024-12-10 16:23:13 CST",,,,,,,,"StartupXLOG, xlog.c:6269",""
2024-12-10 16:24:45.637 CST,,,25096,,6757facd.6208,2,,2024-12-10 16:24:45 CST,,0,LOG,00000,"entering standby mode",,,,,,,,"StartupXLOG, xlog.c:6324",""
2024-12-10 16:24:45.649 CST,,,25096,,6757facd.6208,3,,2024-12-10 16:24:45 CST,1/0,0,LOG,00000,"redo starts at 0/2E000028",,,,,,,,"StartupXLOG, xlog.c:7037",""
2024-12-10 16:24:45.651 CST,,,25096,,6757facd.6208,4,,2024-12-10 16:24:45 CST,1/0,0,LOG,00000,"consistent recovery state reached at 0/2E000100",,,,,,,,"CheckRecoveryConsistency, xlog.c:7880",""
2024-12-10 16:24:45.652 CST,,,25094,,6757facd.6206,2,,2024-12-10 16:24:45 CST,,0,LOG,00000,"database system is ready to accept read only connections",,,,,,,,"sigusr1_handler, postmaster.c:5153",""
2024-12-10 16:24:45.672 CST,,,25104,,6757facd.6210,1,,2024-12-10 16:24:45 CST,,0,LOG,00000,"started streaming WAL from primary at 0/2F000000 on timeline 7",,,,,,,,"WalReceiverMain, walreceiver.c:371",""
4、检查数据库进程
# keep-postgres12-node1
[postgres@keep-postgres12-node1 ~]$ ps -ef | grep postgres
postgres 26423 1 0 16:17 ? 00:00:00 /data/postgres12.2/bin/postgres
postgres 26424 26423 0 16:17 ? 00:00:00 postgres: logger
postgres 26426 26423 0 16:17 ? 00:00:00 postgres: checkpointer
postgres 26427 26423 0 16:17 ? 00:00:00 postgres: background writer
postgres 26428 26423 0 16:17 ? 00:00:00 postgres: walwriter
postgres 26429 26423 0 16:17 ? 00:00:00 postgres: autovacuum launcher
postgres 26430 26423 0 16:17 ? 00:00:00 postgres: archiver last was 00000007000000000000002E.00000028.backup
postgres 26431 26423 0 16:17 ? 00:00:00 postgres: stats collector
postgres 26432 26423 0 16:17 ? 00:00:00 postgres: logical replication launcher
postgres 27747 26423 0 16:20 ? 00:00:00` postgres: walsender replrole 192.168.122.89(54134) streaming 0/2F000148`
postgres 28509 26423 0 16:24 ? 00:00:00 `postgres: walsender replrole 192.168.122.90(62200) streaming 0/2F000148`# keep-postgres12-node2
[postgres@keep-postgres12-node2 data]$ ps -ef | grep postgres
postgres 24030 1 0 16:20 ? 00:00:00 /data/postgres12.2/bin/postgres
postgres 24031 24030 0 16:20 ? 00:00:00 postgres: logger
postgres 24032 24030 0 16:20 ? 00:00:00 postgres: startup recovering 00000007000000000000002F
postgres 24042 24030 0 16:20 ? 00:00:00 postgres: checkpointer
postgres 24043 24030 0 16:20 ? 00:00:00 postgres: background writer
postgres 24045 24030 0 16:20 ? 00:00:00 postgres: stats collector
postgres 24046 24030 0 16:20 ? 00:00:00 `postgres: walreceiver streaming 0/2F000148`# keep-postgres12-node3
[postgres@keep-postgres12-node3 data]$ ps -ef | grep postgres
postgres 25094 1 0 16:24 ? 00:00:00 /data/postgres12.2/bin/postgres
postgres 25095 25094 0 16:24 ? 00:00:00 postgres: logger
postgres 25096 25094 0 16:24 ? 00:00:00 postgres: startup recovering 00000007000000000000002F
postgres 25100 25094 0 16:24 ? 00:00:00 postgres: checkpointer
postgres 25101 25094 0 16:24 ? 00:00:00 postgres: background writer
postgres 25103 25094 0 16:24 ? 00:00:00 postgres: stats collector
postgres 25104 25094 0 16:24 ? 00:00:00 `postgres: walreceiver streaming 0/2F000148`
六、验证数据同步
1、主库检查同步状态
一节点sync_state 为sync，一节点为potential
[postgres@keep-postgres12-node1 data]$ psql
postgres=# \x
Expanded display is on.
postgres=# SELECT * FROM pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid | 27747
usesysid | 16642
usename | replrole
application_name | standby_pg2
client_addr | 192.168.122.89
client_hostname | keep-postgres12-node2
client_port | 54134
backend_start | 2024-12-10 16:20:45.918235+08
backend_xmin |
state | streaming
sent_lsn | 0/2F000148
write_lsn | 0/2F000148
flush_lsn | 0/2F000148
replay_lsn | 0/2F000148
write_lag |
flush_lag |
replay_lag |
sync_priority | 1
sync_state | sync
reply_time | 2024-12-10 16:36:26.891383+08
-[ RECORD 2 ]----+------------------------------
pid | 29698
usesysid | 16642
usename | replrole
application_name | standby_pg3
client_addr | 192.168.122.90
client_hostname | keep-postgres12-node3
client_port | 62202
backend_start | 2024-12-10 16:31:09.071909+08
backend_xmin |
state | streaming
sent_lsn | 0/2F000148
write_lsn | 0/2F000148
flush_lsn | 0/2F000148
replay_lsn | 0/2F000148
write_lag |
flush_lag |
replay_lag |
sync_priority | 2
sync_state | potential
reply_time | 2024-12-10 16:36:29.798595+08
2、测试数据同步
-- 主库执行
drop table if exists employees_multi_sync CASCADE;
CREATE TABLE employees_multi_sync (id SERIAL PRIMARY KEY,name VARCHAR(100),position VARCHAR(100),department VARCHAR(100),hire_date DATE
);INSERT INTO employees_multi_sync (name, position, department, hire_date)
SELECT 'Employee ' || (generate_series(1, 200))::text,'Position ' || (generate_series(1, 200))::text,'Department ' || (random() *(200-1)+1)::text,'2010-01-01'::date + (generate_series(1, 200) * interval '1 day');-- 备库查询数据
select count(1) from employees_multi_sync ;
-- 计算两边的md5值
SELECT md5(string_agg(id::text || '-' ||name || '-' ||position || '-' ||department || '-' ||TO_CHAR(hire_date, 'YYYY-MM-DD'),',')
) AS row_md5
FROM employees_multi_sync;
七、主备切换
1、停主库
[postgres@keep-postgres12-node1 ~]$ pg_ctl stop -m fast
2、主从切换
备库升主
# 切换之后，$PGDATA下原有的 standby.signal 文件不存在了
[postgres@keep-postgres12-node2 ~]$ pg_ctl promote
查看状态
[postgres@keep-postgres12-node2 ~]$ pg_controldata | grep -i cluster
Database cluster state: `in production`
3、检查原备库的配置（现主）
检查配置文件postgresql.auto.conf
[postgres@keep-postgres12-node2 data]$ cat postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
# 检查是否进行注释，配置还是当作备库，自相矛盾
# primary_conninfo = 'user=replrole passfile=''/home/postgres/.pgpass'' host=''keep-postgres12-node1'' application_name=standby_pg2 port=5432 sslmode=prefer sslcompression=0 gssencmode=disable krbsrvname=postgres target_session_attrs=any'
检查配置文件postgresql.conf
# 修改synchronous_standby_names
[postgres@keep-postgres12-node2 data]$ vim postgresql.conf
synchronous_standby_names = 'standby_pg1,standby_pg3'
检查状态
# 此处进程应为 walwriter，表示还是备机状态。若修改了postgresql.auto.conf，需重启数据库
[postgres@keep-postgres12-node2 data]$ ps -ef | grep postgres
postgres 25288 1 0 Dec02 ? 00:00:00 /data/postgres12.2/bin/postgres
postgres 25289 25288 0 Dec02 ? 00:00:00 postgres: logger
postgres 25294 25288 0 Dec02 ? 00:00:02 postgres: checkpointer
postgres 25295 25288 0 Dec02 ? 00:00:02 postgres: background writer
postgres 25296 25288 0 Dec02 ? 00:00:00 postgres: stats collector
postgres 29058 25288 0 16:11 ? 00:00:00 postgres: `walwriter`
postgres 29059 25288 0 16:11 ? 00:00:00 postgres: autovacuum launcher
postgres 29060 25288 0 16:11 ? 00:00:00 postgres: archiver last was 000000030000000000000020.partial
postgres 29061 25288 0 16:11 ? 00:00:00 postgres: logical replication launcher[postgres@keep-postgres12-node2 data]$ pg_ctl restart# 重启数据库后，备机状态为未连接，不会启动walsender
[postgres@keep-postgres12-node2 data]$ ps -ef | grep postgres
postgres 32261 1 0 16:20 ? 00:00:00 /data/postgres12.2/bin/postgres
postgres 32262 32261 0 16:20 ? 00:00:00 postgres: logger
postgres 32264 32261 0 16:20 ? 00:00:00 postgres: checkpointer
postgres 32265 32261 0 16:20 ? 00:00:00 postgres: background writer
postgres 32266 32261 0 16:20 ? 00:00:00 postgres: walwriter
postgres 32267 32261 0 16:20 ? 00:00:00 postgres: autovacuum launcher
postgres 32268 32261 0 16:20 ? 00:00:00 postgres: archiver
postgres 32269 32261 0 16:20 ? 00:00:00 postgres: stats collector
postgres 32270 32261 0 16:20 ? 00:00:00 postgres: logical replication launcher
4、备库配置standby.signal文件
node1
# standby.signal文件需要新建
[postgres@keep-postgres12-node1 data]$ cat standby.signal
# 添加以下内容
primary_conninfo = 'host=keep-postgres12-node2 port=5432 user=replrole password=ReplRole@123 application_name=standby_pg1 options=''-c wal_sender_timeout=5000'''
restore_command = 'cp /data/postgres12.2/arch/%f %p'
archive_cleanup_command = 'pg_archivecleanup /data/postgres12.2/arch %r'
standby_mode = on
node3
# standby.signal文件需要新建
[postgres@keep-postgres12-node1 data]$ cat standby.signal
# 修改host为keep-postgres12-node2
primary_conninfo = 'host=keep-postgres12-node2 port=5432 user=replrole password=ReplRole@123 application_name=standby_pg3 options=''-c wal_sender_timeout=5000'''
restore_command = 'cp /data/postgres12.2/arch/%f %p'
archive_cleanup_command = 'pg_archivecleanup /data/postgres12.2/arch %r'
standby_mode = on
5、备库修改配置文件postgresql.auto.conf
node1
[postgres@keep-postgres12-node1 data]$ cat postgresql.auto.conf
# 添加以下内容
primary_conninfo = 'user=replrole passfile=''/home/postgres/.pgpass'' host=''keep-postgres12-node2'' application_name=standby_pg2 port=5432 sslmode=prefer sslcompression=0 gssencmode=disable krbsrvname=postgres target_session_attrs=any'
node3
[postgres@keep-postgres12-node1 data]$ cat postgresql.auto.conf
# 修改host为keep-postgres12-node2
primary_conninfo = 'user=replrole passfile=''/home/postgres/.pgpass'' host=''keep-postgres12-node2'' application_name=standby_pg3 port=5432 sslmode=prefer sslcompression=0 gssencmode=disable krbsrvname=postgres target_session_attrs=any'
6、启动备库
node1
[postgres@keep-postgres12-node1 data]$ pg_ctl restart
node3
[postgres@keep-postgres12-node3 data]$ pg_ctl restart
7、检查数据库进程
# 备库 node1
[postgres@keep-postgres12-node1 ~]$ ps -ef | grep postgres
postgres 440 1 0 16:52 ? 00:00:00 /data/postgres12.2/bin/postgres
postgres 441 440 0 16:52 ? 00:00:00 postgres: logger
postgres 442 440 0 16:52 ? 00:00:00 postgres: startup recovering 000000080000000000000032
postgres 446 440 0 16:52 ? 00:00:00 postgres: checkpointer
postgres 447 440 0 16:52 ? 00:00:00 postgres: background writer
postgres 448 440 0 16:52 ? 00:00:00 postgres: stats collector
postgres 449 440 0 16:52 ? 00:00:00 `postgres: walreceiver streaming 0/320001C0`# 主库
[postgres@keep-postgres12-node2 ~]$ ps -ef | grep postgres
postgres 25853 1 0 16:47 ? 00:00:00 /data/postgres12.2/bin/postgres
postgres 25854 25853 0 16:47 ? 00:00:00 postgres: logger
postgres 25856 25853 0 16:47 ? 00:00:00 postgres: checkpointer
postgres 25857 25853 0 16:47 ? 00:00:00 postgres: background writer
postgres 25858 25853 0 16:47 ? 00:00:00 postgres: walwriter
postgres 25859 25853 0 16:47 ? 00:00:00 postgres: autovacuum launcher
postgres 25860 25853 0 16:47 ? 00:00:00 postgres: archiver
postgres 25861 25853 0 16:47 ? 00:00:00 postgres: stats collector
postgres 25862 25853 0 16:47 ? 00:00:00 postgres: logical replication launcher
postgres 26311 25853 0 16:52 ? 00:00:00 `postgres: walsender replrole 192.168.122.90(16994) streaming 0/320001C0`
postgres 26312 25853 0 16:52 ? 00:00:00 `postgres: walsender replrole 192.168.122.87(18939) streaming 0/320001C0`# 备库 node3
[postgres@keep-postgres12-node3 ~]$ ps -ef | grep postgres
postgres 31097 1 0 16:52 ? 00:00:00 /data/postgres12.2/bin/postgres
postgres 31098 31097 0 16:52 ? 00:00:00 postgres: logger
postgres 31099 31097 0 16:52 ? 00:00:00 postgres: startup recovering 000000080000000000000032
postgres 31103 31097 0 16:52 ? 00:00:00 postgres: checkpointer
postgres 31104 31097 0 16:52 ? 00:00:00 postgres: background writer
postgres 31105 31097 0 16:52 ? 00:00:00 postgres: stats collector
postgres 31106 31097 0 16:52 ? 00:00:00 `postgres: walreceiver streaming 0/320001C0`
8、检查同步状态
[postgres@keep-postgres12-node2 ~]$ psql
postgres=# \x
Expanded display is on.
postgres=# SELECT * FROM pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid | 26311
usesysid | 16642
usename | replrole
application_name | standby_pg3
client_addr | 192.168.122.90
client_hostname | keep-postgres12-node3
client_port | 16994
backend_start | 2024-12-10 16:52:56.465729+08
backend_xmin |
state | streaming
sent_lsn | 0/32036768
write_lsn | 0/32036768
flush_lsn | 0/32036768
replay_lsn | 0/32036768
write_lag |
flush_lag |
replay_lag |
sync_priority | 2
sync_state | potential
reply_time | 2024-12-10 16:56:27.053456+08
-[ RECORD 2 ]----+------------------------------
pid | 26312
usesysid | 16642
usename | replrole
application_name | standby_pg1
client_addr | 192.168.122.87
client_hostname | keep-postgres12-node1
client_port | 18939
backend_start | 2024-12-10 16:52:58.753138+08
backend_xmin |
state | streaming
sent_lsn | 0/32036768
write_lsn | 0/32036768
flush_lsn | 0/32036768
replay_lsn | 0/32036768
write_lag |
flush_lag |
replay_lag |
sync_priority | 1
sync_state | sync
reply_time | 2024-12-10 16:56:27.047169+08
9、验证是否同步
-- 主库执行
drop table if exists employees_multi_sync1 CASCADE;
CREATE TABLE employees_multi_sync1 (id SERIAL PRIMARY KEY,name VARCHAR(100),position VARCHAR(100),department VARCHAR(100),hire_date DATE
);INSERT INTO employees_multi_sync1 (name, position, department, hire_date)
SELECT 'Employee ' || (generate_series(1, 200))::text,'Position ' || (generate_series(1, 200))::text,'Department ' || (random() *(200-1)+1)::text,'2010-01-01'::date + (generate_series(1, 200) * interval '1 day');-- 备库查询数据
select count(1) from employees_multi_sync1 ;
-- 计算两边的md5值
SELECT md5(string_agg(id::text || '-' ||name || '-' ||position || '-' ||department || '-' ||TO_CHAR(hire_date, 'YYYY-MM-DD'),',')
) AS row_md5
FROM employees_multi_sync1;
八、pg_rewind 工具
当主备集群中的备库意外崩溃，且经过长时间，归档日志又被删除了，需要把这段时间的增量数据同步回来，那么就可以用 到pg_rewind 工具进行同步。pg_rewind 使一个PostgreSQL 数据目录与另一个数据目录一致。
[postgres@keep-postgres12-node1 ~]$ pg_rewind --target-pgdata $PGDATA --source-server='host=keep-postgres12-node2 port=5432 user=keep password=keep dbname=postgres' -P
pg_rewind帮助命令
[postgres@keep-postgres12-node1 ~]$ pg_rewind --help
pg_rewind resynchronizes a PostgreSQL cluster with another copy of the cluster.Usage:pg_rewind [OPTION]...Options:-D, --target-pgdata=DIRECTORY existing data directory to modify--source-pgdata=DIRECTORY source data directory to synchronize with--source-server=CONNSTR source server to synchronize with-n, --dry-run stop before modifying anything-N, --no-sync do not wait for changes to be writtensafely to disk-P, --progress write progress messages--debug write a lot of debug messages-V, --version output version information, then exit-?, --help show this help, then exit