基础环境：

阿里云ECS

Rhel 9.4 5.14.0-427.35.1.el9_4.x86_64

Postgres16

数据库环境搭建

操作系统环境准备，基础包准备

sudo dnf group install "Development Tools" -y
sudo dnf install zlib-devel readline-devel libicu-devel -y

常用工具安装

dnf install vim wget -y

1.关闭selinux

查看状态
getenforce

Enforcing：强制模式，代表SELinux运行中，且已经正确开始限制domain/type
Permissive：宽容模式，代表SELinux运行中，不过仅会有警告信息并不会实际限制domain/type的读写（这种模式可以用来作为SELinux的debuug之用）
Disabled：关闭模式，SELinux并没有实际运行

临时关闭
setenforce 0

永久关闭
vim /etc/selinux/config
SELINUX=disabled

reboot

2.关闭swap

swapon --show

临时关闭
swapoff /dev/dm-1

永久关闭
vim /etc/fstab
注释掉
UUID=xxxx-xxxx none swap sw 0 0 或者 /dev/sdXN none swap sw 0 0

更新 grub
grub2-mkconfig -o /boot/grub2/grub.cfg
重启系统
reboot

3.关闭防火墙

systemctl status firewalld
systemctl stop firewalld
systemctl disable firewalld

4.修改内核参数

vim /etc/sysctl.conf

fs.aio-max-nr= 1048576
fs.file-max= 6815744
kernel.shmall= 2097152
kernel.shmmax= 4294967295
kernel.shmmni= 4096
kernel.sem= 250 32000 100 128
net.ipv4.ip_local_port_range= 9000 65500
net.core.rmem_default= 262144
net.core.rmem_max= 4194304
net.core.wmem_default= 262144
net.core.wmem_max= 1048576

vm.swappiness = 0

生效

/sbin/sysctl -p
/sbin/sysctl -a

5.修改资源使用参数

vim /etc/security/limits.conf

* soft nofile 65536
* hard nofile 65535
* soft nproc 65536
* hard nproc 65535
* soft core unlimited
* hard core unlimited

6.RemoveIPC参数

systemctl daemon-reload
systemctl restart systemd-logind.service

7.关闭透明大页

echo never > /sys/kernel/mm/transparent_hugepage/enabled

8.数据盘挂载

lsblk
fdisk /dev/sdb
n 
p
#1
t 
#1
8e
w

fdisk -l
pvcreate /dev/sdb1
pvs

vgcreate vgmysql /dev/sdb1
vgs

lvcreate -L 99.9G -n lvmysql vgmysql
lvs

mkfs.ext4 /dev/vgmysql/lvmysql

mkdir /mysqldata
mount /dev/mapper/vgmysql-lvmysql /mysqldata

vim /etc/fstab
/dev/mapper/vgmysql-lvmysql /mysqldata ext4 defaults 0 0

重启操作系统
reboot

数据库下载

wget https://ftp.postgresql.org/pub/source/v16.4/postgresql-16.4.tar.gz
tar -zxvf postgresql-16.4.tar.gz
cd postgresql-16.4
# 编译安装数据库
dnf install systemd-devel -y
./configure --help
./configure --with-systemd -with-uuid=e2fs

创建数据库目录

sudo useradd postgres
sudo passwd postgres
sudo mkdir -p /pgdatabase/data
sudo chown -R postgres: /pgdatabase/data
# root和postgres用户下都添加下环境变量
vim .bash_profile
export PG_HOME='/usr/local/pgsql/bin'
export PATH=$PATH:$PG_HOME

source .bash_profile

初始化

su postgres
initdb -D /pgdatabase/data/ -U wangcw -W

修改配置文件

vim /pgdatabase/data/postgresql.conf
修改
listen_addresses = '*'
port = 5432
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d 
log_rotation_size = 100MB
log_min_messages = warning
log_min_duration_statement = 1000
log_lock_waits = on 
log_statement = 'all'
log_timezone = 'Asia/Shanghai'
timezone = 'Asia/Shanghai'

vim /pgdatabase/data/pg_hba.conf 

# for remote user
hostall all 0.0.0.0/0   password

启动PG服务

pg_ctl -D /pgdatabase/data/ start
pg_ctl -D /pgdatabase/data/ -l logfile start

注册成系统服务

vim /usr/lib/systemd/system/postgresd.service

[Unit]
Description=PostgresSQL16
After=network.target remote-fs.target nss-lookup.target

[Service]
Type=forking
ExecStart=/usr/local/pgsql/bin/pg_ctl start -D /pgdatabase/data
ExecStop=/usr/local/pgsql/bin/pg_ctl stop -D /pgdatabase/data
Restart=/usr/local/pgsql/bin/pg_ctl restart -D /pgdatabase/data
User=postgres
Group=postgres

[Install]
WantedBy=multi-user.target

systemctl daemon-reload

PG自带插件安装

cd /root/postgresql-16.4/contrib

