PostgreSQL DBA(33) - HA#2(pg_rewind切換圖解)
基於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=#
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/6906/viewspace-2639592/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- PostgreSQL DBA(32) - HA#1(pg_rewind切換)SQL
- PostgreSQL DBA(133) - Extension(postgresql_anonymizer)SQL
- PostgreSQL pg_rewind原理SQL
- PostgreSQL切換日誌SQL
- PostgreSQL pg_rewind 報錯分析SQL
- Postgresql 手工日誌切換SQL
- jQuery 圖片垂直切換效果詳解jQuery
- switchable圖片切換
- PostgreSQL 原始碼解讀(159)-PG Tools#6(What does pg_rewind do)SQL原始碼
- PostgreSQL DBA(45) - Hypothetical Indexes in PostgreSQLSQLIndex
- PostgreSQL9.5:pg_rewind 快速恢復備節點SQL
- PostgreSQL DBA(154) - pgAdmin(bool轉換為smallint)SQL
- PostgreSQL pg_rewind例項--could not find previous WAL record at %X/%XSQL
- postgresql10主從+keepalived高可用自動切換(切換關鍵在指令碼)SQL指令碼
- PostgreSQL DBA(35) - CTESQL
- PostgreSQL DBA(42) - localeSQL
- js圖片切換例項JS
- JavaScript 圖片切換展示效果JavaScript
- 【PG流複製】Postgresql流複製主備切換SQL
- PostgreSQL DBA(59) - Index(Bloom)SQLIndexOOM
- PostgreSQL DBA(53) - Index(BRIN)SQLIndex
- PostgreSQL DBA(58) - DBLinkSQL
- PostgreSQL DBA(48) - Index(GiST)SQLIndex
- PostgreSQL DBA(51) - Index(GIN)SQLIndex
- PostgreSQL DBA(52) - Index(RUM)SQLIndex
- PostgreSQL DBA(47) - Index(Btree)SQLIndex
- PostgreSQL DBA(43) - Index(Hash)SQLIndex
- PostgreSQL DBA(191) - CollationSQL
- PostgreSQL DBA(182) - HOTSQL
- PostgreSQL DBA(185) - watchSQL
- 安卓-自動切換APP圖示安卓APP
- jQuery手風琴圖片切換jQuery
- WMTS以及TMS切圖能否轉換?
- Openlayers2切換supermap WMS服務底圖,已解決
- 巧妙的有css合併圖片解決tab切換的背景圖片CSS
- PostgreSQL啟動恢復過程中日誌源的切換SQL
- PostgreSQL DBA(181) - Using PostgreSQL as a Data WarehouseSQL
- AntMobile tab 切換解決方案