PostgreSQL 同步复制（1master+2standby）
OS: Red Hat Enterprise Linux Server release 6.5 (Santiago)
PostgreSQL: postgresql-9.4.5.tar.bz2
master: 192.168.0.106
slave1: 192.168.0.108
slave2: 192.168.0.112
useradd -U postgres -p postgres
主备节点时钟同步:
/usr/sbin/ntpdate asia.pool.ntp.org && /sbin/hwclock –systohc
给机器分别命名：node1,node2,node3
第一台机器命名为node1
2.2.1 修改/etc/sysconfig/network中的hostname
vi /etc/sysconfig/network
HOSTNAME=node1 #修改localhost.localdomain为node1
2.2.2 修改/etc/hosts文件
vi /etc/hosts
127.0.0.1 node1 localhost #修改localhost.localdomain为node1
shutdown -r now #最后，重启服务器即可
第二台机器命名为node2
2.2.3 修改/etc/sysconfig/network中的hostname
vi /etc/sysconfig/network
HOSTNAME=node2 #修改localhost.localdomain为node2
2.2.4 修改/etc/hosts文件
vi /etc/hosts
127.0.0.1 node2 localhost #修改localhost.localdomain为node2
shutdown -r now #最后，重启服务器即可。
第三台机器命名为node2
2.2.3 修改/etc/sysconfig/network中的hostname
vi /etc/sysconfig/network
HOSTNAME=node2 #修改localhost.localdomain为node3
2.2.4 修改/etc/hosts文件
vi /etc/hosts
127.0.0.1 node2 localhost #修改localhost.localdomain为node3
shutdown -r now #最后，重启服务器即可。
OS资源调整
时区调整: (如果已经调好同步不需要做)
vi /etc/sysconfig/clock
ZONE="Asia/Shanghai"
UTC=false
ARC=false
vi /etc/sysconfig/i18n
LANG="en_US.UTF-8"
vi /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
使文件修改生效
sysctl –p
vi /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
vi /etc/sysconfig/selinux
SELINUX=disabled
setenforce 0
三台机器安装PG
yum -y install lrzsz sysstat e4fsprogs ntp readline-devel zlib zlib-devel openssl openssl-devel pam-devel libxml2-devel libxslt-devel python-devel tcl-devel gcc make flex bison
tar jxvf postgresql-9.4.5.tar.bz2
cd postgresql-9.4.5
./configure --prefix=/usr/local/pg945/
gmake world
gmake install-world
初始化主库master node1
[postgres@node1 pg945]$ mkdir data
[postgres@node1 data]$ chmod 0700 /usr/local/pg945/data/
[postgres@node1 pg945]$ cd ../bin/
[postgres@node1 bin]$ ./initdb -E UTF8 --locale=C -D ../data/ -U postgres –W postgres
配置主库
postgresql.conf
listen_addresses = '*'
port = 5432
max_connections = 100
# WRITE AHEAD LOG
wal_level = hot_standby
fsync = on
synchronous_commit = on
full_page_writes = on
wal_log_hints = on
# - Archiving –
archive_mode = on
archive_command = 'cd .'
max_wal_senders = 10
# - Master Server -
synchronous_standby_names = 'node2,node3'
# - Standby Servers –
hot_standby = on
recovery文件
[postgres@node1 postgresql]$ pwd
/usr/local/pg945/share/postgresql
[postgres@node1 postgresql]$ ls
conversion_create.sql pg_hba.conf.sample postgres.bki postgres.shdescription snowball_create.sql timezone
extension pg_ident.conf.sample postgres.description psqlrc.sample sql_features.txt timezonesets
information_schema.sql pg_service.conf.sample postgresql.conf.sample recovery.conf.sample system_views.sql tsearch_data
[postgres@node1 postgresql]$ cp recovery.conf.sample /usr/local/pg945/data/recovery.done
[postgres@node1 data]$ vim recovery.done
recovery_target_timeline = 'latest'
standby_mode = on
primary_conninfo = 'host=192.168.0.108 port=5432 user=postgres password=postgres application_name=node1'
pg_hba.conf文件
[postgres@node1 data]$ vim pg_hba.conf
host all all 192.168.0.106/32 trust
host all all 192.168.0.108/32 trust
host all all 192.168.0.112/32 trust
host replication postgres 192.168.0.108/32 trust
host replication postgres 192.168.0.112/32 trust
host replication postgres 192.168.0.106/32 trust
启动主库
pg_ctl –D ../data start
对master 做基础备份pg_basebackup 到standby1 standby2
standby1
[postgres@node2 pg945]$ mkdir data
[postgres@node2 data]$ chmod 0700 /usr/local/pg945/data/
[postgres@node2 bin]$ ./pg_basebackup -h 192.168.0.106 -P -Fp -Xs -v -p 5432 -U postgres -D /usr/local/pg945/data/
transaction log start point: 0/5000028 on timeline 1
pg_basebackup: starting background WAL receiver
20952/20952 kB (100%), 1/1 tablespace
transaction log end point: 0/50000F0
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: base backup completed
[postgres@node2 data]$ mv recovery.done recovery.conf
recovery_target_timeline = 'latest'
standby_mode = on
primary_conninfo = 'host=192.168.0.106 port=5432 user=postgres password=postgres application_name=node2'
[postgres@node2 bin]$ ./pg_ctl -D ../data/ start
server starting
[postgres@node2 bin]$ LOG: database system was interrupted; last known up at 2015-12-28 14:40:16 CST
LOG: entering standby mode
LOG: redo starts at 0/A000060
LOG: consistent recovery state reached at 0/A000128
LOG: database system is ready to accept read only connections
LOG: started streaming WAL from primary at 0/B000000 on timeline 1
standby2
[postgres@node3 pg945]$ mkdir data
[postgres@node3 data]$ chmod 0700 /usr/local/pg945/data/
[postgres@node3 bin]$ ./pg_basebackup -h 192.168.0.106 -P -Fp -Xs -v -p 5432 -U postgres -D /usr/local/pg945/data/
transaction log start point: 0/5000028 on timeline 1
pg_basebackup: starting background WAL receiver
20952/20952 kB (100%), 1/1 tablespace
transaction log end point: 0/50000F0
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: base backup completed
[postgres@node3 data]$ mv recovery.done recovery.conf
recovery_target_timeline = 'latest'
standby_mode = on
primary_conninfo = 'host=192.168.0.106 port=5432 user=postgres password=postgres application_name=node3'
[postgres@node3 bin]$ ./pg_ctl -D ../data/ start
server starting
[postgres@node3 bin]$ LOG: database system was interrupted; last known up at 2015-12-28 14:41:01 CST
LOG: entering standby mode
LOG: redo starts at 0/C000060
LOG: consistent recovery state reached at 0/C000128
LOG: database system is ready to accept read only connections
LOG: started streaming WAL from primary at 0/D000000 on timeline 1
测试
master
[postgres@node1 bin]$ ./psql
psql (9.4.5)
Type "help" for help.
postgres=# LOG: standby "node2" is now the synchronous standby with priority 1
postgres=# \d
No relations found.
postgres=# create database test_db;
CREATE DATABASE
postgres=# \c test_db
You are now connected to database "test_db" as user "postgres".
test_db=# create table test_tb(id int);
CREATE TABLE
test_db=# insert into test_tb values (1),(2),(3);
INSERT 0 3
test_db=# select * from test_tb ;
id
----
1
2
3
(3 rows)
standby1
[postgres@node2 bin]$ ./psql
psql (9.4.5)
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
test_db | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(4 rows)
postgres=# \c test_db
You are now connected to database "test_db" as user "postgres".
test_db=# \d
List of relations
Schema | Name | Type | Owner
--------+---------+-------+----------
public | test_tb | table | postgres
(1 row)
test_db=# select * from test_tb ;
id
----
1
2
3
(3 rows)
standby2
[postgres@node3 bin]$ ./psql
psql (9.4.5)
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
test_db | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(4 rows)
postgres=# \c test_db
You are now connected to database "test_db" as user "postgres".
test_db=# \d
List of relations
Schema | Name | Type | Owner
--------+---------+-------+----------
public | test_tb | table | postgres
(1 row)
test_db=# select * from test_tb ;
id
----
1
2
3
(3 rows)
异步复制：
主库：postgresql.conf(由于是复制的主备的postgresql.conf一样)
synchronous_commit= off
synchronous_standby_name= ' '为空
备库：recovery.conf
primary_conninfo连接串中没有 application_name参数