pg_prewarm
cd pg_prewarm
make && make install

uuid-ossp
cd uuid-ossp
make && make install

查看可用插件
select *
from pg_available_extensions;

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS pg_prewarm;

select *
from pg_available_extensions 
where installed_version is not null;

PostGis插件安装

编译安装Postgis依赖包比较多

cmake //安装geos-3.11.2需要，并且>3.13
geos-3.9.1 //太高版本会报错
sqlite3 //安装proj需要 sqlite3 >= 3.7)
proj-6.1.0.tar.gz >6.0 //gdal版本要求
gdal-3.5.2
json-c-0.13.1
libxml2-2.9.12
protobuf-all-3.15.8 protobuf-c安装需求 (protobuf >= 2.6.0)
protobuf-c-1.4.1
cgal-releases-CGAL-4.13
SFCGAL-1.3.8
pgrouting-3.4.2

各包使用需求：

postgis 支持必装的几个包 geos proj libxml protobuf protobuf-c postgis
要进行栅格处理需要再安装 gdal
要进行网络分析需要再pgRouting
postgis_sfcga使空间数据库支持2D和3D的数据操作，需要再安装SFGAL库

dnf install libxml2-devel json-c cmake sqlite-devel libtiff-devel libcurl-devel protobuf protobuf-c -y

geos
wget http://download.osgeo.org/geos/geos-3.9.1.tar.bz2
tar -xvjf geos-3.9.1.tar.bz2
cd geos-3.9.1
mkdir build && cd build && cmake -DCMAKE_BUILD_TYPE=Release .. && cmake --build . && cmake --build . --target install

proj
wget http://download.osgeo.org/proj/proj-9.5.0.tar.gz
tar -zxvf proj-9.5.0
mkdir build && cd build && cmake .. && cmake --build . && cmake --build . --target install

gdal
wget http://download.osgeo.org/gdal/3.9.2/gdal-3.9.2.tar.gz
tar -zxvf gdal-3.9.2.tar.gz
cd gdal-3.9.2
mkdir build && cd build && cmake -DGDAL_USE_GEOS=ON -DGEOS_INCLUDE_DIR=/usr/local/include -DGEOS_LIBRARY=/usr/local/lib .. && cmake --build . && cmake --build . --target install

mkdir build && cd build && cmake --DCMAKE_BUILD_TYPE=Release .. && cmake --build . && cmake --build . --target install

# protobuf
# wget https://github.com/protocolbuffers/protobuf/releases/download/v28.1/protobuf-28.1.tar.gz
# cd protobuf-28.1
# cd third_party/
# wget https://github.com/abseil/abseil-cpp/releases/download/20240722.0/abseil-cpp-20240722.0.tar.gz
# tar -zxvf abseil-cpp-20240722.0.tar.gz
# mv abseil-cpp abseil-cpp-bak
# mv abseil-cpp-20240722.0 abseil-cpp
# cd ..
# mkdir build && cd build && cmake -Dprotobuf_BUILD_TESTS=OFF .. && cmake --build . && cmake --build . --target install
# 
# protobuf-c
# wget https://github.com/protobuf-c/protobuf-c/releases/download/v1.5.0/protobuf-c-1.5.0.tar.gz
# tar zxvf protobuf-c-1.5.0.tar.gz
# cd protobuf-c-1.5.0
# ./configure 
# make && make install
#

wget https://github.com/postgis/postgis/archive/refs/tags/3.3.7.tar.gz -O postgis-3.3.7.tar.gz
tar -zcvf postgis-3.3.7.tar.gz
./configure --without-protobuf
make && make install

pg_stat_monitor 插件下载

wget https://github.com/percona/pg_stat_monitor/archive/refs/tags/2.1.0.tar.gz -O pg_stat_monitor-2.1.0.tar.gz
tar -zxvf pg_stat_monitor-2.1.0.tar.gz
mv pg_stat_monitor-2.1.0/ postgresql-16.4/contrib/
cd postgresql-16.4/contrib/
make && make install

pg_cron 插件下载

wget https://github.com/citusdata/pg_cron/archive/refs/tags/v1.6.4.tar.gz -O pg_cron-v1.6.4.tar.gz
tar -zxvf pg_cron-v1.6.4.tar.gz
cd postgis-3.3.7
./autogen.sh && ./configure --enable-lto

修改pg配置文件

echo "shared_preload_libraries = 'pg_stat_monitor,pg_cron'" >> /pgdatabase/data/postgresql.conf
echo "cron.database_name = 'postgres'" >> /pgdatabase/data/postgresql.conf
echo "cron.timezone = 'PRC'" >> /pgdatabase/data/postgresql.conf

创建插件

CREATE EXTENSION IF NOT EXISTS pg_stat_monitor;
CREATE EXTENSION IF NOT EXISTS postgis;

-- \c pgcron
CREATE EXTENSION IF NOT EXISTS pg_cron;

如遇报错

