PGSQL流复制

PG双机的搭建(Hot Standby )

PG: Setting up streaming log replication (Hot Standby )

    Postgresql9.0的一个主要新特性是可以实施流复制，这有点像ORACLE 里的DataGuard(Physial Standby)但是这种方式比Oracle的DataGuard更为安全，更为高效，因为从库同步主库是实时的，几乎没有时间差。
而Oracle的 DataGuard的从库接收并应用主库的日志的延迟，本人测试了下，大概有几分钟，具体延时决定于
主库的业务繁忙程度。   

下面是流复制实验的详细步骤

1 环境信息

  PG版本: PostgreSQL 9.3.2
  OS版本: CentOS Linux release 7.5.1804 (Core)
  硬件环境： WINDOWS 10上安装两台虚拟机     
  Master信息   IP: 172.16.1.11
  Standby信息  IP: 172.16.1.12

2 简介

主库使用的postgres数据库，采用编译安装的方式，从库也将使用编译安装的方式，不需要将从库的数据库进行初始化操作，假如已经初始化了，将data目录下的文件删除即可。

现在先从从库上进行配置

3 配置从库主机参数 (On standby)

3.1 设置 /etc/sysctl.conf,增加以下内容
 
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

sysctl -p 生效
	假如有报错信息，将报错的位置注释掉即可，我配置的时候net.ipv4.ip_conntrack_max=655360 报错，没有这个文件或目录，后来将他注释掉了，不影响使用。

3.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

3.3 设置 /etc/pam.d/login ，增加以下内容

session required pam_limits.so

4 在主库上创建创建超级用户( On Master )

4.1 创建用户

CREATE USER repuser  SUPERUSER LOGIN CONNECTION LIMIT 2 ENCRYPTED PASSWORD 'repuser';

4.2 设置 master 库 /data/pg_hba.conf

host   replication     repuser          192.168.1.26/16         md5
 
   说明：超级用户 repuser 是用来从库上读取库主库(Master)的 WAL stream,并且在4。2中设置权限，只允许主机 192.168.1.26(Standby 节点)以 md5 加密方式访问。注意：添加在 # IPv4 local connections:  下面

5 设置日志参数，记录连接信息 ( Both Master and Standby 库)

 该文件下 data/postgresql.conf
 
	log_connections = on
 
  说明："log_connections" 参数用来记录数据库连接信息,打开这个开关，从而在接下来的CSV日志中
能更好的观察Master库和 Standby 库情况。

6 设置库库 postgresql.conf ( On Master )

max_wal_senders = 1     --WAL STREAM 日志发送进程数
wal_level = hot_standby   --主库设置成 hot_standby ，从库才能以READ-ONLY模式打开
archive_mode = on
archive_command = 'cd .'
wal_keep_segments = 64
 
   说明，关键参数"max_wal_senders" 是指 wal 发送进程数, 我这里只有一台从库，所以设置为1，如果有多台
从库，则应该设置成从库个数，因为在Master库上，每台从库需要一个 WAL日志发送进程向从库发送WAL日志流。
一方面是这一参数官网的介绍。
 
max_wal_senders (integer)
Specifies the maximum number of concurrent connections from standby servers (i.e., the maximum number
of simultaneously running WAL sender processes). The default is zero. This parameter can only be set
at server start. wal_level must be set to archive or hot_standby to allow connections from standby servers.

7 主库全备

7.1 tart the backup (On Master )

select pg_start_backup('base backup for log streaming');

7.2 COPY 数据文件

cd /home/postgres/data/
tar czvf pg_data.tar.gz data --exclude=data/pg_xlog
  由于 $PGDATA/pg_xlog 不是必须的，这里排除了这个目录,节省时间。

7.3 将数据文件COPY到standby 主机并解压

scp pgdata.tar.gz 172.16.1.12:/home/postgres/data/

7.4 数据COPY完后,结束备份 Stop the backup (On Master)

select pg_stop_backup(), current_timestamp;
 
   说明：建议主库和从库配置信息一致，包括硬件信息，目录结构，主机配置等。

