`postgresql`的备份与恢复

postgresql的备份恢复方式主要分为三种sql转储，文件系统级别备份，连续归档和时间点恢复，每种备份恢复方式都有其优缺点，实现方式都不太一样。

测试数据库搭建

新建compose.yaml文件

采用postgres15版本

yaml

services:
  postgres:
    image: postgres:15-bullseye
    container_name: demo-postgres
    restart: always
    volumes:
      - demo-postgres-data:/var/lib/postgresql/data
    ports:
      - 127.0.0.1:5432:5432
    environment:
      - POSTGRES_DB=demo
      - POSTGRES_USER=demo
      - POSTGRES_PASSWORD=demo-pass
      - TZ=Asia/Shanghai

volumes:
  demo-postgres-data: {}

services:
  postgres:
    image: postgres:15-bullseye
    container_name: demo-postgres
    restart: always
    volumes:
      - demo-postgres-data:/var/lib/postgresql/data
    ports:
      - 127.0.0.1:5432:5432
    environment:
      - POSTGRES_DB=demo
      - POSTGRES_USER=demo
      - POSTGRES_PASSWORD=demo-pass
      - TZ=Asia/Shanghai

volumes:
  demo-postgres-data: {}

启动数据库

shell

$ docker compose up -d

$ docker compose up -d

创建数据库表

postgresql

CREATE TABLE public.users (
	id serial NOT NULL,
	"name" varchar(45) NOT NULL,
	age int4 NULL,
	"locked" bool NOT NULL DEFAULT false,
	created_at timestamp NOT NULL,
	CONSTRAINT users_pkey PRIMARY KEY (id)
);

CREATE TABLE public.users (
	id serial NOT NULL,
	"name" varchar(45) NOT NULL,
	age int4 NULL,
	"locked" bool NOT NULL DEFAULT false,
	created_at timestamp NOT NULL,
	CONSTRAINT users_pkey PRIMARY KEY (id)
);

测试插入单条数据

postgresql

INSERT INTO users (name, age, created_at) VALUES ('Jim', 18, NOW());

INSERT INTO users (name, age, created_at) VALUES ('Jim', 18, NOW());

测试批量写入数据

通过WHILE i < 5 LOOP 的判断语句决定执行次数

插入数据的name字段通过user前缀拼接一个随机数字写入

age字段也是一个随机整数

postgresql

DO $$
DECLARE
i INTEGER := 1;
BEGIN
  WHILE i < 5 LOOP 
    INSERT INTO users (name, age, created_at) VALUES ('user'||round(random()*i*i), round(random()*i), NOW());
    i = i + 1;
  END LOOP;
END $$;

DO $$
DECLARE
i INTEGER := 1;
BEGIN
  WHILE i < 5 LOOP 
    INSERT INTO users (name, age, created_at) VALUES ('user'||round(random()*i*i), round(random()*i), NOW());
    i = i + 1;
  END LOOP;
END $$;

最后执行查询语句

postgresql

select * from users;

select * from users;

id	name	age	locked	created_at
1	Jim	18	false	`2022-11-18 15:37:22`
2	user0	0	false	`2022-11-18 15:37:33`
3	user0	0	false	`2022-11-18 15:37:33`
4	user1	1	false	`2022-11-18 15:37:33`
5	user13	0	false	`2022-11-18 15:37:33`

`sql`转储实现

概念逻辑
- 创建一个由sql命令组成的文件，当把这个文件回馈给服务器时，服务器将利用其中的sql命令重建与转储时状态一样的数据库
- 采用pgdump或者pg_dumpall工具，可以远程执行，需要有读取数据表的权限
- 转储表现了pg_dump开始运行时刻的数据库快照，在pg_dump运行过程中发生的更新将不会被转储
- pg_dump工作的时候并不阻塞其他的对数据库的操作，但是会阻塞那些需要排它锁的操作，比如大部分形式的ALTER TABLE
- pg_dump实现的sql转储方式，备份和恢复比较简单直接，不大容易遇到什么复杂的问题，由于是整张表或者整个数据库的备份，所以整体资源消耗比较大
- 属于热备份，在数据库服务持续运行的情况下备份与恢复
备份存储

在宿主机上面pg_dump可以采用sudo apt install postgresql-client安装，但是pg_dump如果和docker里面的pg版本对不上的话，执行导出命令会报错如下

pg_dump: error: server version: 15.1 (Debian 15.1-1.pgdg110+1); pg_dump version: 14.5 (Ubuntu 14.5-0ubuntu0.22.04.1)
pg_dump: error: aborting because of server version mismatch

pg_dump: error: server version: 15.1 (Debian 15.1-1.pgdg110+1); pg_dump version: 14.5 (Ubuntu 14.5-0ubuntu0.22.04.1)
pg_dump: error: aborting because of server version mismatch

pg_dump是和postgres容器一起封装的，所以可以使用容器内部的工具执行导出命令

shell

$ docker exec -it demo-postgres pg_dump -h 127.0.0.1 -U demo -d demo -p 5432 -f demo.sql

$ docker exec -it demo-postgres pg_dump -h 127.0.0.1 -U demo -d demo -p 5432 -f demo.sql

参数解释

-h 127.0.0.1指定使用本地数据库
-U demo指定使用账号demo进行操作
-p 5432指定使用端口5432
-d demo指定使用数据库demo
-f demo.sql指定输出到文件demo.sql

上面的命令会在容器的根路径下创建一个demo.sql文件

如果只想转储某几张表的数据，比如users_1和users_2，则需要指定-t参数，可以多次指定，命令可以改为如下

shell

$ docker exec -it demo-postgres pg_dump -h 127.0.0.1 -U demo -d demo -p 5432 -f demo.sql -t users_1 -t users_2

$ docker exec -it demo-postgres pg_dump -h 127.0.0.1 -U demo -d demo -p 5432 -f demo.sql -t users_1 -t users_2

现在需要把该文件从容器内部复制出来

shell

$ docker cp demo-postgres:/demo.sql .

$ docker cp demo-postgres:/demo.sql .

导出的demo.sql包含了数据库demo里面的表users的sql创建语句以及该表关联的SEQUENCE（users_id_seq）的创建语句，users表数据的写入语句

