postgreSQL主从复制

一、简介

postgres在9.0之后引入了主从的流复制机制，所谓流复制，就是从服务器通过tcp流从主服务器中同步相应的数据。这样当主服务器数据丢失时从服务器中仍有备份。
与基于文件日志传送相比，流复制允许保持从服务器更新。 从服务器连接主服务器，其产生的流WAL记录到从服务器， 而不需要等待主服务器写完WAL文件。
PostgreSQL流复制默认是异步的。在主服务器上提交事务和从服务器上变化可见之间有一个小的延迟，这个延迟远小于基于文件日志传送，通常1秒能完成。如果主服务器突然崩溃，可能会有少量数据丢失。
同步复制必须等主服务器和从服务器都写完WAL后才能提交事务。这样在一定程度上会增加事务的响应时间。
注意：本次实验是基于docker完成的

二、postgresql安装

docker pull postgresql:9.4
docker images
REPOSITORY                                               TAG                 IMAGE ID            CREATED             SIZE
docker.io/postgres                                       9.4                 36726735dc3c        2 weeks ago         206 MB

docker run -it --name postgresql postgres:9.4 bash
su postgres
cd /usr/lib/postgresql/9.4/bin

存在/var/lib/postgresql/data目录，初始化数据库

./initdb -D /var/lib/postgresql/data
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
fixing permissions on existing directory /var/lib/postgresql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default timezone ... Etc/UTC
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
creating template1 database in /var/lib/postgresql/data/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating collations ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
loading PL/pgSQL server-side language ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

        ./postgres -D /var/lib/postgresql/data
or
        ./pg_ctl -D /var/lib/postgresql/data -l logfile start

到这里pg数据库就安装完成了
开启数据库，如果不需要日志可以不用加logfile

./pg_ctl start -D /var/lib/postgresql/data

同样启动一个备数据库

docker run -it --name postgresql2 postgres:9.4 bash

步骤与上面相同

三、主服务器的操作

主服务器为172.18.0.4
先创建一个新目录，用来归档日志，我这里其实没有归档日志，按需所求。

mkdir /opt/pgsql/pg_archive

1.首先需要创建一个数据库用户进行主从同步。创建用户replica，并赋予登录和复制的权限。

postgres# CREATE ROLE replica login replication encrypted password 'replica'

2.修改pg_hba.conf，允许replica用户来同步。
在pg_hba.conf里增加两行：

host all all 172.18.0.5/32 trust #允许0.5连接到主服务器
host replication replica 172.18.0.5/32 md5 #允许0.5使用replica用户来复制

这样，就设置了replica这个用户可以从172.18.0.4进行流复制请求。
*注：第二个字段必须要填replication
4.修改postgresql.conf

listen_addresses = '*' # 监听所有IP
archive_mode = on # 允许归档
archive_command = 'cp %p /opt/pgsql/pg_archive/%f' # 用该命令来归档logfile segment，按需所求。
wal_level = hot_standby
max_wal_senders = 32 # 这个设置了可以最多有几个流复制连接，差不多有几个从，就设置几个，设置稍大些较好。
wal_keep_segments = 256 ＃ 设置流复制保留的最多的xlog数目，一个段为16MB，尽量设置大的值，防止主库生成日志太快还没来得及发送给hot_standy就被覆盖。
wal_sender_timeout = 60s ＃ 设置流复制主机发送数据的超时时间
max_connections = 100 # 这个设置要注意下，从库的max_connections必须要大于主库的

配置完两个文件后重启服务器。

pg_ctl stop -D /var/lib/postgresql/data
pg_ctl start -D /var/lib/postgresql/data

3.测试0.5能否连接0.4数据库。在0.5上运行如下命令：

psql -h 172.18.0.4 -U postgres

看看是否能进入数据库。若可以，则正常。

四、从服务器的操作

1.从主节点拷贝数据到从节点

