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 ;

----

(3 rows)

standby1

[postgres@node2 bin]$ ./psql

psql (9.4.5)

Type "help" for help.

postgres=# l

List of databases

-----------+----------+----------+-------------+-------------+-----------------------

(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 ;

----

(3 rows)

standby2

[postgres@node3 bin]$ ./psql

psql (9.4.5)

Type "help" for help.

postgres=# l

List of databases

-----------+----------+----------+-------------+-------------+-----------------------

(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 ;

----

(3 rows)

异步复制：

主库：postgresql.conf(由于是复制的主备的postgresql.conf一样)

synchronous_commit= off

synchronous_standby_name= ' '为空

备库：recovery.conf

primary_conninfo连接串中没有 application_name参数