关于postgresql:postgresql的增量备份与时间点恢复

postgresql的备份复原形式次要分为三种 sql 转储，文件系统级别备份，间断归档和工夫点复原，每种备份复原形式都有其优缺点，实现形式都不太一样。

新建 compose.yaml 文件

采纳 postgres15 版本

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: {}

启动数据库

$ docker compose up -d

创立数据库表

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)
);

测试插入单条数据

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

测试批量写入数据

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

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

age字段也是一个随机整数

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 $$;

最初执行查问语句

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是和 postgres 容器一起封装的，所以能够应用容器外部的工具执行导出命令

$ 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 参数，能够屡次指定，命令能够改为如下

$ 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 cp demo-postgres:/demo.sql .

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

数据恢复

当初执行 sql 命令清空数据表users

truncate users ;

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

$ 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

为了防止数据恢复时候的某些语句报错，能够在导出数据的时候减少一个参数 -a 或者 --data-only，这样在生成的sql 语句就不蕴含了数据表相干的创立语句

$ 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

因为咱们 compose.yaml 文件的所在文件夹的名称是 pg-backup，同时compose.yaml 文件当中蕴含如下数据，docker容器中的 postgres 默认输入存储地位是/var/lib/postgresql/data，如果是间接装置在宿主机下面，数据存储地位可能是/usr/local/pgsql/data

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

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

/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: 从成员名中删除结尾的“/”

须要 cd 到根目录上面

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

数据恢复

如果 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

[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}，尽管不晓得过后设计的时候是什么起因，然而这个起因导致pgbackrest 命令执行的时候无法访问到对象存储，感觉太坑了
- path: 示意应用 endpoint 参数和 bucket 的信息到 url 中
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

装置依赖

$ sudo apt install libnss3-tools

装置

$ sudo apt install mkcert

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

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

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

软件存在于 apt.postgresql.org，所以能够间接 apt 装置

$ sudo apt install pgbackrest

因为咱们的 postgresql 服务镜像没有蕴含该软件，所以须要从新构建一个镜像

新增.dockerignore

.idea/
.git/
certs/

新增一个 Dockerfile 文件

须要先下载 CA 确保能够应用国内源，最初应用国内源下载加快速度

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/

ADD pgbackrest.conf /etc/pgbackrest/pgbackrest.conf

批改 compose.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: {}

`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

命令更新服务

$ 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.

创立 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

$ 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 backup

当初查看备份文件的信息

$ 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

当初去对象存储的页面发现曾经有归档文件和备份文件

当初去数据库执行数据表创立命令，并且写入一些数据

目前数据表的输出如下

> 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 段，该段能够被归档备份

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

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

> 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，即脏数据产生之前的一个工夫点

当初进行服务

$ docker compose down

调整 compose.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--

因为数据恢复的时候须要敞开数据库，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将在达到复原指标之后进行服务器

批改实现之后，开始启动服务

$ docker compose up -d

查看日志如下，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

当初进行服务

$ 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

最初启动容器并且查看日志如下，输入中有最初一句 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，然而在这个示意的是截止到咱们指定工夫点的以前所有事务都会被执行重放复原

$ 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

当初去查看数据库，发现咱们前面的减少的那些脏数据没有了

> 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--

总体来说，增量备份和依照工夫点复原数据的操作是比拟繁琐的，也踩了很多的坑，心愿有一天 postgres 能够官网反对一个配置选项，能够本人增量备份与依照工夫点复原，当初目前发现比拟靠谱的第三方都曾经写成一个我的项目的备份复原，大略只有 pgbackrest,wal-g,barman 比拟靠谱，前面有趣味能够去尝试一下。

postgresql中文文档

pgbackrest文档

mkcert官网我的项目

测试数据库搭建

`sql`转储实现

概念逻辑

备份存储

数据恢复

文件系统级别备份实现

概念逻辑

备份存储

数据恢复

间断归档和工夫点复原实现

概念逻辑

备份类型

备份与复原逻辑流程

`pgbackrest`配置

创立自签名证书

镜像制作与部署

`postgres`启动命令

`minio`局部

备份与复原操作

小结

浏览参考

站内搜索

关于postgresql:postgresql的增量备份与时间点恢复

测试数据库搭建

sql转储实现

概念逻辑

备份存储

数据恢复

文件系统级别备份实现

概念逻辑

备份存储

数据恢复

间断归档和工夫点复原实现

概念逻辑

备份类型

备份与复原逻辑流程

pgbackrest配置

创立自签名证书

镜像制作与部署

postgres启动命令

minio局部

备份与复原操作

小结

浏览参考

站内搜索

`sql`转储实现

`pgbackrest`配置

`postgres`启动命令

`minio`局部