PostgreSQL 設定 Replication

PostgreSQL 要設定 Replication，要怎麼做呢？

PostgreSQL 設定 Replication

PostgreSQL Replication Master / Slave 設定步驟

• mdb ip = 192.168.1.1 # master db
• sdb ip = 192.168.1.2 # slave db

PostgreSQL Master

1. ssh mdb
2. vim /etc/postgresql/13/main/postgresql.conf
   # 基本設定
   listen_addresses = '*'
   wal_level = replica
   max_wal_senders = 10
   wal_keep_size = 512MB # 保留多少 WAL 給 slave 同步
   max_replication_slots = 3 # 可給幾個 slave 使用
3. vim /etc/postgresql/13/main/pg_hba.conf
   TYPE DATABASE USER ADDRESS METHOD
   host replication repl 192.168.1.2/32 md5
4. sudo -u postgres psql
5. CREATE ROLE repl WITH REPLICATION LOGIN PASSWORD 'DB-REPLICATION-PASSWORD';
6. \du repl # 確認
7. exit
8. sudo systemctl restart postgresql
9. sudo -u postgres psql -c "SELECT * FROM pg_stat_replication;" # 查看 master 狀態(現在應該是空的)

PostgreSQL Slave

1. ssh sdb
2. sudo systemctl stop postgresql
3. sudo su -
4. rm -rf /var/lib/postgresql/13/main/*
5. sudo -u postgres pg_basebackup -h mdb -D /var/lib/postgresql/13/main -U repl -Fp -Xs -P -R # sync data from master, 會產生 primary_conninfo 的檔案
   • 密碼是：postgresql repl account password (DB-REPLICATION-PASSWORD)
6. sudo less /var/lib/postgresql/13/main/postgresql.auto.conf # 確認 primary_conninfo 的檔案內容
   • primary_conninfo = 'user=repl password=''DB-REPLICATION-PASSWORD'' channel_binding=prefer host=mdb port=5432 sslmode=prefer sslcompression=0 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any'
7. sudo systemctl start postgresql
8. sudo -u postgres psql -c "SELECT pg_is_in_recovery();" # 要出現 t (standby)

Master 同步狀態確認

1. ssh mdb # 確認同步狀態
2. sudo -u postgres psql -c "SELECT pid, state, client_addr, sync_state FROM pg_stat_replication;"

Master / Slave 測試

Test:

1. ssh mdb # (master)
2. sudo -u postgres psql -c "CREATE TABLE test(x int); INSERT INTO test VALUES (1);"
3. ssh sdb # (slave)
4. sudo -u postgres psql -c "SELECT * FROM test;" # 1
5. ssh mdb
6. sudo -u postgres psql -c "DRPP TABLE test;"
7. ssh sdb # (slave)
8. sudo -u postgres psql -c "SELECT * FROM test;" # not found