数据恢复

现在执行sql命令清空数据表users

postgresql

truncate users ;

truncate users ;

执行数据恢复命令，该命令和导出命令是非常类似的，各个参数都一样，只是执行的命令从pg_dump转变为psql

shell

$ docker exec -it demo-postgres psql -h 127.0.0.1 -U demo -d demo -p 5432 -f demo.sql

$ docker exec -it demo-postgres psql -h 127.0.0.1 -U demo -d demo -p 5432 -f demo.sql

命令执行的输出有些报错，比如类似下面的，主要是由于导出命令包含了数据表创建语句，Sequence创建语句，主键创建语句，由于在执行的时候没有检测是否存在对应的对象，整个sql文件在执行的时候没有被包含在一个事务当中，所以创建数据表的语句是执行失败的，数据导入是成功的

psql:demo.sql:33: ERROR:  relation "users" already exists
ALTER TABLE
psql:demo.sql:48: ERROR:  relation "users_id_seq" already exists
ALTER TABLE
ALTER SEQUENCE
ALTER TABLE
COPY 5
 setval 
--------
      5
(1 row)

psql:demo.sql:92: ERROR:  multiple primary keys for table "users" are not allowed

psql:demo.sql:33: ERROR:  relation "users" already exists
ALTER TABLE
psql:demo.sql:48: ERROR:  relation "users_id_seq" already exists
ALTER TABLE
ALTER SEQUENCE
ALTER TABLE
COPY 5
 setval 
--------
      5
(1 row)

psql:demo.sql:92: ERROR:  multiple primary keys for table "users" are not allowed

为了避免数据恢复时候的某些语句报错，可以在导出数据的时候增加一个参数-a或者--data-only，这样在生成的sql语句就不包含了数据表相关的创建语句

shell

$ docker exec -it demo-postgres pg_dump -h 127.0.0.1 -U demo -d demo -p 5432 -f demo.sql -a

$ docker exec -it demo-postgres pg_dump -h 127.0.0.1 -U demo -d demo -p 5432 -f demo.sql -a

文件系统级别备份实现

概念逻辑
- 冷备份，为了得到一个可用的备份，数据库服务器必须被关闭，在恢复数据之前你也需要关闭服务器
- 文件系统备份值适合于完整地备份或恢复整个数据库集簇
- 一个文件系统备份通常会比一个sql转储体积更大
- 官网文档原话说这种方法不实用，或者说至少比pg_dump方法差(however, which make this method impractical, or at least inferior to the pg_dump method)
- 实际操作过程当中感觉虽然操作不复杂，但是感觉容易出问题，如果在执行备份或者恢复过程当中忘记关闭数据库的话，一不小心可能就是文件损坏或者数据丢失了，尽量不要使用这个方案
备份存储

由于目前测试的数据库是采用docker容器启动的，但是存储位置被挂载出来了

先关闭docker，执行命令如下

$ docker compose down

$ docker compose down

由于我们compose.yaml文件的所在文件夹的名称是pg-backup，同时compose.yaml文件当中包含如下数据，docker容器中的postgres默认输出存储位置是/var/lib/postgresql/data，如果是直接安装在宿主机上面，数据存储位置可能是/usr/local/pgsql/data

yaml

volumes:
  - demo-postgres-data:/var/lib/postgresql/data

volumes:
  - demo-postgres-data:/var/lib/postgresql/data

所以数据存储在宿主机位置

/var/lib/docker/volumes/pg-backup_demo-postgres-data/_data

/var/lib/docker/volumes/pg-backup_demo-postgres-data/_data

执行备份命令

shell

$ sudo tar -cf backup.tar /var/lib/docker/volumes/pg-backup_demo-postgres-data/_data

$ sudo tar -cf backup.tar /var/lib/docker/volumes/pg-backup_demo-postgres-data/_data

如果执行报错的话，比如报错如下

tar: 从成员名中删除开头的“/”

tar: 从成员名中删除开头的“/”

需要cd到根目录下面

shell

$ cd /
$ tar -cf backup.tar var/lib/docker/volumes/pg-backup_demo-postgres-data/_data

$ cd /
$ tar -cf backup.tar var/lib/docker/volumes/pg-backup_demo-postgres-data/_data

数据恢复

如果backup.tar文件是保存在根目录下面(/)的，在停止服务器的情况下，在根目录下执行（需要慎重）

shell

$ tar -xf backup.tar

$ tar -xf backup.tar

连续归档和时间点恢复实现

连续归档备份与恢复的操作实现中，官方文档给出的实际操作中比较偏向于底层实现逻辑，比较繁琐，所以在连续归档的实现上适合引入第三方的备份回复项目用于实现该目的。

推荐两个开源项目，pgbackrest（基于C语言开发）和wal-g（基于golang开发），二者的实现逻辑是很类似的，在使用过程当中，pgbackrest的文档写的更详细，从基本的工具使用上，发现wal-g操作上报错信息比较难以定位问题，wal-g也没有给出一个比较完整的操作说明，使得很多操作需要依靠自行搜索第三方说明文档（第三方文档的质量也一言难尽）或者去直接猜测，后面可能这个情况会有好转，毕竟wal-g在github上面的star数量还是蛮多的

所以下面主要使用pgbackrest，minio对象存储作为备份数据的仓库

概念逻辑

热备份，可以把一个文件系统级别的备份和WAL文件的备份结合起来，当需要恢复时，我们先恢复文件系统备份，然后从备份的WAL文件中重放来把系统带到一个当前状态
数据集簇目录的pg_wal/子目录下都保持有一个预写式日志WAL，为了保证崩溃后的安全，确保没有提交的更改丢失的机制，事务按顺序写入 WAL，当这些写入刷新到磁盘时，事务被视为已提交之后，后台进程将更改写入主数据库集群文件（也称为堆），如果发生崩溃，WAL 将被重放以使数据库保持一致，WAL 在概念上是无限的，但实际上被分解成称为段的单个 16MB (默认大小)文件， WAL 段遵循命名约定 0000000100000A1E000000FE，其中前 8 位十六进制数字表示时间线，接下来的 16 位数字是逻辑序列号 (LSN)
这种方法只能支持整个数据库集簇的恢复