CREATE EXTENSION IF NOT EXISTS postgis;
ERROR:  could not load library "/usr/local/pgsql/lib/postgis-3.so": libproj.so.25: 无法打开共享对象文件: 没有那个文件或目录

CREATE EXTENSION IF NOT EXISTS postgis;
ERROR:  could not load library "/usr/local/pgsql/lib/postgis-3.so": libgeos_c.so.1: 无法打开共享对象文件: 没有那个文件或目录

添加lib到配置文件

echo "/usr/local/lib" >> /etc/ld.so.conf
echo "/usr/local/lib64" >> /etc/ld.so.conf
重新加载
ldconfig

重启数据库后继续创建postgis插件

postgres=# CREATE EXTENSION IF NOT EXISTS postgis;
CREATE EXTENSION

创建pg_corn专用库

迁移数据

创建用户

管理员用户登录创建普通用户

CREATE USER testwong WITH PASSWORD 'Mm19890425';
CREATE DATABASE testdb owner testwong;
GRANT ALL ON DATABASE testdb TO testwong;
GRANT USAGE ON SCHEMA cron TO testwong;
\c testdb;
-- 使用Superuser用户登录数据库并创建插件
CREATE EXTENSION IF NOT EXISTS postgis;

用户登录语句

psql -d postgres -U wangcw -W
psql -d testdb -U testwong -W

数据库完备，备份还原

逻辑备份脚本

pg_dump参数

-h host，指定数据库主机名，或者 IP 
-p port，指定端口号 
-U user，指定连接使用的用户名 
-W，按提示输入密码 
dbname，指定连接的数据库名称，实际上也是要备份的数据库名称。 
-f,--file：输出到指定文件中 
-F,--format=c|d|t|p： 
 c 为自定义格式，也是二进制格式，压缩存储，只能使用 pg_restore 来还原, 可
以指定还原的表, 编辑 TOC 文件, 定制还原的顺序, 表, 索引等。 
 d 为目录 
 t 表示输出为 tar 包 
 p 为纯文本 SQL，大库不推荐； 
-j,--jobs=num：指定并行导出的并行度 
-a,--data-only：只导出数据，不导出表结构 
-c,--clean：是否生成清理该数据库对象的语句，比如 drop table 
-C,--create：是否输出一条创建数据库语句 
-n,--schema：只转存匹配 schema 的模式内容 
-N,--exclude-scheam：不转存匹配 schema 的模式内容 
-O,--no-owner，不设置导出对象的所有权 
-s,--schema-only：只导致对象定义模式，不导出数据 
-t,--table：只转存匹配到的表，视图，序列，可以使用多个-t 匹配多个表 
-T,--exclude-table：不转存匹配到的表。 
--inserts：使用 insert 命令形式导出数据，这种方式比默认的 copy 方式慢很多，但是可
用于将数据导入到非 PostgreSQL 数据库。 
--column-inserts：导出的数据，有显式列名 
--no-privileges 去掉权限
--no-owner 去掉所有者

#!/bin/bash

# PostgreSQL数据库相关信息
db_host="172.16.1.18"
db_port="5432"
db_name="testdb"
db_user="testwong"
db_password="db_password"

# 备份存储目录
backup_dir="/pgdatabase/backupfile"

# 保留备份的天数
retention_days=7

# 创建备份目录
mkdir -p $backup_dir

# 备份文件名
backup_file="$backup_dir/backup_${db_name}_$(date +'%Y%m%d%H%M%S').dump"

# 执行备份
PGPASSWORD=$db_password pg_dump -h $db_host -p $db_port -U $db_user -F c -b -v -f "$backup_file" $db_name

if [ $? -eq 0 ]; then
# gzip $backup_file
echo "数据库备份成功: $backup_file"

# 删除旧的备份文件
find $backup_dir -name "backup_*.dump" -type f -mtime +$retention_days -exec rm -f {} \;
else
echo "数据库备份失败."
fi

备份文件为二进制文件，压缩效果不明显，不使用压缩

可以添加crontab任务备份

mkdir /pgdatabase/backupscript
mkdir /pgdatabase/backupfile
vim /pgdatabase/backupscript/logic_backup.sh
chmod +x /pgdatabase/backupscript/logic_backup_dbname.sh
crontab -e
0 2 * * * /pgdatabase/backupscript/logic_backup_dbname.sh

逻辑备份还原

