PostgreSQL 9.1 主從角色互切示例

wanzai01

【原文地址】
http://blog.163.com/digoal@126/b ... 040201182395310376/

【內(nèi)容】
首先引用 PostgreSQL 9.1 Release Notes 里面的一段話 :
Allow standby recovery to switch to a new timeline automatically (Heikki Linnakangas)

Now standby servers scan the archive directory for new timelines periodically .

解釋一下什么是時間線文件 : 文件名如 000000xx.history
這個文件在standby 數(shù)據(jù)庫被 promote 時產(chǎn)生，放在被promote的standby數(shù)據(jù)庫的pg_xlog目錄 。
這個文件產(chǎn)生的同時，被promote的數(shù)據(jù)庫的timeline隨之改變�？梢栽诒籶romote的數(shù)據(jù)庫的服務(wù)器上執(zhí)行pg_controldata查看到Latest checkpoint's TimeLineID : 這條記錄的改變 。

假設(shè)以上的切換是在primary節(jié)點干凈的關(guān)閉(-m fast或-m smart)數(shù)據(jù)庫的情況下做的，并且關(guān)庫過程中standby節(jié)點和primary節(jié)點保持暢通，那么正常情況下standby節(jié)點將接收到primary節(jié)點關(guān)庫后的最后一次checkpoint信息。也就是說，standby在promote后，原來的主節(jié)點可以作為新的主節(jié)點的standby庫的基礎(chǔ)備份，但是需要設(shè)置recovery.conf的recovery_target_timeline = 'latest' , 并且需要000xxxx.history這個文件作為恢復(fù)文件。其實就是本文開篇時提到的這個動作 “ scan the archive directory for new timelines periodically . "

PostgreSQL 9.0 已經(jīng)存在這個過程，可以參考我以前寫的BLOG ：
http://blog.163.com/digoal@126/b ... 040201141154024306/

下面來測試一下9.1 :
首先是創(chuàng)建一個primary-standby環(huán)境。
primary庫操作 :
1. 新建Primary 數(shù)據(jù)庫，略 .

2. 修改Primary 數(shù)據(jù)庫相關(guān)參數(shù) .
vi $PGDATA/postgresql.conf
wal_level = hot_standby
archive_mode = on
archive_command = '/bin/date'
max_wal_senders = 32
wal_sender_delay = 10ms
wal_keep_segments = 500
replication_timeout = 60s
# 以下部分可以先配置好,省的待會切換為standby的時候再配置
hot_standby = on
wal_receiver_status_interval = 10s
hot_standby_feedback = on

3. 新增replica 用戶.
postgres=# create role replica nosuperuser nocreatedb nocreaterole noinherit login replication encrypted password 'Replica_123';

4. 修改pg_hba.conf
增加一條
host replication replica 0.0.0.0/0 md5

5. 新建~/.pgpass文件,省的待會切換為standby的時候再配置.為對方的IP。
vi /home/postgres/.pgpass
172.16.3.xx:1921:replication:replica:Replica_123
chmod 400 /home/postgres/.pgpass

standby庫操作 :
1. 安裝PostgreSQL軟件
編譯條件與原庫一樣。

2. 編譯與主庫一樣PG插件,
如果主庫新增了插件，那么備庫需要新增一樣的插件。
譬如說我的主庫新增了pgfincore和oracle_fdw的插件，那么在備庫也要新增這兩個插件，否則使用到這些插件的時候會報錯。甚至數(shù)據(jù)庫會起不來。

3. 新建相關(guān)目錄(主數(shù)據(jù)目錄,表空間目錄,pg_xlog目錄,或者賦予對應(yīng)的父目錄權(quán)限,日志目錄)。
如果不使用pg_basebackup來建立standby的話，這些操作會更加明朗一下。

4. 新增.pgpass , 為對方的IP。
vi /home/postgres/.pgpass
172.16.3.xx:1921:replication:replica:Replica_123
chmod 400 /home/postgres/.pgpass

5. 使用pg_basebackup這個9.1新增的命令來創(chuàng)建standby基備。
postgres@172_168_3_xx-> pg_basebackup -D $PGDATA -F p -x -l basebackup -P -v -h 172.16.3.xxx -p 1921 -U replica
WARNING:  skipping special file "./.s.PGSQL.1921"
xlog start point: 1/28000020
WARNING:  skipping special file "./.s.PGSQL.1921"pgdata/pg_root/global/12)
2209131/2209131 kB (100%), 2/2 tablespaces                                    
xlog end point: 1/280001F8
pg_basebackup: base backup completed

6. 拷貝/opt/pgsql/share/recovery.conf.example到$PGDATA下面
修改
postgres@172_168_3_xx-> cat recovery.done |grep -v "^#"
recovery_target_timeline = 'latest'
standby_mode = on
primary_conninfo = 'host=172.16.3.xx port=1921 user=replica'           # e.g. 'host=localhost port=5432'
trigger_file = '/home/pgdata/pg_root/postgresql.trigger.1921'