备份类型

完整备份(Full Backup)
pgBackRest 将数据库集群的全部内容复制到备份中，数据库集群的第一次备份始终是完整备份，pgBackRest 始终能够直接恢复完整备份，完整备份不依赖于完整备份之外的任何文件以保持一致性
差异备份(Differential Backup)
pgBackRest 仅复制自上次完整备份以来发生更改的那些数据库集群文件，pgBackRest 通过复制所选差异备份中的所有文件和先前完整备份中适当的未更改文件来恢复差异备份，差异备份的优点是它比完整备份需要更少的磁盘空间，但是差异备份和完整备份必须都有效才能恢复差异备份
增量备份(Incremental Backup)
pgBackRest 仅复制自上次备份（可以是另一个增量备份、差异备份或完整备份）以来发生更改的那些数据库集群文件，由于增量备份仅包括自上次备份以来更改的文件，因此它们通常比完整备份或差异备份小得多，与差异备份一样，增量备份依赖于其他有效的备份来恢复增量备份，由于增量备份仅包括自上次备份以来的那些文件，因此所有之前的增量备份回到之前的差异、之前的差异备份和之前的完整备份都必须有效才能执行增量备份的恢复，如果不存在差异备份，则所有先前的增量备份都将返回到先前的完整备份，该完整备份必须存在，并且完整备份本身必须有效才能恢复增量备份

备份与恢复逻辑流程

创建pgbackrest的配置文件
创建postgres.conf自定义配置文件
创建Dockerfile重新打包镜像
创建compose.yaml文件编排容器
增加minio对象存储作为数据备份仓库，使用mkcert创建自签名证书使得minio使用https(pgbackrest远程备份仓库只使用https传输数据，没找到配置可以改成http)，pgbackrest支持Azure，GCS,Amazon S3存储，minio对象存储兼容S3协议，所以采用该对象存储
执行stanza创建命令，检查备份状态
测试验证恢复数据

`pgbackrest`配置

新建文件pgbackrest.conf

ini

[demo]
pg1-path=/var/lib/postgresql/data
pg1-user=demo

[global]
repo1-cipher-pass=zWaf6XtpjIVZC5444yXB+cgFDFl7MxGlgkZSaoPvTGirhPygu4jOKOXf9LO4vjfO
repo1-cipher-type=aes-256-cbc
repo1-path=/test-back
repo1-retention-full=2
repo1-s3-bucket=file-bucket
repo1-s3-endpoint=minio:9000
repo1-s3-key=miniouser
repo1-s3-key-secret=miniopass
repo1-s3-uri-style=path
repo1-s3-region=us-east-1
repo1-storage-verify-tls=n
repo1-type=s3
start-fast=y

[global:archive-push]
compress-level=3

[demo]
pg1-path=/var/lib/postgresql/data
pg1-user=demo

[global]
repo1-cipher-pass=zWaf6XtpjIVZC5444yXB+cgFDFl7MxGlgkZSaoPvTGirhPygu4jOKOXf9LO4vjfO
repo1-cipher-type=aes-256-cbc
repo1-path=/test-back
repo1-retention-full=2
repo1-s3-bucket=file-bucket
repo1-s3-endpoint=minio:9000
repo1-s3-key=miniouser
repo1-s3-key-secret=miniopass
repo1-s3-uri-style=path
repo1-s3-region=us-east-1
repo1-storage-verify-tls=n
repo1-type=s3
start-fast=y

[global:archive-push]
compress-level=3

[demo]: 配置stanza的名称，stanza(节)是数据库集群的配置，它定义了它的位置、备份方式、归档选项等，大多数数据库服务器只有一个 postgres数据库集群，因此只有一个节，而备份服务器将有一个需要备份的每个数据库集群的stanza
pg1-path: 指定postgres的数据存储位置
pg1-user: 指定postgres备用数据所使用的用户名称
repo1-cipher-pass和repo1-cipher-type：表示使用备份仓库加密，确保备份数据的安全，加密参数repo1-cipher-pass的数据是使用命令openssl rand -base64 48随机生成的，repo1-cipher-type指定了加密类型
repo1-path: 表示备份的存储位置，如果使用远程对象存储备份的话，该参数用于在存储桶中的位置，如果不配置，则文件会存储在bucket下面，如果配置为test-back，则文件会存储在bucket的test-back文件夹下面
repo1-retention-full: 表示最多保留两个全量备份，超过的这个数量的旧的全量备份会被清理
repo1-s3-bucket：声明s3的存储桶的名称，该存储桶需要在minio服务启动之后访问控制页面手动创建
repo1-s3-endpoint: 指定s3服务的访问地址，由于是使用docker compose方式部署，所以在postgres容器内部可以使用minio:9000访问对象存储
repo1-s3-key和repo1-s3-key-secret: 表示对象存储的账号密码
repo1-s3-uri-style: 可选项是host和path
- host: 默认选项，表示拼接bucket和endpoint的参数，把域名修改为{bucket}.{endpoint}，详情参考Amazon S3: Virtual Hosting of Buckets，如果配置为这个参数，会导致服务minio地址无法访问，该类型的访问地址形式是http://{BUCKET}.s3.amazonaws.com/{KEY}
- path: 表示使用endpoint参数和bucket的信息到url中，访问地址形式变为http://s3.amazonaws.com/{BUCKET}/{KEY}
repo1-s3-region：配置s3区域的信息
repo1-storage-verify-tls: 由于minio服务的https使用自签名证书，所以证书校验是不安全的，所以该参数需要设置为n
repo1-type: 指定使用s3存储，可选项是
- azure - Azure Blob Storage Service
- cifs - Like posix, but disables links and directory fsyncs
- gcs - Google Cloud Storage
- posix - Posix-compliant file systems
- s3 - AWS Simple Storage Service
start-fast=y: 默认情况下，pgBackRest 将在开始备份之前等待postgres服务下一个定期安排的检查点，检查点完成和备份开始之前可能需要相当长的时间，所以最好设置start-fast=y，这样会触发postgres强制执行检查点，但由于备份频率不高，因此额外的检查点不会对性能产生明显影响，如果数据负载一直很高，需要更加需求使用这个命令
compress-level=3: 可以设置较低的压缩级别来加速archive-push命令的归档

