postgresql streaming replication(二)
log_connections = on # 调试阶段建议主库和standby都打开log_connections,调试完后建议关闭.
log_directory = '/opt/pgdata/pg_log' # directory where log files are written,
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern,
log_truncate_on_rotation = on # If on, an existing log file of the
log_rotation_age = 1d # Automatic rotation of logfiles will
log_rotation_size = 10MB # Automatic rotation of logfiles will
log_min_duration_statement = 1000ms # -1 is disabled, 0 logs all statements
log_checkpoints = on
log_lock_waits = on # log lock waits >= deadlock_timeout
log_statement = 'ddl' # none, ddl, mod, all
track_activity_query_size = 2048 # (change requires restart)
autovacuum = on # Enable autovacuum subprocess 'on'
log_autovacuum_min_duration = 0 # -1 disables, 0 logs all actions and
check_function_bodies = on
bytea_output = 'escape' # hex, escape
datestyle = 'iso, mdy'
lc_messages = 'C' # locale for system error message
lc_monetary = 'C' # locale for monetary formatting
lc_numeric = 'C' # locale for number formatting
lc_time = 'C' # locale for time formatting
default_text_search_config = 'pg_catalog.english'
deadlock_timeout = 1s
tcp_keepalives_idle = 60
6.启动master并对其进行全备一次
select pg_start_backup('replication backup');
将$PGDATA压缩传送的slave
select pg_stop_backup();
7.slave配置
相应的也是配置pg_hba.conf,postgressql.conf
host replication repluser 191.168.169.85/32 md5
slave配置hot_standby = on其他基本不需要修改，可以和master一致
除了配置以上的，slave还需要配置recovery.conf
#archive_cleanup_command = 'pg_archivecleanup $PGARCHIVE %r'
standby_mode = 'on'
trigger_file = '/opt/pgdata/pg_root/postgresql.trigger.1921'
primary_conninfo = 'host=192.168.1.168 port=1921 user=repluser password=repluser keepalives_idle=60'
修改
more ~/.pgpass
192.168.1.168:1921:replication:repluser:repluser:repluser
启动slave观察cvs日志
查看wal进程：
master：
wal sender process repluser 192.168.1.169(16494) streaming 0/70273E8
slave
postgres: wal receiver process streaming 0/70273E8
8.测试
建立
数据库，用户，测试数据，注意表空间的一定要建好相关目录，而且要一主从节点一致，否则slave会直接关掉。