8 修改从库 postgresql.conf (On standby )

hot_standby = on     --从库上可以执行只读操作

9 设置从库 recovery.conf (On standby)

编译安装是没有这么文件的，在postgres的安装目录上，名称为share/recovery.conf.sample，将他复制到data目录下，修改名字为recovery.conf即可,注意需要将他修改为普通用户的属主属组
 
cp /opt/pgsql9.3.2/share/recovery.conf.sample /home/postgres/data/
 
recovery_target_timeline = 'latest'
standby_mode = 'on'  --标记PG为STANDBY SERVER
primary_conninfo = 'host=172.16.1.11 port=5432 user=repuser password=repuser'
trigger_file = '/tmp/postgresql.trigger.5432'
 
   说明：关键参数“primary_conninfo (string)” ,这里配置了hostname,port,username ,password,
关于这个参数的更多解释可以参考官网.其中更多关于连接的参数可以配置，这里不说明了
http://www.postgresql.org/docs/9.0/static/libpq-connect.html

10 删除从库文件,并创建 pg_xlog目录 (On standby )

$ rm -f $PGDATA/postmaster.pid
$ mkdir -p $PGDATA/pg_xlog

11 启从库，并观察 csvlog

11.1 观察CSVLOG

	ll /home/postgres/data/pg_log/
2011-01-08 17:22:49.757 CST,,,24243,,4d282ce9.5eb3,2,,2011-01-08 17:22:49 CST,,0,LOG,00000,"entering standby mode",,,,,,,,,""
2011-01-08 17:22:49.887 CST,,,24244,,4d282ce9.5eb4,1,,2011-01-08 17:22:49 CST,,0,LOG,00000,"streaming replication successfully connected to primary",,,,,,,,,""
2011-01-08 17:22:52.677 CST,,,24243,,4d282ce9.5eb3,3,,2011-01-08 17:22:49 CST,1/0,0,LOG,00000,"redo starts at 1/94000020",,,,,,,,,""
2011-01-08 17:22:52.696 CST,,,24243,,4d282ce9.5eb3,4,,2011-01-08 17:22:49 CST,1/0,0,LOG,00000,"consistent recovery state reached at 1/98000000",,,,,,,,,""
2011-01-08 17:22:52.805 CST,,,24241,,4d282ce8.5eb1,4,,2011-01-08 17:22:48 CST,,0,LOG,00000,"database system is ready to accept read only connections",,,,,,,,,""
    日志中 "streaming replication successfully connected to primary","database system is ready to accept read only connections"
    这些信息说明流复制已经成功，从库正准备接收主库的WAL-STREAM。

11.2 主库观察WAL-Sender 进程

[postgres@pg1 pg_root]$ ps -ef | grep post
postgres 27225 27166  0 17:22 ?        00:00:05 postgres: wal sender process repuser 192.168.1.26(59836) streaming 1/9801E000
 
  说明：将输出结果省略部分，可以看到 " wal sender process repuser"进程

11.3 在从库上观察 WAL-接收进程

[postgres@pgb pg_log]$ ps -ef | grep post
postgres 24244 24241  0 17:22 ?        00:00:04 postgres: wal receiver process   streaming 1/9801DF00
 
   说明：同样省略部分输出结果，可以看到“ wal receiver process ” 进程。

12 测试

12.1 主库上创建用户

postgres=# CREATE ROLE browser LOGIN  ENCRYPTED PASSWORD 'browser'
postgres-# nosuperuser noinherit nocreatedb nocreaterole  CONNECTION LIMIT 200;
CREATE ROLE
 
从库上验证
postgres=# \du
                       List of roles
 Role name |            Attributes             | Member of
-----------+-----------------------------------+-----------
 browser   | No inheritance                   +| {}
 
    说明：果然，在从库上就立刻创建了新用户 'browser'

12.2 主库上创建表空间(On Master)

