2013/08/14

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
# 安裝 yum repo , 及軟體啟動 DB
[root@powerful ~]# rpm -i http://yum.postgresql.org/9.2/redhat/rhel-6-x86_64/pgdg-redhat92-9.2-7.noarch.rpm
[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 的活動。這部份請參考上面講義的說明。

Replication PostgreSQL

  • 在這個例子中,我使用兩台機器一台為 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 讀取測試驗證。
  • 參考:
  1. http://opensourcedbms.com/dbms/setup-replication-with-postgres-9-2-on-centos-6redhat-el6fedora/
  2. http://wiki.postgresql.org/wiki/Binary_Replication_Tutorial#What_Can_You_Do_With_Binary_Replication.3F


最新版本:http://jangmt.com/wiki/index.php/PostgreSQL_install#PostgreSQL_install_9.2.28CentOS_6.x.29
這測試只有幾次,如有問題或寫錯請不吝告知,謝謝。
張貼留言

like