PostgreSQL之主备搭建（PostgreSQL 14.2）

  根据章节源码安装PostgreSQL 13.1,在主服务器上搭建PostgreSQL数据库软件（本次实验换成了PostgreSQL14.2版本），并初始化数据库数据集簇，以同样的方式在备服务器上中安装PostgreSQL软件，不初始化数据集簇，命令如下：

# 安装postgresql数据库软件
cd postgresql-14.2
./configure --prefix=/opt/software/pg14 --with-pgport=5432
make
make install

# 创建数据目录，并更改相关目录的属主属组信息
[root@zbm /opt/software/pg14]# mkdir data
[root@zbm /opt/software/pg14]# chown -R postgres.postgres /opt/software/pg14

# 配置postgres用户环境变量
su - postgres
vim .bashrc
export PGBASE=/opt/software/pg14
export PATH=$PGBASE/bin:$PATH
export PGDATA=$PGBASE/data
export PGPORT=5432
export PGUSER=postgres
export PGDATABASE=postgres

source .bashrc

环境介绍：
  主(alldb)：10.0.0.100
  备(zbm)：10.0.0.10

二、基于归档创建一个后备服务器

1.建立wal归档

  在主服务器上设置连续归档到一个后备服务器可访问的归档目录。即使主服务器垮掉该归档位置也应当是后备服务器可访问的，即它应当位于后备服务器本身或者另一个可信赖的服务器，而不是位于主控服务器上。
  本文通过NFS文件系统挂载的方式，将归档目录设置为备服务器上的NFS目录。配置NFS文件系统的操作步骤如下：

# 1.备服务器上操作
## 安装相关软件
yum install -y nfs-utils rpcbind

## 创建nfs目录
mkdir -p /opt/software/pg14/wal_archive

## 创建postgres用户（用户的uid和gid尽量与主服务器的postgres用户一致）
useradd -u 1002 postgres

## 更改nfs目录属主属组
chown -R postgres.postgres /opt/software/pg14

## 配置文件/etc/exports
[root@zbm /opt/software]# vim /etc/exports
/opt/software/pg14/wal_archive 10.0.0.0/24(rw,sync,all_squash,anonuid=1002,anongid=1002)

## 启动rpc和nfs服务
systemctl start rpcbind
systemctl enable rpcbind
systemctl start nfs
systemctl enable nfs

# 2.主服务器上操作
## 下载nfs-utils工具
yum install -y nfs-utils

## 挂载NFS文件系统
mkdir -p /opt/software/pg14/wal_archive
mount -t nfs 10.0.0.10:/opt/software/pg14/wal_archive/ /opt/software/pg14/wal_archive/

## 确认是否挂载成功
[root@alldb /opt/software/pg14/wal_archive]# df -Th /opt/software/pg14/wal_archive/
Filesystem                               Type  Size  Used Avail Use% Mounted on
10.0.0.10:/opt/software/pg14/wal_archive nfs4   49G   13G   36G  27% /opt/software/pg14/wal_archive

## 切换到postgres用户，验证是否可以正常创建文件
[root@alldb ~]# su - postgres
[postgres@alldb ~]$ cd /opt/software/pg14/wal_archive/
[postgres@alldb /opt/software/pg14/wal_archive]$ touch a.txt
[postgres@alldb /opt/software/pg14/wal_archive]$ ll a.txt
-rw-rw-r-- 1 postgres postgres 0 Jun 14 11:44 a.txt

  要启用WAL归档，需设置wal_level配置参数为replica或更高，设置archive_mode为on，并且使用archive_command配置参数指定一个shell命令。

# （1）编辑主服务器上数据集簇下的postgresql.conf配置文件，修改如下参数：
[postgres@alldb /opt/software/pg14/data]$ vim postgresql.conf
wal_level = replica
archive_mode = on

# %p会被将要归档的文件路径所替代（路径名是相对于当前工作目录而言的，即集簇的数据目录）
# %f只会被文件名所替代
archive_command = 'test ! -f /opt/software/pg14/wal_archive/%f && cp %p /opt/software/pg14/wal_archive/%f'
archive_cleanup_command='pg_archivecleanup /opt/software/pg14/wal_archive %r'


# 如果你需要在命令中嵌入一个真正的%字符，可以使用%%

# （2）保存退出后，重启服务器
[postgres@alldb ~]$ pg_ctl restart -l logfile   # 没有指定-D选项，是因为配置了PGDATA环境变量
waiting for server to shut down.... done
server stopped
waiting for server to start.... done
server started
[postgres@alldb ~]$ echo $PGDATA
/opt/software/pg14/data

