PostgreSQL9.5：pg_rewind 快速恢復(fù)備節(jié)點(diǎn)

hersion 發(fā)布于2019-07-25 12:34 / 1926人閱讀

摘要：上操作備注執(zhí)行拋出以上錯(cuò)誤，錯(cuò)誤內(nèi)容很明顯。再次上操作備注成功。啟動(dòng)原主庫(kù)，上操作數(shù)據(jù)驗(yàn)證上操作備注成功，原主庫(kù)現(xiàn)在是以備庫(kù)角色啟動(dòng)，而且數(shù)據(jù)表也同步過(guò)來(lái)了。三原理四參考的主備切換使用搭建流復(fù)制環(huán)境

了解 PG 的朋友應(yīng)該知道 PG 的主備切換并不容易，步驟較嚴(yán)謹(jǐn)，在激活備節(jié)點(diǎn)前需主動(dòng)關(guān)閉主節(jié)點(diǎn)，否則再想以備節(jié)點(diǎn)角色拉起主節(jié)點(diǎn)會(huì)比較困難，之前博客介紹過(guò)主備切換，PostgreSQL HOT-Standby 的主備切換，PG 9.5 版本已經(jīng)將 pg_rewind 加入到源碼，當(dāng)主備發(fā)生切換時(shí)，可以將原來(lái)主庫(kù)通過(guò)同步模式恢復(fù)，避免重做備庫(kù)。這樣對(duì)于較大的庫(kù)來(lái)說(shuō)，節(jié)省了大量重做備庫(kù)時(shí)間。

pg_rewind 會(huì)將目標(biāo)庫(kù)的數(shù)據(jù)文件，配置文件復(fù)制到本地目錄，由于 pg_rewind 不會(huì)讀取所有未發(fā)生變化的數(shù)據(jù)塊，所以速度比重做備庫(kù)要快很多，

一環(huán)境準(zhǔn)備

流復(fù)制環(huán)境
192.168.2.37/1931 主節(jié)點(diǎn)(主機(jī)名 db1)
192.168.2.38/1931 備節(jié)點(diǎn)(主機(jī)名 db2)
備注：流復(fù)制環(huán)境參考 PostgreSQL：使用 pg_basebackup 搭建流復(fù)制環(huán)境，本文略。

--pg_rewind 前提條件
1 full_page_writes
2 wal_log_hints 設(shè)置成 on 或者 PG 在初始化時(shí)開(kāi)啟 checksums 功能

二主備切換

--備節(jié)點(diǎn) recovery.conf 配置: db2 上操作

[pg95@db2 pg_root]$ grep ^[a-z] recovery.conf 
recovery_target_timeline = "latest"
standby_mode = on
primary_conninfo = "host=192.168.2.37 port=1931 user=repuser"           # e.g. "host=localhost port=5432"

--激活備節(jié)點(diǎn): db2 上操作

[pg95@db2 pg_root]$ pg_ctl promote -D $PGDATA
server promoting

[pg95@db2 pg_root]$ pg_controldata | grep cluster
Database cluster state:               in production

--備節(jié)點(diǎn)激活后，創(chuàng)建一張測(cè)試表并插入數(shù)據(jù)

[pg95@db2 pg_root]$ psql
psql (9.5alpha1)
Type "help" for help.

postgres=# create table test_2(id int4);
CREATE TABLE
                   
postgres=# insert into test_2(id) select n from generate_series(1,10000) n;
INSERT 0 10000

--停原來(lái)主節(jié)點(diǎn): db1 上操作

[pg95@db1 ~]$ pg_controldata | grep cluster
Database cluster state:               in production

[pg95@db1 ~]$ pg_ctl stop -m fast -D $PGDATA
waiting for server to shut down....... done
server stopped

備注：停完原主庫(kù)后，千萬(wàn)不能立即以備節(jié)點(diǎn)形式拉起老庫(kù)，否則在執(zhí)行 pg_rewind 時(shí)會(huì)報(bào)，"target server must be shut down cleanly" 錯(cuò)誤。

--pg_rewind: db1 上操作

