postgresql streaming replication

postgresql从9.0开始有流复制,这里记录一下流复制的安装记录。
环境:
redhat 5.5 64位
PG版本:9.1.2
master:192.168.1.168
slave:192.168.1.169

1.配置主机系统相关参数
1.1修改/etc/sysctl.conf
kernel.shmmni = 4096
kernel.sem = 50100 64128000 50100 1280
fs.file-max = 7672460
net.ipv4.ip_local_port_range = 9000 65000
net.core.rmem_default = 1048576
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_max_syn_backlog = 4096
net.core.netdev_max_backlog = 10000
vm.overcommit_memory = 0
net.ipv4.ip_conntrack_max = 655360
fs.aio-max-nr = 1048576
net.ipv4.tcp_timestamps = 0
1.2修改 /etc/security/limits.conf

  • soft nofile 131072
  • hard nofile 131072
  • soft nproc 131072
  • hard nproc 131072
  • soft core unlimited
  • hard core unlimited
  • soft memlock 50000000
  • hard memlock 50000000
    1.3配置系统环境变量
    export PS1=”$USER@/bin/hostname -s-> “
    export PGPORT=1921
    export PGDATA=/opt/pgdata/pg_root
    export LANG=en_US.utf8
    export PGHOME=/opt/pgsql9.1.2
    export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib
    export DATE=date +"%Y%m%d%H%M"
    export PATH=$PGHOME/bin:$PATH:.
    export MANPATH=$PGHOME/share/man:$MANPATH
    alias rm=’rm -i’
    alias ll=’ls -lh’
    1.4配置其他
    防火墙,服务相关等等,这里根据需要就不再一一列举
    2.安装PG(略)
    3.在master建立流复制用户
    create role repluser SUPERUSER LOGIN CONNECTION LIMIT 20 ENCRYPTED PASSWORD ‘repluser’;–注意这里满足login,replication权限即可,不是必须superuser。
    4.master配置pg_hba,conf,添加以下:
    host replication repluser 192.168.1.169/32 md5
    5.master配置postgresql.conf
    listen_addresses = ‘‘ # what IP address(es) to listen on;
    port = 1921 # (change requires restart)
    max_connections = 2000 # (change requires restart)
    unix_socket_directory = ‘/opt/pgdata/pg_root’ # (change requires restart)
    unix_socket_permissions = 0700 # begin with 0 to use octal notation
    password_encryption = on
    shared_buffers = 2048MB # min 128kB
    maintenance_work_mem = 2048MB # min 1MB
    max_stack_depth = 8MB # min 100kB
    wal_level = hot_standby # minimal, archive, or hot_standby
    synchronous_commit = off # immediate fsync at commit
    wal_sync_method = fdatasync # the default is the first option
    wal_buffers = 128000kB # min 32kB
    wal_writer_delay = 20ms # 1-10000 milliseconds
    checkpoint_segments = 64 # in logfile segments, min 1, 16MB each
    checkpoint_timeout = 30min # range 30s-1h
    archive_mode = on # allows archiving to be done
    archive_command = ‘/bin/date’ # command to use to archive a logfile segment
    max_wal_senders = 30 # max number of walsender processes
    wal_keep_segments = 1000 # in logfile segments, 16MB each; 0 disables;因为本例使用的logfile segment单个=64M,所以这里需要至少64
    1000=64GB的pg_xlog目录
    空间,否则可能空间溢出.
    random_page_cost = 2.0 # same scale as above
    effective_cache_size = 12800MB
    constraint_exclusion = partition # on, off, or partition
    log_destination = ‘csvlog’ # Valid values are combinations of
    logging_collector = on # Enable capturing of stderr and csvlog
    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会直接关掉。
你活着,证明了什么?