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

Postgresql 小記

常用指令

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

# Stop
su postgres -c "/usr/local/pgsql/bin/pg_ctl stop -D /usr/local/pgsql/data"

# restart
su postgres -c "/usr/local/pgsql/bin/pg_ctl restart -D /usr/local/pgsql/data"

# Init postgres
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"

# Create user
/usr/local/pgsql/bin/createuser -U postgres -P userA

# Drop user
/usr/local/pgsql/bin/dropuser -U postgres userA

# Create db
/usr/local/pgsql/bin/createdb -O userA -U postgres testdb

# Drop db
/usr/local/pgsql/bin/dropdb -U postgres testdb

# Dump db
/usr/local/pgsql/bin/pg_dump -U postgres testdb > dump.sql

# Restore db
/usr/local/pgsql/bin/psql -U postgres -d testdb < dump.sql

# Grant all privileges
/usr/local/pgsql/bin/psql -U postgres -c "GRANT ALL PRIVILEGES ON DATABASE testdb TO userA;"

# Enter postgresql command line
/usr/local/pgsql/bin/psql -U postgres -d poesgres -h localhost -p 5432

基本設定

• /usr/local/pgsql/data/postgres.conf

listen_addresses = '192.168.0.3'   # 設定listen IP

• /usr/local/pgsql/data/pg_hba.conf

host    all             all             127.0.0.1/32              trust
host    all             all             192.168.0.0/16            trust
host    all             all             10.10.1.5/32              password

Log分析
推薦使用pgFouine - a PostgreSQL log analyzer，它可以分析出某段時間內，做了幾次query，處理時間最久的，次數最多的...可以看一下Sample reports長怎樣。


效能測試:
pgbench
使用 pgbench 进行数据库压力测试

pgbench -U postgres -i -s 50 postgres 
pgbench -U postgres -c 100 -t 100 -S postgres

Performance Tuning PostgreSQL