创建自签名证书

采用mkcert开源项目创建证书，BSD-3协议，golang编写，github目前star有38.5k+，项目地址https://github.com/FiloSottile/mkcert

安装依赖

shell

$ sudo apt install libnss3-tools

$ sudo apt install libnss3-tools

安装

shell

$ sudo apt install mkcert

$ sudo apt install mkcert

创建一个文件夹certs，在这个文件夹当中执行命令

指定私钥为private.key, 指定证书文件为public.crt,

shell

$ mkcert -key-file private.key -cert-file public.crt localhost

$ mkcert -key-file private.key -cert-file public.crt localhost

镜像制作与部署

软件存在于 apt.postgresql.org，所以可以直接apt安装

shell

$ sudo apt install pgbackrest

$ sudo apt install pgbackrest

由于我们的postgresql服务镜像没有包含该软件，所以需要重新构建一个镜像

新增.dockerignore

.idea/
.git/
certs/

.idea/
.git/
certs/

新增一个Dockerfile文件

需要先下载CA确保可以使用国内源，最后使用国内源下载加快速度

dockerfile

FROM postgres:15-bullseye

RUN apt update \
    && apt install ca-certificates -y \
    && sed -i "s#http://deb.debian.org#https://mirrors.ustc.edu.cn#g"  /etc/apt/sources.list \
    && apt update \
    && apt install pgbackrest -y \
    && rm -rf /var/lib/apt/lists/ /var/cache/apt/

FROM postgres:15-bullseye

RUN apt update \
    && apt install ca-certificates -y \
    && sed -i "s#http://deb.debian.org#https://mirrors.ustc.edu.cn#g"  /etc/apt/sources.list \
    && apt update \
    && apt install pgbackrest -y \
    && rm -rf /var/lib/apt/lists/ /var/cache/apt/

修改compose.yaml文件如下

yaml

services:
  postgres:
  # image: postgres:15-bullseye
    image: demo-postgres
    command: postgres -c archive_mode=on -c archive_command='pgbackrest --stanza=demo archive-push %p' -c archive_timeout=30
    container_name: demo-postgres
    restart: always
    volumes:
      - demo-postgres-data:/var/lib/postgresql/data
      - ./pgbackrest.conf:/etc/pgbackrest/pgbackrest.conf
    ports:
      - 127.0.0.1:5433:5432
    environment:
      - POSTGRES_DB=demo
      - POSTGRES_USER=demo
      - POSTGRES_PASSWORD=demo-pass
      - TZ=Asia/Shanghai

  minio:
    image: minio/minio
    command: server /data --console-address ":9002"
    restart: always
    container_name: demo-minio
    environment:
      - MINIO_ROOT_USER=miniouser
      - MINIO_ROOT_PASSWORD=miniopass
    volumes:
    - demo-minio:/data
    - ./certs:/root/.minio/certs
    ports:
    - 9002:9002

volumes:
  demo-postgres-data: {}
  demo-minio: {}

services:
  postgres:
  # image: postgres:15-bullseye
    image: demo-postgres
    command: postgres -c archive_mode=on -c archive_command='pgbackrest --stanza=demo archive-push %p' -c archive_timeout=30
    container_name: demo-postgres
    restart: always
    volumes:
      - demo-postgres-data:/var/lib/postgresql/data
      - ./pgbackrest.conf:/etc/pgbackrest/pgbackrest.conf
    ports:
      - 127.0.0.1:5433:5432
    environment:
      - POSTGRES_DB=demo
      - POSTGRES_USER=demo
      - POSTGRES_PASSWORD=demo-pass
      - TZ=Asia/Shanghai

  minio:
    image: minio/minio
    command: server /data --console-address ":9002"
    restart: always
    container_name: demo-minio
    environment:
      - MINIO_ROOT_USER=miniouser
      - MINIO_ROOT_PASSWORD=miniopass
    volumes:
    - demo-minio:/data
    - ./certs:/root/.minio/certs
    ports:
    - 9002:9002

volumes:
  demo-postgres-data: {}
  demo-minio: {}

`postgres`启动命令

wal_level = replica: wal_level的级别决定了多少信息会被写入wal日志文件中，默认是replica，该参数只能在服务器启动的时候配置
- minimal: 会去掉除从崩溃或者立即关机中进行恢复所需的信息之外的所有记录，对于创建或重写永久关系的事务的其余部分，不会记录任何信息，不会包括足够的信息来从基础备份和 WAL 日志中重建数据，要启用 WAL 归档或者流复制，必须使用replica或更高级别（logical）
- replica: 它会写入足够的数据以支持wal归档和复制，包括在后备服务器上运行只读查询
- logical: 与replica相同的信息会被记录，外加上允许从 WAL 抽取逻辑修改集所需的信息，配置该参数会导致wal日志文件增长更快
- archive或hot_standby: 在9.6之前的版本中可以使用，现在这些参数会被映射为replica
archive_mode：可选项off(默认), on, always
- 当wal_level被设置为minimal时，archive_mode不能被启用
- off: 关闭归档
- on: 配置的archive_command命令将完成的WAL段发送到归档存储
- always: WAL归档器在归档恢复或者后备模式下也会被启用，所有从归档恢复的或者用流复制传来的文件将被（再次）归档
archive_command
- 本地 shell 命令被执行来归档一个完成的 WAL 文件段，默认是空
- 如果archive_mode是off，该命令无效
- pgbackrest --stanza=demo archive-push %p表示使用pgbackrest的程序，只备份stanza为demo的数据，该参数对应pgbackrest.conf文件当中的[demo]参数信息，archive-push是pgbackrest的一个子命令，会把一个指定的wal段文件发送到归档，%p是 PostgreSQL 指定要归档的 WAL 段位置的方式，默认的当前位置是postgres的数据存放路径（比如/var/lib/postgresql/data）,%p参数的值类似pg_wal/00000001000000000000001F，表明生成的一个wal文件的位置是/var/lib/postgresql/data/pg_wal/00000001000000000000001F
archive_timeout
- 由于归档命令只在已经完成wal段上调用，如果服务器上面的更新操作比较少，那么事务完成之后，这个操作被纪录到归档存储之间有一个很大的延迟，所以为了进行测试数据按照时间点恢复的功能，目前把该参数调整为一个较小的数字，实际生产环境上需要把该参数配置为60或者更大，单位是秒
- 这个参数是服务器来周期性地切换到一个新的 WAL 段文件，设置为大于零时，只要从上次段文件切换后过了参数所设置的时间量，并且已经有过任何数据库活动（包括一个单一检查点），服务器将切换到一个新的段文件
- 由于强制切换而提早关闭的被归档文件仍然与完整的归档文件长度相同，所以该参数的配置会导致归档存储的磁盘消耗变多
- 这个参数是很有必要的，比如现在数据库新增了几条数据，在经过了archive_timeout的时长之后，wal段完成，之后进行pgbackrest备份命令，归档备份被传输到minio仓库，之后才可以运行按照时间点恢复（即恢复的时间点必须小于最新的被归档的wal日志段）

