环境: 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-develcd /tmp/wget https://ftp.postgresql.org/pub/source/v9.5.3/postgresql-9.5.3.tar.gztar -xf postgresql-9.5.3.tar.gzuseradd postgres./configure --prefix=/usr/local/postgresqlmake -j2make installchown -R postgres:postgres /usr/local/postgresql/
二、 在2个节点上分别配置环境变量并执行初始化 *** 作
1、切换用户su - postgres2、配置环境变量vi .bash_profile[[email protected] ~]$ cat .bash_profile# .bash_profile# Get the aliases and functionsif [ -f ~/.bashrc ]; then . ~/.bashrcfi# User specific environment and startup programsPGHOME=/usr/local/postgresqlexport PGHOMEPGDATA=/usr/local/postgresql/dataexport PGDATAPATH=$PATH:$HOME/.local/bin:$HOME/bin:$PGHOME/binexport PATH3、使上述环境立即变量生效source .bash_profile4、验证环境变量是否配置成功(查询postgres数据库版本号)[[email protected] ~]$ psql -Vpsql (Postgresql) 9.5.34、执行初始化:initdb
三、编辑node1节点上的postgresql.conf配置文件
仅供参考,以下参数请根据需求进行合理配置修改Listen_addresses = ‘*‘port = 5432wal_level = logicalarchive_mode = onmax_wal_senders = 10hot_standby = onlog_destination = ‘csvlog‘logging_collector = onlog_directory = ‘pg_log‘log_filename = ‘postgresql-%Y-%m-%d_%H%M%s.log‘log_rotation_age = 1dlog_rotation_size = 20MB
四、编辑node1节点上的pg_hba.conf配置文件
# TYPE DATABASE USER ADDRESS METHOD# "local" is for Unix domain socket connections onlylocal all postgres trusthost 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 trusthost replication replicator 0.0.0.0/0 md5host 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:
[[email protected] 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:
[[email protected] 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,flush_location) as flush_delay,replay_location) as replay_delay from pg_stat_replication;
总结
以上是内存溢出为你收集整理的postgres主从基于流复制全部内容,希望文章能够帮你解决postgres主从基于流复制所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错,欢迎将内存溢出网站推荐给程序员好友。
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)