Postgresql流复制1主3从环境搭建

最新推荐文章于 2024-08-06 11:04:47 发布

wangwei830

最新推荐文章于 2024-08-06 11:04:47 发布

阅读量316

点赞数

分类专栏： postgresql 数据库文章标签： postgresql 数据库

本文链接：https://blog.csdn.net/m0_46426259/article/details/115343126

版权

数据库同时被 2 个专栏收录

4 篇文章 0 订阅

订阅专栏

postgresql

3 篇文章 0 订阅

订阅专栏

说明

1234 分别代表：节点55，节点56，节点57，节点58
1做主
234做从

开启归档模式1234：

# su - postgres
cd /opt/pgsql/data
cp postgresql.conf postgresql.conf_0
mkdir /opt/pgsql/data/arch/

cat >>  postgresql.conf <<EOF

listen_addresses = '*'
wal_level = replica
archive_mode = on
archive_command = 'cp %p /opt/pgsql/data/arch/%f'
restore_command = 'cp /opt/pgsql/data/arch/%f %p'
full_page_writes = on
wal_log_hints = on
wal_keep_segments = 100
log_checkpoints = on
EOF

pg_ctl stop 
pg_ctl start
psql postgres -c "show archive_mode;"
psql postgres -c "select pg_switch_wal(); "
 #这里显示的数字是最近一个归档的日志号

关闭1234

pg_ctl stop

配置hosts

cat >> /etc/hosts << EOF

1.1.1.1      wei1
1.1.1.55    pg55
1.1.1.56    pg56
1.1.1.57    pg57
1.1.1.58    pg58
EOF

创建hphdr目录，定义recovery1234文件

mkdir /opt/pghdr
chown postgres:postgres /opt/pghdr
# su - postgres
cat > /opt/pghdr/recovery.conf.z55 << EOF
recovery_target_timeline = 'latest'
standby_mode = on
primary_conninfo = 'host=pg55 port=5432 user=repuser password=repuser'
trigger_file = '/opt/pghdr/trigger_file'
EOF
cat > /opt/pghdr/recovery.conf.z56 << EOF
recovery_target_timeline = 'latest'
standby_mode = on
primary_conninfo = 'host=pg56 port=5432 user=repuser password=repuser'
trigger_file = '/opt/pghdr/trigger_file'
EOF
cat > /opt/pghdr/recovery.conf.z57 << EOF
recovery_target_timeline = 'latest'
standby_mode = on
primary_conninfo = 'host=pg57 port=5432 user=repuser password=repuser'
trigger_file = '/opt/pghdr/trigger_file'
EOF
cat > /opt/pghdr/recovery.conf.z58 << EOF
recovery_target_timeline = 'latest'
standby_mode = on
primary_conninfo = 'host=pg58 port=5432 user=repuser password=repuser'
trigger_file = '/opt/pghdr/trigger_file'
EOF

在主上创建由于复制的用户1

pg_ctl start
psql    postgres  -c " CREATE USER repuser replication LOGIN CONNECTION LIMIT 3 ENCRYPTED PASSWORD 'repuser'; "

配置hba文件

cat >> $PGDATA/pg_hba.conf <<EOF

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host replication      repuser         pg55        md5
host replication      repuser         pg56        md5
host replication      repuser         pg57        md5
host replication      repuser         pg58        md5
EOF
pg_ctl reload

开始恢复从库234