`minio`部分

启动命令和文件挂载需要注意
自签名证书需要挂载到minio的对应位置，服务在启动的时候会自动使用https
environment: 配置minio的账号密码，目前旧的环境变量MINIO_ACCESS_KEY和MINIO_SECRET_KEY还是可以使用的，运行的时候会产生告警WARNING:MINIO_ACCESS_KEY and MINIO_SECRET_KEY are deprecated. Please use MINIO_ROOT_USER and MINIO_ROOT_PASSWORD
--console-address ":9002"参数表示开启页面访问，由于挂载了证书，所以访问https://127.0.0.1:9002可以访问minio

现在整个项目的配置和文件树如下

.
├── certs
│   ├── private.key
│   └── public.crt
├── .dockerignore
├── compose.yaml
├── Dockerfile
└── pgbackrest.conf

.
├── certs
│   ├── private.key
│   └── public.crt
├── .dockerignore
├── compose.yaml
├── Dockerfile
└── pgbackrest.conf

命令更新服务

shell

$ docker build -t demo-postgres .

$ docker build -t demo-postgres .

备份与恢复操作

现在去查看postgres容器的日志发现报错如下，是因为还没有创建pgbackrest的stanza

demo-postgres  | 2022-11-24 16:10:56.029 CST [79] LOG:  archive command failed with exit code 103
demo-postgres  | 2022-11-24 16:10:56.029 CST [79] DETAIL:  The failed archive command was: pgbackrest --stanza=demo archive-push pg_wal/000000010000000000000001
demo-postgres  | ERROR: [103]: unable to find a valid repository:
demo-postgres  |        repo1: [FileMissingError] unable to load info file '/test-back/archive/demo/archive.info' or '/test-back/archive/demo/archive.info.copy':
demo-postgres  |        FileMissingError: unable to open missing file '/test-back/archive/demo/archive.info' for read
demo-postgres  |        FileMissingError: unable to open missing file '/test-back/archive/demo/archive.info.copy' for read
demo-postgres  |        HINT: archive.info cannot be opened but is required to push/get WAL segments.
demo-postgres  |        HINT: is archive_command configured correctly in postgresql.conf?
demo-postgres  |        HINT: has a stanza-create been performed?
demo-postgres  |        HINT: use --no-archive-check to disable archive checks during backup if you have an alternate archiving scheme.

demo-postgres  | 2022-11-24 16:10:56.029 CST [79] LOG:  archive command failed with exit code 103
demo-postgres  | 2022-11-24 16:10:56.029 CST [79] DETAIL:  The failed archive command was: pgbackrest --stanza=demo archive-push pg_wal/000000010000000000000001
demo-postgres  | ERROR: [103]: unable to find a valid repository:
demo-postgres  |        repo1: [FileMissingError] unable to load info file '/test-back/archive/demo/archive.info' or '/test-back/archive/demo/archive.info.copy':
demo-postgres  |        FileMissingError: unable to open missing file '/test-back/archive/demo/archive.info' for read
demo-postgres  |        FileMissingError: unable to open missing file '/test-back/archive/demo/archive.info.copy' for read
demo-postgres  |        HINT: archive.info cannot be opened but is required to push/get WAL segments.
demo-postgres  |        HINT: is archive_command configured correctly in postgresql.conf?
demo-postgres  |        HINT: has a stanza-create been performed?
demo-postgres  |        HINT: use --no-archive-check to disable archive checks during backup if you have an alternate archiving scheme.

创建stanza之前，需要访问https://127.0.0.1:9002去创建存储桶bucket，名称与pgbackrest.conf文件的repo1-s3-bucket参数保持一致

执行创建stanza命令, 该命令需要指定使用postgres用户，pgbackrest --stanza=demo --log-level-console=info stanza-create是实际执行的命令，指定创建的stanza为demo

shell

$ docker exec -it -u postgres demo-postgres pgbackrest --stanza=demo --log-level-console=info stanza-create

2022-11-24 16:12:34.689 P00   INFO: stanza-create command begin 2.41: --exec-id=96-dada82ab --log-level-console=info --pg1-path=/var/lib/postgresql/data --pg1-user=demo --repo1-cipher-pass=<redacted> --repo1-cipher-type=aes-256-cbc --repo1-path=/test-back --repo1-s3-bucket=file-bucket --repo1-s3-endpoint=minio:9000 --repo1-s3-key=<redacted> --repo1-s3-key-secret=<redacted> --repo1-s3-region=us-east-1 --repo1-s3-uri-style=path --no-repo1-storage-verify-tls --repo1-type=s3 --stanza=demo
2022-11-24 16:12:35.293 P00   INFO: stanza-create for stanza 'demo' on repo1
2022-11-24 16:12:35.320 P00   INFO: stanza-create command end: completed successfully (631ms)

$ docker exec -it -u postgres demo-postgres pgbackrest --stanza=demo --log-level-console=info stanza-create

