PostgreSQL HA环境分析

本篇内容介绍了"PostgreSQL HA环境分析"的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

基于streaming replication搭建的PostgreSQL HA环境,Old Standby节点升级为New Master节点后,时间线会切换为新的时间线,比如从n变为n + 1.而Old Master节点的时间线仍然为原来的时间线,比如仍为n,通过使用pg_rewind工具,可使原来其实"完好"的Old Master成为New Standby节点.
图解
如下图所示:
在执行主备切换后,New Master节点的时间线切换为n + 1,通过pg_rewind可使Old Master在分叉点开始与New Master同步,成为New Standby节点.

实测
New Master
切换后为New Master后,执行以下SQL:

testdb=# create table t_new(id int,flag varchar(40));CREATE TABLEtestdb=# insert into t_new select c,'flag'||c from generate_series(1,1000000) as c;INSERT 0 1000000testdb=#

Old Master
执行pg_rewind前,重启主库,执行以下SQL

testdb=# create table t_fork(id int,flag varchar(40));CREATE TABLEtestdb=# insert into t_fork select c,'flag'||c from generate_series(1,1000000) as c;INSERT 0 1000000testdb=#

执行pg_rewind

[xdb@localhost testdb]$ cp /data/archivelog/* ./pg_wal[xdb@localhost testdb]$ pg_rewind --target-pgdata=$PGDATA --source-server="host=192.168.26.25 port=5432 dbname=testdb" --progressconnected to serverservers diverged at WAL location 0/B41F12B8 on timeline 23rewinding from last common checkpoint at 0/AFCF99E0 on timeline 23reading source file listreading target file listreading WAL in targetneed to copy 360 MB (total source directory size is 501 MB)369312/369312 kB (100%) copiedcreating backup label and updating control filesyncing target data directoryDone!

配置recovery.conf文件

[xdb@localhost testdb]$ mv recovery.done recovery.conf[xdb@localhost testdb]$ vim recovery.conf [xdb@localhost testdb]$ cat recovery.conf standby_mode = 'on'primary_conninfo = 'user=replicator password=replicator host=192.168.26.26 port=5432 sslmode=prefer sslcompression=0 krbsrvname=postgres target_session_attrs=any'restore_command = 'cp /data/archivelog/%f %p'

重启数据库

[xdb@localhost testdb]$ pg_ctl startwaiting for server to start....2019-03-28 12:39:31.918 CST [1961] LOG:  listening on IPv4 address "0.0.0.0", port 54322019-03-28 12:39:31.918 CST [1961] LOG:  listening on IPv6 address "::", port 54322019-03-28 12:39:31.920 CST [1961] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"2019-03-28 12:39:31.970 CST [1961] LOG:  redirecting log output to logging collector process2019-03-28 12:39:31.970 CST [1961] HINT:  Future log output will appear in directory "pg_log".... doneserver started

与New Master数据同步了,而在原TL上的t_fork数据表消失了.

testdb=# select count(*) from t_new;  count  --------- 1000000(1 row)testdb=# select count(*) from t_old;  count  --------- 1000000(1 row)testdb=# select count(*) from t_fork;ERROR:  relation "t_fork" does not existLINE 1: select count(*) from t_fork;                             ^testdb=#

"PostgreSQL HA环境分析"的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注网站，小编将为大家输出更多高质量的实用文章！