postgresql 9.6主从流复制部署

⼀、准备环境
操作系统：CentOS Linux release 7.4
数据库版本：PostgreSQL 9.6.12
主库:192.168.189.152
从库:192.168.189.153
⼆、主机数据库部署
以下操作步骤请在两个节点进⾏，主库需要安装数据库软件以及初始化数据库，从库仅需要安装数据库软件即可⽆
需初始化。
1、添加RPM源
[root@localhost opt]# yum install https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7- x86_64/pgdg-centos96-9.6-3.noarch.rpm
2、使⽤yum直接安装postgresql
[root@localhost opt]# yum -y install postgresql96-server postgresql96-contrib postgresql96-devel
3、创建postgresql⽂件⽬录以及⽤户组
创建数据⽂件⽬录
[root@localhost opt]# mkdir -p /data/pgdata/
创建pg⽤户组
[root@localhost opt]# groupadd pg
[root@localhost opt]# useradd -g pg pg
对数据⽂件⽬录授予权限
[root@localhost opt]# chown -R pg:pg /data/
[root@localhost opt]# chown -R pg:pg /var/run/postgresql
4、初始化数据库
注意：此步骤仅主库需要初始化，从库不需要
[pg@localhost ~]$ /usr/pgsql-9.6/bin/initdb -D /data/pgdata/
初始化过程：
The files belonging to this database system will be owned by user “pg”.
This user must also own the server process.The database cluster will be initialized with locale “en_US.UTF-8”. 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 /data/pgdata … ok creating subdirectories … ok PostgreSQL 1 9.6主从流复制部署 1/5 selecting default max_connections … 100 selecting default shared_buffers … 128MB selecting dynamic shared memory implementation … posix creating configuration files … ok running bootstrap script … ok performing post-bootstrap initialization … 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: 初始化完毕之后启动数据库 –>/usr/pgsql-9.6/bin/pg_ctl -D /data/pgdata/ -l logfile start 附停⽌命令：[postgres@localhost pgsql]$ pg_ctl stop -D /mydata/pgdata/备注： 5、添加环境变量 [pg@localhost ~]$ vi ~/.bash_profile PGDATA=/data/pgdata PGHOST=127.0.0.1 PGDATABASE=postgres PGUSER=pg PGPORT=5432 PATH=/usr/pgsql-9.6/bin:$PATH export PATH export PGDATA PGHOST PGDATABASE PGUSER PGPORT export TMOUT=1000 export LD_LIBRARY_PATH=/usr/pgsql-9.6/lib source ~/.bash_profile 步骤6、7仅需在主库操作 6、修改监听配置 修改如下监听参数，是监听允许任何地址使⽤[pg@localhost ~]$ vi /data/pgdata/postgresql.conflisten_addresses = ‘*’7、添加允许访问数据库的⽹段 在127.0.0.1下⼀段添加⽹段[pg@localhost ~]$ vi /data/pgdata/pg_hba.confIPv4 local connections:host all all 127.0.0.1/32 trust添加此⾏—->host all all 0.0.0.0/0 trust2/5 三、主备数据库配置 3.1 主库配置 (1)创建复制⽤户，专门进⾏主从同步使⽤[root@localhost ~]# su - pg[pg@localhost ~]$ psqlpostgres=# create user rpl superuser password ‘11111’;(2)主库上配置从库允许的⽹段 添加从库⽹段[pg@localhost ~]$ vi /data/pgdata/pg_hba.confhost replication rpl 192.168.0.0/0 md5(3)修改主库参数⽂件[pg@localhost ~]$ vi /data/pgdata/postgresql.confwal_level = hot_standby -->启⽤流复制max_wal_senders=2 -->流复制允许连接进程wal_keep_segments =64–>xlog⽬录中最多容纳多少个wal⽇志⽂件，超过了则删掉最初的⼏个。max_connections = 1000 -->主库最⼤连接数(4)重启主库服务[pg@localhost ~]$ pg_ctl -D /data/pgdata/ -l logfile restart3.2 从库配置 (1)基础备份 在从库⽆需拷贝主库的数据⽂件到本地，使⽤pg_basebackup可将主库的数据⽂件通过⽹络复制到本地、包括⽬录 结构。 备注：如果主库存在⾃定义表空间，此操作会失败[pg@localhost ~]$ pg_basebackup -h 192.168.212.152 -p 5432 -U rpl -F p -x -P -R -D /data/pgdataPassword: 46694/46694 kB (100%), 1/1 tablespace 传输完毕 (2) 从库配置⽂件配置[pg@localhost ~]$ vi /data/pgdata/postgresql.conf`
注释这三个参数wal_level，max_wal_senders,wal_keep_segments
修改
hot_standby = on ———>从库上执⾏只读操作
max_standby_streaming_delay = 30s -->从库接收主库⽇志最⼤超时时间
wal_receiver_status_interval = 10s -->从库向主库报告的最⼤时间间隔
hot_standby_feedback = off -->从库与主库发⽣冲突的反馈信息
max_connections = 1500 -->数据库最⼤连接数
3)创建恢复⽂件recovery.conf
recovery.conf ⽤于主库，从库切换时的参数配置。如果从库没有recovery.conf⽂件可拷贝模板
3/5
[pg@localhost pgdata]$ cp /usr/pgsql-9.6/share/recovery.conf.sample /data/pgdata/recovery.conf
[pg@localhost pgdata]$ vi /data/pgdata/recovery.conf
调整参数：
recovery_target_timeline = ‘latest’ moren
standby_mode = on
primary_conninfo = ‘host=192.168.212.152 port=5432 user=rpl password=11111’
(4)启动从库服务
[pg@localhost pgdata]$ pg_ctl -D /data/pgdata/ -l logfile start
四、主从状态检查
查看主库sender进程信息
[root@localhost ~]# ps -ef|grep “wal sender process”
pg 7031 7015 0 17:04 ? 00:00:00 postgres: wal sender process rpl 192.168.212.153(49562) streaming
0/50001E8
查看从库receiver进程信息
[root@localhost ~]# ps -ef|grep “wal receiver”
pg 2360 2354 0 17:04 ? 00:00:00 postgres: wal receiver process streaming 0/50002C8
psql查询主从同步状态
postgres=# select usename,application_name,client_addr,state,sync_state from pg_stat_replication;
usename | application_name | client_addr | state | sync_state
---------±-----------------±----------------±----------±-----------
rpl | walreceiver | 192.168.212.153 | streaming | async–>异步
(1 row)
主库集群状态
[pg@localhost ~]$ pg_controldata /data/pgdata/|grep “Database cluster state”
Database cluster state: in production
从库集群状态
[pg@localhost ~]$ pg_controldata /data/pgdata/|grep “Database cluster state”
Database cluster state: in archive recovery
主从数据同步验证
主库建表、从库进⾏查询测试
主库：
pg=# insert into test values(1);
pg=# select * from test;
id
1
(1 row)
从库：
pg=# insert into test values(2);
ERROR: cannot execute INSERT in a read-only transaction
pg=# select * from test;
4/5
id
1
(1 row)
从库只有只读功能，⽆法做写⼊操作