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

2040阅读　0评论2014-02-08　SingingHeart
分类：LINUX

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

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

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

POSTGRESQL INSTALL 9.2(CENTOS 6.X)

[root@power pgsql]# /etc/init.d/postgresql stopStopping postgresql service: [ OK ][root@power pgsql]# chkconfig postgresql off 
# 安裝 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
  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 
[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

REPLICATION POSTGRESQL 

[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/ 
[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  ] 
[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

上一篇：如何修改linux主机名称
下一篇： lsyncd+rsyncd real-time fs synchronization