# （3）测试能否正常归档
## 执行pg_switch_log函数，手动切换wal段文件
postgres=# select pg_switch_wal();
 pg_switch_wal 
---------------
 0/17001E0
 ## 查看归档目录中是否有归档文件
 [root@alldb ~]# ll /opt/software/pg14/wal_archive/
total 16384
-rw------- 1 postgres postgres 16777216 Jun 14 14:28 000000010000000000000001

  它将把 WAL 段拷贝到目录/opt/software/pg14/wal_archive。当%p和%f被替代之后，实际执行命令类似：

test ! -f /opt/software/pg14/wal_archive/000000010000000000000001 && cp pg_wal/000000010000000000000001 /opt/software/pg14/wal_archive/000000010000000000000001

  当且仅当归档命令成功时，它才返回零退出。在得到一个零值结果之后，PostgreSQL将假设该文件已经成功归档， 因此它稍后将被删除或者被新的数据覆盖。但是，一个非零值告诉PostgreSQL该文件没有被归档； 因此它会周期性的重试直到成功。
  归档命令只会为完成的WAL段调用。这将导致从一个事务完成到它被安全地记录在归档存储中之间将会有较长的延迟。不过我们可以设置archive_timeout来强制要求服务器按照其设定的频度切换到一个新的WAL段。
  如果你希望暂时停止归档，一种方式是将archive_command设置为空串（’’）。这将导致WAL文件积累在pg_wal/中，直到一个可用的archive_command被重新建立。

2.制作一个基础备份

  执行一次基础备份最简单的方法是使用pg_basebackup工具。它将会以普通文件或一个tar归档的方式创建一个基础备份。pg_basebackup被用于获得一个正在运行的PostgreSQL数据库集簇的基础备份。

[postgres@alldb ~]$ pg_basebackup -D /opt/software/pg14/basebackup -F t -X stream -z -U postgres -W -p 5432 -h 10.0.0.100 -P
Password: 
26261/26261 kB (100%), 1/1 tablespace

3.基于wal归档建立一个后备服务器

  要建立后备服务器，恢复从主服务器取得的基础备份。

# 1.将主服务器上的基础备份传输至备服务器上
[postgres@alldb /opt/software/pg14]$ scp -r basebackup 10.0.0.10:/opt/software/pg14/

# 2.从文件系统中恢复数据库文件，将基础备份解压到备服务器的数据目录下，如下
[postgres@zbm /opt/software/pg14/basebackup]$ tar -zxvf base.tar.gz -C ../data
[postgres@zbm /opt/software/pg14/basebackup]$ tar -zxvf pg_wal.tar.gz -C ../data/pg_wal/

# 3.在后备服务器的集簇数据目录创建文件standby.signal
[postgres@zbm /opt/software/pg14/data]$ touch standby.signal

# 4.设定postgresql.conf配置文件
vim postgresql.conf
listen_addresses = '*'
# 备库不开归档，当然，你也可以开启，这里就不开了
archive_mode = off
# 设定恢复配置
restore_command='cp /opt/software/pg14/wal_archive/%f %p'


# 5.启动数据库服务
[postgres@zbm /opt/software/pg14]$ chmod 0700 data
[postgres@zbm ~]$ pg_ctl start -D /opt/software/pg14/data/ -l logfile
waiting for server to start.... done
server started

## 查看进程信息
[postgres@zbm /opt/software/pg14]$ ps -ef | grep postgres
postgres  62309      1  0 18:12 ?        00:00:00 /opt/software/pg14/bin/postgres
postgres  62310  62309  0 18:12 ?        00:00:00 postgres: startup waiting for 000000010000000000000016
postgres  62313  62309  0 18:12 ?        00:00:00 postgres: checkpointer 
postgres  62315  62309  0 18:12 ?        00:00:00 postgres: background writer 
postgres  62316  62309  0 18:12 ?        00:00:00 postgres: stats collector 

4.验证主备状态

  在主库上某表中插入数据：

postgres=# insert into test1 values(2);
INSERT 0 1
postgres=# select * from test1;
 id 
----
  1
  2
# 查看当前使用的wal段文件
postgres=# select pg_current_wal_lsn();
 pg_current_wal_lsn 
--------------------
 0/16000208
(1 row)
  

在备库中查询：
postgres=# select * from test1;
 id 
