配置修改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会直接关掉。

文章结束给大家分享下程序员的一些笑话语录: 古鸽是一种搜索隐禽,在中国快绝迹了…初步的研究表明,古鸽的离去,很可能导致另一种长着熊爪,酷似古鸽,却又习性不同的猛禽类——犤毒鸟

posted @ 2013-05-07 19:19  坚固66  阅读(357)  评论(0编辑  收藏  举报