2022-11-24 16:12:34.689 P00   INFO: stanza-create command begin 2.41: --exec-id=96-dada82ab --log-level-console=info --pg1-path=/var/lib/postgresql/data --pg1-user=demo --repo1-cipher-pass=<redacted> --repo1-cipher-type=aes-256-cbc --repo1-path=/test-back --repo1-s3-bucket=file-bucket --repo1-s3-endpoint=minio:9000 --repo1-s3-key=<redacted> --repo1-s3-key-secret=<redacted> --repo1-s3-region=us-east-1 --repo1-s3-uri-style=path --no-repo1-storage-verify-tls --repo1-type=s3 --stanza=demo
2022-11-24 16:12:35.293 P00   INFO: stanza-create for stanza 'demo' on repo1
2022-11-24 16:12:35.320 P00   INFO: stanza-create command end: completed successfully (631ms)

执行第一次备份，由于没有上一次全量备份，所以备份会自动转换为全量备份，备份命令是需要有一个定时任务定期去执行的，首次备份由于是全量备份，所以首次执行的时候耗时会比较长，后面的都是增量备份，只会备份有变动的数据，所以会比较快

shell

$ docker exec -it -u postgres demo-postgres pgbackrest --stanza=demo --log-level-console=info backup

$ docker exec -it -u postgres demo-postgres pgbackrest --stanza=demo --log-level-console=info backup

现在查看备份文件的信息

shell

$ docker exec -it -u postgres demo-postgres pgbackrest --stanza=demo --log-level-console=info info 

stanza: demo
    status: ok
    cipher: aes-256-cbc

    db (current)
        wal archive min/max (15): 000000010000000000000001/000000010000000000000003

        full backup: 20221124-161252F
            timestamp start/stop: 2022-11-24 16:12:52 / 2022-11-24 16:12:58
            wal start/stop: 000000010000000000000003 / 000000010000000000000003
            database size: 29.1MB, database backup size: 29.1MB
            repo1: backup set size: 3.9MB, backup size: 3.9MB

$ docker exec -it -u postgres demo-postgres pgbackrest --stanza=demo --log-level-console=info info 

stanza: demo
    status: ok
    cipher: aes-256-cbc

    db (current)
        wal archive min/max (15): 000000010000000000000001/000000010000000000000003

        full backup: 20221124-161252F
            timestamp start/stop: 2022-11-24 16:12:52 / 2022-11-24 16:12:58
            wal start/stop: 000000010000000000000003 / 000000010000000000000003
            database size: 29.1MB, database backup size: 29.1MB
            repo1: backup set size: 3.9MB, backup size: 3.9MB

现在去对象存储的页面发现已经有归档文件和备份文件

现在去数据库执行数据表创建命令，并且写入一些数据

目前数据表的输入如下

sql

> select  * from users u ;

1	user0	0	false	2022-11-24 16:14:18
2	user2	1	false	2022-11-24 16:14:18
3	user2	0	false	2022-11-24 16:14:18
4	user16	3	false	2022-11-24 16:14:18
5	user1	1	false	2022-11-24 16:14:18
6	user2	2	false	2022-11-24 16:14:18
7	user3	2	false	2022-11-24 16:14:18
8	user16	3	false	2022-11-24 16:14:18

> select  * from users u ;

1	user0	0	false	2022-11-24 16:14:18
2	user2	1	false	2022-11-24 16:14:18
3	user2	0	false	2022-11-24 16:14:18
4	user16	3	false	2022-11-24 16:14:18
5	user1	1	false	2022-11-24 16:14:18
6	user2	2	false	2022-11-24 16:14:18
7	user3	2	false	2022-11-24 16:14:18
8	user16	3	false	2022-11-24 16:14:18

等待30秒之后，再次执行备份命令，等待时间和archive_timeout参数有关，目的是确保上次数据表创建，写入的事务已经归档产生了新的完整wal段，该段可以被归档备份

shell

$ docker exec -it -u postgres demo-postgres pgbackrest --stanza=demo --log-level-console=info backup

$ docker exec -it -u postgres demo-postgres pgbackrest --stanza=demo --log-level-console=info backup

现在再次往users表写入一些数据，把这些数据当做脏数据

sql

> select  * from users u ;

1	user0	0	false	2022-11-24 16:14:18
2	user2	1	false	2022-11-24 16:14:18
3	user2	0	false	2022-11-24 16:14:18
4	user16	3	false	2022-11-24 16:14:18
5	user1	1	false	2022-11-24 16:14:18
6	user2	2	false	2022-11-24 16:14:18
7	user3	2	false	2022-11-24 16:14:18
8	user16	3	false	2022-11-24 16:14:18
9	user0	0	false	2022-11-24 16:19:35
10	user0	1	false	2022-11-24 16:19:35
11	user5	1	false	2022-11-24 16:19:35
12	user2	2	false	2022-11-24 16:19:35

> select  * from users u ;

1	user0	0	false	2022-11-24 16:14:18
2	user2	1	false	2022-11-24 16:14:18
3	user2	0	false	2022-11-24 16:14:18
4	user16	3	false	2022-11-24 16:14:18
5	user1	1	false	2022-11-24 16:14:18
6	user2	2	false	2022-11-24 16:14:18
7	user3	2	false	2022-11-24 16:14:18
8	user16	3	false	2022-11-24 16:14:18
9	user0	0	false	2022-11-24 16:19:35
10	user0	1	false	2022-11-24 16:19:35
11	user5	1	false	2022-11-24 16:19:35
12	user2	2	false	2022-11-24 16:19:35

现在开始执行数据按照时间点回滚，现在需要回滚到2022-11-24 16:14:20，即脏数据产生之前的一个时间点

现在停止服务

shell

$ docker compose down

$ docker compose down

调整compose.yaml文件如下

yaml

// --snip--
   image: demo-postgres
#   command: postgres -c archive_mode=on -c archive_command='pgbackrest --stanza=demo archive-push %p' -c archive_timeout=30
    command:
      - /bin/sh
      - -c
      - |

           rm -f /var/lib/postgresql/data/postmaster.pid &&
           echo "delete ok" &&
           pgbackrest --stanza=demo --delta --type=time "--target=2022-11-24 16:14:20+08" --target-action=promote restore
// --snip--

// --snip--
   image: demo-postgres
