debian系统pgsql-13主从流复制部署、测试

服务安装列表

IP	NODE	USER	PASSWORD
192.168.1.91	master	postgres	123456
192.168.1.186	slave	postgres	123456

1.安装pg服务(91为主、186为从)

1.1.配置pg安装源

#添加pg官方存储库
sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
#添加用于签名软件包的GPC密钥
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

1.2.更新软件包

apt-get update

1.3.安装pg-13

apt -y install postgresql-13 postgresql-client-13 postgresql-contrib
#查看启动状态
pg_ctlcluster 13 main status 或 systemctl status postgresql@13-main.service

1.4.验证安装

root@k8s-worker01:~# psql -h localhost -p 5432 -U postgres
psql (13.3 (Debian 13.3-1.pgdg100+1))
Type "help" for help.
​
postgres=# select version();
​
                                                     version                                                      
------------------------------------------------------------------------------------------------------------------
​
 PostgreSQL 13.3 (Debian 13.3-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
(1 row)
#为用户postgres设置密码
postgres=# alter user postgres with password '123456';
postgres=# exit;

1.5.自定义目录

#创建数据目录
mkdir -p /app/pgsql/data && chown postgres:postgres /app/pgsql/data
#创建归档目录
mkdir -p /app/pgsql/pg_archive && chown postgres:postgres /app/pgsql/pg_archive
#给目录赋权
chmod 700 /app/pgsql/pg_archive/ && chmod 700 /app/pgsql/data/

1.6.修改默认的数据目录

vim /usr/lib/systemd/system/postgresql@.service
#增加
Environment=PGDATA=/app/pgsql/data/
#启用服务控制守护
systemctl daemon-reload

1.7.创建集群

#删除默认目录的集群
pg_dropcluster --stop 13 main
#在新目录创建集群
pg_createcluster -d /app/pgsql/data 13 main
#重启服务
systemctl restart postgresql@13-main.service

1.8.配置开机启动项

systemctl enable postgresql@13-main.service

1.9.允许远程登录

listen_addresses = '*'
vim /etc/postgresql/13/main/pg_hba.conf
#修改为
host    all     all     0.0.0.0/0   md5
#重启服务
systemctl restart postgresql@13-main.service
​

1.10.开放端口号

apt-get install iptables iptables-persistent
iptables -A INPUT -p tcp --dport 5432 -j ACCEPT
netfilter-persistent save
netfilter-persistent reload

2.主服务器配置(192.168.1.91)

2.1.添加 pg 同步用户

添加 pg 用于同步的用户名

psql -h localhost -p 5432 -U postgres
# 创建具有复制流操作的用户：replica
postgres=# CREATE ROLE replica login replication encrypted password '123456';

2.2.pg_hba.conf

修改配置文件：pg_hba.conf，信任从服务器访问

vi /etc/postgresql/13/main/pg_hba.conf
​
# 文件尾部添加信任的从服务器
# replica为postgres用户,XX.XX.XX.XX为从节点的完整IP，如果为网段配置就不是32了，md5为允许密码验证,trust为免密
host replication replica 192.168.1.186/32 trust

2.3.postgresql.conf

修改配置文件：postgresql.conf

vi /etc/postgresql/13/main/postgresql.conf
​
# 新增或修改下列属性设置（使用命令“/”来查找）
listen_addresses = '*' # （修改）监听所有IP
max_connections = 100 # （修改）最大连接数，据说从机需要大于或等于该值
archive_mode = on # （修改）开启归档
archive_command = 'test ! -f /data/pgsql/pg_archive/%f && cp %p /data/pgsql/pg_archive/%f'
wal_level = replica # （添加）
wal_keep_segments = 16 # （修改）暂时还不知道设置为多大合适
wal_sender_timeout = 60s # （修改）

2.4.重启 pg 服务

用root用户执行重启

pg_ctlcluster 13 main restart
或
systemctl restart postgresql@13-main.service

3.从服务器配置(192.168.1.186)

3.1.停止服务

pg_ctlcluster 13 main stop
或
systemctl stop postgresql@13-main.service

3.2.清空数据文件夹

su postgres
rm -rf /app/pgsql/data/*
cd /app/pgsql/data/

3.3.从主节点获取数据

# 使用replica用户，从主节点获取备份数据
pg_basebackup -h 192.168.1.91 -p 5432 -U replica -Fp -Xs -Pv -R -D /app/pgsql/data/

3.4.standby.signal

编辑standby.signal文件，添加配置

standby_mode = 'on'

3.5.postgresql.conf

编辑postgresql.conf文件

vi /etc/postgresql/13/main/postgresql.conf
​
primary_conninfo = 'host=192.168.1.91 port=5432 user=replica password=123456'
recovery_target_timeline = latest # 默认
max_connections = 100 # 大于等于主节点，正式环境应当重新考虑此值的大小
hot_standby = on
max_standby_streaming_delay = 30s
wal_receiver_status_interval = 10s
hot_standby_feedback = on

3.6.启动从库

pg_ctlcluster 13 main start
或
systemctl start postgresql@13-main.service

4.主从验证

方法一：

登录主节点数据库执行如下命令
psql -h localhost -p 5432 -U postgres
​
postgres=# select client_addr,sync_state from pg_stat_replication;
​
结果如下：
client_addr | sync_state
​
---------------+------------
​
192.168.1.186 | async
​
(1 row)
​
说明186服务器是从节点，在接收流，而且是异步流复制

​

方法二：

在主节点与从节点分别执行如下命令
​
ps -ef | grep postgres
​
可以看到主节点有walsender进程
postgres  3593 29713  0 15:14 ?        00:00:00 postgres: 13/main: walsender replica 192.168.1.186(56672) streaming 0/3000148
​
可以看到从节点有walreceiver进程
postgres 107240 107234  0 03:14 ?        00:00:00 postgres: 13/main: walreceiver streaming 0/3000148

5.主从切换

当主库出现故障时，我们需要将从库提升为主库进行读写操作。

5.1.切换函数说明

pg12 开始新增了一个 pg_promote()函数，让我们可以通过 SQL 命令激活备库。

pg_promote()语法：

pg_promote(wait boolean DEFAULT true, wait_seconds integer DEFAULT 60)

两个参数：

wait: 表示是否等待备库的 promotion 完成或者 wait_seconds 秒之后返回成功，默认值为 true。

wait_seconds: 等待时间，单位秒，默认 60

5.2.切换演示

1）主库操作：关闭主库，模拟主库故障：

#切回root用户执行
pg_ctlcluster 13 main stop
或
systemctl stop postgresql@13-main.service

2）备库操作：激活备库：

#在主库或者从库节点都可执行
psql -h 192.168.1.186 -p 5432 -U postgres
​
postgres=# select pg_promote(true,60);

3）验证

/usr/lib/postgresql/13/bin/pg_controldata -D /app/pgsql/data/
​
主备库英文显示如下：
Database cluster state: in production #此时从库以提升为主库

4）原主库降为从库

1、切换到新主库186

vi /etc/postgresql/13/main/pg_hba.conf
#修改
host replication replica 192.168.1.91/32 trust
#重启
systemctl restart postgresql@13-main.service

2、切换到新从库91

systemctl stop postgresql@13-main.service
​
su postgres
rm -rf /app/pgsql/data/*
​
pg_basebackup -h 192.168.8.186 -p 5432 -U replica -Fp -Xs -Pv -R -D /app/pgsql/data/
​
vi /app/pgsql/data/standby.signal
# 添加
standby_mode = 'on'
​
vi /etc/postgresql/13/main/postgresql.conf
#修改
primary_conninfo = 'host=192.168.8.186 port=5432 user=replica password=123456'
​
systemctl start postgresql@13-main.service

5）再次将老主库切回主库

1、切换到当前备库(91)：
​
# 停止服务
systemctl stop postgresql@13-main.service
​
# 删除“standby.signal”文件
rm -rf /app/pgsql/data/standby.signal
​
# 启动服务:
systemctl start postgresql@13-main.service

2、切换到当前主库(186)：
​
# 停止服务:
systemctl stop postgresql@13-main.service
​
# 以“postgres”用户创建“/data/standby.signal”文件，
su postgres
# 添加内容：
vim /app/pgsql/data/standby.signal
standby_mode = 'on'
​
# 启动服务
systemctl start postgresql@13-main.service

6.数据库测试

1）修改pg_hba.conf,否则新建用户无法登录

vim /etc/postgresql/13/main/pg_hba.conf
​
TYPE    DATABASE    USER    ADDRESS     METHOD
local   all         all                 per  #修改为md5

2）创建用户表空间

# 创建表空间文件夹并赋权
mkdir /app/pgsql/sdlg_test 
chown -R postgres:postgres /app/pgsql/tablespace/test/
# 使用postgres用户登录
psql -h localhost -p 5432 -U postgres
​
#创建用户
postgres=# create user test with password 'test';
#创建表空间
postgres=# create tablespace test owner test location '/app/pgsql/tablespace/test';
#给用户设置表空间
postgres=# alter user test set default_tablespace='test';

3）创建数据库

#创建数据
postgres=# create database test_db encoding='utf-8' owner test;
#给用户赋予数据操作权限
postgres=# grant all privileges on database test_db to test;
#把数据库放到表空间下
postgres=# alter database test_db set tablespace test;

4）创建SCHEMA

postgres=# create schema test_schema authorization test;

5）创建表

postgres=# create table test_schema.test(id int);

6）插入数据

postgres=# insert into test_schema.test values(1);

7）查询数据

postgres=# select * from test_schema.test;