Markus, you should know about not top-posting on these lists. On Thu, 2021-03-11 at 10:52 +0000, Zwettler Markus (OIZ) wrote: > > On Mon, 2021-03-08 at 22:30 +0900, Daisuke Ikeda wrote: > > > I'm running PostgreSQL (enabled timescaledb extension) in the following environment. > > > And I had db pod crashed error several times irregularly. > > > > > > I want to know the cause of this fail. > > > > > > 1) PostgreSQL crash and cannot start Pod.. > > > > > > [20] LOG: invalid primary checkpoint record [20] PANIC: could not locate a valid checkpoint record > > > [1] LOG: startup process (PID 20) was terminated by signal 6: Aborted > > > [1] LOG: aborting startup due to startup process failure > > > > WAL does not contain the checkpoint from before the crash. > > > > > I thought it might be WAL trouble and tried to check with pg_controldata and pg_waldump. > > > --- > > > $ pg_controldata <PostgreSQL data dir> [...] > > > Latest checkpoint location: 15/8FB002C0 > > > Latest checkpoint's REDO location: 15/8FAF3018 > > > Latest checkpoint's REDO WAL file: 00000001000000150000008F > > > [...] > > > > > > I checked the latest checkpoint's REDO WAL file. > > > > > > But the content is all zero data. I checked this situation with "od" command. > > > > > > --- > > > $ od -N 40 -A d -v pg_wal/00000001000000150000008F > > > 0000000 000000 000000 000000 000000 000000 000000 000000 000000 > > > 0000016 000000 000000 000000 000000 000000 000000 000000 000000 > > > 0000032 000000 000000 000000 000000 > > > 0000040 > > > --- > > > > Looks like modifications to this file were lost. > > > > Somehow you lost the data for a WAL segment, and you cannot recover. > > > > Of course, if you have a backup, you know what to do. > > You said use your backup. > > Wouldn't it be enough to start instance crash recovery just before the corrupted wal in that case? > > recovery_target_lsn = <<lsn for 00000001000000150000008F>> > recovery_target_inclusive = off > > No need for a backup. Correct? No, not correct. You could perhaps locate the checkpoint before that in the WAL with "pg_waldump" and manufacture an appropriate "backup_label" to start recovery from there. But by the time you reach 00000001000000150000008F, your recovery would stop, and the database would believe it was recovered. However, there might be some data modifications in the data files from a later point in time, which amounts to data corruption. WAL is called WAL because you always have to write to WAL first, only then to the data files. You cannot have a change to the data files that is not WAL logged. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
