PostgreSQL 9.2 及多主機 Replication 複製機制設定

原文地址：http://blog.jangmt.com/2013/08/postgresql-92-replication.html

PostgreSQL 9.2 及多主機 Replication 複製機制設定

PostgreSQL 9.2 及多主機同步複製機制設定

POSTGRESQL INSTALL 9.2(CENTOS 6.X)

• CentOS 6.X 版本的 postgresql 是 8.4 版，很多機制還沒有很完備。如果只是一般的使用是足夠，但如需要使用 replication 的功能，建議安裝 9.0 以上版本，比較不會有使用上面的限制。且 9.0 以上版本有很多機制可以選擇(例如：Slony-I Replication)，但我選擇內建簡單設定的版本來完成我簡單備份的目的。
• 如果需要有複製的機制，建議更新到最新版本的 postgresql ，最新穩定版為 postgresql 9.2 版本

• 如果之前有預設的 postgresql 請先備份、關閉及移除，避免錯亂。在 centos 的 yum remove or rpm remove 機制並不會將 data 目錄移除，但仍建議如果有資料記得用 pg_dump_all 備份出來再做後續的變更。

[root@power pgsql]# /etc/init.d/postgresql stopStopping postgresql service: [ OK ][root@power pgsql]# chkconfig postgresql off

• 9.2版的安裝，參考官方網站 

# 安裝 yum repo , 及軟體啟動 DB [root@powerful ~]# rpm -i 
[root@powerful ~]# yum install -y postgresql92-server postgresql92-contrib
[root@powerful ~]# service postgresql-9.2 initdb
[root@powerful ~]# chkconfig postgresql-9.2 on # 9.2 版預設路徑
[root@power 9.2]# pwd
/var/lib/pgsql/9.2

# 切換為 postgres 使用者測試，可以不用密碼登入系統且取得最高權限
# 但是 9.2 因為環境變數的關係會有錯誤訊息，需要自行修正
[root@powerful data]# su - postgres [FAILED] -bash-4.1$ psql -f postgres psql: symbol lookup error: psql: undefined symbol: PQconnectdbParams # 請在 /etc/profile 加入下面內容
[root@powerful data]# tail /etc/profile # for postgresql LIB by mtchang
LD_LIBRARY_PATH=/usr/pgsql-9.2/lib
export LD_LIBRARY_PATH
PATH=/usr/pgsql-9.2/bin:$PATH
export PATH # 用 source 將變數重新 load 進 shell
[root@powerful data]# source /etc/profile [root@powerful data]# /etc/init.d/postgresql-9.2 restart Stopping postgresql-9.2 service:                           [  OK  ]
Starting postgresql-9.2 service:                           [  OK  ]

[root@powerful data]# su - postgres -bash-4.1$ psql
psql (9.2.4)
Type "help" for help.

# 改變 postgres 的預設密碼
postgres=# alter user postgres with password '12345678'; postgres=# \q 

• 以上就是一個單純可以本機登入的 postgresql ,可以用來將 pg_dump_all restore 的環境。
• 如果要做 pg_dump_all restore 正常需要這些動作

1. 停止 postgresql 服務 ex: /etc/init.d/postgresql-9.2 stop
2. 刪除原本的 data 目錄 ex:rm -rf /var/lib/pgsql/data
3. 重新初始話 database ex: /etc/init.d/postgresql-9.2 initdb
4. 使用 psql 匯入備份檔案
5. psql -U postgres -f pg_dump_all_備份檔 postgres

# 在 postgres 的身份下
-bash-4.1$ psql -U postgres -f pg_dump_all的備份檔案.sql  postgres 

• 但是實務上要使用，通常是透過網路加上密碼的存取，所以要修改 pg_hba.conf 這檔案讓它可以透過網路工作。

[root@powerful data]# cat /var/lib/pgsql/9.2/data/postgresql.conf  | grep listen listen_addresses = '*' # what IP address(es) to listen on;

[root@powerful data]# tail /var/lib/pgsql/9.2/data/pg_hba.conf 
# ph_hba.conf 檔案的最後請加上這些,讓這些網路可以透過 md5 用密碼登入系統
# 其他行列請先註解 # 或刪除不使用，後續要用再加上去。 host all     all  110.117.69.0/24  md5
host all     all  112.254.0.0/16  md5
host    all         all         127.0.0.1/32          md5
host    all         all         ::1/128               md5
local   all         all                               md5 [root@power data]# /etc/init.d/postgresql-9.2 restart Stopping postgresql-9.2 service:                           [  OK  ]
Starting postgresql-9.2 service:                           [  OK  ]

# 測試透過網路IP及密碼可否登入
[root@powerful data]# psql -U postgres -h 110.117.69.11 -W Password for user postgres: 
psql (9.2.4)
Type "help" for help.

postgres=# \q 

• 以上就是可以透過網路使用的 postgresql 了，但如果要好用這其實很不方便，所以通常會透過 create user 建立 superuser 管理者帳號來負責所有 postgresql 的活動。這部份請參考上面講義的說明。

