Postgresql实现主从复制,读写分离

举报
小米粒-biubiubiu 发表于 2020/12/03 01:28:06 2020/12/03
【摘要】 前言 简单记录一下postgresql主从的实现方式之一——基于Standby的异步流复制,这是PostgreSQL9.x版本(2010.9)之后提供的一个很nice的功能,类似的功能在Oracle中是11g之后才提供的active dataguard和SQL Server 2012版本之后才提供的日志传送,此处再次为pg鼓掌,确实是一个很棒的开源数据库。废话不多说,本篇b...

前言

简单记录一下postgresql主从的实现方式之一——基于Standby的异步流复制,这是PostgreSQL9.x版本(2010.9)之后提供的一个很nice的功能,类似的功能在Oracle中是11g之后才提供的active dataguard和SQL Server 2012版本之后才提供的日志传送,此处再次为pg鼓掌,确实是一个很棒的开源数据库。废话不多说,本篇blog就详细记录一下在pg9.5中实现Hot Standby异步流复制的完整配置过程和注意事项。

Standby数据库原理

简单介绍一些基础概念与原理,首先我们做主从同步的目的就是实现db服务的高可用性,通常是一台主数据库提供读写,然后把数据同步到另一台从库,然后从库不断apply从主库接收到的数据,从库不提供写服务,只提供读服务。在postgresql中提供读写全功能的服务器称为primary database或master database,在接收主库同步数据的同时又能提供读服务的从库服务器称为hot standby server。

PostgreSQL在数据目录下的pg_xlog子目录中维护了一个WAL日志文件,该文件用于记录数据库文件的每次改变,这种日志文件机制提供了一种数据库热备份的方案,即:在把数据库使用文件系统的方式备份出来的同时也把相应的WAL日志进行备份,即使备份出来的数据块不一致,也可以重放WAL日志把备份的内容推到一致状态。这也就是基于时间点的备份(Point-in-Time Recovery),简称PITR。而把WAL日志传送到另一台服务器有两种方式,分别是:

  1. WAL日志归档(base-file)
  2. 流复制(streaming replication)

第一种是写完一个WAL日志后,才把WAL日志文件拷贝到standby数据库中,简言之就是通过cp命令实现远程备份,这样通常备库会落后主库一个WAL日志文件。而第二种流复制是postgresql9.x之后才提供的新的传递WAL日志的方法,它的好处是只要master库一产生日志,就会马上传递到standby库,同第一种相比有更低的同步延迟,所以我们肯定也会选择流复制的方式。

在实际操作之前还有一点需要说明就是standby的搭建中最关键的一步——在standby中生成master的基础备份。postgresql9.1之后提供了一个很方便的工具—— pg_basebackup,关于它的详细介绍和参数说明可以在官网中查看(pg_basebackup tool),下面在搭建过程中再做相关具体说明,关于一些基础概念和原理先介绍到这里。

详细配置

下面开始实战,首先准备两台服务器,我这里开了2个虚拟机做测试,分别是:

  1. 主库(master) centos-release-7-3.1511 192.168.42.71 postgresql 9.5
  2. 从库(standby) centos-release-7-3.1511 192.168.42.70 postgresql 9.5

一、主库(master)安装 并 配置postgresql 

(1)安装postgresql

  1.添加RPM
   $ sudo yum install https://download.postgresql.org/pub/repos/yum/9.5/redhat/rhel-7-x86_64/pgdg-centos95-9.5-2.noarch.rpm
   # 2.安装PostgreSQL 9.5
   $ sudo yum install postgresql95-server postgresql95-contrib
   # 3.初始化数据库(切记:从库不需要初始化数据库)
   $ sudo /usr/pgsql-9.5/bin/postgresql95-setup initdb
   # 4.设置开机自启动
   $ sudo systemctl enable postgresql-9.5.service
   #5.启动服务
   $ sudo systemctl start postgresql-9.5.service

#6.停止服务

$sudo systemctl stop postgresql-9.5.service


   安装完成默认会做三件事:

   1. 创建 `postgres` 的 Linux 用户;
   2. 创建 `postgres` 不带密码的默认数据库管理员账户;
   3. 创建 `postgres` 系统数据库。

(2)修改配置文件

cd  /var/lib/pgsql/9.5/data

vim pg_hba.conf 增加以下配置:(切记:必须要配置为从库的ip/32)

 host all all 0.0.0.0 0.0.0.0 md5

host replication postgres 192.168.42.70/32 md5      #这句话的意思允许从数据库连接主数据库去拖wal日志数据

vim postgresql.conf


  
  1. Listen_adresses = '*'
  2. wal_level = hot_standby #主从设置为热血模式,流复制必选
  3. max_wal_senders=2 #流复制允许连接进程
  4. wal_keep_segments =64
  5. max_connections = 1000 默认参数,非主从配置相关参数,表示到数据库的连接数

第一个不用说了,wal_level表示启动搭建Hot Standby,max_wal_senders则需要设置为一个大于0的数,它表示主库最多可以有多少个并发的standby数据库,而最后一个wal_keep_segments也应当设置为一个尽量大的值,以防止主库生成WAL日志太快,日志还没有来得及传送到standby就被覆盖,但是需要考虑磁盘空间允许,一个WAL日志文件的大小是16M: 
这里写图片描述

