postgres主从基于流复制
环境： CentOS Linux release 7.6.1810 (Core) 内核版本:3.10.0-957.10.1.el7.x86_64
node1:192.168.216.130
node2:192.168.216.132
node3:192.168.216.134
一、首先在3个节点分别安装postgres,这里由于线上环境使用9.5.3,故本人直接使用9.5.3用于实验测试,其他版本的编译安装方式是相同的
yum install gcc readline-devel zlib-devel cd /tmp/ wget https://ftp.postgresql.org/pub/source/v9.5.3/postgresql-9.5.3.tar.gz tar -xf postgresql-9.5.3.tar.gz useradd postgres ./configure --prefix=/usr/local/postgresql make -j2 make install chown -R postgres:postgres /usr/local/postgresql/
二、 在2个节点上分别配置环境变量并执行初始化操作
1、切换用户 su - postgres 2、配置环境变量 vi .bash_profile [postgres@localhost ~]$ cat .bash_profile # .bash_profile # Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc fi # User specific environment and startup programs PGHOME=/usr/local/postgresql export PGHOME PGDATA=/usr/local/postgresql/data export PGDATA PATH=$PATH:$HOME/.local/bin:$HOME/bin:$PGHOME/bin export PATH 3、使上述环境立即变量生效 source .bash_profile 4、验证环境变量是否配置成功(查询postgres数据库版本号) [postgres@localhost ~]$ psql -V psql (PostgreSQL) 9.5.3 4、执行初始化： initdb
三、编辑node1节点上的postgresql.conf配置文件
仅供参考，以下参数请根据需求进行合理配置修改 listen_addresses = '*' port = 5432 wal_level = logical archive_mode = on max_wal_senders = 10 hot_standby = on log_destination = 'csvlog' logging_collector = on log_directory = 'pg_log' log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' log_rotation_age = 1d log_rotation_size = 20MB
四、编辑node1节点上的pg_hba.conf配置文件
# TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all postgres trust host all all 0.0.0.0/0 md5 # IPv4 local connections: host all postgres 127.0.0.1/32 trust # IPv6 local connections: host all postgres ::1/128 trust # Allow replication connections from localhost, by a user with the # replication privilege. local replication replicator trust host replication replicator 0.0.0.0/0 md5 host replication postgres 127.0.0.1/32 trust #host replication postgres ::1/128 trust
五、启动node1节点的数据库服务,并连接主库
pg_ctl -D /usr/local/postgresql/data start
验证主库是否正常 执行创建一个测试库sql语句，用于测试主库 create database test; 下图是我已经执行过的
执行sql语句创建基于流复制的用户，后面会用到，这里所创建的replicator用户名注意和上文的pg_hba.conf配置文件中的用户保持一致 create user replicator replication login encrypted password '1qaz2wsx';
六、创建物理复制槽，分别对应node2，node3
select * from pg_create_physical_replication_slot('pgsql95_132');
select * from pg_create_physical_replication_slot('pgsql95_134');
七、在node2、node3节点上清空初始化后的data数据
进入初始化目录 cd /usr/local/postgresql/data/ 清空当前目录下的所有文件 rm -rf *
八、在node2、node3节点上执行基础备份，配置stream replication
./pg_basebackup -h 192.168.216.130 -D /usr/local/postgresql/data -U replicator -c fast -X stream -v -P -R
这里的密码为创建replicator用户时所指定的密码
编辑node2、node3上的recovery.conf文件，注意 recovery.conf 的 primary_slot_name 在不同节点值会不同。
node2：
[postgres@localhost data]$ cat recovery.conf standby_mode = 'on' recovery_target_timeline = 'latest' primary_conninfo = 'user=replicator password=1qaz2wsx host=192.168.216.130 port=5432 sslmode=disable sslcompression=1' primary_slot_name = 'pgsql95_132'
node3：
[postgres@localhost data]$ cat recovery.conf standby_mode = 'on' recovery_target_timeline = 'latest' primary_conninfo = 'user=replicator password=1qaz2wsx host=192.168.216.130 port=5432 sslmode=disable sslcompression=1' primary_slot_name = 'pgsql95_134'
九、分别启动node2、node3节点的postgres服务
pg_ctl -D /usr/local/postgresql/data start
此时在node1节点上执行以下sql语句，可以看到active字段由原来的“f”变为“t”
且在主库上(node1)可以看到postgres进程为wal sender状态
两个从库上node2，node3上的postgres进程为wal receiver状态
查询复制状态
select client_addr,
pg_xlog_location_diff(sent_location, write_location) as write_delay,
pg_xlog_location_diff(sent_location, flush_location) as flush_delay,
pg_xlog_location_diff(sent_location, replay_location) as replay_delay
from pg_stat_replication;