#   command: postgres -c archive_mode=on -c archive_command='pgbackrest --stanza=demo archive-push %p' -c archive_timeout=30
    command:
      - /bin/sh
      - -c
      - |

           rm -f /var/lib/postgresql/data/postmaster.pid &&
           echo "delete ok" &&
           pgbackrest --stanza=demo --delta --type=time "--target=2022-11-24 16:14:20+08" --target-action=promote restore
// --snip--

由于数据恢复的时候需要关闭数据库，postgres又是运行在容器内部的，所以需要调整compose.yaml文件的postgres服务的启动command
执行恢复的时候pgbackrest会检测数据库是否正在运行，有一个判断条件是检测一个pid文件是否存在，所以在执行恢复之前执行删除/var/lib/postgresql/data/postmaster.pid
执行pgbackrest指定恢复类型是按照时间点恢复，时间点参数是上文中的2022-11-24 16:14:20+08，需要带上时区信息，确保时间是绝对的
target-action指定在达到恢复目标时服务器应该立刻采取的动作，默认动作是pause
- pause表示恢复将会被暂停
- promote表示恢复处理将会结束并且服务器将开始接受连接
- shutdown将在达到恢复目标之后停止服务器

修改完成之后，开始启动服务

shell

$ docker compose up -d

$ docker compose up -d

查看日志如下，demo-postgres exited with code 0表示数据恢复成功运行，容器正常退出

shell

$ docker compose logs -f 

Attaching to demo-minio, demo-postgres
demo-postgres  | delete ok
// --snip--
demo-postgres exited with code 0
demo-postgres exited with code 0

$ docker compose logs -f 

Attaching to demo-minio, demo-postgres
demo-postgres  | delete ok
// --snip--
demo-postgres exited with code 0
demo-postgres exited with code 0

现在停止服务

shell

$ docker compose down

$ docker compose down

把compose.yaml文件当中postgres的启动命令修改为原来的正常命令

command: postgres -c archive_mode=on -c archive_command='pgbackrest --stanza=demo archive-push %p' -c archive_timeout=30

command: postgres -c archive_mode=on -c archive_command='pgbackrest --stanza=demo archive-push %p' -c archive_timeout=30

最后启动容器并且查看日志如下，输出中有最后一句last completed transaction was at log time last completed transaction was at log time 2022-11-24 16:14:18.533467+08，表示恢复在2022-11-24 16:14:18.533467+08的最后一个事务停止了，虽然我们指定的是数据库恢复到--target=2022-11-24 16:14:20+08，但是在这个表示的是截止到我们指定时间点的以前所有事务都会被执行重放恢复

shell

$ docker compose up -d
$ docker compose logs -f

// --snip--
demo-postgres  | 2022-11-24 16:39:07.347 CST [28] LOG:  database system was interrupted; last known up at 2022-11-24 16:12:52 CST
demo-postgres  | 2022-11-24 16:39:07.411 CST [28] LOG:  starting point-in-time recovery to 2022-11-24 16:14:20+08
demo-postgres  | 2022-11-24 16:39:07.452 CST [28] LOG:  restored log file "000000010000000000000003" from archive
demo-postgres  | 2022-11-24 16:39:07.466 CST [28] LOG:  redo starts at 0/3000028
demo-postgres  | 2022-11-24 16:39:07.509 CST [28] LOG:  restored log file "000000010000000000000004" from archive
demo-postgres  | 2022-11-24 16:39:07.523 CST [28] LOG:  consistent recovery state reached at 0/3000138
demo-postgres  | 2022-11-24 16:39:07.523 CST [1] LOG:  database system is ready to accept read-only connections
demo-postgres  | 2022-11-24 16:39:07.562 CST [28] LOG:  restored log file "000000010000000000000005" from archive
demo-postgres  | 2022-11-24 16:39:07.613 CST [28] LOG:  restored log file "000000010000000000000006" from archive
demo-postgres  | 2022-11-24 16:39:07.744 CST [28] LOG:  restored log file "000000010000000000000007" from archive
demo-postgres  | 2022-11-24 16:39:07.963 CST [28] LOG:  restored log file "000000010000000000000008" from archive
demo-postgres  | 2022-11-24 16:39:08.000 CST [28] LOG:  recovery stopping before commit of transaction 740, time 2022-11-24 16:19:35.987987+08
demo-postgres  | 2022-11-24 16:39:08.000 CST [28] LOG:  redo done at 0/7000618 system usage: CPU: user: 0.00 s, system: 0.01 s, elapsed: 0.53 s
demo-postgres  | 2022-11-24 16:39:08.000 CST [28] LOG:  last completed transaction was at log time 2022-11-24 16:14:18.533467+08
demo-postgres  | 2022-11-24 16:39:08.069 CST [28] LOG:  restored log file "000000010000000000000007" from archive
demo-postgres  | 2022-11-24 16:39:08.099 CST [28] LOG:  selected new timeline ID: 2
demo-postgres  | 2022-11-24 16:39:08.143 CST [28] LOG:  archive recovery complete
demo-postgres  | 2022-11-24 16:39:08.144 CST [26] LOG:  checkpoint starting: end-of-recovery immediate wait
demo-postgres  | 2022-11-24 16:39:08.162 CST [26] LOG:  checkpoint complete: wrote 50 buffers (0.3%); 0 WAL file(s) added, 0 removed, 4 recycled; write=0.004 s, sync=0.005 s, total=0.019 s; sync files=39, longest=0.002 s, average=0.001 s; distance=65537 kB, estimate=65537 kB
demo-postgres  | 2022-11-24 16:39:08.169 CST [1] LOG:  database system is ready to accept connections

$ docker compose up -d
$ docker compose logs -f

