![](/style/images/good.png)
![](/style/images/bad.png)
PostgreSQL的流复制搭建
source link: https://blog.51cto.com/u_13874232/5756337
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
单机版流复制
测试环境搭建
/pgdata/12/data | /pgdata/1202/data | |
/pgdata/12/arch | /pgdata/1202/arch | |
创建流复制用户
create role replica with replication login password '123456';
pg_basebackup -D /backup/ -Ft -Pv -U postgres -h 1.15.57.253 -p5432 -R
解压备份
cd /backup
tar -xvf base.tar -C /pgdata/12/data
tar -xvf pg-wal.tar -C /pgdata/12/arch
修改postgresql.conf
max_wal_senders = 10 #设置可以最多有几个流复制连接,差不多有几个从,就设置几个 ,相当月mysql的binlog dump线程
wal_keep_segments = 0 #设置流复制保留的最多的xlog数目 128
wal_sender_timeout = 60s #设置流复制主机发送数据包的超时时间
max_connections = 100 #一般查多于写的应用从库的最大连接数比较大
hot_standby = on #针对从库,说明这台机器不仅仅是用于数据归档,也用于数据查询
max_standby_streaming_delay = 30s #数据备份的最大延迟时间
wal_receiver_status_interval = 10s #多久向主报告一次从的状态,当然从每次数据复制都会向主报告状态,这里只是设置最长的时间间隔
hot_standby_feedback = on #如果有错误的数据复制,是否向主进行反馈
recovery_target_timeline = 'latest' #指定恢复到一个最近的时间线
另外因为是单机多实例
还需要修改这几个参数
archive_command = 'cp %p /pgdata/1202/arch/%f'
port = 5433
修改standby.signal
standby_mode = 'on'
表示为备库
pg_ctl -D /pgdata/1202/data
CST [22362] LOG: started streaming WAL from primary at 0/6000000 on timeline 2
select pid,state,client_addr,sync_priority,sync_state from pg_stat_replication;
psql -U postgres -h localhost -p 5433 -c "\x" -c "select * from pg_stat_wal_receiver;"
![PostgreSQL的流复制搭建_数据复制_03](https://s2.51cto.com/images/blog/202210/14131414_6348f0265e9e94409.png?x-oss-process=image/watermark,size_14,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_30,g_se,x_10,y_10,shadow_20,type_ZmFuZ3poZW5naGVpdGk=,x-oss-process=image/resize,m_fixed,w_1184/format,webp)
主库上新建一个库
![PostgreSQL的流复制搭建_数据复制_04](https://s2.51cto.com/images/blog/202210/14131414_6348f026624fc44046.png?x-oss-process=image/watermark,size_14,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_30,g_se,x_10,y_10,shadow_20,type_ZmFuZ3poZW5naGVpdGk=,x-oss-process=image/resize,m_fixed,w_1184/format,webp)
![PostgreSQL的流复制搭建_数据复制_05](https://s2.51cto.com/images/blog/202210/14131414_6348f0266123058611.png?x-oss-process=image/watermark,size_14,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_30,g_se,x_10,y_10,shadow_20,type_ZmFuZ3poZW5naGVpdGk=,x-oss-process=image/resize,m_fixed,w_1184/format,webp)
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK