PostgreSQL HOT STANDBY - using Stream - 1

digoal

案例解析二、
PostgreSQL HOT STANDBY by stream replication 测试:
一、准备硬件
1. 主节点硬件配置
DISK : 146GB*6
MEM : 14GB
CPU : 2.83GHz*8
2. standby节点硬件配置
DISK : 146GB*4
MEM : 8GB
CPU : 2.0GHz*8

二、准备环境
1. 系统
Red Hat Enterprise Linux Server release 5.5 (Tikanga) x64
2. 时钟同步
8 * * * * /usr/sbin/ntpdate asia.pool.ntp.org && /sbin/hwclock --systohc
3. 配置目录
mkdir -p /database/pgdata/tbs1
mkdir -p /database/pgdata/tbs2
mkdir -p /database/pgdata/tbs3
mkdir -p /database/pgdata/tbs4
mkdir -p /database/pgdata/tbs5
fdisk
mkfs.ext3
mount /dev/cciss/c0d1p1 /database/pgdata/tbs1
mount /dev/cciss/c0d2p1 /database/pgdata/tbs2
mount /dev/cciss/c0d3p1 /database/pgdata/tbs3
mount /dev/cciss/c0d4p1 /database/pgdata/tbs4
mount /dev/cciss/c0d5p1 /database/pgdata/tbs5
master节点:
[root@db-172-16-3-33 ~]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/cciss/c0d0p1      31G  8.1G   21G  29% /
/dev/cciss/c0d0p3      88G  1.7G   81G   3% /opt
tmpfs                 6.9G     0  6.9G   0% /dev/shm
/dev/cciss/c0d1p1     135G   76M  128G   1% /database/pgdata/tbs1
/dev/cciss/c0d2p1     135G  6.1G  122G   5% /database/pgdata/tbs2
/dev/cciss/c0d3p1     135G  3.3G  125G   3% /database/pgdata/tbs3
/dev/cciss/c0d4p1     135G  5.6G  123G   5% /database/pgdata/tbs4
/dev/cciss/c0d5p1     135G   16G  113G  13% /database/pgdata/tbs5

slave节点:
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda1              31G  3.5G   26G  13% /
/dev/sda3              94G  386M   89G   1% /opt
tmpfs                 3.9G     0  3.9G   0% /dev/shm
/dev/sdb1             134G   76M  128G   1% /database/pgdata/tbs1
/dev/sdc1             134G  188M  127G   1% /database/pgdata/tbs2
/dev/sdd1             134G  2.9G  125G   3% /database/pgdata/tbs3
172.16.3.33:/database/pgdata/tbs4
                      135G  5.6G  123G   5% /database/pgdata/tbs4

vi /etc/fstab
4. 在主节点配置nfs,将wal归档目录export出去,
(这里没有使用中央日志服务器,有条件的话还是需要一个比较大的日志服务器为好,以便支持更多的slave节点)
/database/pgdata/tbs4 172.16.3.39/32(rw,no_root_squash,sync)
  slave节点mount这个目录.
  确保master节点和slave节点的postgres用户gid uid相同,否则可能有权限的问题.
5. 配置内核参数等
kernel.shmmni = 4096
kernel.sem = 501000 6412800000 501000 12800
fs.file-max = 767246
net.ipv4.ip_local_port_range = 1024 65000
net.core.rmem_default = 1048576
net.core.rmem_max = 1048576
net.core.wmem_default = 262144
net.core.wmem_max = 262144
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

*  soft    nofile  131072
*  hard    nofile  131072
*  soft    nproc   131072
*  hard    nproc   131072
*  soft    core    unlimited
*  hard    core    unlimited
*  soft    memlock 50000000
*  hard    memlock 50000000

6. 配置系统服务
chkconfig --level 35 nfs on
chkconfig --level 35 portmap pn

7. 配置防火墙
vi /etc/sysconfig/iptables

8. 升级操作系统补丁,驱动等

三、安装PostgreSQL 9.0.2
1. postgres user profile:
export PS1="$USER@`/bin/hostname -s`-> "
export PGPORT=1921
export PGDATA=/database/pgdata/tbs1/pg_root
export PGARCHIVE=/database/pgdata/tbs4/pg_arch

export LANG=en_US.utf8

export PGHOME=/opt/pgsql
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/binPATH:.
export MANPATH=$PGHOME/share/manMANPATH
alias rm='rm -i'
alias ll='ls -lh'

2. 配置数据库相关目录
2.1 log
  /var/applog/pg_log
2.2 pghome
  /opt/pgsql
2.3 pgdata
  /database/pgdata/tbs1/pg_root
2.4 pgarchive
  /database/pgdata/tbs4/pg_arch