su - postgres
rm -rf /opt/pgsql/data/* #先将data目录下的数据都清空
pg_basebackup -h 172.18.0.4 -U replica -D /var/lib/postgresql/data -X stream -P # 从0.4拷贝数据到0.5（基础备份）
mkdir /opt/pgsql/pg_archive

2.配置recovery.conf
复制/usr/share/postgresql/9.4/recovery.conf.sample 到 /var/lib/postgresql/data/recovery.conf

cp /usr/share/postgresql/9.4/recovery.conf.sample /var/lib/postgresql/data/recovery.conf

修改recovery.conf

standby_mode = on # 说明该节点是从服务器
primary_conninfo = 'host=172.18.0.4 port=5432 user=replica password=replica' # 主服务器的信息以及连接的用户
recovery_target_timeline = 'latest'

3.配置postgresql.conf

wal_level = hot_standby
max_connections = 1000 ＃ 一般查多于写的应用从库的最大连接数要比较大
hot_standby = on ＃ 说明这台机器不仅仅是用于数据归档，也用于数据查询
max_standby_streaming_delay = 30s # 数据流备份的最大延迟时间
wal_receiver_status_interval = 10s # 多久向主报告一次从的状态，当然从每次数据复制都会向主报告状态，这里只是设置最长的间隔时间
hot_standby_feedback = on # 如果有错误的数据复制，是否向主进行反馈

配置完后重启从服务器

pg_ctl stop -D /var/lib/postgresql/data
pg_ctl start -D /var/lib/postgresql/data

五、验证是否部署成功

在主节点数据库里执行：

select client_addr,sync_state from pg_stat_replication;

结果如下：

postgres=# select client_addr,sync_state from pg_stat_replication;
 client_addr | sync_state 
-------------+------------
 172.18.0.5  | async
(1 row)

postgres=# 

说明0.5是从服务器，在接收流，而且是异步流复制。
此外，还可以分别在主、从节点上运行 ps aux | grep postgres 来查看进程：
主服务器（0.4）上：

ps aux | grep postgres
root 210 0.0 0.0 48508 1548 ? S 06:34 0:00 su postgres
postgres 211 0.0 0.1 19864 2256 ? S 06:34 0:00 bash
postgres 250 0.0 0.9 273940 17632 ? S 06:41 0:00 /usr/lib/postgresql/9.4/bin/postgres -D /var/lib/postgresql/data
postgres 252 0.0 0.2 274044 3800 ? Ss 06:41 0:00 postgres: checkpointer process
postgres 253 0.0 0.1 274072 3216 ? Ss 06:41 0:00 postgres: writer process
postgres 254 0.0 0.3 273940 6108 ? Ss 06:41 0:00 postgres: wal writer process
postgres 255 0.0 0.1 274348 2656 ? Ss 06:41 0:00 postgres: autovacuum launcher process
postgres 256 0.0 0.0 129220 1836 ? Ss 06:41 0:00 postgres: stats collector process
postgres 276 0.0 0.1 274480 3164 ? Ss 06:57 0:00 postgres: wal sender process replica 172.18.0.5(42834) streaming 0/3019C90
postgres 391 0.0 0.0 38296 1752 ? R+ 07:36 0:00 ps aux
postgres 392 0.0 0.0 12772 692 ? S+ 07:36 0:00 grep postgres

可以看到有一个 wal sender 进程。

从服务器（94）上：

ps aux | grep postgres
root 394 0.0 0.0 48508 1548 ? S 06:42 0:00 su postgres
postgres 395 0.0 0.1 19884 2320 ? S 06:42 0:00 bash
postgres 488 0.0 2.3 314268 45052 ? S 06:57 0:00 /usr/lib/postgresql/9.4/bin/postgres -D /var/lib/postgresql/data
postgres 489 0.0 0.2 314452 4904 ? Ss 06:57 0:00 postgres: startup process recovering 000000010000000000000003
postgres 490 0.0 0.1 314388 3524 ? Ss 06:57 0:00 postgres: checkpointer process
postgres 491 0.0 0.1 314268 2956 ? Ss 06:57 0:00 postgres: writer process
postgres 492 0.0 0.0 129220 1848 ? Ss 06:57 0:00 postgres: stats collector process
postgres 493 0.0 0.2 319036 4384 ? Ss 06:57 0:01 postgres: wal receiver process streaming 0/3019C90
postgres 508 0.0 0.0 38296 1756 ? R+ 07:37 0:00 ps aux
postgres 509 0.0 0.0 12772 700 ? S+ 07:37 0:00 grep postgres
可以看到有一个 wal receiver 进程。
至此，PostgreSQL主从流复制安装部署完成。
在主服务器上插入数据或删除数据，在从服务器上能看到相应的变化。从服务器上只能查询，不能插入或删除。

主上：

postgres=# \c test;
You are now connected to database "test" as user "postgres".
test=# create table company(
test(# id int primary KEY NOT NULL,
test(# name TEXT NOT NULL,
test(# age INT NOT NULL,
test(# address CHAR(50),
test(# salary REAL,
test(# join_date DATE
test(# );
CREATE TABLE
test=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY,JOIN_DATE) VALUES (1, 'Paul', 32, 'California', 20000.00,'2001-07-13');
INSERT 0 1
test=#
test=#
test=# select * from company;
id | name | age | address | salary | join_date
----+------+-----+----------------------------------------------------+--------+------------
1 | Paul | 32 | California | 20000 | 2001-07-13
(1 row)

从上：

postgres=# \l
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   
-----------+----------+----------+------------+------------+-----------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 test      | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
(4 rows)

postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# select * from company
test-# ;
 id | name | age |                      address                       | salary | join_date  
----+------+-----+----------------------------------------------------+--------+------------
  1 | Paul |  32 | California                                         |  20000 | 2001-07-13
(1 row)
s

完成！