mkdir -p /database/pgdata/pg_tbs/tbs_browser
从库上也执行 mkdir -p /database/pgdata/pg_tbs/tbs_browser (On  Sandby)
postgres=# create tablespace tbs_browser owner skytf LOCATION '/database/pgdata/pg_tbs/tbs_browser';
CREATE TABLESPACE
 
--在从库上验证
postgres=# \db
                     List of tablespaces
    Name     |  Owner   |              Location               
-------------+----------+-------------------------------------
 pg_default  | postgres |
 pg_global   | postgres |
 tbs_browser | skytf    | /database/pgdata/pg_tbs/tbs_browser
 tbs_mydb    | skytf    | /database/pgdata/pg_tbs/tbs_mydb
 
   表空间"tbs_browser" 也立刻创建过来了

12.3 主库上创建数据库

postgres=# CREATE DATABASE browser
postgres-# WITH  OWNER = skytf
postgres-#       TEMPLATE = template0
postgres-#       ENCODING = 'UTF8'
postgres-#       TABLESPACE = tbs_browser;
CREATE DATABASE
 
--从库上验证
postgres=# \l
                              List of databases
   Name    |  Owner   | Encoding | Collation | Ctype |   Access privileges   
-----------+----------+----------+-----------+-------+-----------------------
 browser   | skytf    | UTF8     | C         | C     |
 mydb      | skytf    | UTF8     | C         | C     |
 postgres  | postgres | UTF8     | C         | C     | =Tc/postgres         +
           |          |          |           |       | postgres=CTc/postgres
 template0 | postgres | UTF8     | C         | C     | =c/postgres          +
           |          |          |           |       | postgres=CTc/postgres
 template1 | postgres | UTF8     | C         | C     | =c/postgres          +
           |          |          |           |       | postgres=CTc/postgres
 
       从库上数据库 "browser" 也立刻有了，几乎没有延时。

12.4 同时观察CSV日志，从日志上看，基本没有延迟

2011-01-08 17:28:59.335 CST,"postgres","postgres",24274,"[local]",4d282e5b.5ed2,2,"authentication",2011-01-08 17:28:59 CST,2/3,0,LOG,00000,"connection authorized: user=postgres database=postgres",,,,,,,,,""

12.5 在从库上建表

mydb=> create table table3(id integer);
ERROR:  cannot execute CREATE TABLE in a read-only transaction
   说明：从库是以只读形式打开，只能执行读操作，不能写。

13 监控streaming

CREATE OR REPLACE VIEW pg_stat_replication AS
    SELECT
            S.procpid,
            S.usesysid,
            U.rolname AS usename,
            S.application_name,
            S.client_addr,
            S.client_port,
            S.backend_start
    FROM pg_stat_get_activity(NULL) AS S, pg_authid U
    WHERE S.usesysid = U.oid AND S.datid = 0;
 
postgres=# select * from pg_stat_replication ;
procpid | usesysid | usename | application_name | client_addr  | client_port |         backend_start         
---------+----------+---------+------------------+--------------+-------------+-------------------------------
   27225 |    64949 | repuser |                  | 192.168.1.26 |       59836 | 2011-01-08 17:22:05.480584+08
(1 row)

14 总结

以上就是搭建 streaming(又称Hot Standby)的详细过程，这是一个令人兴奋的学习过程，
因为PG的HOT STANDBY 提供的数据及时性和可靠性丝毫不比ORACLE的DataGuard逊色，相反，
本人还觉得比在这方面比Oracle更给力，谢谢开源的人们提供这么优秀的数据库。

--参考文档
http://www.postgresql.org/docs/9.0/static/high-availability.html
PDF电子书: PostgreSQL-Admin-Cookbook.PDF

15 报错

1.登录从库数据库时无法登录，
[postgres@localhost bin]$ ./psql -U postgres -p 5432
psql: FATAL:  the database system is starting up
 
解决方法：
	修改从库的postgresql.conf文件中的
	hot_standby为on，模式是注释掉的。
	改完之后重启数据库即可，如果无法重启，看下是不是有postgres的进行在运行，如果有，杀掉即可。