3. 初始化数据库
initdb -D /database/pgdata/tbs1/pg_root -E UTF8 --locale=C -U postgres -X /database/pgdata/tbs2/pg_xlog -W

四、配置master节点
1. 新建slave用于连接master的数据库超级用户(在v8的版本中建议为每个stream standby新建一个超级用户,在v9中使用一个用户的情况下建议不同的stream standby配置不同的application_name参数值来区分不同的stream standby.)
   为了安全的考虑,配置pg_hba.conf,数据库复制的超级用户只允许从使用该用户的stream standby的主机连过来.
   连接限制:一个stream standby数据库至少需要一个连接,因为连接有hang住的可能,建议不要配太少了.
create role repuser1 SUPERUSER LOGIN CONNECTION LIMIT 20 ENCRYPTED PASSWORD 'repuser1REPUSER1';

2. pg_hba.conf
# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
# host    all             all             ::1/128                 trust
host replication repuser1 172.16.3.39/32  md5

3. 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 = '/database/pgdata/tbs1/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 = 'cp %p $PGARCHIVE/%f'         # 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 = '/var/applog/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

4. 启动主节点.

五、传输基础文件至slave节点,模拟一个正在运行的数据库生成复制库的操作.
1. on the master
select pg_start_backup('replication backup');
2. on the master
scp $PGDATA $SLAVE_IPPGDATA
3. on the master
select pg_stop_backup();

六、配置slave节点
1. on the slave
chown -R postgres:postgres $PGDATA
su - postgres
cd $PGDATA
rm postmaster.pid
rm .s.PGSQL.1921.lock
rm -rf pg_xlog
ln -s /database/pgdata/tbs2/pg_xlog ./pg_xlog
如果有非默认表空间,需要手工处理pg_tblspc

2. 配置pg_hba.conf
允许需要访问的客户端,

3. 配置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 = '/database/pgdata/tbs1/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 = 'cp %p $PGARCHIVE/%f'         # 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 = '/var/applog/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
hot_standby = off                        # 这个参数在初始化slave的时候关闭是比较明智的选择,在初始同步完成后在开启
tcp_keepalives_idle = 60                 #

3. 配置recovery.conf
cp $PGHOME/share/recovery.conf.sample $PGDATA/
cd $PGDATA
mv recovery.conf.sample recovery.conf
vi recovery.conf
restore_command = 'cp $PGARCHIVE/%f %p'         # e.g. 'cp /mnt/server/archivedir/%f %p'
archive_cleanup_command = 'pg_archivecleanup $PGARCHIVE %r'
standby_mode = 'on'
trigger_file = '/database/pgdata/tbs1/pg_root/postgresql.trigger.1921'
primary_conninfo = 'host=172.16.3.33 port=1921 user=repuser1 keepalives_idle=60'  # application_name也可以加在这里

4. 配置~/.pgpass 或$PGPASSFILE (PGPASSFILE specifies the name of the password file to use for lookups. If not set, it defaults to ~/.pgpass)
格式:hostname:port:database:username:password
172.16.3.33:1921:replication:repuser1:repuser1REPUSER1

chmod 400 .pgpass

5. 启动slave节点

启动完后,可以通过top看到slave节点在拼命的恢复pg_start_backup以来的所有wal.

查看slave节点日志:
2011-01-04 14:51:51.363 CST,,,25950,,4d22c387.655e,1,,2011-01-04 14:51:51 CST,,0,LOG,00000,"database system was shut down in recovery at 2011-01-04 14:51:26 CST",,,,,,,,,""
2011-01-04 14:51:51.363 CST,,,25950,,4d22c387.655e,2,,2011-01-04 14:51:51 CST,,0,LOG,00000,"entering standby mode",,,,,,,,,""
2011-01-04 14:51:51.442 CST,,,25950,,4d22c387.655e,3,,2011-01-04 14:51:51 CST,,0,LOG,00000,"restored log file ""00000001000000020000003E"" from archive",,,,,,,,,""
2011-01-04 14:51:51.443 CST,,,25950,,4d22c387.655e,4,,2011-01-04 14:51:51 CST,,0,LOG,00000,"redo starts at 2/F8000020",,,,,,,,,""
2011-01-04 14:51:51.443 CST,,,25950,,4d22c387.655e,5,,2011-01-04 14:51:51 CST,,0,LOG,00000,"consistent recovery state reached at 2/FC000000",,,,,,,,,""
2011-01-04 14:51:51.480 CST,,,25954,,4d22c387.6562,1,,2011-01-04 14:51:51 CST,,0,LOG,00000,"streaming replication successfully connected to primary",,,,,,,,,""

查看主节点进程:
postgres: wal sender process repuser1 172.16.3.39(18716) startup

恢复完后修改hot_standby = on,重启slave节点