// --snip--
demo-postgres  | 2022-11-24 16:39:07.347 CST [28] LOG:  database system was interrupted; last known up at 2022-11-24 16:12:52 CST
demo-postgres  | 2022-11-24 16:39:07.411 CST [28] LOG:  starting point-in-time recovery to 2022-11-24 16:14:20+08
demo-postgres  | 2022-11-24 16:39:07.452 CST [28] LOG:  restored log file "000000010000000000000003" from archive
demo-postgres  | 2022-11-24 16:39:07.466 CST [28] LOG:  redo starts at 0/3000028
demo-postgres  | 2022-11-24 16:39:07.509 CST [28] LOG:  restored log file "000000010000000000000004" from archive
demo-postgres  | 2022-11-24 16:39:07.523 CST [28] LOG:  consistent recovery state reached at 0/3000138
demo-postgres  | 2022-11-24 16:39:07.523 CST [1] LOG:  database system is ready to accept read-only connections
demo-postgres  | 2022-11-24 16:39:07.562 CST [28] LOG:  restored log file "000000010000000000000005" from archive
demo-postgres  | 2022-11-24 16:39:07.613 CST [28] LOG:  restored log file "000000010000000000000006" from archive
demo-postgres  | 2022-11-24 16:39:07.744 CST [28] LOG:  restored log file "000000010000000000000007" from archive
demo-postgres  | 2022-11-24 16:39:07.963 CST [28] LOG:  restored log file "000000010000000000000008" from archive
demo-postgres  | 2022-11-24 16:39:08.000 CST [28] LOG:  recovery stopping before commit of transaction 740, time 2022-11-24 16:19:35.987987+08
demo-postgres  | 2022-11-24 16:39:08.000 CST [28] LOG:  redo done at 0/7000618 system usage: CPU: user: 0.00 s, system: 0.01 s, elapsed: 0.53 s
demo-postgres  | 2022-11-24 16:39:08.000 CST [28] LOG:  last completed transaction was at log time 2022-11-24 16:14:18.533467+08
demo-postgres  | 2022-11-24 16:39:08.069 CST [28] LOG:  restored log file "000000010000000000000007" from archive
demo-postgres  | 2022-11-24 16:39:08.099 CST [28] LOG:  selected new timeline ID: 2
demo-postgres  | 2022-11-24 16:39:08.143 CST [28] LOG:  archive recovery complete
demo-postgres  | 2022-11-24 16:39:08.144 CST [26] LOG:  checkpoint starting: end-of-recovery immediate wait
demo-postgres  | 2022-11-24 16:39:08.162 CST [26] LOG:  checkpoint complete: wrote 50 buffers (0.3%); 0 WAL file(s) added, 0 removed, 4 recycled; write=0.004 s, sync=0.005 s, total=0.019 s; sync files=39, longest=0.002 s, average=0.001 s; distance=65537 kB, estimate=65537 kB
demo-postgres  | 2022-11-24 16:39:08.169 CST [1] LOG:  database system is ready to accept connections

现在去查看数据库，发现我们后面的增加的那些脏数据没有了

sql

> select  * from users u ;

1	user0	0	false	2022-11-24 16:14:18
2	user2	1	false	2022-11-24 16:14:18
3	user2	0	false	2022-11-24 16:14:18
4	user16	3	false	2022-11-24 16:14:18
5	user1	1	false	2022-11-24 16:14:18
6	user2	2	false	2022-11-24 16:14:18
7	user3	2	false	2022-11-24 16:14:18
8	user16	3	false	2022-11-24 16:14:18

> select  * from users u ;

1	user0	0	false	2022-11-24 16:14:18
2	user2	1	false	2022-11-24 16:14:18
3	user2	0	false	2022-11-24 16:14:18
4	user16	3	false	2022-11-24 16:14:18
5	user1	1	false	2022-11-24 16:14:18
6	user2	2	false	2022-11-24 16:14:18
7	user3	2	false	2022-11-24 16:14:18
8	user16	3	false	2022-11-24 16:14:18

如果不需要指定按照时间点恢复，则可以指定全量恢复，即备份了多少归档wal，恢复到多少，compose.yaml的postgres服务的启动command改为如下即可，主要变动是--type的参数改成immediate

// --snip--
   image: demo-postgres
#   command: postgres -c archive_mode=on -c archive_command='pgbackrest --stanza=demo archive-push %p' -c archive_timeout=30
    command:
      - /bin/sh
      - -c
      - |

           rm -f /var/lib/postgresql/data/postmaster.pid &&
           echo "delete ok" &&
           pgbackrest --stanza=demo --delta --type=immediate --target-action=promote restore
// --snip--

// --snip--
   image: demo-postgres
#   command: postgres -c archive_mode=on -c archive_command='pgbackrest --stanza=demo archive-push %p' -c archive_timeout=30
    command:
      - /bin/sh
      - -c
      - |

           rm -f /var/lib/postgresql/data/postmaster.pid &&
           echo "delete ok" &&
           pgbackrest --stanza=demo --delta --type=immediate --target-action=promote restore
// --snip--

小结

总体来说，增量备份和按照时间点恢复数据的操作是比较繁琐的，也踩了很多的坑，希望有一天postgres可以官方支持一个配置选项，可以自己增量备份与按照时间点恢复，目前发现比较靠谱的第三方都已经写成一个项目的备份恢复，大概只有pgbackrest,wal-g,barman比较靠谱，后面有兴趣可以去尝试一下。

阅读参考

postgresql中文文档

pgbackrest文档

mkcert官方项目

Amazon S3: Virtual Hosting of Buckets

postgresql的备份与恢复 ​

测试数据库搭建 ​

sql转储实现 ​

概念逻辑 ​

备份存储 ​

数据恢复 ​

文件系统级别备份实现 ​

概念逻辑 ​

备份存储 ​

数据恢复 ​

连续归档和时间点恢复实现 ​

概念逻辑 ​

备份类型 ​

备份与恢复逻辑流程 ​

pgbackrest配置 ​

创建自签名证书 ​

镜像制作与部署 ​

postgres启动命令 ​

minio部分 ​

备份与恢复操作 ​

小结 ​

阅读参考 ​

`postgresql`的备份与恢复

测试数据库搭建

`sql`转储实现

概念逻辑

备份存储

数据恢复

文件系统级别备份实现

概念逻辑

备份存储

数据恢复

连续归档和时间点恢复实现

概念逻辑

备份类型

备份与恢复逻辑流程

`pgbackrest`配置

创建自签名证书

镜像制作与部署

`postgres`启动命令

`minio`部分

备份与恢复操作

小结

阅读参考