记得在2年前写过一篇PostgreSQL的文章,当时处于兴趣,本来想在工作中接一下PG的业务,***因为各种各样的原因就搁置了。
今天整理了下PostgreSQL的一些基础内容,参考的书是唐成老师的那本《PostgreSQL修炼之道》,有了Oracle和MySQL的基础,看起来会比从零开始要容易一些,总体的感觉,PG功能确实很多很全,功能上像Oracle看齐,技术风格和MySQL很像,在做一些总结的时候,不停的在两个数据库之间来回切换。
关于主备环境的搭建,我使用的基于流复制的方式搭建,这是在PG 9.0之后提供的对WAL传递日志的方法,是基于物理复制,在9.4开始有了逻辑解码,而细粒度的逻辑复制在PG 10中会有较大的改进。
1.安装部署数据库软件
安装部署还是得啰嗦几句,使用的是9.5版本的源码安装,源码包很小,就几十兆。
1)解压
- tar -zxvf postgresql-9.5.0.tar.gz
2)切换到解压目录,尝试编译准备
- cd postgresql-9.5.0
- ./configure -prefix /usr/local/pgsql
这个过程很可能有问题,比如下面的错误。
- configure: error: zlib library not found
- If you have zlib already installed, see config.log for details on the
- failure. It is possible the compiler isn't looking in the proper directory.
- Use --without-zlib to disable zlib support.
类似的错误还有readline,实际的情况zlib包和readline包都是有的。
这里需要注意一点:
redhat 系列下这个软件包叫 readline-devel ubuntu 下叫readline-dev 细分又分为libreadline5-dev 和 libreadline6-dev
所以我们需要安装的是readline-devel和zlib-devel的包即可搞定,而不要只是怀疑,然后把--without-zlib选项给启用了。
接下来的步骤就简单了。
3)开始编译安装
这两个过程耗时相对会多一些,大概几分钟吧,比MySQL的源码编译要快很多。
- make
- make install
4)创建用户和组
- useradd postgres
- mkdir -p /data/pgsql9.5
- chown -R postgres:postgres /data/pgsql9.5
- su - postgres
5)初始化部署
- /usr/local/pgsql/bin/initdb -D /data/pgsql9.5
至此,数据库软件部署就搞定了,在这里我们只做了功能,还没有涉及性能层面的调整和优化。
2.配置主库
使用的环境是两台服务器
192.168.179.128 主库
192.168.253.134 备库
1)创建一个复制角色
CREATE ROLE replica login replication encrypted password 'replica';
2)配置访问权限文件gp_hba.conf
添加一条记录,使得备库可以访问,修改后需要重启
- host replication replica 192.168.253.134/24 trust
因为是跨网段,我额外补充了一条网关的记录
- host replication replica 192.168.179.1/24 trust
3)修改参数配置文件postgresql.conf
修改如下的几个参数设置,端口还是保留默认的5432
- listen_addresses = '*"
- port = 5432
- wal_level = hot_standby
- max_wal_senders = 2
- wal_keep_segments = 32
- wal_sender_timeout =60s
- max_connections =100
这些步骤完成后,切记要重启一下PG使得配置生效
4)重启PG
- $ /usr/local/pgsql/bin/pg_ctl -D /data/pgsql9.5 -l logfile restart
3.配置备库
备库需要同样的步骤来部署数据库软件,参考***部分即可。
这个时候备库上还没有初始化数据,我们模拟客户端的方式来访问,可能会有如下的错误。
- $ psql -Ureplica -h192.168.179.128 -p5432 --password
- Password for user replica:
- psql: FATAL: no pg_hba.conf entry for host "192.168.179.1", user "replica", database "replica"
1)使用pg_basebackup还原数据
先不必担心,我们可以使用pg_basebackup或者命令行的方式来做备份恢复
- $ pg_basebackup -F p --progress -D /data/pgsql9.5 -h 192.168.179.128 -p 5432 -U replica --password
- Password:
- 22484/22484 kB (100%), 1/1 tablespace
- NOTICE: WAL archiving is not enabled; you must ensure that all required WAL segments are copied through other means to complete the backup
2)配置恢复配置recovery.conf
这个步骤是关键,和Oracle里面的归档参数或者和MySQL里的change master的设置类似。
recovery.conf文件可以从模板里拿到:
- cp /usr/local/pgsql/share/recovery.conf.sample /data/pgsql9.5/recovery.conf
recovery.conf文件的内容改动参考如下:
- standby_mode = on
- primary_conninfo = 'host=192.168.179.128 port=5432 user=replica password=replica'
- recovery_target_timeline = 'latest'
- trigger_file = '/data/pgsql9.5/trigger_activestb'
3)修改参数文件postgresql.conf的配置
postgresql.conf文件的内容修改如下,配置和主库差别较大,需要注意。
- listen_addresses = '*'
- port = 5432
- wal_level = minimal
- max_wal_senders = 0
- wal_keep_segments = 0
- max_connections = 1000
- synchronous_commit = off
- synchronous_standby_names = ''
- hot_standby = on
- max_standby_streaming_delay = 30
- wal_receiver_status_interval = 1s
- hot_standby_feedback = on
4)启动PG备库
- $ /usr/local/pgsql/bin/pg_ctl -D /data/pgsql9.5 -l logfile start
5)查看复制状态
可以在主库端查看复制状态,参考pg_stat_replication视图,在查看的过程中,这个视图字段较大,看起来会有些乱,我们可以使用类似MySQL \G的方式来查看,即\x的扩展模式。
- postgres=# \x
- Expanded display is on.
- postgres=# select * from pg_stat_replication;
- -[ RECORD 1 ]----+------------------------------
- pid | 20539
- usesysid | 16384
- usename | replica
- application_name | walreceiver
- client_addr | 192.168.179.1
- client_hostname |
- client_port | 49374
- backend_start | 2018-03-25 05:19:15.215181+08
- backend_xmin | 1756
- state | streaming
- sent_location | 0/302F600
- write_location | 0/302F600
- flush_location | 0/302F600
- replay_location | 0/302F600
- sync_priority | 0
- sync_state | async