PostgreSQL10.4主从流复制
系统版本:CentOS 6.9 x64
数据库版本:PostgreSQL10.4
主库IP:192.168.111.13
从库IP:192.168.111.23
数据库确保打开支持流复制的选项,以下是postgresql.conf的配置文件,供参考
系统内存128G,硬盘SAS 10K RPM
listen_addresses = '*' port = 5432 max_connections = 1000 tcp_keepalives_idle = 7200 tcp_keepalives_interval = 75 tcp_keepalives_count = 9 shared_buffers = 32GB work_mem = 128MB maintenance_work_mem = 4GB autovacuum_work_mem = 1GB dynamic_shared_memory_type = posix bgwriter_lru_maxpages = 1000 bgwriter_lru_multiplier = 10.0 effective_io_concurrency = 2 max_worker_processes = 40 max_parallel_workers_per_gather = 20 max_parallel_workers = 40 wal_level = replica fsync = on synchronous_commit = off wal_buffers = 1GB checkpoint_timeout = 30min max_wal_size = 64GB min_wal_size = 16GB checkpoint_completion_target = 0.9 archive_mode = on archive_command = '/bin/date >/dev/null' max_wal_senders = 10 wal_keep_segments = 512 hot_standby = on max_standby_archive_delay = 700s max_standby_streaming_delay = 700s effective_cache_size = 96GB log_destination = 'stderr' logging_collector = on log_directory = 'log' log_filename = 'postgresql-%Y%m%d_%H%M%S.log' log_rotation_age = 1d log_rotation_size = 100MB log_min_duration_statement = 5s log_checkpoints = on log_connections = on log_disconnections = on log_error_verbosity = verbose log_line_prefix = '%t [%p]: [%l-1] db=%d,user=%u,app=%a,client=%h ' log_lock_waits = on log_statement = 'ddl' log_temp_files = 0 log_timezone = 'PRC' log_autovacuum_min_duration = 0 autovacuum_max_workers = 8 autovacuum_naptime = 10min datestyle = 'iso, ymd' timezone = 'PRC' lc_messages = 'zh_CN.UTF-8' lc_monetary = 'zh_CN.UTF-8' lc_numeric = 'zh_CN.UTF-8' lc_time = 'zh_CN.UTF-8' default_text_search_config = 'pg_catalog.simple'
主库创建流复制角色
CREATE ROLE app_rep LOGIN replication ENCRYPTED PASSWORD 'passwd@123';
主库增加pg_hba.conf授信
在最后增加从库的IP,允许免密访问
host replication all 192.168.111.23/32 trust
从库安装好PostgreSQL10.4,不需要initdb,然后执行:
/data/pgsql/bin/pg_basebackup -D /data/pgsql/data -h192.168.111.13 -p5432 -Uapp_rep -X stream -v --checkpoint=fast -r 50M
等待复制完成后,在从库/data/pgsql/data新建recovery.conf,内容如下:
recovery_target_timeline = 'latest' standby_mode = on primary_conninfo = 'host=192.168.111.12 port=5432 user=app_rep password=xxxx'
然后启动从库,检查下主从复制有无成功。
主从后续问题收集:
1、从库执行SQL时间长时会失败,提示:Error message from server: ERROR: canceling statement due to conflict with recovery
从库调整postgresql.conf文件下面的参数,增加等待时间,酌情调整,-1则是等待从库执行完,主库会有日志膨胀的风险。
max_standby_archive_delay = 700s
max_standby_streaming_delay = 700s
2、主从角色如何切换?
主机器停掉,然后修改从机器/data/pgsql/data/recovery.conf,变更如下:
standby_mode = on
primary_conninfo = 'host=192.168.111.23 port=5432 user=app_rep password=xxxx'
trigger_file = '/data/pgsql/data/cctv.txt'
重启从机器(不用重启也可以,我等不及直接重启),原先的主机器上新建/data/pgsql/data/recovery.conf,内容如下:
recovery_target_timeline = 'latest' standby_mode = on primary_conninfo = 'host=192.168.111.23 port=5432 user=app_rep password=xxxx'
然后启动。
目录 返回
首页