-a 或 --data-only：只恢复数据，不恢复数据定义，大对象和序列值也会被恢复。
-c 或 --clean：在重新创建数据库对象之前清除它们。
-C 或 --create：在恢复一个数据库之前先创建它。如果还指定了–clean，在连接到目标数据库之前重建它。
-d dbname 或 --dbname=dbname：连接到数据库dbname并且直接恢复到该数据库中。
-e 或 --exit-on-error：恢复期间碰到错误就退出，默认恢复结束时显示一个错误计数。
-f filename 或 --file=filename：为生成的脚本指定输出文件，或在与-l选项一起使用时为列表指定输出文件
-F format 或 --format=format：指定归档的格式。
-I index 或 --index=index：只恢复提及的索引的定义，可以通过写多个-I开关指定多个索引。
-j number-of-jobs 或 --jobs=number-of-jobs：使用并发任务运行 pg_restore，这个选项只支持自定义和目录归档格式，输入必须是一个常规文件或目录。
-l 或 --list：列出归档的内容的表格。这个操作的输出能被用作-L选项的输入。注意如果把-n或-t这样的过滤开关与-l一起使用，它们将会限制列出的项。
-L list-file 或 --use-list=list-file：只恢复在list-file中列出的归档元素，并且按照它们出现在该文件中的顺序进行恢复。注意如果把-n或-t这样的过滤开关与-L一起使用，它们将会进一步限制要恢复的项。
-n shcema 或 --schema=schema：只恢复在被提及的模式中的对象。可以用多个-n开关来指定多个模式。这可以与-t选项组合在一起只恢复一个指定的表。
-N schema 或 --exclude-schema=schema：不恢复所提及方案中的对象。可以用多个-N开关指定多个要被排除的方案，优先级高于 -n。
-O 或 --no-owner：恢复时排除原库 owner。
-P function-name(argtype [, …]) 或–function=function-name(argtype [, …])：只恢复被提及的函数，可以使用多个-P开关指定多个函数。
-s 或 --schema-only：只恢复数据定义，不恢复数据。
-S username 或 --superuser=username：指定在禁用触发器时要用的超级用户名。只有使用–disable-triggers时这个选项才相关。
-t table 或 --table=table：只恢复所提及的表，“table”包括视图、物化视图、序列和外部表。
-T trigger 或 --trigger=trigger：只恢复所提及的触发器，可以用多个-T开关指定多个触发器。
-v 或 --verbose：指定冗长模式。
-V 或 --version：打印该pg_restore的版本并退出。
-x 或 --no-privileges 或 --no-acl：阻止恢复访问特权（授予/收回命令）。
-1 或 --single-transaction：将恢复作为单一事务执行（即把发出的命令包裹在BEGIN/COMMIT中），这个选项隐含了–exit-on-error。
–disable-triggers：只有在执行一个只恢复数据的恢复时，这个选项才相关。它指示pg_restore在装载数据时执行命令临时禁用目标表上的触发器。目前，为–disable-triggers发出的命令必须以超级用户身份完成。因此你还应该用-S指定一个超级用户名，或者更好的方法是以一个PostgreSQL超级用户运行pg_restore。
–enable-row-security：只有在恢复具有行安全性的表的内容时，这个选项才相关。默认情况下，pg_restore将把row_security设置为 off 来确保所有数据都被恢复到表中。注意当前这个选项还要求转储处于INSERT格式，因为COPY FROM不支持行安全性。
–if-exists：使用条件命令（即增加一个IF EXISTS子句）删除数据库对象。只有指定了–clean时，这个选项才有效。
–no-comments：不输出恢复注释的命令。
–no-data-for-failed-tables：默认情况下，即便表的创建命令失败（例如因为表已经存在），表数据也会被恢复。通过这个选项，对这类表的数据会被跳过。只有当直接恢复到一个数据库中时这个选项才有效，在产生 SQL脚本输出时这个选项不会产生效果。
–no-publications：不输出恢复publication的命令。
–no-security-labels：不输出恢复安全标签的命令。
–no-subscriptions：不输出恢复subscription的命令。
–no-tablespaces：不输出命令选择表空间。通过这个选项，所有的对象都会被创建在恢复时的默认表空间中。
–section=sectionname：只恢复提及的小节。小节的名称可以是pre-data、data或者post-data。可以把这个选项指定多次来选择多个小节。默认值是恢复所有小节。 数据小节包含实际的表数据以及大对象定义。Post-data 项由索引定义、触发器、规则和除已验证的检查约束之外的约束构成。Pre-data 项由所有其他数据定义项构成。
–strict-names：要求每一个模式（-n/–schema）以及表（-t/–table）限定词匹配备份文件中至少一个模式/表。
–use-set-session-authorization：输出 SQL 标准的SET SESSION AUTHORIZATION命令取代ALTER OWNER命令来决定对象拥有权。这会让转储更加兼容标准，但是依赖于转储中对象的历史，可能无法正确恢复。

创建数据库

drop database testdb;
create database testdb;

# Superuser用户
psql -d postgres -U wangcw -W
drop database if exists testdb with (force); # 模拟还原，将原库强制删除，其他情况切忌直接执行
create database testdb owner testwong;
# grant create on database testdb to testwong;
# grant pg_read_server_files to testwong;
\c testdb;
create extension if not exists postgis;

dump文件转为sql

pg_restore backup_testdb_20240918213621.dump -C -f backup_testdb_20240918213621.sql

