熱備援解決方案 - Streaming Replication

PostgreSQL 9.0 以後, 熱備援多了一個解決方案, 即是資料流複製(Streaming Replication), 它的功能類似 Oracle Active Data Guard 功能, 除了能提昇熱備援伺服器之高可用性, 並且以近乎即時的資料流複製, 使得系統架構在災難回復及備援功能更趨於完善.

系統配置步驟如下:

1. 建置 ssh trust tunnel

     hostA# ssh-keygen -t rsa

     hostA# cat ~/.ssh/id_rsa.pub >~/.ssh/authorized_keys

     hostA# scp ~/.ssh/authorized_keys hostB:/root/.ssh/authorized_keys

     hostB# ssh-keygen -t rsa

     hostB# cat ~/.ssh/id_rsa.pub >>~/.ssh/authorized_keys

     hostB# scp ~/.ssh/authorized_keys hostA:/root/.ssh/authorized_keys

2. 設定SELinux為Permissve模式

     hostA# setenforce 0

     hostA# getenforce 

     Permissive

     hostB# setenforce 0

     hostB# getenforce 

     Permissive

3. 配置工作目錄, 備份目錄

     hostA# mkdir -p /opt/Backup/Archive     

     hostA# mkdir -p /opt/Backup/FullBackup 

     hostA# chown -R enterprisedb:enterprisedb /opt/Backup 

     hostB# mkdir -p /opt/Backup/Archive

     hostB# mkdir -p /opt/Backup/FullBackup

     hostB# chown -R enterprisedb:enterprisedb /opt/Backup

4. 設定 postgresql.conf 資料庫定義檔, 並設定Database 為Archive mode.

    max_connections = 100

    shared_buffers = 2048MB
    work_mem = 32MB
    shared_preload_libraries = '$libdir/dbms_pipe,$libdir/edb_gen,$libdir/plugins/plugin_debugger,$libdir/plugins/plugin_spl_debugger,$libdir/sql-profiler'
    wal_level = hot_standby
    synchronous_commit = on
    archive_mode = on
    archive_command = '/bin/bash /opt/Backup/archive.sh %p %f'
    max_wal_senders = 2
    synchronous_standby_names = '*'
    hot_standby = on

5. 重新啟動HostA及HostB服務.

6. 測試
    試著在HostA新增資料, 新增後即查詢HostB(Standby Database), HostB將會查詢出與HostA最新的資料內容.