PostgreSQL 9.6.6基础安装及流复制配置

最新内容请见个人博http://piggybaba.cn/blog,原创内容，请勿转载

1. 背景描述

PostgreSQL 9.6.6数据库，基于CentOS 7操作系统的的源码编译安装

2. 架构基础概述

• PostgreSQL 9.6.6 + 原生流复制
• node1节点为主节点，node2节点为异步流复制节点

3. 功能说明摘要

• 基础功能，原生支持流复制，用于数据级备份
• 无高可用架构设计

4. 基础环境配置

4.1 系统及软件版本

• CentOS 7 操作系统
• PostgreSQL 9.6.6安装包

4.2 主机名、IP和端口规划(示例)

• 主机名：node1， IP:192.168.6.201（物理）， 端口：TCP 5432;
• 主机名：node2， IP:192.168.6.202（物理）， 端口：TCP 5432;

4.3 主机文件目录规划(示例)

软件安装目录:/app/pg9.6
数据库数据目录：/data/pgdata，属组：postgres，700
数据库归档目录：/bak/pgarch ，属组：postgres，700

4.4 配置系统/ETC/HOSTS

# echo "192.168.6.201 node1
192.168.6.202 node2" >> /etc/hosts

4.5 系统时间检查与配置

可以使用ntp服务或手工调整时间
# date
# timedatectl


4.6 禁用SELINUX

# sed -i s/"SELINUX=enforcing"/"SELINUX=disabled"/g /etc/selinux/config
# setenforce 0
# getenforce

4.7 关闭部分系统相关服务

# systemctl disable firewalld
# systemctl stop firewalld
# systemctl disable NetworkManager
# systemctl stop NetworkManager
# systemctl disable libvirtd
# systemctl stop libvirtd

4.8 调整部分系统相关参数

# echo "# For PG
vm.min_free_kbytes = 1048576
kernel.sem = 500 512000 500 1024
net.ipv4.tcp_keepalive_time = 360
">> /etc/sysctl.conf
# sysctl -p
# echo "# For PostgreSQL
postgres soft nproc 65535
postgres hard nproc 65535
postgres hard nofile 65535
postgres soft nofile 65535
postgres soft memlock unlimited
postgres hard memlock unlimited
" >> /etc/security/limits.conf

5. 安装及基础配置

5.1 创建系统POSTGRESQL安装用户

node1、node2节点，root用户下，创建系统用户，用于安装部署PostgreSQL数据库，样例初始用户密码为postgres，生产请勿使用；
# userdel -r postgres
# useradd postgres
# echo -e "postgres\npostgres\n" | passwd postgres

5.2 数据库的编译和安装

postgres用户，一并安装Contrib及说明手册
$ tar -xzf postgresql-9.6.6.tar.gz
$ cd postgresql-9.6.6/
$ ./configure --prefix=/app/pg9.6 --with-python
$ make world
$ make install-world

5.3 用户环境变量配置

$ echo "
#
## For PG ENV_SETTING
#
export PGHOME=/app/pg9.6
export PGPORT=5432
export PGDATA=/data/pgdata
export PATH=\$PGHOME/bin:\$PATH
export LD_LIBRARY_PATH=\$PGHOME/lib
export MANPATH=\$PGHOME/share/man/:\$MANPATH
" >> ~/.bash_profile
$ source ~/.bash_profile

5.4 初始化数据库

$ initdb -D /data/pgdata

5.5 数据库参数文件的修改

修改postgresql.conf
listen_addresses = '0.0.0.0'
port = 5432
max_connections = 3000
shared_buffers = 24GB #需要修改
wal_level = replica
archive_mode = on
archive_command = 'cp %p /bak/pgarch/%f'
max_wal_senders = 6
wal_log_hints=on
hot_standby = on
deadlock_timeout = 1s
restart_after_crash = off
修改pg_hba.conf
host all all 192.168.6.0/24 md5
host all all 10.10.10.0/24 md5
host replication replicator 192.168.6.0/24 md5

6. 从库流复制配置

6.1 创建数据库复制用户

node1节点上，postgres用户执行以下操作，新建用户用于流复制，并输入新建用户密码
$ pg_ctl start
$ createuser --login --replication replicator -P -s

6.2 创建基础备份

node2节点上，postgres用户执行以下操作，创建基础备份
$ pg_basebackup -h node1 -U replicator -D /data/pgdata/ -X stream -P
node2节点上，$PGDATA目录下，编辑recovery.conf
$ vi recovery.conf
standby_mode = 'on'
primary_conninfo = 'host=xxxxxxx port=5432 user=postgres application_name=node1 user=replicator password=xxxxxxxx keepalives_idle=60 keepalives_interval=5 keepalives_count=5'
restore_command = ''
recovery_target_timeline = 'latest

6.3 流复制验证

主节点node1上，postgres用户执行以下操作
postgres=# select * from pg_stat_replication ;
-[ RECORD 1 ]----+------------------------------
pid | 1831
usesysid | 16394
......
从节点node2上，postgres用户执行以下操作，若返回值为f，则流复制有问题
postgres=\# select pg_is_in_recovery();
t

postgres=# show transaction_read_only ;
on

主节点node1上，postgres用户执行以下操作
# create role test_streaming;
从节点node2上，postgres用户执行以下操作查看，流复制结果
postgres=# \du
test_streaming | Cannot login ....