编辑 list文件，使用 ; 注销不想要的行，然后再还原指定表，可编辑注释掉postgis等插件创建语句，防止还原时报错(建库时创建好插件也可忽略错误)

pg_restore -l backup_testdb_20240918213621.dump > backup_testdb_20240918213621.list
pg_restore -L backup_testdb_20240918213621.list backup_testdb_20240918213621.dump -C -d testdb

还原库

pg_restore backup_testdb_20240918213621.dump -d testdb -U testwong -W

忽略报错

[postgres@rhelpg backupfile]$ pg_restore backup_testdb_20240918213621.sql -d tes
tdb -U testwong -W
Password:
pg_restore: error: could not execute query: ERROR:  must be owner of extension postgis
Command was: COMMENT ON EXTENSION postgis IS 'PostGIS geometry and geography spatial types and functions';

注意：函数和存储过程没有备份，还原时间较久（可以按CPU核数增加参数 -j）

物理备份还原

pgbackrest

部署

dnf install tar make gcc libxml2 libxml2-devel openssl openssl-devel bzip2 bzip2-devel libyaml yaml -y
wget https://github.com/pgbackrest/pgbackrest/archive/refs/tags/release/2.53.1.tar.gz
tar -zcvf 2.53.1.tar.gz
cd pgbackrest-release-2.53.
cd src
./configure
make && make install 

编译成功 结尾：
install -d /usr/local/bin
install -m 755 pgbackrest /usr/local/bin

编译报错
configure: error: library 'yaml' is required

安装 libyaml-devel
wget https://dl.rockylinux.org/pub/rocky/9/CRB/x86_64/os/Packages/l/libyaml-devel-0.2.5-7.el9.x86_64.rpm
rmp -ivh libyaml-devel-0.2.5-7.el9.x86_64.rpm

编译报错
./build-code postgres
./build-code: error while loading shared libraries: libpq.so.5: cannot open shared object file: No such file or directory
make: *** [Makefile:256：postgres/interface.auto.c.inc] 错误 127

find / -name libpq.so.5
/usr/local/pgsql/lib/libpq.so.5
vim /etc/ld.so.conf
添加
/usr/local/pgsql/lib

ldconfig

查看信息

pgbackrest help
pgbackrest help backup

创建备份专用系统用户

groupadd pgbackrest
useradd -r -g pgbackrest pgbackrest
mkdir -p home/pgbackrest
chown pgbackrest:pgbackrest home/pgbackrest

创建配置日志目录
mkdir -p /var/log/pgbackrest
chmod 770 /var/log/pgbackrest
chown -R postgres:postgres /var/log/pgbackrest

创建仓库路径
mkdir -p /var/lib/pgbackrest
chmod 750 /var/lib/pgbackrest
chown -R postgres:postgres /var/lib/pgbackrest

创建配置目录和文件
mkdir -p /etc/pgbackrest/conf.d
touch /etc/pgbackrest/pgbackrest.conf
chmod 640 /etc/pgbackrest/pgbackrest.conf
chown -R postgres:postgres /etc/pgbackrest

生成备份所需密码
openssl rand -base64 48
j3mAZQ9qxKlRs//bavE6g/lecggc5nlGT/rT50kMH9TO9fqGqNpStE+YDMJ5V2KJ

修改配置文件
repo1-path=/var/lib/pgbackrest  -- PG 实例的data路径
repo1-retention-full=2  -- 保持最近2个full backup 文件

vim /etc/pgbackrest/pgbackrest.conf
[pgdatabase]
pg1-path=/pgdatabase/data
pg1-port=5432
pg1-socket-path=/tmp
pg1-user=wangcw
# pg1-host-user=wangcw
[global]
repo1-path=/pgdatabase/pgbackrest
repo1-retention-full=2
repo1-cipher-pass=j3mAZQ9qxKlRs//bavE6g/lecggc5nlGT/rT50kMH9TO9fqGqNpStE+YDMJ5V2KJ
repo1-cipher-type=aes-256-cbc
start-fast=y
process-max=3
# backup-user=wangcw
log-path=/var/log/pgbackrest
[global:archive-push]
compress-level=3


数据库配置项修改
su - postgres
vim /pgdatabase/data/postgresql.conf
wal_level = replica
max_wal_senders = 3
archive_mode = on
archive_command = 'pgbackrest --stanza=pgdatabase archive-push %p'
:/log_truncate_on_rotation = on

创建.pgpass文件，
su - postgres
vim .pgpass
格式
hostname:port:database:username:password 

localhost:5432:postgres:postgres:postgres #或者(指定端口所有数据库) localhost:5432:*:postgres:postgres
127.0.0.1:5432:postgres:postgres:postgres #或者(指定端口所有数据库) 127.0.0.1:5432:*:postgres:postgres

localhost:5432:*:wangcw:Mm19890425
127.0.0.1:5432:*:wangcw:Mm19890425

chmod 600 .pgpass

设置环境变量
vim .bashrc
export PGPASSFILE=~/.pgpass

配置生效
source ./.bashrc

