乐趣区

关于运维:运行在容器中Postgres数据库数据损坏后如何恢复

jiezi

前言

在应用 K8S 部署 RSS 全套自托管解决方案 - RssHub + Tiny Tiny Rss, 我介绍了将 RssHub + Tiny Tiny RSS 部署到 K8s 集群中的计划. 其中 TTRSS 会用到 Postgres 存储数据, 也一并部署到 K8s 容器中.

然而最近, 因为一次错误操作, 导致 Postgres 数据库的 WAL 损坏, Postgres 的 Pod 频繁 CrashBackoffLoop. 具体报错如下:

Postgres shutdown exit code 1:

2023-09-27 02:32:17.127 UTC [1] LOG:  received fast shutdown request
2023-09-27 02:32:17.181 UTC [1] LOG:  aborting any active transactions
2023-09-27 02:32:17.434 UTC [1] LOG:  background worker "logical replication launcher" (PID 26) exited with exit code 1
2023-09-27 02:32:17.481 UTC [21] LOG:  shutting down
2023-09-27 02:32:17.880 UTC [1] LOG:  database system is shut down

Postgres “invalid resource manager ID in primary checkpoint record” and “could not locate a valid checkpoint record”

2023-09-27 02:33:23.189 UTC [1] LOG:  starting PostgreSQL 13.5 on x86_64-pc-linux-musl, compiled by gcc (Alpine 10.3.1_git20211027) 10.3.1 20211027, 64-bit
2023-09-27 02:33:23.190 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2023-09-27 02:33:23.190 UTC [1] LOG:  listening on IPv6 address "::", port 5432
2023-09-27 02:33:23.199 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2023-09-27 02:33:23.210 UTC [21] LOG:  database system was shut down at 2023-09-27 02:32:22 UTC
2023-09-27 02:33:23.210 UTC [21] LOG:  invalid resource manager ID in primary checkpoint record
2023-09-27 02:33:23.210 UTC [21] PANIC:  could not locate a valid checkpoint record
2023-09-27 02:33:24.657 UTC [1] LOG:  startup process (PID 21) was terminated by signal 6: Aborted
2023-09-27 02:33:24.657 UTC [1] LOG:  aborting startup due to startup process failure
2023-09-27 02:33:24.659 UTC [1] LOG:  database system is shut down

如上, WAL 文件已损坏, 应该如何复原?

复原步骤

🐾Warning:

目标是启动 Postgres 复原利用的失常运行. 数据可能存在失落.

这是一个 TTRSS feed 利用, 只供我本人应用, 只有能启动起来, 失落一点数据无所谓.

首先, Postgres Pod 在 CrashBackoffLoop, 无奈进行任何操作, 首要任务是使 Pod 启动起来, 不要敞开. 这里通过在 Deployment 增加一些命令来实现. 如下:

apiVersion: apps/v1
kind: Deployment
metadata:
  ...
spec:
  ...
  template:
    spec:
      containers:
      - image: postgres:13-alpine
        imagePullPolicy: IfNotPresent
        name: postgres
        command: ["sh"]
        args: ["-c", "tail -f /dev/null"]
...

如上, 通过 sh -c tail -f /dev/null 实现 Pod 运行. 也能够通过相似 while true; do sleep 30; done; 等相似命令来实现.

Pod 稳固运行后, 通过 kubectl exec -it 进入该 Pod:

k3s kubectl exec -it database-postgres-56cff865bb-92pcx -n rsshub -- /bin/sh

并切换到 postgres 用户:

su - postgres

🐾Warning:

切换到 postgres 用户方可执行上面命令.

接下来就顺利了, 应用 pg_reset_wal 复原 WAL:

先用 --dry-run 看看运行后果:

pg_resetwal --dry-run /var/lib/postgresql/data/

如果后果合乎预期, 再运行:

pg_resetwal /var/lib/postgresql/data/
Write-ahead log reset

胜利后, 退出 Pod. 并移除 Deploy 的 command 和 args 后, postgres 即可失常启动. 如下:

2023-09-27 04:03:25.172 UTC [1] LOG:  starting PostgreSQL 13.5 on x86_64-pc-linux-musl, compiled by gcc (Alpine 10.3.1_git20211027) 10.3.1 20211027, 64-bit
2023-09-27 04:03:25.173 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2023-09-27 04:03:25.173 UTC [1] LOG:  listening on IPv6 address "::", port 5432
2023-09-27 04:03:25.179 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2023-09-27 04:03:25.187 UTC [20] LOG:  database system was shut down at 2023-09-27 04:02:42 UTC
2023-09-27 04:03:25.210 UTC [1] LOG:  database system is ready to accept connections

实现🎉🎉🎉

三人行, 必有我师; 常识共享, 天下为公. 本文由东风微鸣技术博客 EWhisper.cn 编写.

退出移动版