PostgreSQL的一主一从集群搭建部署 (两同步)

一、实验环境

虚拟机名IP身份简称keep-postgres12-node1192.168.122.87主节点node1keep-postgres12-node2192.168.122.89备节点node2keep-postgres12-node3192.168.122.90备节点node3 二、安装数据库

源码包方式（主）

1、创建用户

1. [root@keep-postgres12-node1 ~]# groupadd postgres
2. [root@keep-postgres12-node1 ~]# useradd -g postgres postgres -m -s /bin/bash
3. [root@keep-postgres12-node1 ~]# echo "Database@123" | passwd --stdin postgres

复制代码

2、修改服务器内核信息

1. [root@keep-postgres12-node1 ~]# echo '# 最大共享内存段大小 (默认值68719476736)
2. kernel.shmmax = 68719476736
3. # 可以使用的共享内存的总量 (默认值4294967296)
4. kernel.shmall = 4294967296
5. # 整个系统共享内存段的最大数目
6. kernel.shmmni = 4096
7. # 每个信号对象集的最大信号对象数
8. kernel.sem = 50100 64128000 50100 1280
9. # 文件句柄的最大数量
10. fs.file-max = 7672460
11. # 应用程序可使用的IPv4端口范围
12. net.ipv4.ip_local_port_range = 9000 65000
13. # 套接字接收缓冲区大小的缺省值
14. net.core.rmem_default = 1048576
15. # 套接字发送缓冲区大小的缺省值
16. net.core.wmem_default = 262144
17. # 套接字发送缓冲区大小的最大值
18. net.core.wmem_max = 1048576' >> /etc/sysctl.conf
20. # 使参数生效
21. [root@keep-postgres12-node1 ~]# sysctl -p

复制代码

3、安装依赖包

