分类目录归档：PostgreSQL恢复

pg_control丢失/损坏处理

发表于 2025 年 3 月 11 日由惜分飞

-l ：设置下一个wal日志名
可以通过 $PGDATA/pg_wal 目录中查找数值最新的WAL段的文件名+1

[postgres@localhost pg_wal]$ ls
00000001000000000000008E  archive_status

-l 00000001000000000000008F

-m：设置下一个和最旧的事务ID
mxid1：下一个多事务ID的安全值可以通过在 $PGDATA/pg_multixact/offsets目录中查找数值最大的文件名+1，然后乘以65536（0×10000）来确定。
mxid2：最旧的事务ID的安全值可以通过$PGDATA/pg_multixact/offsets目录中数字最小的文件名+1，乘以65536（0×10000）来确定。文件名是十六进制

[postgres@localhost pg_wal]$ cd ../pg_multixact
[postgres@localhost pg_multixact]$ ls
members  offsets
[postgres@localhost pg_multixact]$ cd offsets/
[postgres@localhost offsets]$ ls
0000

-m 0x10000,0x10000

-O：设置下一个多事务处理偏移量
安全值可以通过在 $PGDATA/pg_multixact/members 目录中查找数值最大的文件名，+1，然后乘以52352（0xCC80）来确定。文件名为十六进制

[postgres@localhost members]$ ls
0000

-O 0xCC80

-x：设置下一个事务ID
安全值可以通过在$PGDATA/pg_xact目录中查找数值最大的文件名，+1，然后乘以1048576（0×100000）来确定。请注意，文件名是十六进制

[postgres@localhost pg_xact]$ ls -ltr
total 504
-rw------- 1 postgres postgres 262144 Mar  8 01:53 0000
-rw------- 1 postgres postgres 253952 Mar  9 10:54 0001

-x 0x200000

删除postmaster.pid文件
如果正常关闭库,该文件会被自动删除,异常关闭的才需要处理

[postgres@localhost pg_wal]$ pg_resetwal -l 00000001000000000000008F -m 0x10000,0x10000 -O 0xCC80 -x 0x200000 -f $PGDATA
pg_resetwal: error: lock file "postmaster.pid" exists
pg_resetwal: hint: Is a server running?  If not, delete the lock file and try again.
[postgres@localhost pg_wal]$ cd ../
[postgres@localhost data]$ ls -l postmaster.pid
-rw------- 1 postgres postgres 75 Mar  9 11:02 postmaster.pid
[postgres@localhost data]$ rm -rf postmaster.pid

touch pg_control文件

[postgres@localhost data]$ pg_resetwal -l 00000001000000000000008F -m 0x10000,0x10000 -O 0xCC80 -x 0x200000 -f $PGDATA
pg_resetwal: error: could not open file "global/pg_control" for reading: No such file or directory
pg_resetwal: hint: If you are sure the data directory path is correct, execute
  touch global/pg_control
and try again.
[postgres@localhost data]$ touch global/pg_control

重建pg_control

[postgres@localhost data]$ pg_resetwal -l 00000001000000000000008F -m 0x10000,0x10000 -O 0xCC80 -x 0x200000 -f $PGDATA
pg_resetwal: warning: pg_control exists but is broken or wrong version; ignoring it
Write-ahead log reset
[postgres@localhost data]$ pg_ctl start
waiting for server to start....
 done
server started

发表在 PostgreSQL恢复 | 标签为 pg_control丢失恢复, pg_control异常恢复, pg_control强制启动 | 留下评论

pg启动报invalid checkpoint record处理

发表于 2025 年 3 月 9 日由惜分飞

pg库启动报PANIC: could not locate a valid checkpoint record错误

2025-03-09 10:59:10.365 EDT [73013] LOG:  starting PostgreSQL 16.8 on x86_64-pc-linux-gnu, 
                                          compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit
2025-03-09 10:59:10.365 EDT [73013] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2025-03-09 10:59:10.365 EDT [73013] LOG:  listening on IPv6 address "::", port 5432
2025-03-09 10:59:10.367 EDT [73013] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2025-03-09 10:59:10.401 EDT [73018] LOG:  database system was interrupted; last known up at 2025-03-09 10:54:43 EDT
2025-03-09 10:59:11.506 EDT [73018] LOG:  invalid checkpoint record
2025-03-09 10:59:11.508 EDT [73018] PANIC:  could not locate a valid checkpoint record
2025-03-09 10:59:12.004 EDT [73013] LOG:  startup process (PID 73018) was terminated by signal 6: Aborted
2025-03-09 10:59:12.004 EDT [73013] LOG:  aborting startup due to startup process failure
2025-03-09 10:59:12.006 EDT [73013] LOG:  database system is shut down