7. 啟動standby數(shù)據(jù)庫
pg_ctl start -D $PGDATA

至此環(huán)境搭建完畢。

接下來測試的是角色切換，譬如說我們要對主庫添加內(nèi)存或者維護主機的服務(wù)器或者存儲。需要把主庫停掉。但是在維護完后還要起來。
維護可能需要幾個小時，業(yè)務(wù)肯定受不了。那么需要激活備庫先頂一段時間。

接下來就是整個切換過程。
一、 檢查操作：
檢查主庫和備庫的復(fù)制正常進行中。

二、 主庫操作：
停止主庫 , 千萬不要用-m immediate,這個不會產(chǎn)生關(guān)機checkpoint,也不會發(fā)送checkpoint給standby。會造出過程不可逆。
pg_ctl stop -m fast -D $PGDATA
停庫日志解析 :
2011-09-23 12:55:13.002 CST,,,6764,,4e7c0cca.1a6c,3,,2011-09-23 12:36:26 CST,,0,LOG,00000,"received fast shutdown request",,,,,,,,,""
2011-09-23 12:55:13.002 CST,,,6764,,4e7c0cca.1a6c,4,,2011-09-23 12:36:26 CST,,0,LOG,00000,"aborting any active transactions",,,,,,,,,""
2011-09-23 12:55:13.002 CST,,,6804,,4e7c0d2a.1a94,2,,2011-09-23 12:38:02 CST,1/0,0,LOG,00000,"autovacuum launcher shutting down",,,,,,,,,""
2011-09-23 12:55:13.004 CST,,,6767,,4e7c0cca.1a6f,3,,2011-09-23 12:36:26 CST,,0,LOG,00000,"shutting down",,,,,,,,,""
2011-09-23 12:55:13.052 CST,,,6767,,4e7c0cca.1a6f,4,,2011-09-23 12:36:26 CST,,0,LOG,00000,"checkpoint starting: shutdown immediate",,,,,,,,,""
2011-09-23 12:55:14.344 CST,,,6767,,4e7c0cca.1a6f,5,,2011-09-23 12:36:26 CST,,0,LOG,00000,"checkpoint complete: wrote 0 buffers (0.0%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=0.007 s, sync=0.000 s, total=1.316 s; sync files=0, longest=0.000 s, average=0.000 s",,,,,,,,,""
2011-09-23 12:55:14.344 CST,,,6767,,4e7c0cca.1a6f,6,,2011-09-23 12:36:26 CST,,0,LOG,00000,"database system is shut down",,,,,,,,,""
停庫結(jié)束后，主庫還有幾個進程，一個是主進程，一個是記錄日志的進程，一個是SENDER進程。所以這種關(guān)閉是會把所有的信息發(fā)送給STANDBY庫的。
進程如下 :
/opt/pgsql/bin/postgres
postgres: logger process   
postgres: wal sender process replica 172.16.3.xxx(29599) streaming 1/46BE0000
數(shù)據(jù)發(fā)完，這些進程會自動關(guān)閉。

備庫日志 :
2011-09-23 12:55:12.912 CST,,,22646,,4e7c0d86.5876,12,,2011-09-23 12:39:34 CST,1/0,0,LOG,00000,"redo starts at 1/40000078",,,,,,,,,""
2011-09-23 12:56:09.309 CST,,,22723,,4e7c0e85.58c3,2,,2011-09-23 12:43:49 CST,,0,FATAL,XX000,"could not receive data from WAL stream: server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
",,,,,,,,,""
在主庫關(guān)閉過程中，備庫上可以看到類似以上的日志，表明主庫正在發(fā)關(guān)閉過程的XLOG信息給備庫。主庫關(guān)閉后，備庫連不到主庫，因此有后面那段報錯，這是正常的。此時就可以對備庫進行激活操作了，這樣的主備角色切換不會丟失任何數(shù)據(jù)。