----
  1

  发现在备库中并没有查到，在主库中刚刚插入的数据。这是因为该备库是基于wal归档对数据进行恢复的，然而主库当前使用wal段文件并没有归档，所以备库无法应用最新的wal记录来恢复数据。此时我们可以在主库上执行函数pg_switch_wal来强制切换wal段文件，以达到将主库当前使用的wal段文件进行归档。当然我们在主库上设置参数archive_timeout来定期强制切换wal段文件。

# 主库上切换wal日志
postgres=# select pg_switch_wal();
 pg_switch_wal 
---------------
 0/16000258
 
 # 备库上再次查询
 postgres=# select * from test1;
 id 
----
  1
  2

  查看备库运行日志文件：

[postgres@zbm ~]$ tail logfile
cp: cannot stat ‘/opt/software/pg14/wal_archive/000000010000000000000017’: No such file or directory
cp: cannot stat ‘/opt/software/pg14/wal_archive/00000002.history’: No such file or directory

  可以发现，备库每隔一段时间，就会尝试去归档目录中获取主库当前正在使用的wal段文件进行数据恢复。

三、基于流复制创建一个后备服务器

  流复制允许一台后备服务器比使用基于文件的日志传送更能保持为最新的状态。后备服务器连接到主服务器，主服务器则在 WAL 记录产生时即将它们以流式传送给后备服务器而不必等到 WAL 文件被填满。

1.主库上创建一个复制用户

# 创建一个用来复制的用户
postgres=# create user repl replication password '123456';
CREATE ROLE

# 配置pg_hba.conf
[postgres@alldb /opt/software/pg14/data]$ vim pg_hba.conf
host 	replication	repl		10.0.0.10/32		md5
[postgres@alldb /opt/software/pg14/data]$ pg_ctl reload -D .
server signaled

2.主库上创建复制槽

  复制槽可以保证备库未接收到的wal段不会被移除。

postgres=# select * from pg_create_physical_replication_slot('repl_slot');
 slot_name | lsn 
-----------+-----
 repl_slot | 

3.配置主库的postgresql.conf文件

  主库可以关闭归档。

[postgres@alldb /opt/software/pg14/data]$ vim postgresql.conf
listen_addresses = '*'
archive_mode = off

# 重启数据库
[postgres@alldb ~]$ pg_ctl restart -l logfile
waiting for server to shut down........ done
server stopped
waiting for server to start.... done
server started

4.制作基础备份

[postgres@alldb /opt/software/pg14]$ pg_basebackup -D /opt/software/pg14/basebackup -F t -X stream -z -U postgres -W -p 5432 -h 10.0.0.100 -P
Password: 
26270/26270 kB (100%), 1/1 tablespace

5.基于流复制建立一个后备服务器

# 1.将基础备份传输至备库
[postgres@alldb /opt/software/pg14]$ scp -r basebackup 10.0.0.10:/opt/software/pg14/

# 2.清空备库的data目录，然后将基础备份解压到数据目录下
[postgres@zbm /opt/software/pg14]$ tar -zxvf basebackup/base.tar.gz -C data
[postgres@zbm /opt/software/pg14]$ tar -zxvf basebackup/pg_wal.tar.gz -C data/pg_wal/
# 3.在后备服务器的集簇数据目录创建文件standby.signal
[postgres@zbm /opt/software/pg14/data]$ touch standby.signal

# 4.编辑postgresql.conf文件
[postgres@zbm /opt/software/pg14/data]$ vim postgresql.conf
primary_conninfo = 'host=10.0.0.100 port=5432 user=repl password=123456'
primary_slot_name = 'repl_slot' 

# 5.启动备库
[postgres@zbm ~]$ pg_ctl start -l logfile
waiting for server to start.... done
server started

# 6.备库进程信息查看
[postgres@zbm ~]$ ps -ef | grep postgres
postgres  23310      1  0 15:08 ?        00:00:00 /opt/software/pg14/bin/postgres
postgres  23311  23310  0 15:08 ?        00:00:00 postgres: startup recovering 00000001000000000000001B
postgres  23312  23310  0 15:08 ?        00:00:00 postgres: checkpointer 
postgres  23313  23310  0 15:08 ?        00:00:00 postgres: background writer 
postgres  23314  23310  0 15:08 ?        00:00:00 postgres: stats collector 
postgres  23315  23310  0 15:08 ?        00:00:00 postgres: walreceiver streaming 0/1B000148

6.主备验证

# 主库中插入数据
postgres=# insert into test1 values(3);
INSERT 0 1

# 备库中查看
postgres=# select * from test1;
 id 
----
  1
 2
 3