主库配置

  1. # 1.进入主库数据库，创建同步的用户名
  2. su postgres
  3. psql
  4. create role repl login replication encrypted password 'password';
  5. # 2. 修改主库 pg_hba.conf 的配置文件
  6. vim /usr/local/postgresql/data/pg_hba.conf
  7. # 增加如下配置
  8. host    all             all             192.168.1.0/24          trust
  9. host    replication     repl            192.168.1.0/24          md5
  10. # 3. 修改 主库 postgres.conf 的配置文件
  11. vim /usr/local/postgresql/data/postgresql.conf
  13. shared_buffers = 128MB           
  14. dynamic_shared_memory_type = posix  
  15. wal_level = hot_standby           #此参数主数据库和从数据库不一样（从库为relpica）
  16. archive_mode = on        
  17. archive_command = 'cp %p /usr/local/postgresql/data/pg_archive/%f'        
  18. max_wal_senders = 6       
  19. wal_keep_segments = 10240   
  20. wal_sender_timeout = 60s    
  21. log_directory = 'log'
  23. # 4. 创建归档目录
  24. mkdir /usr/local/postgresql/data/pg_archive
  25. # 5. 重启主库
  26. systemctl restart postgresql11
  27. systemctl status postgresql11

从库配置

# 从库不需要从初始化操作，直接从主库备份
# 如果已经初始化，则清空data目录即可 rm -rf /usr/local/postgresql/data

# 1. 从主库备份
su postgres
cd # 切换家目录
pg_basebackup -D /usr/local/postgresql/data -Fp -Xs -v -P -h 192.168.1.10 -p 5432 -U repl 
# 3. 修改配置文件
vim /usr/local/postgresql/data/postgresql.conf
shared_buffers = 128MB            
dynamic_shared_memory_type = posix    
wal_level = replica        
archive_mode = on        
archive_command = 'cp %p /usr/local/postgresql/data/pg_archive/%f'        
wal_sender_timeout = 60s    
hot_standby = on            
max_standby_streaming_delay = 30s    
wal_receiver_status_interval = 10s    
hot_standby_feedback = on        
log_directory = 'log'

# 4. 拷贝并修改recovery文件
cp /usr/local/postgresql/share/recovery.conf.sample /usr/local/postgresql/data/recovery.conf

vim /usr/local/postgresql/data/recovery.conf
recovery_target_timeline = 'latest'   #同步到最新数据
standby_ mode = on          #指明从库身份
trigger_file = 'failover.now'
primary_conninfo = 'host=192.168.1.10 port=5432 user=repl password=password' 

# 重启数据库
systemctl restart postgresql11
# 如果启动报错 
chmod 700 -R /usr/local/postgresql

# 5. 验证从库

postgres=# select pg_is_in_recovery(); 
 pg_is_in_recovery 
-------------------
 t
(1 row)

# 6. 验证主库
postgres=# select pg_is_in_recovery(); 
 pg_is_in_recovery 
-------------------
 f
(1 row)
postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 61806
usesysid         | 16384
usename          | repl
application_name | walreceiver
client_addr      | 192.168.1.11
client_hostname  | 
client_port      | 57038
backend_start    | 2022-06-15 22:23:48.630817+08
backend_xmin     | 
state            | streaming
sent_lsn         | 0/5000140
write_lsn        | 0/5000140
flush_lsn        | 0/5000140
replay_lsn       | 0/5000140
write_lag        | 
flush_lag        | 
replay_lag       | 
sync_priority    | 0
sync_state       | async