四、 備庫操作：
確認主庫正常關(guān)庫后，激活備庫.
pg_ctl promote -D $PGDATA
備庫激活成主庫后，把應(yīng)用改為連接新的主庫。
接到promote信號后，數(shù)據(jù)庫日志如下 :
2011-09-23 12:37:59.218 CST,,,6766,,4e7c0cca.1a6e,6,,2011-09-23 12:36:26 CST,1/0,0,LOG,00000,"received promote request",,,,,,,,,""
2011-09-23 12:37:59.218 CST,,,6766,,4e7c0cca.1a6e,7,,2011-09-23 12:36:26 CST,1/0,0,LOG,00000,"redo done at 1/3C00C3B0",,,,,,,,,""
2011-09-23 12:37:59.218 CST,,,6766,,4e7c0cca.1a6e,8,,2011-09-23 12:36:26 CST,1/0,0,LOG,00000,"last completed transaction was at log time 2011-09-23 12:17:04.838732+08",,,,,,,,,""
2011-09-23 12:37:59.218 CST,,,6766,,4e7c0cca.1a6e,9,,2011-09-23 12:36:26 CST,1/0,0,LOG,00000,"selected new timeline ID: 5",,,,,,,,,""
2011-09-23 12:38:00.508 CST,,,6766,,4e7c0cca.1a6e,10,,2011-09-23 12:36:26 CST,1/0,0,LOG,00000,"archive recovery complete",,,,,,,,,""
2011-09-23 12:38:00.539 CST,,,6767,,4e7c0cca.1a6f,1,,2011-09-23 12:36:26 CST,,0,LOG,00000,"checkpoint starting: end-of-recovery immediate wait",,,,,,,,,""
2011-09-23 12:38:01.939 CST,,,6767,,4e7c0cca.1a6f,2,,2011-09-23 12:36:26 CST,,0,LOG,00000,"checkpoint complete: wrote 9 buffers (0.0%); 0 transaction log file(s) added, 0 removed, 0 recycled; write=0.009 s, sync=0.099 s, total=1.430 s; sync files=8, longest=0.057 s, average=0.012 s",,,,,,,,,""
2011-09-23 12:38:02.030 CST,,,6804,,4e7c0d2a.1a94,1,,2011-09-23 12:38:02 CST,,0,LOG,00000,"autovacuum launcher started",,,,,,,,,""
2011-09-23 12:38:02.032 CST,,,6764,,4e7c0cca.1a6c,2,,2011-09-23 12:36:26 CST,,0,LOG,00000,"database system is ready to accept connections",,,,,,,,,""
激活前后進程差異解析 :
激活前,recovery狀態(tài).進程如下 :
/opt/pgsql/bin/postgres
postgres: logger process   
postgres: startup process   waiting for 000000040000000100000010
postgres: writer process   
postgres: stats collector process   
postgres: wal receiver process   streaming 1/40000078
激活后,open狀態(tài).進程如下 :
/opt/pgsql/bin/postgres
postgres: logger process   
postgres: writer process   
postgres: stats collector process   
postgres: wal writer process   
postgres: autovacuum launcher process   
postgres: archiver process   last was 00000005.history
postgres: wal sender process replica 172.16.3.xxx(26059) streaming 1/40000078
激活后多了 wal writer process , autovacuum launcher process , archiver process , wal sender process .
少了 startup process , wal receiver process .

五、 維護原來的主庫。

六、 維護好后，把原來的主庫作為standby 啟動。
1. 拷貝/opt/pgsql/share/recovery.conf.example到$PGDATA下面
修改
postgres@172_168_3_xx-> cat recovery.done |grep -v "^#"
recovery_target_timeline = 'latest'
standby_mode = on
primary_conninfo = 'host=172.16.3.xx port=1921 user=replica'           # e.g. 'host=localhost port=5432'
trigger_file = '/home/pgdata/pg_root/postgresql.trigger.1921'

2. 啟動數(shù)據(jù)庫
pg_ctl start -D $PGDATA

3. 此時，這個老主庫會報錯，
2011-09-23 10:38:21.965 CST,,,4054,,4e7bf11d.fd6,1,,2011-09-23 10:38:21 CST,,0,FATAL,XX000,"timeline 2 of the primary does not match
recovery target timeline 1",,,,,,,,,""
這個就是在找時間線文件。這里的時間線文件內(nèi)容如下 :
postgres@172_168_3_xxx-> cat 00000002.history
1       00000001000000010000000B        no recovery target specified
這個報錯可在源碼文件src/backend/replication/libpqwalreceiver/libpqwalreceiver.c中找到。
對應(yīng)部分 :
        /*
         * Confirm that the current timeline of the primary is the same as the
         * recovery target timeline.
         */
        standby_tli = GetRecoveryTargetTLI();
        PQclear(res);
        if (primary_tli != standby_tli)
                ereport(ERROR,
                                (errmsg("timeline %u of the primary does not match recovery target timeline %u",
                                                primary_tli, standby_tli)));

4. 我們到新主庫的pg_xlog找到這個文件，然后再老的主庫pg_xlog目錄新建這個文件寫入同樣的內(nèi)容就解決了�；蛘咧苯觭cp過去。

5. 檢查復(fù)制，確保復(fù)制正常。

6. 主庫和備庫接近一致后，再來一次主庫和備庫角色切換的操作。
    6.1 關(guān)閉新主庫。
    6.2 激活老主庫。
激活完后，新增了一個時間線文件內(nèi)容如下，每次時間線變更的記錄都將累計下來 :
postgres@172_168_3_xxx-> cat 00000003.history
1       00000001000000010000000B        no recovery target specified

2       00000002000000010000000D        no recovery target specified
    6.3 然后配置新主庫變回STANDBY。

當(dāng)然，這個時間線文件也可以放在archive directory。
然后通過以下的recovery.conf里面配置的命令來尋找。
restore_command = ''           # e.g. 'cp /mnt/server/archivedir/%f %p'

albeta

幫德哥頂一下，cu的pg氛圍很冷淡啊。

lampmatao

配置好了 主從數(shù)據(jù)庫 不行啊