PG: Setting up streaming log replication (Hot Standby )

Postgresql9.0的一個主要新特性是可以實施流複制，這有點像ORACLE 裡的DataGuard(Physial Standby)

但是這種方式比Oracle的DataGuard更為安全，更為高效，因為從庫同步主庫是實時的，幾乎沒有時間差。

而Oracle的 DataGuard的從庫接收并應用主庫的日志的延遲，本人測試了下，大概有幾分鐘，具體延時決定于

主庫的業務繁忙程度。

下面是流複制實驗的詳細步驟

1 環境資訊

PG版本: PostgreSQL 9.0beta3

OS版本: Red Hat Enterprise Linux Server release 5.5

硬體環境： WINDOWS XP上安裝兩台虛拟機

Master資訊 IP: 192.168.1.25

Standby資訊 IP: 192.168.1.26

2 由于主庫已經安裝 Postgresql ，主庫上安裝PG的步驟這裡就不介紹了

3 配置從庫主機參數 (On standby)

3.1 設定 /etc/sysctl.conf,增加以下内容

kernel.shmmni = 4096

kernel.sem = 501000 6412800000 501000 12800

fs.file-max = 767246

net.ipv4.ip_local_port_range = 1024 65000

net.core.rmem_default = 1048576

net.core.rmem_max = 1048576

net.core.wmem_default = 262144

net.core.wmem_max = 262144

net.ipv4.tcp_tw_recycle=1

net.ipv4.tcp_max_syn_backlog=4096

net.core.netdev_max_backlog=10000

vm.overcommit_memory=0

net.ipv4.ip_conntrack_max=655360

sysctl -p 生效

3.2 設定/etc/security/limits.conf 增加以下内容

* soft nofile 131072

* hard nofile 131072

* soft nproc 131072

* hard nproc 131072

* soft core unlimited

* hard core unlimited

* soft memlock 50000000

* hard memlock 50000000

3.3 設定 /etc/pam.d/login ，增加以下内容

session required pam_limits.so

4 在主庫上建立建立超級使用者( On Master )

4.1 建立使用者

CREATE USER repuser

SUPERUSER

LOGIN

CONNECTION LIMIT 2

ENCRYPTED PASSWORD 'repuser';

4.2 設定 master 庫 pg_hba.conf

host replication repuser 192.168.1.26/16 md5

說明：超級使用者 repuser 是用來從庫上讀取庫主庫(Master)的 WAL stream,并且在4。2中設定

權限，隻允許主機 192.168.1.26(Standby 節點)以 md5 加密方式通路。

5 設定日志參數，記錄連接配接資訊 ( Both Master and Standby 庫)

log_connections = on

說明："log_connections" 參數用來記錄資料庫連接配接資訊,打開這個開關，進而在接下來的CSV日志中

能更好的觀察Master庫和 Standby 庫情況。

6 設定庫庫 postgresql.conf ( On Master )

max_wal_senders = 1 --WAL STREAM 日志發送程序數

wal_level = hot_standby --主庫設定成 hot_standby ，從庫才能以READ-ONLY模式打開

archive_mode = on

archive_command = 'cd .'

wal_keep_segments = 64

說明，關鍵參數"max_wal_senders" 是指 wal 發送程序數, 我這裡隻有一台從庫，是以設定為1，如果有多台

從庫，則應該設定成從庫個數，因為在Master庫上，每台從庫需要一個 WAL日志發送程序向從庫發送WAL日志流。

一方面是這一參數官網的介紹。

max_wal_senders (integer)

Specifies the maximum number of concurrent connections from standby servers (i.e., the maximum number

of simultaneously running WAL sender processes). The default is zero. This parameter can only be set

at server start. wal_level must be set to archive or hot_standby to allow connections from standby servers.

7 主庫全備

7.1 tart the backup (On Master )

select pg_start_backup('base backup for log streaming');

7.2 COPY 資料檔案

tar czvf pg_root.tar.gz pg_root --exclude=pg_root/pg_xlog

由于 $PGDATA/pg_xlog 不是必須的，這裡排除了這個目錄,節省時間。

7.3 将資料檔案COPY到standby 主機并解壓

scp pgdata.tar.gz pgb:/database

7.4 資料COPY完後,結束備份 Stop the backup (On Master)

select pg_stop_backup(), current_timestamp;

說明：建議主庫和從庫配置資訊一緻，包括硬體資訊，目錄結構，主機配置等。

8 修改從庫 postgresql.conf (On standby )

hot_standby = on --從庫上可以執行隻讀操作

9 設定從庫 recovery.conf (On standby)

standby_mode = 'on' --标記PG為STANDBY SERVER

primary_conninfo = 'host=192.168.1.25 port=1921 user=repuser password=repuser'

trigger_file = '/tmp/postgresql.trigger.1921'

說明：關鍵參數“primary_conninfo (string)” ,這裡配置了hostname,port,username ,password,

關于這個參數的更多解釋可以參考官網.其中更多關于連接配接的參數可以配置，這裡不說明了

http://www.postgresql.org/docs/9.0/static/libpq-connect.html

10 删除從庫檔案,并建立 pg_xlog目錄 (On standby )

$ rm -f $PGDATA/postmaster.pid

$ mkdir -p $PGDATA/pg_xlog

11 啟從庫，并觀察 csvlog

11.1 觀察CSVLOG

2011-01-08 17:22:49.757 CST,,,24243,,4d282ce9.5eb3,2,,2011-01-08 17:22:49 CST,,0,LOG,00000,"entering standby mode",,,,,,,,,""

