共计 2626 个字符,预计需要花费 7 分钟才能阅读完成。
本篇内容介绍了“PostgreSQL HA 环境分析”的有关知识,在实际案例的操作过程中,不少人都会遇到这样的困境,接下来就让丸趣 TV 小编带领大家学习一下如何处理这些情况吧!希望大家仔细阅读,能够学有所成!
基于 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 TABLE
testdb=# insert into t_new select c, flag ||c from generate_series(1,1000000) as c;
INSERT 0 1000000
testdb=#
Old Master
执行 pg_rewind 前, 重启主库, 执行以下 SQL
testdb=# create table t_fork(id int,flag varchar(40));
CREATE TABLE
testdb=# insert into t_fork select c, flag ||c from generate_series(1,1000000) as c;
INSERT 0 1000000
testdb=#
执行 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 --progress
connected to server
servers diverged at WAL location 0/B41F12B8 on timeline 23
rewinding from last common checkpoint at 0/AFCF99E0 on timeline 23
reading source file list
reading target file list
reading WAL in target
need to copy 360 MB (total source directory size is 501 MB)
369312/369312 kB (100%) copied
creating backup label and updating control file
syncing target data directory
Done!
配置 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 start
waiting for server to start....2019-03-28 12:39:31.918 CST [1961] LOG: listening on IPv4 address 0.0.0.0 , port 5432
2019-03-28 12:39:31.918 CST [1961] LOG: listening on IPv6 address :: , port 5432
2019-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 process
2019-03-28 12:39:31.970 CST [1961] HINT: Future log output will appear in directory pg_log .
... done
server 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 exist
LINE 1: select count(*) from t_fork;
^
testdb=#
“PostgreSQL HA 环境分析”的内容就介绍到这里了,感谢大家的阅读。如果想了解更多行业相关的知识可以关注丸趣 TV 网站,丸趣 TV 小编将为大家输出更多高质量的实用文章!