1. [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、创建数据库目录

1. [root@keep-postgres12-node1 ~]# mkdir /data
2. [root@keep-postgres12-node1 ~]# mkdir /data/postgres12.2
3. [root@keep-postgres12-node1 ~]# chown -R postgres: /data

复制代码

5、创建环境变量

1. [root@keep-postgres12-node1 ~]# su - postgres
2. [postgres@keep-postgres12-node1 ~]$ echo 'export PGPORT=5432
3. export PG_HOME=/data/postgres12.2
4. export PATH=$PG_HOME/bin:$PATH
5. export PGDATA=$PG_HOME/data
6. export LD_LIBRARY_PATH=$PG_HOME/lib
7. 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/

1. # 安装postgresql-12.2版本
2. # 下载postgresql-12.2源码包
3. [postgres@keep-postgres12-node1 ~]$ cd /data
4. [postgres@keep-postgres12-node1 data]$ wget https://ftp.postgresql.org/pub/source/v12.2/postgresql-12.2.tar.bz2

复制代码

1. # 解压源码包
2. [postgres@keep-postgres12-node1 data]$ tar -xf postgresql-12.2.tar.bz2

复制代码

7、编译源码包

1. [postgres@keep-postgres12-node1 postgresql-12.2]$ ./configure --prefix=/data/postgres12.2 --with-pgport=5432 --with-openssl --with-perl \
2. --with-tcl --with-python --with-pam --without-ldap --with-libxml --with-libxslt \
3. --enable-thread-safety --with-wal-blocksize=16 --with-blocksize=8

复制代码

1. # 包括第三方插件全部编译，包含文档和所有的contirb
2. [postgres@keep-postgres12-node1 postgresql-12.2]$ gmake world

复制代码

8、安装数据库

1. [postgres@keep-postgres12-node1 postgresql-12.2]$ make && make install

复制代码

9、设置wal日志目录

1. [postgres@keep-postgres12-node1 postgresql-12.2]$ # 用于保存wal日志
2. [postgres@keep-postgres12-node1 postgresql-12.2]$ mkdir $PG_HOME/arch

复制代码

10、初始化数据库

1. [postgres@keep-postgres12-node1 postgresql-12.2]$ # 创建目录
2. [postgres@keep-postgres12-node1 postgresql-12.2]$ mkdir $PG_HOME/data
3. [postgres@keep-postgres12-node1 postgresql-12.2]$ # 初始化数据库集簇
4. [postgres@keep-postgres12-node1 postgresql-12.2]$ # --data-checksums 主从复制时需要
5. [postgres@keep-postgres12-node1 postgresql-12.2]$ initdb -D $PGDATA -W --data-checksums

复制代码

11、启动数据库

1. [postgres@keep-postgres12-node1 postgresql-12.2]$ pg_ctl -D $PGDATA start

复制代码

12、创建数据库用户和数据库

1. [postgres@keep-postgres12-node1 data]$ psql -d postgres -p 5432
2. postgres=# create database testdb;
3. postgres=# create user keep with Superuser password  'keep';

复制代码

13、设置远程毗连

# 设置毗连

1. [postgres@keep-postgres12-node1 data]$ cd $PGDATA
2. [postgres@keep-postgres12-node1 data]$ sed -i "s/#listen_addresses = 'localhost'/listen_addresses = '*'/g" postgresql.conf
3. [postgres@keep-postgres12-node1 data]$ echo 'host all all 0.0.0.0/0 md5' >> pg_hba.conf

复制代码

# 重启数据库

1. [postgres@keep-postgres12-node1 data]$ pg_ctl stop
2. [postgres@keep-postgres12-node1 data]$ pg_ctl -D $PGDATA start

复制代码

# 验证远程毗连

1. [postgres@keep-postgres12-node1 data]$ psql -d testdb -U keep -p 5432 -h 192.168.140.96

复制代码

14、设置数据库日志

1. [postgres@keep-postgres12-node1 data]$ echo "# Add settings for extensions here
2. log_destination='csvlog'
3. logging_collector=on
4. log_directory='pg_log'
5. log_filename='postgresql-%Y-%m-%d.log'
6. log_truncate_on_rotation=off
7. log_rotation_age=1d
8. log_rotation_size=0
9. log_error_verbosity=verbose" >> $PGDATA/postgresql.conf

复制代码

源码包方式（备 node2/node3）

1、创建用户

1. [root@keep-postgres12-node2 ~]# groupadd postgres
2. [root@keep-postgres12-node2 ~]# useradd -g postgres postgres -m -s /bin/bash
3. [root@keep-postgres12-node2 ~]# echo "Database@123" | passwd --stdin postgres

复制代码

2、修改服务器内核信息

1. [root@keep-postgres12-node2 ~]# echo '# 最大共享内存段大小 (默认值68719476736)
2. kernel.shmmax = 68719476736
3. # 可以使用的共享内存的总量 (默认值4294967296)
4. kernel.shmall = 4294967296
5. # 整个系统共享内存段的最大数目
6. kernel.shmmni = 4096
7. # 每个信号对象集的最大信号对象数
8. kernel.sem = 50100 64128000 50100 1280
9. # 文件句柄的最大数量
10. fs.file-max = 7672460
11. # 应用程序可使用的IPv4端口范围
12. net.ipv4.ip_local_port_range = 9000 65000
13. # 套接字接收缓冲区大小的缺省值
14. net.core.rmem_default = 1048576
15. # 套接字发送缓冲区大小的缺省值
16. net.core.wmem_default = 262144
17. # 套接字发送缓冲区大小的最大值
18. net.core.wmem_max = 1048576' >> /etc/sysctl.conf
20. # 使参数生效
21. [root@keep-postgres12-node2 ~]# sysctl -p

复制代码

3、安装依赖包

1. [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、创建数据库目录

1. [root@keep-postgres12-node2 ~]# mkdir /data
2. [root@keep-postgres12-node2 ~]# mkdir /data/postgres12.2
3. [root@keep-postgres12-node2 ~]# chown -R postgres: /data

复制代码

5、创建环境变量

1. [root@keep-postgres12-node2 ~]# su - postgres
2. [postgres@keep-postgres12-node2 ~]$ echo 'export PGPORT=5432
3. export PG_HOME=/data/postgres12.2
4. export PATH=$PG_HOME/bin:$PATH
5. export PGDATA=$PG_HOME/data
6. export LD_LIBRARY_PATH=$PG_HOME/lib
7. 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/

1. # 安装postgresql-12.2版本
2. # 下载postgresql-12.2源码包
3. [postgres@keep-postgres12-node2 ~]$ cd /data
4. [postgres@keep-postgres12-node2 data]$ wget https://ftp.postgresql.org/pub/source/v12.2/postgresql-12.2.tar.bz2

复制代码

1. # 解压源码包
2. [postgres@keep-postgres12-node2 data]$ tar -xf postgresql-12.2.tar.bz2

复制代码

7、编译源码包

1. [postgres@keep-postgres12-node2 postgresql-12.2]$ ./configure --prefix=/data/postgres12.2 --with-pgport=5432 --with-openssl --with-perl \
2. --with-tcl --with-python --with-pam --without-ldap --with-libxml --with-libxslt \
3. --enable-thread-safety --with-wal-blocksize=16 --with-blocksize=8

复制代码

1. # 包括第三方插件全部编译，包含文档和所有的contirb
2. [postgres@keep-postgres12-node2 postgresql-12.2]$ gmake world

复制代码

8、安装数据库

1. [postgres@keep-postgres12-node2 postgresql-12.2]$ make && make install

复制代码

9、设置wal日志目录

1. [postgres@keep-postgres12-node1 postgresql-12.2]$ # 用于保存wal日志
2. [postgres@keep-postgres12-node1 postgresql-12.2]$ mkdir $PG_HOME/arch

复制代码

设置互信

1、设置主机名

1. # keep-postgres12-node1
2. [root@keep-postgres12-node1 ~]# echo '192.168.122.87 keep-postgres12-node1
3. 192.168.122.88 keep-postgres12-node2
4. 192.168.122.90 keep-postgres12-node3' >> /etc/hosts
6. # keep-postgres12-node2
7. [root@keep-postgres12-node2 ~]# echo '192.168.122.87 keep-postgres12-node1
8. 192.168.122.88 keep-postgres12-node2
9. 192.168.122.90 keep-postgres12-node3' >> /etc/hosts
11. # keep-postgres12-node3
12. [root@keep-postgres12-node3 ~]# echo '192.168.122.87 keep-postgres12-node1
13. 192.168.122.88 keep-postgres12-node2
14. 192.168.122.90 keep-postgres12-node3' >> /etc/hosts

复制代码

2、设置postgres用户互信

# keep-postgres12-node1

1. [postgres@keep-postgres12-node1 ~]$ ssh-keygen
2. [postgres@keep-postgres12-node1 ~]$ ssh-copy-id keep-postgres12-node1
3. [postgres@keep-postgres12-node1 ~]$ ssh-copy-id keep-postgres12-node2
4. [postgres@keep-postgres12-node1 ~]$ ssh-copy-id keep-postgres12-node3
6. # 验证
7. [postgres@keep-postgres12-node1 ~]$ ssh keep-postgres12-node1
8. [postgres@keep-postgres12-node1 ~]$ ssh keep-postgres12-node2
9. [postgres@keep-postgres12-node1 ~]$ ssh keep-postgres12-node3

复制代码

# keep-postgres12-node2

1. [postgres@keep-postgres12-node2 ~]$ ssh-keygen
2. [postgres@keep-postgres12-node2 ~]$ ssh-copy-id keep-postgres12-node1
3. [postgres@keep-postgres12-node2 ~]$ ssh-copy-id keep-postgres12-node2
4. [postgres@keep-postgres12-node2 ~]$ ssh-copy-id keep-postgres12-node3
6. # 验证
7. [postgres@keep-postgres12-node2 ~]$ ssh keep-postgres12-node1
8. [postgres@keep-postgres12-node2 ~]$ ssh keep-postgres12-node2
9. [postgres@keep-postgres12-node2 ~]$ ssh keep-postgres12-node3

复制代码

# keep-postgres12-node3

1. [postgres@keep-postgres12-node3 ~]$ ssh-keygen
2. [postgres@keep-postgres12-node3 ~]$ ssh-copy-id keep-postgres12-node1
3. [postgres@keep-postgres12-node3 ~]$ ssh-copy-id keep-postgres12-node2
4. [postgres@keep-postgres12-node3 ~]$ ssh-copy-id keep-postgres12-node3
6. # 验证
7. [postgres@keep-postgres12-node3 ~]$ ssh keep-postgres12-node1
8. [postgres@keep-postgres12-node3 ~]$ ssh keep-postgres12-node2
9. [postgres@keep-postgres12-node3 ~]$ ssh keep-postgres12-node3

复制代码

三、修改主节点设置

1、创建同步用户

1. # 数据库需要在线状态
2. [postgres@keep-postgres12-node1 ~]$ psql -c "create role replrole login replication encrypted password 'ReplRole@123';"

复制代码

2、设置$PGDATA/pg_hba.conf

1. [postgres@keep-postgres12-node1 ~]$ echo 'host  replication  replrole  keep-postgres12-node1  trust
2. host  replication  replrole  keep-postgres12-node2  trust
3. host  replication  replrole  keep-postgres12-node3  trust
4. ' >> $PGDATA/pg_hba.conf

复制代码

3、修改数据库参数

1. [postgres@keep-postgres12-node1 ~]$ echo "wal_level = replica
2. max_wal_senders=20
3. wal_keep_segments =64
4. # 开启归档
5. archive_mode = on
6. archive_command = 'cp %p /data/postgres12.2/arch/%f'
7. restore_command = 'cp /data/postgres12.2/arch/%f %p'
8. recovery_target_timeline = 'latest'
9. # full_page_writes是控制是否开启全页写入
10. full_page_writes = on
11. # 将每个磁盘页的全部内容写入到WAL
12. wal_log_hints = on
13. # 配置同步,此时pg2的优先级比pg3的要高
14. # 一个节点的sync_state为sync，剩下的是potential
15. synchronous_standby_names = 'standby_pg2,standby_pg3'
16. # 默认值为 on
17. # 可以设置为remote_write，对主库性能有利
18. # 开启同步模式，此刻需要强同步，数据无法入库
19. synchronous_commit = on
20. " >> $PGDATA/postgresql.conf

复制代码

4、重启数据库

1. [postgres@keep-postgres12-node1 ~]$ pg_ctl restart

复制代码

5、synchronous_standby_names参数详解

1. # 该参数指定流复制中需要同步复制的服务器列表，需要配置standby服务器的名字
2. # 情况一：（无顺序要求）
3. # 只有这三台备机会被选为同步备机，即其中一个会被选为活动的同步备机，其余两台将处于备用状态。
4. synchronous_standby_names = 'standby_pg1,standby_pg2,standby_pg3'
6. # 设置WAL日志强同步至N个节点中的某M个节点
7. # 情况二：（无顺序要求）
8. # 只有这三台备机会被选为同步备机，即其中一个会被选为活动的同步备机，其余两台将处于备用状态。
9. synchronous_standby_names = 'ANY 1 (standby_pg1,standby_pg2,standby_pg3)'
11. # 情况三：（无顺序要求）
12. # 只有这三台备机会被选为同步备机，即其中两个会被选为活动的同步备机，其余一台将处于备用状态。
13. synchronous_standby_names = 'ANY 2 (standby_pg1,standby_pg2,standby_pg3)'
15. # 情况四：（按照指定节点的顺序）
16. # 从括号中列出的备机中按顺序选择前两台作为同步备机，选择不了两台节点时，则会无法操作。
17. synchronous_standby_names = 'FIRST 2 (standby_pg1,standby_pg2,standby_pg3)'

复制代码

四、修改备节点设置

node2 设置

1、进行数据恢复

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设置

1. [postgres@keep-postgres12-node2 data]$ cd $PGDATA
2. [postgres@keep-postgres12-node2 data]$ cat standby.signal
3. primary_conninfo = 'host=keep-postgres12-node1 port=5432 user=replrole password=ReplRole@123 application_name=standby_pg2 options=''-c wal_sender_timeout=5000'''
4. restore_command = 'cp /data/postgres12.2/arch/%f %p'
5. archive_cleanup_command = 'pg_archivecleanup /data/postgres12.2/arch %r'
6. standby_mode = on

复制代码

3、修改postgresql.auto.conf设置

# 注意：强同步必要注意在postgresql.auto.conf中加入application_name=standby_pg2

1. [postgres@keep-postgres12-node2 data]$ cat postgresql.auto.conf
2. 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、进行数据恢复

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设置

1. [postgres@keep-postgres12-node3 data]$ cd $PGDATA
2. [postgres@keep-postgres12-node3 data]$ cat standby.signal
3. primary_conninfo = 'host=keep-postgres12-node1 port=5432 user=replrole password=ReplRole@123 application_name=standby_pg3 options=''-c wal_sender_timeout=5000'''
4. restore_command = 'cp /data/postgres12.2/arch/%f %p'
5. archive_cleanup_command = 'pg_archivecleanup /data/postgres12.2/arch %r'
6. standby_mode = on

复制代码

3、修改postgresql.auto.conf设置

# 注意：强同步必要注意在postgresql.auto.conf中加入application_name=standby_pg3

1. [postgres@keep-postgres12-node3 data]$ cat postgresql.auto.conf
2. 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、主库查抄点

1. [postgres@keep-postgres12-node1 ~]$ pg_controldata
2. pg_control version number:            1201
3. Catalog version number:               201909212
4. Database system identifier:           7435168474920575893
5. Database cluster state:               `in production`
6. pg_control last modified:             Tue 10 Dec 2024 04:23:13 PM CST
7. Latest checkpoint location:           0/2E000060
8. Latest checkpoint's REDO location:    0/2E000028
9. Latest checkpoint's REDO WAL file:    00000007000000000000002E
10. Latest checkpoint's TimeLineID:       7
11. Latest checkpoint's PrevTimeLineID:   7
12. Latest checkpoint's full_page_writes: on
13. Latest checkpoint's NextXID:          0:640
14. Latest checkpoint's NextOID:          24913
15. Latest checkpoint's NextMultiXactId:  1
16. Latest checkpoint's NextMultiOffset:  0
17. Latest checkpoint's oldestXID:        480
18. Latest checkpoint's oldestXID's DB:   1
19. Latest checkpoint's oldestActiveXID:  640
20. Latest checkpoint's oldestMultiXid:   1
21. Latest checkpoint's oldestMulti's DB: 1
22. Latest checkpoint's oldestCommitTsXid:0
23. Latest checkpoint's newestCommitTsXid:0
24. Time of latest checkpoint:            Tue 10 Dec 2024 04:23:13 PM CST
25. Fake LSN counter for unlogged rels:   0/3E8
26. Minimum recovery ending location:     0/0
27. Min recovery ending loc's timeline:   0
28. Backup start location:                0/0
29. Backup end location:                  0/0
30. End-of-backup record required:        no
31. wal_level setting:                    replica
32. wal_log_hints setting:                on
33. max_connections setting:              100
34. max_worker_processes setting:         8
35. max_wal_senders setting:              20
36. max_prepared_xacts setting:           0
37. max_locks_per_xact setting:           64
38. track_commit_timestamp setting:       off
39. Maximum data alignment:               8
40. Database block size:                  8192
41. Blocks per segment of large relation: 131072
42. WAL block size:                       16384
43. Bytes per WAL segment:                16777216
44. Maximum length of identifiers:        64
45. Maximum columns in an index:          32
46. Maximum size of a TOAST chunk:        1996
47. Size of a large-object chunk:         2048
48. Date/time type storage:               64-bit integers
49. Float4 argument passing:              by value
50. Float8 argument passing:              by value
51. Data page checksum version:           1
52. Mock authentication nonce:            2fd610c9c82cf604c47448c0e7c842aa9ac4944e8fea828fcc0d2425b6f3a6e4

复制代码

2、启动备库

node2

1. [postgres@keep-postgres12-node2 data]$ pg_ctl start

复制代码

node3

1. [postgres@keep-postgres12-node3 data]$ pg_ctl start

复制代码

3、查看备库日志

node2

1. [postgres@keep-postgres12-node2 pg_log]$ tail -f postgresql-2024-12-10.csv
3. 2024-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",""
4. 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",""
5. 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",""
6. 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",""
7. 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",""
8. 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",""
9. 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

1. [postgres@keep-postgres12-node3 pg_log]$ tail -f postgresql-2024-12-10.csv
3. 2024-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",""
4. 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",""
5. 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",""
6. 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",""
7. 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",""
8. 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",""
9. 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、查抄数据库历程

1. # keep-postgres12-node1
2. [postgres@keep-postgres12-node1 ~]$ ps -ef | grep postgres
3. postgres 26423     1  0 16:17 ?        00:00:00 /data/postgres12.2/bin/postgres
4. postgres 26424 26423  0 16:17 ?        00:00:00 postgres: logger
5. postgres 26426 26423  0 16:17 ?        00:00:00 postgres: checkpointer
6. postgres 26427 26423  0 16:17 ?        00:00:00 postgres: background writer
7. postgres 26428 26423  0 16:17 ?        00:00:00 postgres: walwriter
8. postgres 26429 26423  0 16:17 ?        00:00:00 postgres: autovacuum launcher
9. postgres 26430 26423  0 16:17 ?        00:00:00 postgres: archiver   last was 00000007000000000000002E.00000028.backup
10. postgres 26431 26423  0 16:17 ?        00:00:00 postgres: stats collector
11. postgres 26432 26423  0 16:17 ?        00:00:00 postgres: logical replication launcher
12. postgres 27747 26423  0 16:20 ?        00:00:00` postgres: walsender replrole 192.168.122.89(54134) streaming 0/2F000148`
13. postgres 28509 26423  0 16:24 ?        00:00:00 `postgres: walsender replrole 192.168.122.90(62200) streaming 0/2F000148`
15. # keep-postgres12-node2
16. [postgres@keep-postgres12-node2 data]$ ps -ef | grep postgres
17. postgres 24030     1  0 16:20 ?        00:00:00 /data/postgres12.2/bin/postgres
18. postgres 24031 24030  0 16:20 ?        00:00:00 postgres: logger
19. postgres 24032 24030  0 16:20 ?        00:00:00 postgres: startup   recovering 00000007000000000000002F
20. postgres 24042 24030  0 16:20 ?        00:00:00 postgres: checkpointer
21. postgres 24043 24030  0 16:20 ?        00:00:00 postgres: background writer
22. postgres 24045 24030  0 16:20 ?        00:00:00 postgres: stats collector
23. postgres 24046 24030  0 16:20 ?        00:00:00 `postgres: walreceiver   streaming 0/2F000148`
25. # keep-postgres12-node3
26. [postgres@keep-postgres12-node3 data]$ ps -ef | grep postgres
27. postgres 25094     1  0 16:24 ?        00:00:00 /data/postgres12.2/bin/postgres
28. postgres 25095 25094  0 16:24 ?        00:00:00 postgres: logger
29. postgres 25096 25094  0 16:24 ?        00:00:00 postgres: startup   recovering 00000007000000000000002F
30. postgres 25100 25094  0 16:24 ?        00:00:00 postgres: checkpointer
31. postgres 25101 25094  0 16:24 ?        00:00:00 postgres: background writer
32. postgres 25103 25094  0 16:24 ?        00:00:00 postgres: stats collector
33. postgres 25104 25094  0 16:24 ?        00:00:00 `postgres: walreceiver   streaming 0/2F000148`

复制代码

六、验证数据同步

1、主库查抄同步状态

一节点sync_state 为sync，一节点为potential

1. [postgres@keep-postgres12-node1 data]$ psql
2. postgres=# \x
3. Expanded display is on.
4. postgres=#  SELECT * FROM pg_stat_replication;
5. -[ RECORD 1 ]----+------------------------------
6. pid              | 27747
7. usesysid         | 16642
8. usename          | replrole
9. application_name | standby_pg2
10. client_addr      | 192.168.122.89
11. client_hostname  | keep-postgres12-node2
12. client_port      | 54134
13. backend_start    | 2024-12-10 16:20:45.918235+08
14. backend_xmin     |
15. state            | streaming
16. sent_lsn         | 0/2F000148
17. write_lsn        | 0/2F000148
18. flush_lsn        | 0/2F000148
19. replay_lsn       | 0/2F000148
20. write_lag        |
21. flush_lag        |
22. replay_lag       |
23. sync_priority    | 1
24. sync_state       | sync
25. reply_time       | 2024-12-10 16:36:26.891383+08
26. -[ RECORD 2 ]----+------------------------------
27. pid              | 29698
28. usesysid         | 16642
29. usename          | replrole
30. application_name | standby_pg3
31. client_addr      | 192.168.122.90
32. client_hostname  | keep-postgres12-node3
33. client_port      | 62202
34. backend_start    | 2024-12-10 16:31:09.071909+08
35. backend_xmin     |
36. state            | streaming
37. sent_lsn         | 0/2F000148
38. write_lsn        | 0/2F000148
39. flush_lsn        | 0/2F000148
40. replay_lsn       | 0/2F000148
41. write_lag        |
42. flush_lag        |
43. replay_lag       |
44. sync_priority    | 2
45. sync_state       | potential
46. reply_time       | 2024-12-10 16:36:29.798595+08

复制代码

2、测试数据同步

1. -- 主库执行
2. drop table if exists employees_multi_sync CASCADE;
3. CREATE TABLE employees_multi_sync (
4.     id SERIAL PRIMARY KEY,
5.     name VARCHAR(100),
6.     position VARCHAR(100),
7.     department VARCHAR(100),
8.     hire_date DATE
9. );
11. INSERT INTO employees_multi_sync (name, position, department, hire_date)
12. SELECT
13.     'Employee ' || (generate_series(1, 200))::text,
14.     'Position ' || (generate_series(1, 200))::text,
15.     'Department ' || (random() *(200-1)+1)::text,
16.     '2010-01-01'::date + (generate_series(1, 200) * interval '1 day');
18. -- 备库查询数据
19. select count(1) from employees_multi_sync ;
20. -- 计算两边的md5值
21. SELECT md5(
22.         string_agg(
23.     id::text || '-' ||
24.     name || '-' ||
25.     position || '-' ||
26.     department || '-' ||
27.     TO_CHAR(hire_date, 'YYYY-MM-DD'),',')
28. ) AS row_md5
29. FROM employees_multi_sync;

复制代码

七、主备切换

1、停主库

1. [postgres@keep-postgres12-node1 ~]$ pg_ctl stop -m fast

复制代码

2、主从切换

备库升主

1. # 切换之后，$PGDATA下原有的 standby.signal 文件不存在了
2. [postgres@keep-postgres12-node2 ~]$ pg_ctl promote

复制代码

查看状态

1. [postgres@keep-postgres12-node2 ~]$ pg_controldata | grep -i cluster
2. Database cluster state:               `in production`

复制代码

3、查抄原备库的设置（现主）

查抄设置文件postgresql.auto.conf

1. [postgres@keep-postgres12-node2 data]$ cat postgresql.auto.conf
2. # Do not edit this file manually!
3. # It will be overwritten by the ALTER SYSTEM command.
4. # 检查是否进行注释，配置还是当作备库，自相矛盾
5. # 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

1. # 修改synchronous_standby_names
2. [postgres@keep-postgres12-node2 data]$ vim postgresql.conf
3. synchronous_standby_names = 'standby_pg1,standby_pg3'

复制代码

查抄状态

1. # 此处进程应为 walwriter，表示还是备机状态。若修改了postgresql.auto.conf，需重启数据库
2. [postgres@keep-postgres12-node2 data]$ ps -ef | grep postgres
3. postgres 25288     1  0 Dec02 ?        00:00:00 /data/postgres12.2/bin/postgres
4. postgres 25289 25288  0 Dec02 ?        00:00:00 postgres: logger
5. postgres 25294 25288  0 Dec02 ?        00:00:02 postgres: checkpointer
6. postgres 25295 25288  0 Dec02 ?        00:00:02 postgres: background writer
7. postgres 25296 25288  0 Dec02 ?        00:00:00 postgres: stats collector
8. postgres 29058 25288  0 16:11 ?        00:00:00 postgres: `walwriter`
9. postgres 29059 25288  0 16:11 ?        00:00:00 postgres: autovacuum launcher
10. postgres 29060 25288  0 16:11 ?        00:00:00 postgres: archiver   last was 000000030000000000000020.partial
11. postgres 29061 25288  0 16:11 ?        00:00:00 postgres: logical replication launcher
13. [postgres@keep-postgres12-node2 data]$ pg_ctl restart
15. # 重启数据库后，备机状态为未连接，不会启动walsender
16. [postgres@keep-postgres12-node2 data]$ ps -ef | grep postgres
17. postgres 32261     1  0 16:20 ?        00:00:00 /data/postgres12.2/bin/postgres
18. postgres 32262 32261  0 16:20 ?        00:00:00 postgres: logger
19. postgres 32264 32261  0 16:20 ?        00:00:00 postgres: checkpointer
20. postgres 32265 32261  0 16:20 ?        00:00:00 postgres: background writer
21. postgres 32266 32261  0 16:20 ?        00:00:00 postgres: walwriter
22. postgres 32267 32261  0 16:20 ?        00:00:00 postgres: autovacuum launcher
23. postgres 32268 32261  0 16:20 ?        00:00:00 postgres: archiver
24. postgres 32269 32261  0 16:20 ?        00:00:00 postgres: stats collector
25. postgres 32270 32261  0 16:20 ?        00:00:00 postgres: logical replication launcher

复制代码

4、备库设置standby.signal文件

node1

1. # standby.signal文件需要新建
2. [postgres@keep-postgres12-node1 data]$ cat standby.signal
3. # 添加以下内容
4. primary_conninfo = 'host=keep-postgres12-node2 port=5432 user=replrole password=ReplRole@123 application_name=standby_pg1 options=''-c wal_sender_timeout=5000'''
5. restore_command = 'cp /data/postgres12.2/arch/%f %p'
6. archive_cleanup_command = 'pg_archivecleanup /data/postgres12.2/arch %r'
7. standby_mode = on

复制代码

node3

1. # standby.signal文件需要新建
2. [postgres@keep-postgres12-node1 data]$ cat standby.signal
3. # 修改host为keep-postgres12-node2
4. primary_conninfo = 'host=keep-postgres12-node2 port=5432 user=replrole password=ReplRole@123 application_name=standby_pg3 options=''-c wal_sender_timeout=5000'''
5. restore_command = 'cp /data/postgres12.2/arch/%f %p'
6. archive_cleanup_command = 'pg_archivecleanup /data/postgres12.2/arch %r'
7. standby_mode = on

复制代码

5、备库修改设置文件postgresql.auto.conf

node1

1. [postgres@keep-postgres12-node1 data]$ cat postgresql.auto.conf
2. # 添加以下内容
3. 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

1. [postgres@keep-postgres12-node1 data]$ cat postgresql.auto.conf
2. # 修改host为keep-postgres12-node2
3. 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

1. [postgres@keep-postgres12-node1 data]$ pg_ctl restart

复制代码

node3

1. [postgres@keep-postgres12-node3 data]$ pg_ctl restart

复制代码

7、查抄数据库历程

1. # 备库 node1
2. [postgres@keep-postgres12-node1 ~]$ ps -ef | grep postgres
3. postgres   440     1  0 16:52 ?        00:00:00 /data/postgres12.2/bin/postgres
4. postgres   441   440  0 16:52 ?        00:00:00 postgres: logger
5. postgres   442   440  0 16:52 ?        00:00:00 postgres: startup   recovering 000000080000000000000032
6. postgres   446   440  0 16:52 ?        00:00:00 postgres: checkpointer
7. postgres   447   440  0 16:52 ?        00:00:00 postgres: background writer
8. postgres   448   440  0 16:52 ?        00:00:00 postgres: stats collector
9. postgres   449   440  0 16:52 ?        00:00:00 `postgres: walreceiver   streaming 0/320001C0`
11. # 主库
12. [postgres@keep-postgres12-node2 ~]$ ps -ef | grep postgres
13. postgres 25853     1  0 16:47 ?        00:00:00 /data/postgres12.2/bin/postgres
14. postgres 25854 25853  0 16:47 ?        00:00:00 postgres: logger
15. postgres 25856 25853  0 16:47 ?        00:00:00 postgres: checkpointer
16. postgres 25857 25853  0 16:47 ?        00:00:00 postgres: background writer
17. postgres 25858 25853  0 16:47 ?        00:00:00 postgres: walwriter
18. postgres 25859 25853  0 16:47 ?        00:00:00 postgres: autovacuum launcher
19. postgres 25860 25853  0 16:47 ?        00:00:00 postgres: archiver
20. postgres 25861 25853  0 16:47 ?        00:00:00 postgres: stats collector
21. postgres 25862 25853  0 16:47 ?        00:00:00 postgres: logical replication launcher
22. postgres 26311 25853  0 16:52 ?        00:00:00 `postgres: walsender replrole 192.168.122.90(16994) streaming 0/320001C0`
23. postgres 26312 25853  0 16:52 ?        00:00:00 `postgres: walsender replrole 192.168.122.87(18939) streaming 0/320001C0`
25. # 备库 node3
26. [postgres@keep-postgres12-node3 ~]$ ps -ef | grep postgres
27. postgres 31097     1  0 16:52 ?        00:00:00 /data/postgres12.2/bin/postgres
28. postgres 31098 31097  0 16:52 ?        00:00:00 postgres: logger
29. postgres 31099 31097  0 16:52 ?        00:00:00 postgres: startup   recovering 000000080000000000000032
30. postgres 31103 31097  0 16:52 ?        00:00:00 postgres: checkpointer
31. postgres 31104 31097  0 16:52 ?        00:00:00 postgres: background writer
32. postgres 31105 31097  0 16:52 ?        00:00:00 postgres: stats collector
33. postgres 31106 31097  0 16:52 ?        00:00:00 `postgres: walreceiver   streaming 0/320001C0`

复制代码

8、查抄同步状态

1. [postgres@keep-postgres12-node2 ~]$ psql
2. postgres=# \x
3. Expanded display is on.
4. postgres=# SELECT * FROM pg_stat_replication;
5. -[ RECORD 1 ]----+------------------------------
6. pid              | 26311
7. usesysid         | 16642
8. usename          | replrole
9. application_name | standby_pg3
10. client_addr      | 192.168.122.90
11. client_hostname  | keep-postgres12-node3
12. client_port      | 16994
13. backend_start    | 2024-12-10 16:52:56.465729+08
14. backend_xmin     |
15. state            | streaming
16. sent_lsn         | 0/32036768
17. write_lsn        | 0/32036768
18. flush_lsn        | 0/32036768
19. replay_lsn       | 0/32036768
20. write_lag        |
21. flush_lag        |
22. replay_lag       |
23. sync_priority    | 2
24. sync_state       | potential
25. reply_time       | 2024-12-10 16:56:27.053456+08
26. -[ RECORD 2 ]----+------------------------------
27. pid              | 26312
28. usesysid         | 16642
29. usename          | replrole
30. application_name | standby_pg1
31. client_addr      | 192.168.122.87
32. client_hostname  | keep-postgres12-node1
33. client_port      | 18939
34. backend_start    | 2024-12-10 16:52:58.753138+08
35. backend_xmin     |
36. state            | streaming
37. sent_lsn         | 0/32036768
38. write_lsn        | 0/32036768
39. flush_lsn        | 0/32036768
40. replay_lsn       | 0/32036768
41. write_lag        |
42. flush_lag        |
43. replay_lag       |
44. sync_priority    | 1
45. sync_state       | sync
46. reply_time       | 2024-12-10 16:56:27.047169+08

复制代码

9、验证是否同步

1. -- 主库执行
2. drop table if exists employees_multi_sync1 CASCADE;
3. CREATE TABLE employees_multi_sync1 (
4.     id SERIAL PRIMARY KEY,
5.     name VARCHAR(100),
6.     position VARCHAR(100),
7.     department VARCHAR(100),
8.     hire_date DATE
9. );
11. INSERT INTO employees_multi_sync1 (name, position, department, hire_date)
12. SELECT
13.     'Employee ' || (generate_series(1, 200))::text,
14.     'Position ' || (generate_series(1, 200))::text,
15.     'Department ' || (random() *(200-1)+1)::text,
16.     '2010-01-01'::date + (generate_series(1, 200) * interval '1 day');
18. -- 备库查询数据
19. select count(1) from employees_multi_sync1 ;
20. -- 计算两边的md5值
21. SELECT md5(
22.         string_agg(
23.     id::text || '-' ||
24.     name || '-' ||
25.     position || '-' ||
26.     department || '-' ||
27.     TO_CHAR(hire_date, 'YYYY-MM-DD'),',')
28. ) AS row_md5
29. FROM employees_multi_sync1;

复制代码

八、pg_rewind 工具

​ 当主备集群中的备库不测瓦解，且颠末长时间，归档日志又被删除了，必要把这段时间的增量数据同步回来，那么就可以用 到pg_rewind 工具进行同步。pg_rewind 使一个PostgreSQL 数据目录与另一个数据目录一致。

1. [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帮助下令

1. [postgres@keep-postgres12-node1 ~]$ pg_rewind --help
2. pg_rewind resynchronizes a PostgreSQL cluster with another copy of the cluster.
4. Usage:
5.   pg_rewind [OPTION]...
7. Options:
8.   -D, --target-pgdata=DIRECTORY  existing data directory to modify
9.       --source-pgdata=DIRECTORY  source data directory to synchronize with
10.       --source-server=CONNSTR    source server to synchronize with
11.   -n, --dry-run                  stop before modifying anything
12.   -N, --no-sync                  do not wait for changes to be written
13.                                  safely to disk
14.   -P, --progress                 write progress messages
15.       --debug                    write a lot of debug messages
16.   -V, --version                  output version information, then exit
17.   -?, --help                     show this help, then exit

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。