示例环境 |
|||||
主机名 |
IP |
角色 |
系统版本 |
数据目录 |
pg版本 |
master |
192.168.174.200 |
主库 |
CentOS Linux release 7.4.1708 (Core)
|
/var/lib/pgsql/data |
9.2.23 |
slave1 |
192.168.129.201 |
备库 |
CentOS Linux release 7.4.1708 (Core) |
/var/lib/pgsql/data |
9.2.23 |
备库不需要执行:service postgresql initdb、service postgresql start
如已经执行请删除对应目录
rm -rf /var/lib/pgsql
mkdir -p /var/lib/pgsql/{data,xlog_archive} chown -R postgres:postgres /var/lib/pgsql/ chmod 0700 /var/lib/pgsql/data
主库配置(192.168.174.200)
创建复制角色
#启动数据库 service postgresql start #切换用户 su - postgres #登入数据库 psql -U postgres #创建replicator用户 create role replicator with login replication password '123456'; #将用户postgres的密码修改为postgres alter user postgres with password 'postgres'; #退出 \q
配置pg_hba.conf
vi /var/lib/pgsql/data/pg_hba.conf
# IPv4 local connections:
host all all 0.0.0.0/0 trust
# IPv6 local connections:
host all all ::1/128 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
host replication all 0.0.0.0/0 trust
配置postgresql.conf
mkdir /var/lib/pgsql/xlog_archive chmod 0700 /var/lib/pgsql/xlog_archive
vi /var/lib/pgsql/data/postgresql.conf
listen_addresses = '*' wal_level = hot_standby synchronous_commit = on archive_mode = on archive_command = 'cp %p /var/lib/pgsql/xlog_archive/%f' max_wal_senders=5 wal_keep_segments = 32 hot_standby = on restart_after_crash = off replication_timeout = 5000 wal_receiver_status_interval = 2 max_standby_streaming_delay = -1 max_standby_archive_delay = -1 synchronous_commit = on restart_after_crash = off hot_standby_feedback = on
重启服务
service postgresql restart
创建目录
mkdir -p /var/lib/pgsql/{data,xlog_archive} chown -R postgres:postgres /var/lib/pgsql/ chmod 0700 /var/lib/pgsql/data
pg_basebackup搭建主备流复制环境
su - postgres pg_basebackup -h 192.168.174.200 -U postgres -D /var/lib/pgsql/data/ -X stream -P
修改recovery.conf配置
vim /var/lib/pgsql/data/recovery.conf standby_mode = 'on' primary_conninfo = 'host=192.168.174.200 port=5432 user=replicator application_name=myapp-1 password=123456 keepalives_idle=60 keepalives_interval=5 keepalives_count=5' restore_command = 'cp /var/lib/pgsql/xlog_archive/%f %p' recovery_target_timeline = 'latest'
启动服务
su - postgres pg_ctl -D /var/lib/pgsql/data/ start测试
select * from pg_stat_replication;
主库201中创建mytab的表里面插入三条数据:
登入备库进行查看,数据已经同步过来。