• 官方網站-建立SQL使用者：
• 安裝 postgresql 9.2 in centos:

REPLICATION POSTGRESQL

• postgresql 的 replaication 的功能有很多種，但我選擇 9.0以後內建的簡單抄寫功能。
• 官方網站有介紹眾多的複寫機制，說不定有更多好的選擇....

• 在這個例子中，我使用兩台機器一台為 master DB 負責主要的資料庫讀寫，和平常的 postgresql DB 一樣的功能。另外一台為 slave DB 負責依據紀錄檔案的交易紀錄，抄寫來自援 master DB 的資料，即使斷線也可以從斷線的那時候繼續抄入複製。
• 資料庫同步複寫功能啟用，他是透過交易的功能達成同步抄寫資料庫的目的。

[root@power data]# cat postgresql.conf  # 加上這三行在最後 wal_level = hot_standby
max_wal_senders = 1
wal_keep_segments = 50 # 加入底下這條，讓 slave db 可以連入 master db 讀取交易紀錄的資料。
[root@power data]# cat /var/lib/pgsql/9.2/data/pg_hba.conf  host replication postgres 110.117.69.14/32     trust # 重新啟動
[root@power data]# /etc/init.d/postgresql-9.2 restart Stopping postgresql-9.2 service:                           [  OK  ]
Starting postgresql-9.2 service:                           [  OK  ]

# 切換到 postgres 帳號，準備將 master DB 的紀錄複製到 slave DB，方法很簡單
# 使用線上即時備份成為一個 sql.tar 的檔案，透過 ssh copy 複製到 slave DB
# 然後將回復到 slave 機器的 data 資料夾內，這時包含資料交易紀錄也一併複製過去了。
[root@power data]# su - postgres -bash-4.1$ psql -c "SELECT pg_start_backup('replbackup');" Password: 
 pg_start_backup 
-----------------
 0/DF000020
(1 row)

-bash-4.1$ tar cfP /tmp/db_file_backup.tar /var/lib/pgsql/9.2/data -bash-4.1$ psql -c "SELECT pg_stop_backup();" Password: 
NOTICE:  WAL archiving is not enabled; you must ensure that all required 
WAL segments are copied through other means to complete the backup
 pg_stop_backup 
----------------
 0/DF0000E0
(1 row)

-bash-4.1$ ls -lh /tmp/db_file_backup.tar  -rw-r--r--. 1 postgres postgres 6.7G Aug 14 21:36 /tmp/db_file_backup.tar

-bash-4.1$ exit
logout

[root@power data]# scp /tmp/db_file_backup.tar root@110.117.69.14:/tmp/ 

• 切換到 Slave postgresql DB 設定，先確定是同一個 postgresql 版本，先關閉 postgresql 服務，並將檔案回復還原。修改 postgresql.conf 設定為 host_standby 的機器，隨時同步 master db 的資料。

[root@powerful 9.2]# /etc/init.d/postgresql-9.2 stop [root@powerful 9.2]# mv /var/lib/pgsql/9.2/data /var/lib/pgsql/9.2/data.bak [root@powerful 9.2]# pwd
/var/lib/pgsql/9.2
[root@powerful 9.2]# tar xvfP /tmp/db_file_backup.tar [root@powerful data]# rm /var/lib/pgsql/9.2/data/postmaster.pid  rm: remove regular file `/var/lib/pgsql/9.2/data/postmaster.pid'? y

[root@powerful data]# vim /var/lib/pgsql/9.2/data/postgresql.conf  # These settings are ignored on a master server. hot_standby = on #hot_standby = off                      # "on" allows queries during recovery

# 設定要抄寫的 master DB 位置
[root@powerful data]# cp /usr/pgsql-9.2/share/recovery.conf.sample recovery.conf [root@powerful data]# vim recovery.conf 
standby_mode = on
primary_conninfo = 'host=110.117.69.11 port=5432' [root@powerful data]# chown postgres.postgres /var/lib/pgsql/9.2/data/recovery.conf  [root@powerful data]# /etc/init.d/postgresql-9.2 restart Stopping postgresql-9.2 service:                           [  OK  ]
Starting postgresql-9.2 service:                           [  OK  ] 

• 觀看 log 紀錄應該會看到連到 primary DB 的紀錄，及最後中斷前的交易紀錄。

[root@powerful data]# tail /var/lib/pgsql/9.2/data/pg_log/postgresql-Wed.log  LOG:  database system was shut down at 2013-08-14 21:34:44 CST
LOG:  autovacuum launcher started
LOG:  database system is ready to accept connections
LOG:  database system was interrupted; last known up at 2013-08-14 21:35:36 CST
LOG:  entering standby mode
LOG:  redo starts at 0/DF000020
LOG:  record with zer寫錯或有o length at 0/DF0000B8

LOG:  streaming replication successfully connected to primary
LOG:  consistent recovery state reached at 0/DF0000E0
LOG:  database system is ready to accept read only connections 

• 接下來請自行對 Master DB 寫入 , Slave DB 讀取測試驗證。

• 參考: