postgresql主从切换(promote方式主从切换)

主：192.168.1.130

从:192.168.1.131

1、主备库进程查看
主库
[postgres@localhost data]$ pg_controldata /opt/postgresql-11.6/data/| grep 'Database cluster state'
Database cluster state:               in production
[postgres@localhost data]$

备库
[postgres@localhost data]$ pg_controldata /opt/postgresql-11.6/data/| grep 'Database cluster state'
Database cluster state:               in archive recovery
[postgres@localhost data]$

2、停掉主库
pg_ctl -D /opt/postgresql-11.6/data/ -l /opt/postgresql-11.6/log/postgres.log stop

查看状态已经处于stop状态
[postgres@localhost data]$ pg_controldata /opt/postgresql-11.6/data/| grep 'Database cluster state'
Database cluster state:               shut down


3、提升从库为主库
这个时候从库保持运行状态,不需要停掉
[postgres@localhost data]$ pg_ctl promote -D /opt/postgresql-11.6/data/
waiting for server to promote.... done
server promoted

查看状态
[postgres@localhost data]$ pg_controldata /opt/postgresql-11.6/data/| grep 'Database cluster state'
Database cluster state:               in production



4、验证
这个时候从库的recovery.conf文件会自动命名为recovery.done

尝试在原来的从库写入数据

insert into tb_hxl01 values(20,'name6');
insert into tb_hxl01 values(21,'name7');
insert into tb_hxl01 values(22,'name8');
insert into tb_hxl01 values(23,'name9');
insert into tb_hxl01 values(24,'name10');
insert into tb_hxl01 values(25,'name10');
写入新增数据,重库启动后,模拟差异数据是否同步到从库



5、将原来的主库部署成为重库
5.1 创建recovery.conf文件
在原来主库的data目录下创建recovery.conf文件
standby_mode='on'
recovery_target_timeline = 'latest'
primary_conninfo = 'host=192.168.1.131 port=5432 user=repl password=repl'

5.2 启动
[postgres@localhost data]$ pg_ctl -D /opt/postgresql-11.6/data/ -l /opt/postgresql-11.6/log/postgres.log start

查看状态
[postgres@localhost data]$ pg_controldata /opt/postgresql-11.6/data/| grep 'Database cluster state'
Database cluster state:               in archive recovery


这个时候发现数据库无法连接
[postgres@localhost log]$ psql
psql: could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/tmp/.s.PGSQL.5432"?


查看错误日志postgres.log
2020-01-19 15:07:55.297 CST [31189] FATAL:  hot standby is not possible because max_connections = 100 is a lower setting than on the master server (its value was 1000)

解决办法,修改当前数据库的max_connections与主库保持一致

5.3 验证刚才从库写入的数据是否同步过来
查看新备库中数据是否与现主库内容相同，9.6以后的版本应该会自动同步差异

 

-- The End --