一、数据库要求
1.1、数据库基本要求
Property | Setting | Comment | |
1 | Environment | Production Environment | 生产环境 |
2 | IoT Role | PDS (Production Database) | 业务处理数据库 |
3 | Instance Name (sid/service name) | TWXPDS | 服务名称 |
4 | DB Version | PostgreSQL 11.7 | 数据库版本 |
5 | Template | General Purpose or Transaction Processing | 数据库模板 |
6 | Encoding | UTF8 | 编码 |
7 | LC_COLLATE | zh_CN.utf8 | |
8 | LC_CTYPE | zh_CN.utf8 | |
9 | listen_addresses | ‘*’ | 监听所有网络接口。在 某些情况下，如果存在 多个网络接口，则最好 将其限制为特定的网络 接口。 |
10 | Port | 5432 | 默认端口号 |
11 | max_connections
| 500 | |
12 | shared_buffers | 16 GB | 可选性能调节。设置数 据库服务器用于共享内 存缓冲区的内存量。建 议将此值设置为计算机 可用内存的四分之一。 |
13 | work_mem | 32 MB | 可选性能调节。指定写 入临时磁盘文件之前， 用于内部排序操作和哈希表的内存量。 |
14 | maintenance_work_mem | 512 MB | 可选性能调节。指定用 于维修操作的最大内存 量 |
15 | wal_level | replica | 可选性能调节。指定用 于维修操作的最大内存 量。 |
16 | synchronous_commit | On | |
17 | max_wal_size | 1G |
二、基础环境
2.1、创建用户组。
以root身份运行以下命令：
groupadd -g 1001 postgres
useradd -u 1000 -g postgres postgres
设置postgres账户的密码：
passwd postgres
Changing password for user postgres.
New UNIX password: password
retype new UNIX password: password
passwd: all authentication tokens updatedsuccessfully.
2.2、IP规划
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6localhost6.localdomain6
192.168.140.74 micky1
192.168.140.75 micky2
192.168.140.76 micky3
#192.168.140.77 vip
2.3、创建相关目录
数据库存放位置：
mkdir -p /pgdata/data
---存放pg数据库
mkdir -p /backup
---备份pg数据库
mkdir -p /archive_wals
---WALS归档
mkdir -p /scripts
---特殊脚本部署位置
授权给psotgres账户：
chown -R postgres.postgres/backup
chown -R postgres.postgres /archive_wals
chown -R postgres.postgres /scripts
chown -R postgres.postgres /pgdata/data
chown -R postgres:postgres /usr/local/postgresql
2.4、存储规划：
生产存储数据库部分存储容量规划如下：
数据库名 | raid级别 | LUN | 可用容量(GB) | 用途 |
TWXPDS | 随操作系统 | LUN1:100G | 1T | 主要用于数据库存储， 暂时分配1T空间，Linux LVM方式，方便后面空的动态扩展 |
备份存储容量规划如下：
系统名称 | raid级别 | LUN | 可用容量(GB) | 用途 |
TWXPDS | 随操作系统 | LUN2:600G | 600G | 主要用于数据库备份，暂时分配600G空间，Linux LVM方式，方便后面空的动态扩展 |
WALS归档存储容量规划如下：
数据库名 | raid级别 | LUN | 可用容量(GB) | 用途 |
TWXPDS | 随操作系统 | LUN3:300G | 300G | 主要用于WALS归档，暂时分配300G空间，Linux LVM方式，方便后面空的动态扩展 |
/dev/mapper/pgbackup-pgbackup: 600 GB
/dev/mapper/pgdata-pgdata: 1T
/dev/mapper/pgwals-pgwals: 300 GB
[root@micky1 ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/mapper/rhel-root 140G 11G 130G 8% /
devtmpfs 32G 0 32G 0% /dev
tmpfs 32G 8.0K 32G 1% /dev/shm
tmpfs 32G 13M 32G 1% /run
tmpfs 32G 0 32G 0% /sys/fs/cgroup
/dev/sda1 1014M 179M 836M 18% /boot
/dev/mapper/pgbackup-pgbackup 601G 33M 601G 1% /backup
/dev/mapper/pgdata-pgdata 1.0T 736M 1023G 1% /pgdata
/dev/mapper/pgwals-pgwals 300G 225M 300G 1% /archive_wals
tmpfs 6.3G 12K 6.3G 1% /run/user/985
tmpfs 6.3G 0 6.3G 0% /run/user/0
2.5、LVM相关操作
[root@micky1 ~]# vgcreate pgdata /dev/sdb2
Volume group"pgdata" successfully created
[root@micky1 ~]# vgcreate pgwals /dev/sdb3
Volume group"pgwals" successfully created
[root@micky1 ~]# vgcreate pgbackup /dev/sdb4
Volume group"pgbackup" successfully created
[root@micky1 ~]# lvcreate -l 100%VG -n pgdata pgdata
Logicalvolume "pgdata" created.
[root@micky1 ~]# lvcreate -l 100%VG -n pgwals pgwals
Logicalvolume "pgwals" created.
[root@micky1 ~]# lvcreate -l 100%VG -n pgbackuppgbackup
Logical volume"pgbackup" created.
2.6、创建用户Profile
创建 所有 用户环境文件:
export PATH=/usr/local/postgresql/bin:$PATH
exportLD_LIBRARY_PATH=/usr/local/postgresql/lib:$LD_LIBRARY_PATH
export ZKHOME=/u01/apache-zookeeper-3.6.1
PATH=$PATH:$HOME/bin:$ZKHOME/bin
创建 postgres 用户环境文件:
export PGHOME=/usr/local/postgresql
export PGDATA=/pgdata/data
2.7、安全设置
关闭防火墙:
[root@micky3 ~]# systemctl stop firewalld.service
[root@micky3 ~]# systemctl disable firewalld.service
设置SELinux：
[root@micky3 ~]# vi /etc/selinux/config
# This file controls the state of SELinux on thesystem.
# SELINUX= can take one of these three values:
# enforcing - SELinux security policy is enforced.
# permissive - SELinux prints warnings instead of enforcing.
# disabled- No SELinux policy is loaded.
SELINUX=disabled
# SELINUXTYPE= can take one of three values:
# targeted- Targeted processes are protected,
# minimum- Modification of targeted policy. Only selected processes are protected.
# mls -Multi Level Security protection.
SELINUXTYPE=targeted
2.8、安装依赖软件
安装两个rpm包：
rpm -ivh https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
yum -y install libffi-devel
安装Python环境：
wget -c https://www.python.org/ftp/python/3.8.2/Python-3.8.2.tar.xz
cd Python-3.8.2
./configure --with-ssl
make && make install
创建Python软连接：
rm -f /usr/bin/python
ln -s /usr/local/bin/python3 /usr/bin/python
三、软件安装
3.1、postgresql安装设置
软件安装
tar zxvf postgresql-11.7.tar.gz
cd postgresql-11.7
./configure--prefix=/usr/local/postgresql
#编译安装
make world && make install-world
……
make[2]: Leaving directory`/root/postgresql-11.7/contrib/unaccent'
make -C vacuumlo install
make[2]: Entering directory`/root/postgresql-11.7/contrib/vacuumlo'
/usr/bin/mkdir -p'/usr/local/postgresql/bin'
/usr/bin/install -c vacuumlo '/usr/local/postgresql/bin'
make[2]: Leaving directory`/root/postgresql-11.7/contrib/vacuumlo'
make[1]: Leaving directory`/root/postgresql-11.7/contrib'
PostgreSQL, contrib, and documentationinstallation complete.
初始化数据库
Step1初始化并启动数据库
#初始化数据库
initdb -D /pgdata/data
#启动服务
pg_ctl -D /pgdata/data -l/pgdata/data/logfile start
Step2修改参数并重启数据库
#修改postgresql.conf文件
vi /pgdata/data/postgresql.conf
#修改为如下：
listen_addresses = '*'
port = 5432
#--------------------允许远程连接---------------------------
#修改客户端认证配置文件pg_hba.conf，将需要远程访问数据库的IP地址或地址段加入该文件
vi /pgdata/data/pg_hba.conf
#在文件的最下方加上下面的这句话（出于安全考虑，不建议这样配置）
host all all 0.0.0.0/0 trust
host replication repuser 192.168.140.74/32 md5
host replication repuser 192.168.140.75/32 md5
host replication repuser 192.168.140.76/32 md5
pg_ctl -D /pgdata/data -l/pgdata/data/logfile stop
pg_ctl -D /pgdata/data -l/pgdata/data/logfile start
配置主从同步
Step1主库创建Replication专有用户
主库创建Replication专有用户
CREATE USER repuser REPLICATION ENCRYPTEDPASSWORD 'postgres';
Step2配置主库postgresql.conf参数
alter system set wal_level = replica;
alter system set archive_mode = on;
alter system set archive_command = '/bin/true';
alter system set max_wal_senders = 10;
alter system set wal_keep_segments = 512;
alter system set hot_standby = on;
alter system set synchronous_commit = on;
alter system set synchronous_standby_names= 'micky01,micky02,micky03';
alter system set archive_timeout = '30s';
alter system set archive_command = 'cp %p/archive_wals/%f';
重启数据库：
pg_ctl -D /pgdata/data -l/pgdata/data/logfile stop
pg_ctl -D /pgdata/data -l/pgdata/data/logfile start
Step3备库上使用pg_basebackup从主库同步数据
[postgres@micky2data]$ pg_basebackup -D /pgdata/data -Fp -Xs -v -P -h 192.168.140.74 -p 5432 -Urepuser
Password:
pg_basebackup: initiating base backup,waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log startpoint: 0/2000060 on timeline 1
pg_basebackup: starting background WALreceiver
pg_basebackup: created temporaryreplication slot "pg_basebackup_81452"
23831/23831 kB (100%), 1/1tablespace
pg_basebackup: write-ahead log end point:0/2000130
pg_basebackup: waiting for backgroundprocess to finish streaming ...
pg_basebackup: base backup completed
Step4配置备库recovery.conf
cp/usr/local/postgresql/share/recovery.conf.sample /pgdata/data/recovery.conf
vi /pgdata/data/recovery.conf
#节点micky2
recovery_target_timeline = 'latest'
standby_mode = on
primary_conninfo = 'host=192.168.140.74port=5432 user=repuser password=postgres application_name=micky02'
#节点micky3
recovery_target_timeline = 'latest'
standby_mode = on
primary_conninfo = 'host=192.168.140.74port=5432 user=repuser password=postgres application_name=micky03'
重启备库
pg_ctl -D /pgdata/data -l/pgdata/data/logfile stop
pg_ctl -D /pgdata/data -l/pgdata/data/logfile start
Step5查看配置是否生效
postgres=# SELECTusename,application_name,client_addr,sync_state FROM pg_stat_replication ;
usename | application_name | client_addr | sync_state
---------+------------------+---------------+------------
repuser | micky02 | 192.168.140.74 | sync
repuser | micky03 | 192.168.140.75 | potential
(2 rows)
Step6插入数据查看同步情况
#创建测试数据库
create database micky;
#切换到micky 数据库
\c micky
#创建测试表
create table micky (idinteger, name text);
#插入测试数据
insert into micky values(1,'micky');
#选择数据
select * from micky ;
备库查询：
micky=#select * from micky ;
id | name
----+-------
1 | micky
(1 row)z
3.2、zookeeper安装设置
安装zookeeper
Step1获取软件
wget -c http://mirror.bit.edu.cn/apache/zookeeper/zookeeper-3.6.1/apache-zookeeper-3.6.1-bin.tar.gz
Step2解压软件
tar zxvf apache-zookeeper-3.6.1-bin.tar.gz
mv apache-zookeeper-3.6.1-bin/u01/apache-zookeeper-3.6.1
配置zookeeper
Step1配置zookeeper
cd /u01/apache-zookeeper-3.6.1/conf/
cp zoo_sample.cfg zoo.cfg
vi zoo.cfg
tickTime=2000
initLimit=10
syncLimit=5
dataLogDir=/u01/apache-zookeeper-3.6.1/logs
dataDir=/u01/apache-zookeeper-3.6.1/data
clientPort=2181
autopurge.snapRetainCount=500
autopurge.purgeInterval=24
server.1= 192.168.140.74:2888:3888
server.2= 192.168.140.75:2888:3888
server.3= 192.168.140.76:2888:3888
Step2 新增myid文件
创建配置文件中存在的目录：
mkdir -p /u01/apache-zookeeper-3.6.1/{logs,data}
在三个节点上新增myid文件，里面的内容和server.*中*对应
节点一：
echo "1" >/u01/apache-zookeeper-3.6.1/data/myid
节点二：
echo "2" >/u01/apache-zookeeper-3.6.1/data/myid
节点三：
echo "3" >/u01/apache-zookeeper-3.6.1/data/myid
启动zookeeper服务
启动zookeeper
[root@micky1 ~]# zkServer.sh start
/usr/bin/java
ZooKeeper JMX enabled by default
Using config:/u01/apache-zookeeper-3.6.1/bin/../conf/zoo.cfg
Starting zookeeper ... STARTED
查看zookeeper
[root@micky1 ~]# zkServer.sh status
/usr/bin/java
ZooKeeper JMX enabled by default
Using config:/u01/apache-zookeeper-3.6.1/bin/../conf/zoo.cfg
Client port found: 2181. Client address:localhost.
Mode: follower
3.3、Patroni安装配置
安装patroni
Step1获取软件
wget -c https://files.pythonhosted.org/packages/d9/71/f1465e00f5e2d3e4c00c4ef8d80c02e3c31ac76dcf6a9d3b0cdef3b62305/patroni-1.6.5.tar.gz
Step2解压软件
mv patroni-1.6.5.tar.gz /home/postgres/
cd /home/postgres/
tar zxvf patroni-1.6.5.tar.gz
cd patroni-1.6.5/
配置相关组件
Step1安装组件
pip3 install --upgrade pip3
pip3 install --upgrade pip
pip3 install patroni[zookeeper] -i https://mirrors.aliyun.com/pypi/simple/
pip3 install psycopg2-binary -ihttps://mirrors.aliyun.com/pypi/simple/
Step2 配置patroni
postgres用户执行：
节点一：[root@micky1 ~]# cat/home/postgres/patroni/micky01.yml
scope: micky
namespace: /service/
name: micky01
restapi:
listen: 192.168.140.74:8008
connect_address: 192.168.140.74:8008
zookeeper:
hosts: 192.168.140.74:2181,192.168.140.75:2181,192.168.140.76:2181
bootstrap:
#this section will be written into Etcd:/<namespace>/<scope>/configafter initializing new cluster
#and all other cluster members will use it as a `global configuration`
dcs:
ttl: 30
loop_wait: 10
retry_timeout: 10
maximum_lag_on_failover: 1048576
master_start_timeout: 300
synchronous_mode: true
#standby_cluster:
#host: 127.0.0.1
#port: 1111
#primary_slot_name: patroni
postgresql:
use_pg_rewind: true
use_slots: true
parameters:
wal_level: replica
hot_standby: "on"
wal_keep_segments: 128
max_wal_senders: 10
max_replication_slots: 10
wal_log_hints: "on"
archive_mode: "on"
# primary_conninfo: 'host=192.168.140.74 port=5432 user=repuser'
hot_standby: on
archive_timeout: 1800s
postgresql:
listen: 0.0.0.0:5432
connect_address: 192.168.140.74:5432
data_dir: /pgdata/data
bin_dir: /usr/local/postgresql/bin
config_dir: /pgdata/data
#pgpass: /home/pg12/patroni/.pgpass
authentication:
replication:
username: repuser
password: postgres
superuser:
username: postgres
password: postgres
rewind: # Has no effect onpostgres 10 and lower
username: postgres
password: postgres
callbacks:
on_start: /home/postgres/patroni/patroni_callback.sh
on_stop: /home/postgres/patroni/patroni_callback.sh
on_role_change: /home/postgres/patroni/patroni_callback.sh
tags:
nofailover: false
noloadbalance: false
clonefrom: false
nosync: false
节点二：[postgres@micky2 ~]$ cat/home/postgres/patroni/micky02.yml
scope: micky
namespace: /service/
name: micky02
restapi:
listen: 192.168.140.75:8008
connect_address: 192.168.140.75:8008
zookeeper:
hosts: 192.168.140.74:2181,192.168.140.75:2181,192.168.140.76:2181
bootstrap:
#this section will be written into Etcd:/<namespace>/<scope>/configafter initializing new cluster
#and all other cluster members will use it as a `global configuration`
dcs:
ttl: 30
loop_wait: 10
retry_timeout: 10
maximum_lag_on_failover: 1048576
master_start_timeout: 300
synchronous_mode: true
#standby_cluster:
#host: 127.0.0.1
#port: 1111
#primary_slot_name: patroni
postgresql:
use_pg_rewind: true
use_slots: true
parameters:
wal_level: replica
hot_standby: "on"
wal_keep_segments: 128
max_wal_senders: 10
max_replication_slots: 10
wal_log_hints: "on"
archive_mode: "on"
# primary_conninfo: 'host=192.168.140.75 port=5432 user=repuser'
hot_standby: on
archive_timeout: 1800s
postgresql:
listen: 0.0.0.0:5432
connect_address: 192.168.140.75:5432
data_dir: /pgdata/data
bin_dir: /usr/local/postgresql/bin
config_dir: /pgdata/data
#pgpass: /home/pg12/patroni/.pgpass
authentication:
replication:
username: repuser
password: postgres
superuser:
username: postgres
password: postgres
rewind: # Has no effect onpostgres 10 and lower
username: postgres
password: postgres
parameters:
# Fully qualified kerberos ticket file for the running user
#same as KRB5CCNAME used by the GSS
# krb_server_keyfile: /var/spool/keytabs/postgres
unix_socket_directories: '.'
callbacks:
on_start: /home/postgres/patroni/patroni_callback.sh
on_stop: /home/postgres/patroni/patroni_callback.sh
on_role_change: /home/postgres/patroni/patroni_callback.sh
tags:
nofailover: false
noloadbalance: false
clonefrom: false
nosync: false
节点三：[postgres@micky3 ~]$ cat/home/postgres/patroni/micky03.yml
scope: micky
namespace: /service/
name: micky03
restapi:
listen: 192.168.140.76:8008
connect_address: 192.168.140.76:8008
zookeeper:
hosts: 192.168.140.74:2181,192.168.140.75:2181,192.168.140.76:2181
bootstrap:
#this section will be written into Etcd:/<namespace>/<scope>/configafter initializing new cluster
#and all other cluster members will use it as a `global configuration`
dcs:
ttl: 30
loop_wait: 10
retry_timeout: 10
maximum_lag_on_failover: 1048576
master_start_timeout: 300
synchronous_mode: true
#standby_cluster:
#host: 127.0.0.1
#port: 1111
#primary_slot_name: patroni
postgresql:
use_pg_rewind: true
use_slots: true
parameters:
wal_level: replica
hot_standby: "on"
wal_keep_segments: 128
max_wal_senders: 10
max_replication_slots: 10
wal_log_hints: "on"
archive_mode: "on"
# primary_conninfo: 'host=192.168.140.76 port=5432 user=repuser'
hot_standby: on
archive_timeout: 1800s
postgresql:
listen: 0.0.0.0:5432
connect_address: 192.168.140.76:5432
data_dir: /pgdata/data
bin_dir: /usr/local/postgresql/bin
config_dir: /pgdata/data
#pgpass: /home/pg12/patroni/.pgpass
authentication:
replication:
username: repuser
password: postgres
superuser:
username: postgres
password: postgres
rewind: # Has no effect onpostgres 10 and lower
username: postgres
password: postgres
callbacks:
on_start: /home/postgres/patroni/patroni_callback.sh
on_stop: /home/postgres/patroni/patroni_callback.sh
on_role_change: /home/postgres/patroni/patroni_callback.sh
tags:
nofailover: false
noloadbalance: false
clonefrom: false
启动patroni服务
对应节点启动patroni
patroni /home/postgres/patroni/micky01.yml>/home/postgres/patroni/micky01.log 2>&1 &
patroni /home/postgres/patroni/micky02.yml>/home/postgres/patroni/micky02.log 2>&1 &
patroni /home/postgres/patroni/micky03.yml>/home/postgres/patroni/micky03.log 2>&1 &
对应节点查看patroni
[postgres@micky1 ~]$ patronictl -c/home/postgres/patroni/micky01.yml list
+ Cluster: micky (6864143465257765565)--+---------+----+-----------+
| Member | Host | Role | State | TL | Lag in MB |
+----------+---------------+--------------+---------+----+-----------+
| micky01 | 192.168.140.74 | Sync Standby| running | 5 | 0 |
| micky02 | 192.168.140.75 | | running | 5 | 0 |
| micky03 | 192.168.140.76 | Leader | running | 5 | |
+----------+---------------+--------------+---------+----+-----------+
[postgres@micky2 ~]$ patronictl -c/home/postgres/patroni/micky03.yml list
+ Cluster: micky (6864143465257765565)--+---------+----+-----------+
| Member | Host | Role | State | TL | Lag in MB |
+----------+---------------+--------------+---------+----+-----------+
| micky01 | 192.168.140.74 | Sync Standby| running | 5 | 0 |
| micky02 | 192.168.140.75 | | running | 5 | 0 |
| micky03 | 192.168.140.76 | Leader | running | 5 | |
+----------+---------------+--------------+---------+----+-----------+
[postgres@micky3 ~]$ patronictl -c/home/postgres/patroni/micky03.yml list
+ Cluster: micky (6864143465257765565)--+---------+----+-----------+
| Member | Host | Role | State | TL | Lag in MB |
+----------+---------------+--------------+---------+----+-----------+
| micky01 | 192.168.140.74 | Sync Standby| running | 5 | 0 |
| micky02 | 192.168.140.75 | | running | 5 | 0 |
| micky03 | 192.168.140.76 | Leader | running | 5 | |
+----------+---------------+--------------+---------+----+-----------+
当前主库运行在micky3节点上。
3.4、安装配置haproxy和keepalive
安装haproxy和keepalive
Step1安装软件
yum -y install haproxy
yum install -y keepalived
Step2确认安装
[root@micky1 ~]# rpm -qa | grep haproxy
haproxy-1.5.18-8.el7.x86_64
[root@micky1 ~]# rpm -qa | grep keepalive
keepalived-1.3.5-6.el7.x86_64
配置haproxy和keepalive
Step1配置keepalive
vi /etc/keepalived/keepalived.conf
global_defs {
smtp_connect_timeout 30
router_id LVS_DEVEL01
}
vrrp_instance VI_1 {
state MASTER
interface ens192
virtual_router_id 51
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.140.77
}
}
Step2 配置haproxy
vi /etc/haproxy/haproxy.cfg
defaults
mode tcp
log 127.0.0.1 local3 err
option tcplog
option dontlognull
retries 3
option redispatch
timeout queue 1m
timeout connect 10s
timeout client 1m
timeout server 1m
timeout check 5s
maxconn 5120
#---------------------------------------------------------------------
listen status
bind 0.0.0.0:1080
mode http
log global
stats enable
stats refresh 30s
stats uri /haproxy-stats
stats realm Private lands
stats auth admin:passw0rd
# stats hide-version
#---------------------------------------------------------------------
listen master
bind *:5000
mode tcp
option tcplog
balance roundrobin
option httpchk OPTIONS /master
http-check expect status 200
default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
server node1 192.168.140.74:5432 maxconn 1000 check port 8008 inter 5000rise 2 fall 2
server node2 192.168.140.75:5432 maxconn 1000 check port 8008 inter 5000rise 2 fall 2
server node3 192.168.140.76:5432 maxconn 1000 check port 8008 inter 5000rise 2 fall 2
listen replicas
bind *:5001
mode tcp
option tcplog
balance roundrobin
option httpchk OPTIONS /replica
http-check expect status 200
default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
server node1 192.168.140.74:5432 maxconn 1000 check port 8008 inter 5000rise 2 fall 2
server node2 192.168.140.75:5432 maxconn 1000 check port 8008 inter 5000rise 2 fall 2
server node3 192.168.140.76:5432 maxconn 1000 check port 8008 inter 5000rise 2 fall 2
启动haproxy和keepalive
启动haproxy和keepalive
[root@micky1 ~]# systemctl start haproxy
systemctl start keepalived
查看haproxy和keepalive
[root@micky1 ~]#systemctl status haproxy
● haproxy.service -HAProxy Load Balancer
Loaded: loaded(/usr/lib/systemd/system/haproxy.service; disabled; vendor preset: disabled)
Active: active (running) since Mon 2020-08-2412:03:27 CST; 1 day 3h ago
Main PID: 24903 (haproxy-systemd)
Tasks: 3
CGroup: /system.slice/haproxy.service
├─24903 /usr/sbin/haproxy-systemd-wrapper -f /etc/haproxy/haproxy.cfg-p /run/haproxy.pid
├─24908 /usr/sbin/haproxy -f /etc/haproxy/haproxy.cfg -p/run/haproxy.pid -Ds
└─24912 /usr/sbin/haproxy -f /etc/haproxy/haproxy.cfg -p /run/haproxy.pid-Ds
Aug 24 12:03:27 micky1systemd[1]: Started HAProxy Load Balancer.
Aug 24 12:03:27 micky1haproxy-systemd-wrapper[24903]: haproxy-systemd-wrapper: executing/usr/sbin/haproxy -f /e... -Ds
Hint: Some lines wereellipsized, use -l to show in full.
[root@micky1 ~]#systemctl status keepalived
● keepalived.service -LVS and VRRP High Availability Monitor
Loaded: loaded(/usr/lib/systemd/system/keepalived.service; disabled; vendor preset: disabled)
Active: active (running) since Mon2020-08-24 12:03:27 CST; 1 day 3h ago
Process: 24916 ExecStart=/usr/sbin/keepalived$KEEPALIVED_OPTIONS (code=exited, status=0/SUCCESS)
Main PID: 24919 (keepalived)
Tasks: 3
CGroup: /system.slice/keepalived.service
├─24919 /usr/sbin/keepalived -D
├─24920 /usr/sbin/keepalived -D
└─24921 /usr/sbin/keepalived -D
Aug 24 12:03:29 micky1Keepalived_vrrp[24921]: VRRP_Instance(VI_1) setting protocol VIPs.
Aug 24 12:03:29 micky1 Keepalived_vrrp[24921]:Sending gratuitous ARP on ens192 for 192.168.140.77
Aug 24 12:03:29 micky1Keepalived_vrrp[24921]: VRRP_Instance(VI_1) Sending/queueing gratuitous ARPs onens192 fo...40.77
Aug 24 12:03:29 micky1Keepalived_vrrp[24921]: Sending gratuitous ARP on ens192 for 192.168.140.77
Aug 24 12:03:29 micky1Keepalived_vrrp[24921]: Sending gratuitous ARP on ens192 for 192.168.140.77
Aug 24 12:03:29 micky1Keepalived_vrrp[24921]: Sending gratuitous ARP on ens192 for 192.168.140.77
Aug 24 12:03:29 micky1Keepalived_vrrp[24921]: Sending gratuitous ARP on ens192 for 192.168.140.77
Aug 24 12:03:31 micky1Keepalived_vrrp[24921]: VRRP_Instance(VI_1) Received advert with higherpriority 100, ours 100
Aug 24 12:03:31 micky1Keepalived_vrrp[24921]: VRRP_Instance(VI_1) Entering BACKUP STATE
Aug 24 12:03:31 micky1Keepalived_vrrp[24921]: VRRP_Instance(VI_1) removing protocol VIPs.
Hint: Some lines wereellipsized, use -l to show in full.
四、数据库档案
4.1、数据库版本信息
PostgreSQL Version |
postgres=# show server_version; server_version ---------------- 11.7 (1 row)
postgres=# SELECT current_setting('server_version_num'); current_setting ----------------- 110007 (1 row)
postgres=# select version(); version --------------------------------------------------------------------------------------------------------- PostgreSQL 11.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit (1 row) |
4.2、数据库创建
Step1创建专用表空间
create tablespace twxpdsowner postgres location '/pgdata/data';
Step2创建数据库
CREATEDATABASE TWXPDS
WITH
OWNER= postgres
ENCODING= ‘UTF8’
lc_collate='zh_CN.utf8'
lc_ctype='zh_CN.utf8'
TEMPLATE=template0
TABLESPACE= twxpds
CONNECTIONLIMIT = -1;
Step3数据库信息
postgres=#\l
List ofdatabases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 |en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |=c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |=c/postgres +
| | | | | postgres=CTc/postgres
twxpds | postgres | UTF8 |zh_CN.utf8 | zh_CN.utf8 |
(4rows)
postgres=#\db
List of tablespaces
Name | Owner | Location
------------+----------+--------------
pg_default | postgres |
pg_global | postgres |
twxpds | postgres | /pgdata/data
(3rows)
4.3、主从复制状态查询
通过patroni查询主从复制状态信息
[postgres@micky1 ~]$ patronictl -c /home/postgres/patroni/micky01.ymllist
+Cluster: micky (6864143465257765565) --+---------+----+-----------+
| Member | Host | Role | State |TL | Lag in MB |
+----------+---------------+--------------+---------+----+-----------+
|micky01 | 192.168.140.74 | Sync Standby | running | 5 | 0 |
|micky02 | 192.168.140.75 | |running | 5 | 0 |
|micky03 | 192.168.140.76 | Leader | running | 5 | |
+----------+---------------+--------------+---------+----+-----------+
通过psql查询主从复制状态信息
[postgres@micky3 ~]$ psql
psql (11.7)
Type "help" for help.
postgres=# select pid,state,client_addr,sync_priority,sync_statefrom pg_stat_replication;
pid | state | client_addr | sync_priority |sync_state
-------+-----------+---------------+---------------+------------
29479 | streaming | 192.168.140.74 | 1 | sync
29477 | streaming | 192.168.140.75 | 2 | potential
(2rows)
4.4、数据库连接
通过VIP访问5432端口
该方式访问的是VIP所在服务器的数据库，一般情况访问的是主库，没有读写分离功能。
[postgres@micky1~]$ psql -h 192.168.140.77 -p 5432 -U postgres
psql(11.7)
Type"help" for help.
postgres=#select pid,state,client_addr,sync_priority,sync_state from pg_stat_replication;
pid | state | client_addr | sync_priority |sync_state
-------+-----------+---------------+---------------+------------
29479 | streaming | 192.168.140.74 | 1 | sync
29477 | streaming | 192.168.140.75 | 2 | potential
(2rows)
通过VIP访问5001端口
该方式访问的是从数据库或者成为standby database，一般情况访问的是从库，支持读写分离中的只读操作。
[postgres@micky1~]$ psql -h 192.168.140.77 -p 5001 -U postgres
psql(11.7)
Type"help" for help.
postgres=#select pid,state,client_addr,sync_priority,sync_state from pg_stat_replication;
pid | state | client_addr | sync_priority |sync_state
-----+-------+-------------+---------------+------------
(0rows)
通过VIP访问5000端口
该方式访问的是主数据库或者成为primary database，一般情况访问的是主库，支持读写分离中的读写操作。
[postgres@micky1~]$ psql -h 192.168.140.77 -p 5000 -U postgres
psql(11.7)
Type"help" for help.
postgres=#select pid,state,client_addr,sync_priority,sync_state from pg_stat_replication;
pid | state | client_addr | sync_priority |sync_state
-------+-----------+---------------+---------------+------------
29479 | streaming | 192.168.140.74 | 1 | sync
29477 | streaming | 192.168.140.75 | 2 | potential
(2rows)
4.5、数据库主要参数
[postgres@micky1~]$ patronictl -c /home/postgres/patroni/micky01.yml show-config
loop_wait:10
master_start_timeout:300
maximum_lag_on_failover:1048576
postgresql:
parameters:
archive_command: /bin/date
archive_mode: true
autovacuum: true
autovacuum_max_workers: 3
bgwriter_delay: 10ms
checkpoint_timeout: 30min
full_page_writes: true
hot_standby: true
listen_addresses: '*'
log_autovacuum_min_duration: -1
log_checkpoints: true
log_connections: true
log_destination: csvlog
log_directory: log
log_file_mode: 384
log_filename:postgresql-%Y-%m-%d_%H%M%S.log
log_min_messages: warning
log_rotation_age: 1d
log_rotation_size: 10MB
log_timezone: Asia/Shanghai
log_truncate_on_rotation: false
logging_collector: true
maintenance_work_mem: 128MB
max_connections: 1000
max_wal_senders: 10
max_wal_size: 1GB
min_wal_size: 80MB
port: 1921
random_page_cost: 2.0
seq_page_cost: 1.0
shared_buffers: 128MB
superuser_reserved_connections: 3
synchronous_commit: false
unix_socket_directories: /tmp
unix_socket_group: ''
unix_socket_permissions: 511
wal_keep_segments: 512
wal_level: logical
work_mem: 4MB
use_pg_rewind: true
use_slots: true
retry_timeout:10
synchronous_mode:false
ttl: 30