rm -fr /opt/pgsql/data/*
pg_basebackup -h pg55 -p 5432 -U repuser -R -F p -X stream  -v -P -l replbackup -D /opt/pgsql/data

参考：-------------------------------可以略过 begin
###### 这样恢复会自动创建standby.signal和backup_label和auto里面的关系，可以直接启动备实例就是复制关系了，这个文件auto文件也会自动生成。
postgres@pg56:/opt/pgsql/data$ ls -lrt $PGDATA |grep -E "standby|auto|back"
-rw------- 1 postgres postgres   209 3月  31 10:30 backup_label.old
-rw------- 1 postgres postgres     0 3月  31 10:30 standby.signal
-rw------- 1 postgres postgres   256 3月  31 10:30 postgresql.auto.conf
postgres@pg56:/opt/pgsql/data$ 

postgres@pg56:/opt/pgsql/data$ cat backup_label.old 
START WAL LOCATION: 0/4000028 (file 000000010000000000000004)
CHECKPOINT LOCATION: 0/4000060
BACKUP METHOD: streamed
BACKUP FROM: master
START TIME: 2021-03-31 10:30:25 CST
LABEL: replbackup
START TIMELINE: 1
postgres@pg56:/opt/pgsql/data$ 

postgres@pg56:/opt/pgsql/data$ cat standby.signal 
postgres@pg56:/opt/pgsql/data$ 

postgres@pg56:/opt/pgsql/data$ cat postgresql.auto.conf 
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
primary_conninfo = 'user=repuser password=repuser host=pg55 port=5432 sslmode=disable sslcompression=0 gssencmode=disable krbsrvname=postgres target_session_attrs=any'
postgres@pg56:/opt/pgsql/data$ 
参考：-------------------------------可以略过 end

启动从库

pg_ctl start

验证从库模式：
postgres@pg56:~$ pg_controldata|grep cluster
Database cluster state: in archive recovery
postgres@pg56:~$

查看流复制关系

select pg_is_in_recovery();
-- postgres=# \x
-- Expanded display is on.
select * from pg_stat_replication; 
--正常结果：主有3条记录，备无记录

测试表测试数据

CREATE TABLE adg (c1 bigint  primary key,c2 bigint,c3 time,c4 character varying(136));
-- 插入5行随机数据
insert into adg(c1,c2,c3,c4) values (random()*10000000, random()*1000,now(),md5(random()::text));
insert into adg(c1,c2,c3,c4) values (random()*10000000, random()*1000,now(),md5(random()::text));
insert into adg(c1,c2,c3,c4) values (random()*10000000, random()*1000,now(),md5(random()::text));
insert into adg(c1,c2,c3,c4) values (random()*10000000, random()*1000,now(),md5(random()::text));
insert into adg(c1,c2,c3,c4) values (random()*10000000, random()*1000,now(),md5(random()::text));

参考主进程

[postgres@w21 data]$ ps -ef |grep postgres|grep -v grep
postgres   8723      1  0 13:59 ?        00:00:00 /opt/pgsql/bin/postgres
postgres   8725   8723  0 13:59 ?        00:00:00 postgres: checkpointer   
postgres   8726   8723  0 13:59 ?        00:00:00 postgres: background writer   
postgres   8727   8723  0 13:59 ?        00:00:00 postgres: walwriter   
postgres   8728   8723  0 13:59 ?        00:00:00 postgres: autovacuum launcher   
postgres   8729   8723  0 13:59 ?        00:00:00 postgres: archiver   last was 000000020000000000000019.00000028.backup
postgres   8730   8723  0 13:59 ?        00:00:00 postgres: stats collector   
postgres   8731   8723  0 13:59 ?        00:00:00 postgres: logical replication launcher   
postgres   8992   8723  0 14:15 ?        00:00:00 postgres: walsender repuser 1.1.1.22(58512) streaming 0/1A000148
postgres   9003   8723  0 14:16 ?        00:00:00 postgres: postgres postgres [local] idle
[postgres@w21 data]$

参考备进程

[postgres@w22 data]$ ps -ef |grep postgres |grep -v grep
postgres  11719      1  0 14:16 ?        00:00:00 /opt/pgsql/bin/postgres
postgres  11720  11719  0 14:16 ?        00:00:00 postgres: startup   recovering 00000002000000000000001A
postgres  11724  11719  0 14:16 ?        00:00:00 postgres: checkpointer   
postgres  11725  11719  0 14:16 ?        00:00:00 postgres: background writer   
postgres  11727  11719  0 14:16 ?        00:00:00 postgres: stats collector   
postgres  11728  11719  0 14:16 ?        00:00:00 postgres: walreceiver   streaming 0/1A000148
postgres  11732  11719  0 14:16 ?        00:00:00 postgres: postgres postgres [local] idle
[postgres@w22 data]$

同步和异步

备：
[postgres@w22 data]$ cat postgresql.auto.conf 
primary_conninfo = 'application_name=apph22 user=repuser password=repuser host=h21 port=5432 sslmode=disable sslcompression=0 gssencmode=disable krbsrvname=postgres target_session_attrs=any'

主：
[postgres@w21 data]$ 
vi postgresql.conf
synchronous_standby_names = 'apph22'   #主pg_ctl reload即可，无需重启。

高级指定：
synchronous_standby_names  = 'any 1 (apph21,apph22,apph23,apph24,apph25)'   #任意一个同步
synchronous_standby_names  = 'first 2 (apph21,apph22,apph23,apph24,apph25)'  #前面两个同步

会话级别控制同异步：
set synchronous_commit = off ;    #取消同步 （测试这个好像不管用）

注明：主备切换，下一篇博文写一写。