[pg95@db1 pg_root]$ pg_ctl stop -m fast -D $PGDATA
waiting for server to shut down......... done
server stopped

[pg95@db1 pg_root]$ pg_rewind --target-pgdata $PGDATA --source-server="host=192.168.2.38 port=1931 user=postgres dbname=postgres" -P 
connected to server
target server needs to use either data checksums or "wal_log_hints = on"

備注：執(zhí)行 pg_rewind 拋出以上錯(cuò)誤，錯(cuò)誤內(nèi)容很明顯。

--pg_rewind 代碼分析

  364     /*
  365      * Target cluster need to use checksums or hint bit wal-logging, this to
  366      * prevent from data corruption that could occur because of hint bits.
  367      */
  368     if (ControlFile_target.data_checksum_version != PG_DATA_CHECKSUM_VERSION &&
  369         !ControlFile_target.wal_log_hints)
  370     {
  371         pg_fatal("target server needs to use either data checksums or "wal_log_hints = on"
");
  372     }
  373

備注：數(shù)據(jù)庫(kù)在 initdb 時(shí)需要開(kāi)啟 checksums 或者設(shè)置 "wal_log_hints = on"，接著設(shè)置主，備節(jié)點(diǎn)的 wal_log_hints 參數(shù)并重啟數(shù)據(jù)庫(kù)。

--再次 pg_rewind, db1 上操作

[pg95@db1 pg_root]$ pg_rewind --target-pgdata $PGDATA --source-server="host=192.168.2.38 port=1931 user=postgres dbname=postgres" -P
connected to server
The servers diverged at WAL position 0/1300CEB0 on timeline 5.
Rewinding from last common checkpoint at 0/1200008C on timeline 5
reading source file list
reading target file list
reading WAL in target
need to copy 59 MB (total source directory size is 76 MB)
61185/61185 kB (100%) copied
creating backup label and updating control file
Done!

備注：pg_rewind 成功。

--調(diào)整 recovery.conf 文件: db1 操作
[pg95@db1 ~]$ cd $PGDATA
[pg95@db1 pg_root]$ mv recovery.done recovery.conf

備注：注意是否需要修改 primary_conninfo 配置。

[pg95@db1 pg_root]$ grep ^[a-z] recovery.conf 
recovery_target_timeline = "latest"
standby_mode = on
primary_conninfo = "host=192.168.2.38 port=1931 user=repuser"           # e.g. "host=localhost port=5432"

--啟動(dòng)原主庫(kù)， db1 上操作

[pg95@db1 pg_root]$ pg_ctl start -D $PGDATA
server starting

[pg95@db1 pg_root]$ pg_controldata | grep cluster
Database cluster state:               in archive recovery

--數(shù)據(jù)驗(yàn)證, db1 上操作

[pg95@db1 pg_root]$ psql
psql (9.5alpha1)
Type "help" for help.

postgres=# select count(*) from test_2;
 count 
-------
 10000
(1 row)

備注：pg_rewind 成功，原主庫(kù)現(xiàn)在是以備庫(kù)角色啟動(dòng)，而且數(shù)據(jù)表 test_2 也同步過(guò)來(lái)了。

三 pg_rewind 原理

The basic idea is to copy everything from the new cluster to the old cluster, except for the blocks that we know to be the same.

    1)Scan the WAL log of the old cluster, starting from the last checkpoint before the point where the new cluster"s timeline history forked off from the old cluster. For each WAL record, make a note of the data blocks that were touched. This yields a list of all the data blocks that were changed in the old cluster, after the new cluster forked off.

    2)Copy all those changed blocks from the new cluster to the old cluster.

    3)Copy all other files like clog, conf files etc. from the new cluster to old cluster. Everything except the relation files.

    4) Apply the WAL from the new cluster, starting from the checkpoint created at failover. (Strictly speaking, pg_rewind doesn"t apply the WAL, it just creates a backup label file indicating that when PostgreSQL is started, it will start replay from that checkpoint and apply all the required WAL.)

四參考

PostgreSQL HOT-Standby 的主備切換
PostgreSQL：使用 pg_basebackup 搭建流復(fù)制環(huán)境
pg_rewind