Postgresql：建立master-slave database

架構圖

主要參考資料

• Postgres 9.1: Setup a synchronous stand-by server in 5 minutes | Michael Paquier

假定我們的兩台DB分別在不同機器

• Master 10.0.0.2
• Slave 10.0.0.3

Master
1. 初始化 DB

$ sudo mkdir /usr/local/pgsql/data
$ chown postgres:postgres /usr/local/pgsql/data
$ su postgres -c "/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data"

2. 基本設定，編輯/usr/local/pgsql/data/postgresql.conf

listen_addresses = '10.0.0.2'
wal_level = hot_standby
max_wal_senders = 10
wal_keep_segments = 10

3. 允許slave連到master，編輯/usr/local/pgsql/data/pg_hba.conf

host   replication   postgres   10.0.0.3/32   trust

4. 啟動master

$ su postgres -c "/usr/local/pgsql/bin/pg_ctl start -D /usr/local/pgsql/data"

5. 打包master的database storage area

$ /usr/local/pgsql/bin/psql -c "select pg_start_backup('hot_backup');"  //進入備份模式
$ cd /usr/local/pgsql/
$ tar -zcvf data_backup.tar.gz ./data
$ /usr/local/pgsql/bin/psql -c "select pg_stop_backup();"  //結束備份模式

* 一定要從master打包data過去slave，不然會有下面這種error:

database system identifier differs between the primary and standby

Slave
假設slave是全新的server，也就是不存在database storage area
把剛剛master的備份資料data_backup.tar解壓縮到/usr/local/pgsql/data
此時的database storage area是跟master一模一樣的

1. 編輯postgresql.conf

hot_standby = on

2. 加入recovery.conf

$ cp /usr/local/pgsql/share/recovery.conf.sample /usr/local/pgsql/data/recovery.conf
$ vim recovery.conf

standby_mode = on
primary_conninfo = 'host=10.0.0.2 port=5432'

3. 啟動slave

$ su postgres -c "/usr/local/pgsql/bin/pg_ctl start -D /usr/local/pgsql/data"
LOG:  database system was shut down in recovery at 2011-09-29 20:54:00 CST
LOG:  entering standby mode
LOG:  consistent recovery state reached at 0/8BE76A8
LOG:  record with zero length at 0/8BE76A8
LOG:  database system is ready to accept read only connections
LOG:  streaming replication successfully connected to primary
LOG:  redo starts at 0/8BE76A8

若看到上述訊息就是成功了！
試著在master建立table，master會複製一份到slave。
Slave是read only，若有寫入的動作就會出現warning。


參考資料

• Hot Standby - PostgreSQL wiki
• PostgreSQL: Documentation: Manuals: PostgreSQL 9.0: 25.2.4. Setting Up a Standby Server
• PostgreSQL: Documentation: Manuals: PostgreSQL 9.0: 24.3.3. Recovering using a Continuous Archive Backup
• PostgreSQL: Documentation: Manuals: PostgreSQL 8.1: On-line backup and point-in-time recovery (PITR)