postgresql搭建从库_196.27

master   10.40.196.27
slave       10.40.55.69
需求:master和slave作为主从流复制,当master宕机后,slave切换为新主继续服务;然而当master恢复故障后,作为从重新加入主从流复制。
master
1.创建复制用户
create user rep replication password 'rep';
2.设置pg_hba.conf
-bash-4.1$ cat pg_hba.conf|grep repli|grep -v '#'
host    replication     rep             10.40.55.69/32            md5
3.设置主库postgresql.conf
#add by b
wal_level='hot_standby'
archive_mode = on
archive_command = 'ssh 10.40.55.201 test ! -f /backup2/pgdata/digoal/19627/data04/pg93archdir/%f && scp %p
10.40.55.201:/backup2/pgdata/digoal/19627/data04/pg93archdir/%f'
max_worker_processes = 16
max_wal_senders = 8
wal_keep_segments = 32
4.重启数据库
pg_ctl restart -D /data/pgsql/data
slave配置
1.创建数据目录
[postgres@db-hx-55-69 ~]$ echo $PGDATA
/opt/bodaodao /postgresql/data
mkdir -p /opt/bodaodao /postgresql/data;cd /opt/bodaodao /postgresql
chown -R postgres.postgres data
chmod 700 data
cd $PGDATA   (/opt/bodaodao /postgresql/data)
rm -rf *
master 196.27
-bash-4.1$ cd $PGDATA/pg_tblspc/
-bash-4.1$ ll
total 0
lrwxrwxrwx 1 postgres postgres 25 Jan 12 13:34 1031792 -> /opt/pgsql/bi_erp_itf_tbs
lrwxrwxrwx 1 postgres postgres 14 May 20  2016 16385 -> /opt/pgsql/tbs
[root@db-hx-55-69 pgsql]# pwd
/opt/pgsql
[root@db-hx-55-69 pgsql]# rm -rf *
2.使用pg_basebackup生成基础备份,放到slave上数据目录下
[postgres@db-hx-55-69 ~]$ pg_basebackup -D /opt/bohai /postgresql/data -Fp -Xs -v -P -h 10.40.196.27 -p 5432 -U rep
Password: rep
3.配置recovery.conf
vi $PGDATA/recovery.conf
standby_mode = on
trigger_file = '/opt/bodaodao /postgresql/pg.trigger'
primary_conninfo = 'host=10.40.196.27 port=5432 user=rep password=rep'
4.启动pgsql (确定配置文件含有 hot_standby = on )
$ pg_ctl start -D /opt/bodaodao /postgresql/data
测试流复制
1.查看主库
主库$ ps -ef |grep postgres|grep sender |grep -v grep
postgres  89707  65237  0 00:15 ?        00:00:00 postgres: wal sender process rep 10.40.55.69(26717) streaming 9F1/51EE4FB0
确认主库中进程有“postgres: wal sender process”
2.查看从库
$ ps -ef |grep postgres |grep -v grep |grep receiver
postgres 14055 14013  1 00:15 ?        00:00:02 postgres: wal receiver process   streaming 9F1/5423E000  
确定备库进程中有”postgres: wal receiver process”
4.查看主备同步状态
postgres=# select * from pg_stat_replication;
  pid  | usesysid | usename | application_name | client_addr | client_hostname | client_port |         backend_start         | backend_xmin |   
state   | sent_location
| write_location | flush_location | replay_location | sync_priority | sync_state
-------+----------+---------+------------------+-------------+-----------------+-------------+-------------------------------+--------------
+-----------+---------------
+----------------+----------------+-----------------+---------------+------------
89707 |   988512 | rep     | walreceiver      | 10.40.55.69 |                 |       26717 | 2018-03-10 00:15:14.610005+08 |              |
streaming | 9F1/6073C458  
| 9F1/6073C458   | 9F1/6073C458   | 9F1/6073BFE0    |             0 | async
(1 row)
字段state显示的同步状态有:startup(连接中)、catchup(同步中)、streaming(同步);
字段sync_state显示的模式有:async(异步)、sync(同步)、potential(虽然现在是异步模式,但是有可能升级到同步模式)

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值