欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

PostgreSQL流复制

程序员文章站 2024-03-21 13:48:16
...

异步流复制hot standby
0、环境说明:
1、主库配置
首先需要在主库配置接受流复制的连接,修改pg_hba.conf文件

# TYPE  DATABASE        USER            ADDRESS                 METHOD
  host  replication     all             192.168.7.177/24        trust

修改postgresql.conf,由于要使用流复制,所以max_wal_senders要修改成一个大于0的数。
listen_addresses = ‘*’
max_wal_senders = 5
wal_level = hot_standby

2、备库配置
2、1在备库生成基础备份
[email protected]> pg_basebackup -h 192.168.17.190 -U osdba -F p -P -R -D /home/itm_pg/bk_data/ -l backup190117
WARNING: skipping special file “./.s.PGSQL.1921”
WARNING: skipping special file “./.s.PGSQL.1921”
1580781/1580781 kB (100%), 1/1 tablespace
这里需要注意,要使用一个空的目录,这里新建了一个目录,否则会报错:
pg_basebackup: directory “/home/itm_pg/pgdata” exists but is not empty

2.2、修改参数文件
在/home/itm_pg/bk_data/目录下修改recovery.conf文件,由于pg_basebackup使用了-R参数,所以生成了recovery.conf文件,否则需要手动去share目录中拷贝
standby_mode = ‘on’
primary_conninfo = ‘user=osdba password=osdba host=192.168.17.190 port=1921 sslmode=disable sslcompression=1 target_session_attrs=any’

还要修改postgresql.conf文件
hot_standby=on

2.3、启动数据库
这里需要注意要指定bk_data目录启动数据库。
[email protected]> pg_ctl start -D /home/itm_pg/bk_data
[email protected]> ps -ef|grep postgres
itm_pg 13910 1 0 16:11 pts/1 00:00:00 /home/itm_pg/pgsql10.3/bin/postgres -D /home/itm_pg/pg_bak
itm_pg 13911 13910 0 16:11 ? 00:00:00 postgres: logger process
itm_pg 13912 13910 0 16:11 ? 00:00:00 postgres: startup process recovering 000000010000000000000051
itm_pg 13913 13910 0 16:11 ? 00:00:00 postgres: checkpointer process
itm_pg 13914 13910 0 16:11 ? 00:00:00 postgres: writer process
itm_pg 13915 13910 0 16:11 ? 00:00:00 postgres: stats collector process
itm_pg 13916 13910 0 16:11 ? 00:00:00 postgres: wal receiver process streaming 0/51000140
itm_pg 13918 13852 0 16:11 pts/1 00:00:00 grep postgres

postgres: wal receiver process,说明是备库。至此,异步standby就搭建完成了!

同步流复制standby
0、环境说明:

1、主库配置
同样需要在主库配置接受流复制的连接,修改pg_hba.conf文件,添加另一个备库的信息

# TYPE  DATABASE        USER            ADDRESS                 METHOD
  host  replication     all             192.168.7.166/24        trust
在postgresql.conf文件添加
listen_addresses = '*'
max_wal_senders = 5
wal_level = hot_standby
synchronous_standby_names = 'standby01,standby02'

2、备库配置
在两个备库执行同样操作
备份数据:
[email protected]> pg_basebackup -h 192.168.17.190 -U osdba -F p -P -R -D /home/itm_pg/pgdata/bk_data/ -l backup190117
WARNING: skipping special file “./.s.PGSQL.1921”
WARNING: skipping special file “./.s.PGSQL.1921”
1580790/1580790 kB (100%), 1/1 tablespace
修改recovery.conf文件:
standby_mode = ‘on’
primary_conninfo = ‘application_name=standby01 user=osdba passfile=’’/home/digoal/.pgpass’’ host=192.168.17.190 port=1921 sslmode=disable sslcompression=1 target_session_attrs=any’
然后启动数据库即可,接着在另一台备库执行同样的操作。
主库查询:

postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 25494
usesysid         | 16420
usename          | osdba
application_name | standby02
client_addr      | 192.168.7.166
client_hostname  | 
client_port      | 45526
backend_start    | 2019-01-16 10:29:01.391783+08
backend_xmin     | 
state            | streaming
sent_lsn         | 0/55000220
write_lsn        | 0/55000220
flush_lsn        | 0/55000220
replay_lsn       | 0/55000220
write_lag        | 00:00:00.00067
flush_lag        | 00:00:00.00067
replay_lag       | 00:00:00.00067
sync_priority    | 2
sync_state       | potential
-[ RECORD 2 ]----+------------------------------
pid              | 25492
usesysid         | 16420
usename          | osdba
application_name | standby01
client_addr      | 192.168.7.177
client_hostname  | 
client_port      | 55266
backend_start    | 2019-01-16 10:29:00.600386+08
backend_xmin     | 
state            | streaming
sent_lsn         | 0/55000220
write_lsn        | 0/55000220
flush_lsn        | 0/55000220
replay_lsn       | 0/55000220
write_lag        | 00:00:00.000536
flush_lag        | 00:00:00.000536
replay_lag       | 00:00:00.000536
sync_priority    | 1
sync_state       | sync

至此,完成同步流复制standby数据库的搭建!