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

admin管理员组

文章数量:1657062

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以后的版本应该会自动同步差异

本文标签： 主从方式postgresqlPromotejavapostgresql