配置pg动态库链接
ln -s /usr/local/pgsql/lib/libpq.so.5 /usr/lib64/libpq.so.5

重启数据库实例
systemctl restart postgresd

备份还原示例

数据库目录
PGDATA=/pgdatabase/data

环境准备

1 创建归档和备份目录
mkdir -p /pgdatabase/pgbackrest/pgarchive /pgdatabase/pgbackrest/pgbackup
chown -R postgres:postgres /pgdatabase/pgbackrest/pgarchive /pgdatabase/pgbackrest/pgbackup
2 开启归档模式
vim postgresql.conf
wal_level = ‘replica’
archive_mode = ‘on’
3 创建复制权限用户
创建replication权限的角色, 或者超级用户的角色。
create role repuser nosuperuser replication login connection limit 5 encrypted password ‘repuser’;

4 创建节(初始化stanza)
pgbackrest –stanza=pgdatabase –log-level-console=info stanza-create
更新配置文件后更新
pgbackrest –stanza=pgdatabase –log-level-console=info stanza-upgrade

[postgres@rhelpg ~]$ pgbackrest --stanza=pgdatabase --log-level-console=info stanza-create
2024-09-23 04:08:26.739 P00   INFO: stanza-create command begin 2.53.1: --exec-id=202041-d961c11e --log-level-console=info --log-path=/var/log/pgbackrest --pg1-path=/pgdatabase/data --pg1-port=5432 --pg1-socket-path=/tmp --pg1-user=wangcw --repo1-cipher-pass=<redacted> --repo1-cipher-type=aes-256-cbc --repo1-path=/var/lib/pgbackrest --stanza=pgdatabase
2024-09-23 04:08:27.342 P00   INFO: stanza-create for stanza 'pgdatabase' on repo1
2024-09-23 04:08:27.841 P00   INFO: stanza-create command end: completed successfully (1104ms)

执行初始化后，在仓库目录下创建如下目录：
tree -L 2 /var/lib/pgbackrest

[postgres@rhelpg ~]$ tree -L 2 /var/lib/pgbackrest
/var/lib/pgbackrest
├── archive
│   └── pgdatabase
└── backup
    └── pgdatabase

4 directories, 0 files

5 检查配置(测试archive 命令)

pgbackrest –stanza=pgdatabase –log-level-console=info check

[postgres@rhelpg data]$ pgbackrest --stanza=pgdatabase --log-level-console=info check
2024-09-23 04:31:53.601 P00   INFO: check command begin 2.53.1: --exec-id=202398-abe9201d --log-level-console=info --log-path=/var/log/pgbackrest --pg1-path=/pgdatabase/data --pg1-port=5432 --pg1-socket-path=/tmp --pg1-user=wangcw --repo1-cipher-pass=<redacted> --repo1-cipher-type=aes-256-cbc --repo1-path=/var/lib/pgbackrest --stanza=pgdatabase
2024-09-23 04:31:54.204 P00   INFO: check repo1 configuration (primary)
2024-09-23 04:31:54.605 P00   INFO: check repo1 archive for WAL (primary)
2024-09-23 04:31:54.906 P00   INFO: WAL segment 00000001000000020000009D successfully archived to '/var/lib/pgbackrest/archive/pgdatabase/16-1/0000000100000002/00000001000000020000009D-2577190898ff3d39170108cac612aaf4ae588c6d.gz' on repo1
2024-09-23 04:31:54.906 P00   INFO: check command end: completed successfully (1306ms)

这将强制PostgreSQL服务器切换WAL段并使用archive_command归档该WAL段。

示例1 全量备份恢复

–type=full 全量备份。
–type=incr 增量备份仅备份自上次备份(全量、增量、差异)以来的更改。
–type=diff 差异备份备份自上次全量备份以来的所有更改。
–exclude 排除某个目录或文件，例如排除log日志目录 –exclude=log/

https://pgbackrest.org/user-guide-rhel.html#backup

https://pgbackrest.org/user-guide-rhel.html#restore

全量备份恢复步骤如下：

执行全量备份
pgbackrest –stanza=pgdatabase –log-level-console=info backup –type=full –start-fast –compress –process-max=4

