postgresql streaming replication

archer05

postgresql从9.0开始有流复制，这里记录一下流复制的安装记录。
<wbr style=""><div style="">环境：</div><div style="">redhat 5.5 64位</div><div style="">PG版本：9.1.2</div><div style="">master：192.168.1.168</div><div style="">slave:192.168.1.169</div><div style=""><br style="line-height:25px"></div><div style=""><strong style="line-height:25px"><span style="font-size:14px; line-height:28px">1.配置主机系统相关参数</span></strong></div><div style=""><strong style="line-height:25px"><span style="font-size:14px; line-height:28px"> 1.1修改/etc/sysctl.conf</span></strong></div><div style="">kernel.shmmni = 4096</div><div style="">kernel.sem = 50100 64128000 50100 1280</div><div style="">fs.file-max = 7672460</div><div style="">net.ipv4.ip_local_port_range = 9000 65000</div><div style="">net.core.rmem_default = 1048576</div><div style="">net.core.rmem_max = 4194304</div><div style="">net.core.wmem_default = 262144</div><div style="">net.core.wmem_max = 1048576</div><div style="">net.ipv4.tcp_tw_recycle = 1</div><div style="">net.ipv4.tcp_max_syn_backlog = 4096</div><div style="">net.core.netdev_max_backlog = 10000</div><div style="">vm.overcommit_memory = 0</div><div style="">net.ipv4.ip_conntrack_max = 655360</div><div style="">fs.aio-max-nr = 1048576</div><div style="">net.ipv4.tcp_timestamps = 0</div><div style="">1.2修改/etc/security/limits.conf</div><div style=""><div style="line-height:25px">* soft  nofile 131072</div><div style="line-height:25px">* hard  nofile 131072</div><div style="line-height:25px">* soft  nproc  131072</div><div style="line-height:25px">* hard  nproc  131072</div><div style="line-height:25px">* soft  core  unlimited</div><div style="line-height:25px">* hard  core  unlimited</div><div style="line-height:25px">* soft  memlock 50000000</div><div style="line-height:25px">* hard  memlock 50000000</div></div><div style=""><strong style="line-height:25px"><span style="font-size:14px; line-height:28px">1.3配置系统环境变量</span></strong></div><div style=""><div style="line-height:25px">export PS1="$USER@`/bin/hostname -s`-&gt; "</div><div style="line-height:25px">export PGPORT=1921</div><div style="line-height:25px">export PGDATA=/opt/pgdata/pg_root</div><div style="line-height:25px">export LANG=en_US.utf8</div><div style="line-height:25px">export PGHOME=/opt/pgsql9.1.2</div><div style="line-height:25px">export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib</div><div style="line-height:25px">export DATE=`date +"%Y%m%d%H%M"`</div><div style="line-height:25px">export PATH=$PGHOME/bin:$PATH:.</div><div style="line-height:25px">export MANPATH=$PGHOME/share/man:$MANPATH</div><div style="line-height:25px">alias rm='rm -i'</div><div style="line-height:25px">alias ll='ls -lh'</div></div><div style=""><strong style="line-height:25px"><span style="font-size:14px; line-height:28px">1.4配置其他</span></strong></div><div style="">防火墙，服务相关等等，这里根据需要就不再一一列举</div><div style=""><strong style="line-height:25px"><span style="font-size:14px; line-height:28px">2.安装PG(略)</span></strong></div><div style="">3.在master建立流复制用户</div><div style=""><span style="font-family:Arial,Helvetica,simsun,u5b8bu4f53; color:#333333; line-height:25px">create role repluser SUPERUSER LOGIN CONNECTION LIMIT 20 ENCRYPTED PASSWORD 'repluser';--注意这里满足login，replication权限即可，不是必须superuser。</span></div><div style=""><span style="font-family:Arial,Helvetica,simsun,u5b8bu4f53; color:#333333; line-height:25px"><strong style="line-height:25px"><span style="font-size:14px; line-height:28px">4.master配置pg_hba,conf,添加以下：</span></strong></span></div><div style=""><span style="font-family:Arial,Helvetica,simsun,u5b8bu4f53; color:#333333; line-height:25px">host replication    repluser    192.168.1.169/32    md5</span></div><div style=""><span style="font-family:Arial,Helvetica,simsun,u5b8bu4f53; font-size:14px; color:#333333; line-height:28px"><strong style="line-height:28px">5.master配置postgresql.conf</strong></span></div><div style=""><span style="font-family:Arial,Helvetica,simsun,u5b8bu4f53; color:#333333; line-height:25px"></span><div style="line-height:25px">listen_addresses = '*'     # what IP address(es) to listen on;</div><div style="line-height:25px">port = 1921               # (change requires restart)</div><div style="line-height:25px">max_connections = 2000         # (change requires restart)</div><div style="line-height:25px">unix_socket_directory = '/opt/pgdata/pg_root'     # (change requires restart)</div><div style="line-height:25px">unix_socket_permissions = 0700     # begin with 0 to use octal notation</div><div style="line-height:25px">password_encryption = on</div><div style="line-height:25px">shared_buffers = 2048MB         # min 128kB</div><div style="line-height:25px">maintenance_work_mem = 2048MB      # min 1MB</div><div style="line-height:25px">max_stack_depth = 8MB          # min 100kB</div><div style="line-height:25px">wal_level = hot_standby         # minimal, archive, or hot_standby</div><div style="line-height:25px">synchronous_commit = off        # immediate fsync at commit</div><div style="line-height:25px">wal_sync_method = fdatasync       # the default is the first option</div><div style="line-height:25px">wal_buffers = 128000kB         # min 32kB</div><div style="line-height:25px">wal_writer_delay = 20ms         # 1-10000 milliseconds</div><div style="line-height:25px">checkpoint_segments = 64        # in logfile segments, min 1, 16MB each</div><div style="line-height:25px">checkpoint_timeout = 30min       # range 30s-1h</div><div style="line-height:25px">archive_mode = on        # allows archiving to be done</div><div style="line-height:25px">archive_command = '/bin/date'     # command to use to archive a logfile segment</div><div style="line-height:25px">max_wal_senders = 30      # max number of walsender processes</div><div style="line-height:25px">wal_keep_segments = 1000    # in logfile segments, 16MB each; 0 disables;因为本例使用的logfile segment单个=64M,所以这里需要至少64*1000=64GB的pg_xlog目录</div><div style="line-height:25px">空间,否则可能空间溢出.</div><div style="line-height:25px">random_page_cost = 2.0         # same scale as above</div><div style="line-height:25px">effective_cache_size = 12800MB</div><div style="line-height:25px">constraint_exclusion = partition    # on, off, or partition</div><div style="line-height:25px">log_destination = 'csvlog'       # Valid values are combinations of</div><div style="line-height:25px">logging_collector = on     # Enable capturing of stderr and csvlog</div><div style="line-height:25px">log_connections = on      # 调试阶段建议主库和standby都打开log_connections,调试完后建议关闭.</div><div style="line-height:25px">log_directory = '/opt/pgdata/pg_log'      # directory where log files are written,</div><div style="line-height:25px">log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern,</div><div style="line-height:25px">log_truncate_on_rotation = on      # If on, an existing log file of the</div><div style="line-height:25px">log_rotation_age = 1d          # Automatic rotation of logfiles will</div><div style="line-height:25px">log_rotation_size = 10MB        # Automatic rotation of logfiles will</div><div style="line-height:25px">log_min_duration_statement = 1000ms   # -1 is disabled, 0 logs all statements</div><div style="line-height:25px">log_checkpoints = on</div><div style="line-height:25px">log_lock_waits = on           # log lock waits &gt;= deadlock_timeout</div><div style="line-height:25px">log_statement = 'ddl'          # none, ddl, mod, all</div><div style="line-height:25px">track_activity_query_size = 2048    # (change requires restart)</div><div style="line-height:25px">autovacuum = on         # Enable autovacuum subprocess? 'on'</div><div style="line-height:25px">log_autovacuum_min_duration = 0 # -1 disables, 0 logs all actions and</div><div style="line-height:25px">check_function_bodies = on</div><div style="line-height:25px">bytea_output = 'escape'         # hex, escape</div><div style="line-height:25px">datestyle = 'iso, mdy'</div><div style="line-height:25px">lc_messages = 'C'            # locale for system error message</div><div style="line-height:25px">lc_monetary = 'C'            # locale for monetary formatting</div><div style="line-height:25px">lc_numeric = 'C'            # locale for number formatting</div><div style="line-height:25px">lc_time = 'C'              # locale for time formatting</div><div style="line-height:25px">default_text_search_config = 'pg_catalog.english'</div><div style="line-height:25px">deadlock_timeout = 1s</div><div style="line-height:25px">tcp_keepalives_idle = 60</div><div style="line-height:25px"><strong style="line-height:25px"><span style="font-size:14px; line-height:28px">6.启动master并对其进行全备一次</span></strong></div><span style="font-family:Arial,Helvetica,simsun,u5b8bu4f53; color:#333333; line-height:25px">select pg_start_backup('replication backup');</span></div><div style=""><span style="font-family:Arial,Helvetica,simsun,u5b8bu4f53; color:#333333; line-height:25px"><span style="line-height:25px">将$PGDATA压缩传送的slave</span></span></div><div style=""><span style="font-family:Arial,Helvetica,simsun,u5b8bu4f53; color:#333333; line-height:25px">select pg_stop_backup();</span></div><div style=""><span style="font-family:Arial,Helvetica,simsun,u5b8bu4f53; color:#333333; line-height:25px"><strong style="line-height:25px"><span style="font-size:14px; line-height:28px">7.slave配置</span></strong></span></div><div style=""><span style="font-family:Arial,Helvetica,simsun,u5b8bu4f53; color:#333333; line-height:25px">相应的也是配置pg_hba.conf,postgressql.conf</span></div><div style=""><span style="font-family:Arial,Helvetica,simsun,u5b8bu4f53; color:#333333; line-height:25px"></span><div style="line-height:25px">host   replication   repluser    191.168.169.85/32   md5</div><div style="line-height:25px">slave配置hot_standby = on其他基本不需要修改，可以和master一致</div><span style="font-family:Arial,Helvetica,simsun,u5b8bu4f53; color:#333333; line-height:25px"></span><div style="line-height:25px">除了配置以上的，slave还需要配置recovery.conf</div><div style="line-height:25px"><div style="line-height:25px">#archive_cleanup_command = 'pg_archivecleanup $PGARCHIVE %r'</div><div style="line-height:25px">standby_mode = 'on'</div><div style="line-height:25px">trigger_file = '/opt/pgdata/pg_root/postgresql.trigger.1921'</div><div style="line-height:25px">primary_conninfo = 'host=192.168.1.168 port=1921 user=repluser password=repluser keepalives_idle=60'</div></div><span style="font-family:Arial,Helvetica,simsun,u5b8bu4f53; color:#333333; line-height:25px"></span><div style="line-height:25px"><span style="font-family:Arial,Helvetica,simsun,u5b8bu4f53; color:#333333; line-height:25px">修改</span></div>more ~/.pgpass</div><div style=""><span style="font-family:Arial,Helvetica,simsun,u5b8bu4f53; color:#333333; line-height:25px"></span><span style="font-family:Arial,Helvetica,simsun,u5b8bu4f53; color:#333333; line-height:25px">192.168.1.168:1921:replication:repluser:repluser:repluser</span></div><div style=""><span style="font-family:Arial,Helvetica,simsun,u5b8bu4f53; color:#333333; line-height:25px">启动slave观察cvs日志</span></div><div style=""><span style="font-family:Arial,Helvetica,simsun,u5b8bu4f53; color:#333333; line-height:25px">查看wal进程：</span></div><div style=""><span style="font-family:Arial,Helvetica,simsun,u5b8bu4f53; color:#333333; line-height:25px">master：</span></div><div style=""><span style="font-family:Arial,Helvetica,simsun,u5b8bu4f53; color:#333333; line-height:25px">wal sender process repluser 192.168.1.169(16494) streaming 0/70273E8</span></div><div style=""><span style="font-family:Arial,Helvetica,simsun,u5b8bu4f53; color:#333333; line-height:25px">slave</span></div><div style=""><span style="font-family:Arial,Helvetica,simsun,u5b8bu4f53; color:#333333; line-height:25px">postgres: wal receiver process  streaming 0/70273E8</span></div><div style=""><span style="font-family:Arial,Helvetica,simsun,u5b8bu4f53; font-size:14px; color:#333333; line-height:28px"><strong style="line-height:28px">8.测试</strong></span></div><div style=""><span style="font-family:Arial,Helvetica,simsun,u5b8bu4f53; color:#333333; line-height:25px">建立数据库，用户，测试数据，注意表空间的一定要建好相关目录，而且要一主从节点一致，否则slave会直接关掉。</span></div></wbr>