1. 云图网首页
  2. 技术专区
  3. 数据库

PostgreSQL: Setting up streaming log replication (Hot Standby )

• 数据库

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

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

环境信息

PG版本: PostgreSQL 9.0beta3
OS版本: Red Hat Enterprise Linux Server release 5.5
硬件环境： WINDOWS XP上安装两台虚拟机
Master信息 IP: 192.168.1.25
Standby信息 IP: 192.168.1.26

安装 PostgreSQL

由于主库已经安装 PostgreSQL 软件 ，主库上安装PG的步骤这里就不介绍了

配置从库主机参数

3.1 设置 /etc/sysctl.conf,增加以下内容

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
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 生效

3.2 设置/etc/security/limits.conf 增加以下内容

1
2
3
4
5
6
7
8
 
* 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 ，增加以下内容

1
 
session required pam_limits.so

主库上创建超级用户

4.1 创建用户

1
2
3
4
5
 
CREATE USER repuser  
 SUPERUSER  
 LOGIN  
 CONNECTION LIMIT 2  
 ENCRYPTED PASSWORD 'repuser';

4.2 设置 master 库 pg_hba.conf

1
 
host replication repuser 192.168.1.26/16 md5

说明：超级用户 repuser 是用来从库上读取库主库(Master)的 WAL stream,并且在4。2中设置 权限，只允许主机 192.168.1.26(Standby 节点)以 md5 加密方式访问。

设置日志参数

设置postgresql.conf以下参数，记录连接信息 ( Both Master and Standby 库)

1
 
log_connections = on

说明：”log_connections” 参数用来记录数据库连接信息,打开这个开关，从而在接下来的CSV日志中能更好的观察Master库和 Standby 库情况。

设置主库 postgresql.conf

1
2
3
4
5
 
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.1 tart the backup (On Master )

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

7.2 COPY 数据文件

1
 
tar czvf pg_root.tar.gz pg_root --exclude=pg_root/pg_xlog

由于 $PGDATA/pg_xlog 不是必须的，这里排除了这个目录,节省时间。

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

1
 
scp pgdata.tar.gz pgb:/database

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

1
 
select pg_stop_backup(), current_timestamp;

说明：建议主库和从库配置信息一致，包括硬件信息，目录结构，主机配置等。

修改从库 postgresql.conf

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

设置从库 recovery.conf

1
2
3
4
 
recovery_target_timeline = 'latest'  
standby_mode = 'on' --标记PG为STANDBY SERVER  
primary_conninfo = 'host=192.168.1.25 port=1921 user=repuser password=repuser'  
trigger_file = '/tmp/postgresql.trigger.1921'

说明：关键参数“primary_conninfo (string)” ,这里配置了hostname,port,username ,password, 关于这个参数的更多解释可以参考官网.其中更多关于连接的参数可以配置，这里不说明了 ,详见 http://www.postgresql.org/docs/9.0/static/libpq-connect.html

删除从库文件,并创建 pg_xlog目录

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

开启从库

11.1 观察CSVLOG

1
2
3
4
5
 
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 进程

1
2
 
[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-接收进程

1
2
 
[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.1 主库上创建用户

1
2
3
 
postgres=# CREATE ROLE browser LOGIN ENCRYPTED PASSWORD 'browser'  
postgres-# nosuperuser noinherit nocreatedb nocreaterole CONNECTION LIMIT 200;  
CREATE ROLE

从库上验证

1
2
3
4
5
 
postgres=# /du  
 List of roles  
Role name | Attributes | Member of  
-----------+-----------------------------------+-----------  
browser | No inheritance +| {}

说明：果然，在从库上就立刻创建了新用户 ‘browser’

12.2 主库上创建表空间
主库上创建表空间目录

1
 
mkdir -p /database/pgdata/pg_tbs/tbs_browser

从库上也执行 mkdir -p /database/pgdata/pg_tbs/tbs_browser (On Sandby)

主库上创建表空间

1
2
 
postgres=# create tablespace tbs_browser owner skytf LOCATION '/database/pgdata/pg_tbs/tbs_browser';  
CREATE TABLESPACE

在从库上验证

1
2
3
4
5
6
7
8
 
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 主库上创建数据库

1
2
3
4
5
6
 
postgres=# CREATE DATABASE browser  
postgres-# WITH OWNER = skytf  
postgres-# TEMPLATE = template0  
postgres-# ENCODING = 'UTF8'  
postgres-# TABLESPACE = tbs_browser;  
CREATE DATABASE

从库上验证

1
2
3
4
5
6
7
8
9
10
11
12
 
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日志，从日志上看，基本没有延迟

1
2
3
4
 
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

说明：从库是以只读形式打开，只能执行读操作，不能写。

监控流复制

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
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)

总结

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

参考

原创文章，作者：奋斗，如若转载，请注明出处：https://blog.ytso.com/236357.html

赞 (0)
0
上一篇 2022年1月24日
下一篇 2022年1月24日

相关推荐

发表回复

登录后才能评论