2024-09-23 04:51:13.702 P00   INFO: backup command begin 2.53.1: --compress --exec-id=202497-c2051e95 --log-level-console=info --log-path=/var/log/pgbackrest --pg1-path=/pgdatabase/data --pg1-port=5432 --pg1-socket-path=/tmp --pg1-user=wangcw --process-max=4 --repo1-cipher-pass=<redacted> --repo1-cipher-type=aes-256-cbc --repo1-path=/pgdatabase/pgbackrest --repo1-retention-full=2 --stanza=pgdatabase --start-fast --type=full
2024-09-23 04:51:14.605 P00   INFO: execute non-exclusive backup start: backup begins after the requested immediate checkpoint completes
2024-09-23 04:51:16.007 P00   INFO: backup start archive = 0000000100000002000000A1, lsn = 2/A1000060
2024-09-23 04:51:16.007 P00   INFO: check archive for prior segment 0000000100000002000000A0
2024-09-23 04:58:39.532 P00   INFO: execute non-exclusive backup stop and wait for all WAL segments to archive
2024-09-23 04:58:39.942 P00   INFO: backup stop archive = 0000000100000002000000A1, lsn = 2/A1000170
2024-09-23 04:58:40.113 P00   INFO: check archive for segment(s) 0000000100000002000000A1:0000000100000002000000A1
2024-09-23 04:58:40.324 P00   INFO: new backup label = 20240923-045114F
2024-09-23 04:58:41.117 P00   INFO: full backup size = 12.7GB, file total = 2258
2024-09-23 04:58:41.117 P00   INFO: backup command end: completed successfully (447416ms)
2024-09-23 04:58:41.117 P00   INFO: expire command begin 2.53.1: --exec-id=202497-c2051e95 --log-level-console=info --log-path=/var/log/pgbackrest --repo1-cipher-pass=<redacted> --repo1-cipher-type=aes-256-cbc --repo1-path=/pgdatabase/pgbackrest --repo1-retention-full=2 --stanza=pgdatabase
2024-09-23 04:58:41.295 P00   INFO: expire command end: completed successfully (178ms)

备份12.7G的数据用了8分钟

–start-fast ：触发快速检查点，而不是等到下一个定时检查点。
–compress ：启用压缩备份。默认情况下压缩是打开的。
–process-max ：可以启动备份以加快备份速度的并发进程数。

查看备份信息
pgbackrest info –stanza=pgdatabase

[postgres@rhelpg ~]$ pgbackrest info --stanza=pgdatabase
stanza: pgdatabase
    status: ok
    cipher: aes-256-cbc

    db (current)
        wal archive min/max (16): 00000001000000020000009F/0000000100000002000000A1

        full backup: 20240923-045114F
            timestamp start/stop: 2024-09-23 04:51:14-04 / 2024-09-23 04:58:39-04
            wal start/stop: 0000000100000002000000A1 / 0000000100000002000000A1
            database size: 12.7GB, database backup size: 12.7GB
            repo1: backup set size: 1.2GB, backup size: 1.2GB

执行全量恢复

