pg因invalid primary checkpoint record无法启动

2020-06-28 22:13 abce 阅读(3470) 评论(0) 编辑收藏举报

有个开发环境，因为磁盘空间满了，有人直接将pg_wal下的文件rm了，然后，重启报错

2020-06-28 18:13:59.148 CST   [10387]: LOG:  database system was shut down at 2020-06-28 18:12:41 CST
2020-06-28 18:13:59.148 CST   [10387]: LOG:  invalid primary checkpoint record
2020-06-28 18:13:59.148 CST   [10387]: PANIC:  could not locate a valid checkpoint record
2020-06-28 18:13:59.560 CST   [10342]: LOG:  startup process (PID 10387) was terminated by signal 6: Aborted
2020-06-28 18:13:59.560 CST   [10342]: LOG:  aborting startup due to startup process failure
2020-06-28 18:13:59.581 CST   [10342]: LOG:  database system is shut down

从日志文件也可以看到因为wal segment找不到，而无法启动：

$ more postgresql-Sun.log 
2020-06-28 18:17:25.882 CST   [12259]: LOG:  database system was shut down at 2020-06-28 18:16:48 CST
2020-06-28 18:17:25.882 CST   [12259]: LOG:  recovered replication state of node 1 to 2/F6A001C0
2020-06-28 18:17:25.887 CST   [12216]: LOG:  database system is ready to accept connections
2020-06-28 18:17:25.912 CST   [12269]: LOG:  logical replication apply worker for subscription "b_sub" has started
2020-06-28 18:17:25.922 CST ap a [12272]: LOG:  starting logical decoding for slot "publication_abce"
2020-06-28 18:17:25.922 CST ap a [12272]: DETAIL:  Streaming transactions committing after F/CD215498, reading WAL from F/CD215498.
2020-06-28 18:17:25.922 CST ap a [12272]: ERROR:  requested WAL segment 000000010000000F000000CD has already been removed
2020-06-28 18:17:25.922 CST   [12269]: ERROR:  could not receive data from WAL stream: ERROR:  requested WAL segment 000000010000000F000000CD has already been removed
2020-06-28 18:17:25.923 CST   [12216]: LOG:  background worker "logical replication worker" (PID 12269) exited with exit code 1
2020-06-28 18:17:30.935 CST   [12324]: LOG:  logical replication apply worker for subscription "b_sub" has started
2020-06-28 18:17:30.941 CST ap a [12325]: LOG:  starting logical decoding for slot "publication_abce"
2020-06-28 18:17:30.941 CST ap a [12325]: DETAIL:  Streaming transactions committing after F/CD215498, reading WAL from F/CD215498.
2020-06-28 18:17:30.941 CST ap a [12325]: ERROR:  requested WAL segment 000000010000000F000000CD has already been removed
2020-06-28 18:17:30.941 CST   [12324]: ERROR:  could not receive data from WAL stream: ERROR:  requested WAL segment 000000010000000F000000CD has already been removed
2020-06-28 18:17:30.943 CST   [12216]: LOG:  background worker "logical replication worker" (PID 12324) exited with exit code 1

因为不是重要环境，直接使用pg_resetwal直接重置了wal和其他控制信息，然后重启。

1 2	`$ pg_resetwal` `-f` `/data/pgdata/11/data` `Write-ahead log reset`

pg_resetwal会清空存储在pg_control文件中的wal和其他可选的控制信息。不到万不得已，可别使用这个命令。
执行该工具之后，数据库可以启动，但是可能会包含不一致的数据，因为会有事务部分提交。重启后，建议立即将数据dump出来，运行initdb并reload数据。检查数据一致性并根据需要进行数据修复。

需要显式的指定目录，pg_resetwal不会使用环境变量PGDATA。

参数-f表示强制执行pg_resetwal。