2011-01-08 17:22:49.887 CST,,,24244,,4d282ce9.5eb4,1,,2011-01-08 17:22:49 CST,,0,LOG,00000,"streaming replication successfully connected to primary",,,,,,,,,""

2011-01-08 17:22:52.677 CST,,,24243,,4d282ce9.5eb3,3,,2011-01-08 17:22:49 CST,1/0,0,LOG,00000,"redo starts at 1/94000020",,,,,,,,,""

2011-01-08 17:22:52.696 CST,,,24243,,4d282ce9.5eb3,4,,2011-01-08 17:22:49 CST,1/0,0,LOG,00000,"consistent recovery state reached at 1/98000000",,,,,,,,,""

2011-01-08 17:22:52.805 CST,,,24241,,4d282ce8.5eb1,4,,2011-01-08 17:22:48 CST,,0,LOG,00000,"database system is ready to accept read only connections",,,,,,,,,""

日志中 "streaming replication successfully connected to primary","database system is ready to accept read only connections"

這些資訊說明流複制已經成功，從庫正準備接收主庫的WAL-STREAM。

11.2 主庫觀察WAL-Sender 程序

[postgres@pg1 pg_root]$ ps -ef | grep post

postgres 27225 27166 0 17:22 ? 00:00:05 postgres: wal sender process repuser 192.168.1.26(59836) streaming 1/9801E000

說明：将輸出結果省略部分，可以看到 " wal sender process repuser"程序

11.3 在從庫上觀察 WAL-接收程序

[postgres@pgb pg_log]$ ps -ef | grep post

postgres 24244 24241 0 17:22 ? 00:00:04 postgres: wal receiver process streaming 1/9801DF00

說明：同樣省略部分輸出結果，可以看到“ wal receiver process ” 程序。

12 測試

12.1 主庫上建立使用者

postgres=# CREATE ROLE browser LOGIN ENCRYPTED PASSWORD 'browser'

postgres-# nosuperuser noinherit nocreatedb nocreaterole CONNECTION LIMIT 200;

CREATE ROLE

從庫上驗證

postgres=# \du

List of roles

Role name | Attributes | Member of

-----------+-----------------------------------+-----------

browser | No inheritance +| {}

說明：果然，在從庫上就立刻建立了新使用者 'browser'

12.2 主庫上建立表空間(On Master)

mkdir -p /database/pgdata/pg_tbs/tbs_browser

從庫上也執行 mkdir -p /database/pgdata/pg_tbs/tbs_browser (On Sandby)

postgres=# create tablespace tbs_browser owner skytf LOCATION '/database/pgdata/pg_tbs/tbs_browser';

CREATE TABLESPACE

--在從庫上驗證

postgres=# \db

List of tablespaces

Name | Owner | Location

-------------+----------+-------------------------------------

pg_default | postgres |

pg_global | postgres |

tbs_browser | skytf | /database/pgdata/pg_tbs/tbs_browser

tbs_mydb | skytf | /database/pgdata/pg_tbs/tbs_mydb

表空間"tbs_browser" 也立刻建立過來了

12.3 主庫上建立資料庫

postgres=# CREATE DATABASE browser

postgres-# WITH OWNER = skytf

postgres-# TEMPLATE = template0

postgres-# ENCODING = 'UTF8'

postgres-# TABLESPACE = tbs_browser;

CREATE DATABASE

--從庫上驗證

postgres=# \l

List of databases

Name | Owner | Encoding | Collation | Ctype | Access privileges

-----------+----------+----------+-----------+-------+-----------------------

browser | skytf | UTF8 | C | C |

mydb | skytf | UTF8 | C | C |

postgres | postgres | UTF8 | C | C | =Tc/postgres +

| | | | | postgres=CTc/postgres

template0 | postgres | UTF8 | C | C | =c/postgres +

template1 | postgres | UTF8 | C | C | =c/postgres +

從庫上資料庫 "browser" 也立刻有了，幾乎沒有延時。

12.4 同時觀察CSV日志，從日志上看，基本沒有延遲

2011-01-08 17:28:59.335 CST,"postgres","postgres",24274,"[local]",4d282e5b.5ed2,2,"authentication",2011-01-08 17:28:59 CST,2/3,0,LOG,00000,"connection authorized: user=postgres database=postgres",,,,,,,,,""

12.5 在從庫上建表

mydb=> create table table3(id integer);

ERROR: cannot execute CREATE TABLE in a read-only transaction

說明：從庫是以隻讀形式打開，隻能執行讀操作，不能寫。

13 監控streaming

CREATE OR REPLACE VIEW pg_stat_replication AS

SELECT

S.procpid,

S.usesysid,

U.rolname AS usename,

S.application_name,

S.client_addr,

S.client_port,

S.backend_start

FROM pg_stat_get_activity(NULL) AS S, pg_authid U

WHERE S.usesysid = U.oid AND S.datid = 0;

postgres=# select * from pg_stat_replication ;

procpid | usesysid | usename | application_name | client_addr | client_port | backend_start

---------+----------+---------+------------------+--------------+-------------+-------------------------------

27225 | 64949 | repuser | | 192.168.1.26 | 59836 | 2011-01-08 17:22:05.480584+08

(1 row)

14 總結

以上就是搭建 streaming(又稱Hot Standby)的詳細過程，這是一個令人興奮的學習過程，

因為PG的HOT STANDBY 提供的資料及時性和可靠性絲毫不比ORACLE的DataGuard遜色，相反，

本人還覺得比在這方面比Oracle更給力，謝謝開源的人們提供這麼優秀的資料庫。

--參考文檔

http://www.postgresql.org/docs/9.0/static/high-availability.html

PDF電子書: PostgreSQL-Admin-Cookbook.PDF