停止数据库、删除数据目录文件
systemctl stop postgresd
rm -rf /pgdatabase/data/*
pgbackrest –stanza=pgdatabase –log-level-console=info restore –type=default

[postgres@rhelpg ~]$ pgbackrest --stanza=pgdatabase --log-level-console=info restore --type=default
2024-09-23 05:08:21.377 P00   INFO: restore command begin 2.53.1: --exec-id=202563-90300afc --log-level-console=info --log-path=/var/log/pgbackrest --pg1-path=/pgdatabase/data --process-max=3 --repo1-cipher-pass=<redacted> --repo1-cipher-type=aes-256-cbc --repo1-path=/pgdatabase/pgbackrest --stanza=pgdatabase --type=default
2024-09-23 05:08:21.383 P00   INFO: repo1: restore backup set 20240923-045114F, recovery will start at 2024-09-23 04:51:14
2024-09-23 05:13:43.456 P00   INFO: write updated /pgdatabase/data/postgresql.auto.conf
2024-09-23 05:13:43.909 P00   INFO: restore global/pg_control (performed last to ensure aborted restores cannot be started)
2024-09-23 05:13:43.940 P00   INFO: restore size = 12.7GB, file total = 2258
2024-09-23 05:13:43.941 P00   INFO: restore command end: completed successfully (322566ms)

验证恢复数据
select pg_wal_replay_resume();

示例2 增量备份恢复

执行增量备份恢复步骤如下：

新增测试数据

create table t1(id serial primary key, name varchar(30), create_time timestamp default now());
insert into t1(name) select '用户-' || n from generate_series(1,10000) n;
insert into t1(name) select '增量备份恢复-' || n from generate_series(1,5) n;
select * from t1 where id > 10000;
insert into t1(name) select '增量备份-' || n from generate_series(1,5) n;
select * from t1 where id > 10000;

postgres=# \c testdb;
Password:
You are now connected to database "testdb" as user "wangcw".
testdb=# create table t1(id serial primary key, name varchar(30), create_time timestamp default now());
insert into t1(name) select '用户-' || n from generate_series(1,10000) n;
insert into t1(name) select '增量备份恢复-' || n from generate_series(1,5) n;
select * from t1 where id > 10000;
insert into t1(name) select '增量备份-' || n from generate_series(1,5) n;
select * from t1 where id > 10000;
CREATE TABLE
INSERT 0 10000
INSERT 0 5
  id   |      name      |        create_time
-------+----------------+---------------------------
 10001 | 增量备份恢复-1 | 2024-09-23 17:16:32.89487
 10002 | 增量备份恢复-2 | 2024-09-23 17:16:32.89487
 10003 | 增量备份恢复-3 | 2024-09-23 17:16:32.89487
 10004 | 增量备份恢复-4 | 2024-09-23 17:16:32.89487
 10005 | 增量备份恢复-5 | 2024-09-23 17:16:32.89487
(5 rows)

INSERT 0 5
  id   |      name      |        create_time
-------+----------------+----------------------------
 10001 | 增量备份恢复-1 | 2024-09-23 17:16:32.89487
 10002 | 增量备份恢复-2 | 2024-09-23 17:16:32.89487
 10003 | 增量备份恢复-3 | 2024-09-23 17:16:32.89487
 10004 | 增量备份恢复-4 | 2024-09-23 17:16:32.89487
 10005 | 增量备份恢复-5 | 2024-09-23 17:16:32.89487
 10006 | 增量备份-1     | 2024-09-23 17:16:33.074767
 10007 | 增量备份-2     | 2024-09-23 17:16:33.074767
 10008 | 增量备份-3     | 2024-09-23 17:16:33.074767
 10009 | 增量备份-4     | 2024-09-23 17:16:33.074767
 10010 | 增量备份-5     | 2024-09-23 17:16:33.074767
(10 rows)

执行增量备份
pgbackrest –stanza=pgdatabase –log-level-console=info backup –type=incr

查看备份信息
pgbackrest info

执行增量恢复

恢复到第一个增量备份集 20240207-143144F_20240207-145858I

停止数据库、删除数据目录文件
systemctl stop postgresd
rm -rf /pgdatabase/data/*
pgbackrest –stanza=pgdatabase –set=20240207-143144F_20240207-145858I –type=immediate –delta restore –log-level-console=info

验证恢复数据
#select pg_is_in_recovery();
#select pg_wal_replay_resume();
\c testdb;
select * from t1 where id > 10000;

注：在备份集 20240207-143144F_20240207-145858I 之后的记录没有回复。

示例3 恢复指定数据库

备份实例包括多个数据库时，也可在恢复的时候仅恢复指定的数据。

新建备份仓库
pgbackrest –stanza=pgdatabase stop –log-level-console=info
pgbackrest –stanza=pgdatabase –repo=1 stanza-delete –log-level-console=info
pgbackrest –stanza=pgdatabase stanza-create –log-level-console=info

创建测试数据(testdb1,testdb2)
create database testdb1;
create database testdb2;
\c testdb1;
create table t1(id serial primary key, name varchar(30), create_time timestamp default now());
insert into t1(name) select ‘用户-‘ || n from generate_series(1,10000) n;
\c testdb2;
create table t1(id serial primary key, name varchar(30), create_time timestamp default now());
insert into t1(name) select ‘用户-‘ || n from generate_series(1,10000) n;

执行全量备份
pgbackrest –stanza=pgdatabase –log-level-console=info backup –type=full –start-fast

查看备份集信息
pgbackrest info

删除数据库
drop database testdb1;
drop database testdb2;

停止数据库服务
systemctl stop postgresd

恢复指定数据库(testdb1)
pgbackrest –stanza=pgdatabase –delta –db-include=testdb1 –type=immediate –target-action=promote restore –log-level-console=info

验证恢复数据

注：如上图所示testdb2数据库没有恢复，仅恢复了数据库testdb1

示例4 恢复到指定时间点

恢复到指定时间点验证如下：

#创建测试数据
\c testdb1
insert into t1(name) select ‘恢复到指定时间点-‘ || n from generate_series(1,5) n;
select * from t1 where id > 10000;

记录恢复时间点
#select current_timestamp;
select now(); #2024-02-07 17:33:51.991182+08

再插入5条测试记录
insert into t1(name) select ‘指定时间点后记录-‘ || n from generate_series(1,5) n;
select * from t1 where id > 10000;
select pg_switch_wal();
checkpoint;

执行增量备份
pgbackrest –stanza=pgdatabase backup –type=incr –log-level-console=info

查看备份集信息
pgbackrest –stanza=pgdatabase info –log-level-console=info

恢复到指定时间点
systemctl stop postgresd
pgbackrest –stanza=pgdatabase –delta –type=time –target=”2024-02-07 17:33:51.991182+08” –target-action=promote restore –log-level-console=info

验证恢复数据
\c testdb1;
select * from t1 where t1 where id > 10000;

注：如上图所示，数据仅恢复到了指定时间点记录，之后新增记录没有回复。

示例5 恢复到指定LSN点

恢复到指定LSN点验证如下：

#创建测试数据
\c testdb2
insert into t1(name) select ‘恢复到指定LSN点-‘ || n from generate_series(1,5) n;
select * from t1 where id > 10000;

记录恢复LSN点
select pg_current_wal_lsn(); #0/1E009540

再插入5条测试记录
insert into t1(name) select ‘指定LSN点后记录-‘ || n from generate_series(1,5) n;
select * from t1 where id > 10000;
select pg_switch_wal();
checkpoint;