postgresql streaming replication

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

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会直接关掉。