PostgreSQL10流复制环境搭建及主备库切换
环境
centos7.1 两台postgresql10数据库
步骤
一、搭建流复制环境
1.启动主库 在主库创建一个流复制账户:
create role replication login replication encrypted password ‘replication’;
2.在主库的pg_hba.conf文件中加入一行信息:备库的IP和刚才创建的流复制用户replication:
host replication replication 192.168.2.12/32 md5
3.关闭备库 ,在备库的bash_shell 中执行:
pg_basebackup -h 192.168.2.11 -p 1921 -U replication -F p -P -D /pgdata/10/data/ -R
这里不用编写 recovery.conf配置文件,因为-R命令会自动帮我们写这个文件。
参数解释:
-R: write recovery conf;
-h : 主库的ip地址
-p: 主库的端口号
-D:从库用来接收主库文件的目录,如果该目录不为空,要清空该文件夹
-F:用来指定复制的文件的格式 后面跟 p 表示跟原来一样的文件,t 表示tar压缩文件。
4.执行pg_basebackup命令后,会把备库的数据文件都覆盖掉,备库的端口号会和主库的端口号一样,备库的端口号可以修改。
5.启动备库
pg_ctl -D /pgdata/10/data/ start
6.检查流复制是否搭建成功:
在主库执行create table test(id int); 并插入一条记录:insert into test id values(1);
去备库中检查是否有上面新建的表和插入的记录。如果有表示流复制搭建成功。
还有一种方法:
在主库中执行 select * from pg_stat_replication;有记录说明流复制搭建成功。
postgres=# \x
Expanded display is on.
postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid | 56656
usesysid | 16384
usename | replication
application_name | walreceiver
client_addr | 192.168.2.11
client_hostname |
client_port | 34364
backend_start | 2019-11-02 11:23:50.027053+08
backend_xmin |
state | streaming
sent_lsn | 0/3000140
write_lsn | 0/3000140
flush_lsn | 0/3000140
replay_lsn | 0/3000140
write_lag |
flush_lag |
replay_lag |
sync_priority | 0
sync_state | async
也可以在备库中执行:
postgres=# \x
Expanded display is on.
postgres=# select * from pg_stat_wal_receiver;
-[ RECORD 1 ]---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pid | 9567
status | streaming
receive_start_lsn | 0/3000000
receive_start_tli | 1
received_lsn | 0/3000140
received_tli | 1
last_msg_send_time | 2019-11-02 11:25:50.316725+08
last_msg_receipt_time | 2019-11-02 11:25:50.313582+08
latest_end_lsn | 0/3000140
latest_end_time | 2019-11-02 11:23:50.02965+08
slot_name |
conninfo | user=replication password=******** dbname=replication host=192.168.2.13 port=5432 fallback_application_name=walreceiver sslmode=disable sslcompression=1 target_session_attrs=any
二、主备切换
1.主库挂了,备库怎么提升为主库呢?
在从库的bash_shell中执行pg_ctl promote命令,这个备库会切换为主库
-bash-4.2$ pg_ctl status
pg_ctl: server is running (PID: 2555)
/usr/local/pgsql/bin/postgres
-bash-4.2$ pg_ctl promote
waiting for server to promote.... done
server promoted
-bash-4.2$ psql
psql (10.0)
Type "help" for help.
由此可见,备库已提升为主库:
postgres=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
f
(1 row)
还可以看下$PGDATA目录下的recovery.conf是否变成recovery.done.
2.那么原来的主库怎么变成备库呢?
原来主库没有recovery.conf文件,可以从备库拷贝一份到$PGDATA目录下。
改下recovery.conf文件中的host信息,指向现在的主库即可,然后重启,原来的主库就变成了备库;
postgres=# select pg_is_in_recovery();
pg_is_in_recovery
-------------------
t
(1 row)
或者这样看:
postgres=# select * from pg_stat_wal_receiver;
-[ RECORD 1 ]---------+-------------------------------------------------------------------------------------------------------------------------------
pid | 4189
status | waiting
receive_start_lsn |
receive_start_tli | 0
received_lsn | 0/61000000
received_tli | 1
last_msg_send_time | 2019-11-02 17:03:37.756998+08
last_msg_receipt_time | 2019-11-02 17:03:37.762319+08
latest_end_lsn | 0/61C64000
latest_end_time | 2019-11-02 16:44:11.21797+08
slot_name |
conninfo | user=replication password=replication host=192.168.2.13 port=5432 sslmode=disable sslcompression=1 target_session_attrs=any