从报错信息中看,是有无法读取到有效的checkpoint记录导致,初步怀疑是wal异常,检查pg_wal目录,发现wal日志为空

[root@localhost pg_wal]# ls -ltr
total 16388
drwx------  2 postgres postgres        6 Mar  6 08:10 archive_status
[root@localhost pg_wal]#

进一步检查系统操作命令rm删除wal日志命令

cd pg_wal
rm -rf 0000000*

初步确认是由于wal日志被意外删除导致pg库无法启动,尝试重置wal,由于库不是干净关闭,无法直接重置成功

[postgres@localhost log]$ pg_resetwal $PGDATA
The database server was not shut down cleanly.
Resetting the write-ahead log might cause data to be lost.
If you want to proceed anyway, use -f to force reset.

使用pg_resetwal -f强制重置

[postgres@localhost log]$ pg_resetwal -f $PGDATA
Write-ahead log reset

尝试启动pg库成功

[postgres@localhost log]$ pg_ctl start 
waiting for server to start....2025-03-09 11:02:02.609 EDT [73088] LOG:  
redirecting log output to logging collector process
2025-03-09 11:02:02.609 EDT [73088] HINT:  Future log output will appear in directory "log".
 done
server started

2025-03-09 11:02:02.609 EDT [73088] LOG:  starting PostgreSQL 16.8 on x86_64-pc-linux-gnu, 
                                          compiled by gcc (GCC) 8.3.1 20191121 (Red Hat 8.3.1-5), 64-bit
2025-03-09 11:02:02.609 EDT [73088] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2025-03-09 11:02:02.609 EDT [73088] LOG:  listening on IPv6 address "::", port 5432
2025-03-09 11:02:02.610 EDT [73088] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2025-03-09 11:02:02.645 EDT [73092] LOG:  database system was shut down at 2025-03-09 11:01:53 EDT
2025-03-09 11:02:02.650 EDT [73088] LOG:  database system is ready to accept connections

发表在 PostgreSQL恢复 | 标签为 invalid checkpoint record, pg启动异常处理 | 留下评论

pg误删除数据恢复（PostgreSQL delete数据恢复）

发表于 2025 年 3 月 6 日由惜分飞

应用维护人员通过应用程序误删除了一些数据,希望对其进行恢复.通过咨询确认是PostgreSQL数据,wal和arch文件都在,取客户删除数据时间点相关wal文件
QQ20250306-231208

然后让客户提供具体涉及误删除的相关的库和表信息
QQ20250306-230846

通过工具解析wal日志,比较顺利的获取到了undo sql语句

Switch wal to 0000000300000525000000B0 on time 2025-03-05 22:23:14.263661+08
Switch wal to 0000000300000525000000B1 on time 2025-03-05 22:23:14.423082+08
Switch wal to 0000000300000525000000B2 on time 2025-03-05 22:23:15.983833+08
Switch wal to 0000000300000525000000B3 on time 2025-03-05 22:23:17.802107+08
Switch wal to 0000000300000525000000B4 on time 2025-03-05 22:23:18.942125+08
Switch wal to 0000000300000525000000B5 on time 2025-03-05 22:23:20.293585+08
Switch wal to 0000000300000525000000B6 on time 2025-03-05 22:23:21.531484+08
Switch wal to 0000000300000525000000B7 on time 2025-03-05 22:23:24.217501+08
Switch wal to 0000000300000525000000B8 on time 2025-03-05 22:23:27.504164+08
Switch wal to 0000000300000525000000B9 on time 2025-03-05 22:23:29.260754+08
Switch wal to 0000000300000525000000BA on time 2025-03-05 22:23:33.544486+08
Switch wal to 0000000300000525000000BB on time 2025-03-05 22:23:35.568116+08
Switch wal to 0000000300000525000000BC on time 2025-03-05 22:23:37.329659+08
Switch wal to 0000000300000525000000BD on time 2025-03-05 22:23:38.971834+08
Switch wal to 0000000300000525000000BE on time 2025-03-05 22:23:39.922353+08
Switch wal to 0000000300000525000000BF on time 2025-03-05 22:23:40.897294+08

QQ20250306-231657