如上图,一个WAL日志文件是16M,如果wal_keep_segments设置为64,也就是说将为standby库保留64个WAL日志文件,那么就会占用16*64=1GB的磁盘空间,所以需要综合考虑,在磁盘空间允许的情况下设置大一些,就会减少standby重新搭建的风险。接下来还需要在主库创建一个超级用户来专门负责让standby连接去拖WAL日志:

(3)启动 主 数据库

$ sudo systemctl start postgresql-9.5.service

二、从库(standby)安装 并 配置postgresql 

(1)从库安装完成后,不初始化,若已经初始化,删除其/var/lib/pgsql/9.5/data目录

执行,以下命令将主数据库的/var/lib/pgsql/9.5/data的目录同步过来。

pg_basebackup -h 192.168.42.71 -U postgres -F p -x -P -R -D /var/lib/pgsql/9.5/data/ -l postgresbackup20190129

下面简单做一下参数说明(可以通过pg_basebackup --help进行查看),-h指定连接的数据库的主机名或IP地址,这里就是主库的ip。-U指定连接的用户名,此处是我们刚才创建的专门负责流复制的repl用户。-F指定了输出的格式,支持p(原样输出)或者t(tar格式输出)。-x表示备份开始后,启动另一个流复制连接从主库接收WAL日志。-P表示允许在备份过程中实时的打印备份的进度。-R表示会在备份结束后自动生成recovery.conf文件,这样也就避免了手动创建。-D指定把备份写到哪个目录,这里尤其要注意一点就是做基础备份之前从库的数据目录(/usr/local/postgresql/data)目录需要手动清空。-l表示指定一个备份的标识,运行命令后看到如下进度提示就说明生成基础备份成功: 
这里写图片描述

如上图,由于我们在pg_hba.conf中指定的md5认证方式,所以需要输入主数据库postgres用户的密码postgres。

(2)修改配置文件

vi /postgres/data/postgresql.conf

#在基础备份时,初始化文件是从主库复制来的,所以配置文件一致,注释掉

wal_level,

max_wal_senders

wal_keep_segments等参数

打开如下参数:


  
  1. hot_standby = on #在备份的同时允许查询
  2. max_standby_streaming_delay = 30s #可选,流复制最大延迟
  3. wal_receiver_status_interval = 10s #可选,从向主报告状态的最大间隔时间
  4. hot_standby_feedback = on #可选,查询冲突时向主反馈
  5. max_connections = 1000 #默认参数,非主从配置相关参数,表示到数据库的连接数,一般从库做主要的读服务时,设置值需要高于主库

(3)创建恢复文件recovery.conf(如果我们在执行pg_basebackup的时候指定了-R 参数,可以忽略此步骤 )

recovery.conf    #在做基础备份时,也可通过-R参数在备份结束后自动生产一个recovery.conf文件

standby_mode = on  #指明从库身份

primary_conninfo = 'host=10.0.120.150 port=5432 user=repl password=repl1234'      #连接到主库信息

recovery_target_timeline = 'latest'     #同步到最新数据

#trigger_file = ‘/postgres/data/trigger_activestandby’

指定触发文件,文件存在时,将触发从库提升为主库,前提是必须设置”standby_mode = on”;如果不设置此参数,也可采用”pg_ctl promote“触发从库切换成主库

(4)启动从数据库,可能会报权限不正确的错误

[root@scoder19 9.3]# service postgresql-9.3 start
Starting postgresql-9.3 service: [FAILED]
日志文件(/var/lib/pgsql/9.3/pgstartup.log):报错如下:

原因是应该是 文件夹 “/var/lib/pgsql/9.3/data”权限错误, 权限应该是 rwx (0700)。
解决方法:
切换到 具有root权限的用户,
先把文件夹 “/var/lib/pgsql/9.5/data” 的用户所属组,给postgres 用户:
进入/var/lib/pgsql/9.5目录
cd /var/lib/pgsql/9.5
chown -R postgres:postgres data
把data目前的所有文件及子目录文件权限改成: rwx (0700)
chmod -R 0700 data
重启PostgreSQL 数据库,问题解决。

三 、验证主从是否配置成功

(1)查看主库sender进程

(2)查看从库receiver进程

 (3)向主库中创建表,并插入数据,查看从库是否同步过去 

su postgres

psql

select * from pg_stat_replication;

create table test(id int primary key,name vatchar(20),salary real);

insert into test values(10,'i love you',10000.00);

insert into test values(2,'li si',12000.00);


从库只读,不可插入数据,修改,删除数据

 到此主从复制建立完成。

四  、主从切换(未完待续)

(1)通过自带的函数,是备机则是t
postgres=# select pg_is_in_recovery();
pg_is_in_recovery
——————-

 

 

 


 

 

 

 

文章来源: blog.csdn.net,作者:血煞风雨城2018,版权归原作者所有,如需转载,请联系作者。

原文链接:blog.csdn.net/qq_31905135/article/details/86689735

【版权声明】本文为华为云社区用户转载文章,如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

举报
请填写举报理由
0/200