PostgreSQL Streaming Replication

มาทำระบบ Backup Replicate PostgeSQL กันครับ

1. เริ่มต้นติดตั้ง PostgreSQL ก่อนโดยโหลด PostgreSQL Version ล่าสุด version PostgreSQL9.6.4

2. ติดตั้งเสร็จแล้วให้แก้ไขไฟล์ postgresql.conf

#sudo nano /opt/PostgreSQL/9.6/data/postgresql.conf

เพิ่ม include ‘postgresql.tuning’ ไปที่บรรทัดล่างสุด

3. สร้างไฟล์ postgresql.tuning ขึ้นมาโดยวางใน path เดียวกันกับ postgresql.conf

#sudo nano /opt/PostgreSQL/9.6/data/postgresql.tuning

##############################
port = 5432 # (change requires restart)
max_connections = 700 # (change requires restart)

##### Tuning for Ram 32GB #####
shared_buffers = 7680MB # (change requires restart)
effective_cache_size = 23040MB
work_mem = 11234kB
maintenance_work_mem = 1920MB
min_wal_size = 2GB
max_wal_size = 4GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB # (change requires restart)
default_statistics_target = 100

##### Tuning for Ram 16GB #####
#shared_buffers = 3584MB
#effective_cache_size = 10752MB
#work_mem = 5242kB
#maintenance_work_mem = 896MB
#max_wal_size = 2GB
#min_wal_size = 4GB
#checkpoint_completion_target = 0.9
#wal_buffers = 16MB
#default_statistics_target = 100

##### Tuning for Ram 8GB #####
#shared_buffers = 1536MB
#effective_cache_size = 4608MB
#work_mem = 2246kB
#maintenance_work_mem = 384MB
#max_wal_size = 2GB
#min_wal_size = 4GB
#checkpoint_completion_target = 0.9
#wal_buffers = 16MB
#default_statistics_target = 100

##### Tuning for Ram 4GB #####
#shared_buffers = 768MB
#effective_cache_size = 2304MB
#work_mem = 1123kB
#maintenance_work_mem = 192MB
#max_wal_size = 2GB
#min_wal_size = 4GB
#checkpoint_completion_target = 0.9
#wal_buffers = 16MB
#default_statistics_target = 100

#### Parallel Query ####
max_worker_processes = 32 # (change requires restart)
max_parallel_workers_per_gather = 8 # (change requires restart)

## Streaming Replication ##
wal_level = hot_standby # (change requires restart)
wal_log_hints = on # (change requires restart)
max_wal_senders = 4 # (change requires restart)
wal_keep_segments = 128
hot_standby = on # (change requires restart)
hot_standby_feedback = on
max_standby_streaming_delay = -1

### Log ###
log_directory = ‘pg_log’
log_filename = ‘%a.log’
#log_statement = ‘all’

# AUTOVACUUM PARAMETERS
autovacuum = on
autovacuum_max_workers = 10 # (change requires restart)
autovacuum_vacuum_threshold = 50
autovacuum_analyze_threshold = 50
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_scale_factor = 0.05

4. แก้ไขไฟล์ pg_hba.conf

#sudo nano /opt/PostgreSQL/9.6/data/pg_hba.conf

เพิ่ม host replication postgres xxx.xxx.xxx.xxx/32 trust

5. restart service postgresql

#sudo /etc/init.d/postgresql-9.6 restart

6. ไปที่เครื่อง Slave ใช้คำสั่งดังนี้

#sudo -u postgres /opt/PostgreSQL/9.6/bin/pg_basebackup -h xxx.xxx.xxx.xxx(ipmaster) -D data -U postgres -v -P -R -X stream -c fast

7. สั่ง start service postgresql

#sudo /etc/init.d/postgresql-9.6 start

8. ตรวจสอบที่เครื่อง Master ว่า Replicate ทำงานไหมด้วยคำสั่งนี้

/opt/PostgreSQL/9.6/bin/psql -U postgres -p 5432 -h localhost -x -c “select * from pg_stat_replication;”
/opt/PostgreSQL/9.6/bin/psql -U postgres -p 5432 -h localhost -c “select pg_xlog_location_diff(pg_current_xlog_location(),replay_location) from pg_stat_replication;”
/opt/PostgreSQL/9.6/bin/psql -U postgres -h localhost -c “select client_addr, state, sent_location, write_location,flush_location, replay_location, sync_priority from pg_stat_replication;”
/opt/PostgreSQL/9.6/bin/psql -U postgres -h xxx.xxx.xxx.xxx(ip Slave) -c “select now() — pg_last_xact_replay_timestamp() AS replication_delay;”