Backup restore question "could not locate required checkpoint record"

From: A Farkas <frks(at)tutanota(dot)com>
To: <pgsql-admin(at)lists(dot)postgresql(dot)org>
Subject: Backup restore question "could not locate required checkpoint record"
Date: 2019-03-08 09:31:35
Message-ID: L_Ra1J3--3-1@tutanota.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin


On a new apt-get install of postgresql 11, I performed the following:
backup (pg_start_backup ('b1'), rsync, pg_stop_backup)
CREATE DATABASE
CREATE TABLE
INSERT INTO TABLE
DROP TABLE
backup (pg_start_backup ('b1'), rsync, pg_stop_backup)

The point is to restore point in time, before DROP.
From the logs:
2019-03-08 10:02:17 EET,6/8,0,LOG,00000,"statement: DROP table test;",,,,,,,,,"pgAdmin 4 - CONN:821988"

So I write recovery.conf:
restore_command='cp /pg-data/xlog/%f %p'
recovery_target_time='2019-03-08 10:02:17 EET'
recovery_target_inclusive='false'
recovery_target_timeline=1

Server says:
postgres(at)postgre01-Virtual-Machine:/pg-data/pglogs$ cat postgresql-2019-03-08_103727..csv
cat: postgresql-2019-03-08_103727..csv: No such file or directory
postgres(at)postgre01-Virtual-Machine:/pg-data/pglogs$ cat postgresql-2019-03-08_103727.csv
2019-03-08 10:37:27.263 EET,,,39151,,5c8229c6.98ef,1,,2019-03-08 10:37:26 EET,,0,LOG,00000,"ending log output to stderr",,"Future log output will go to log destination ""csvlog"".",,,,,,,""
2019-03-08 10:37:27.349 EET,,,39153,,5c8229c7.98f1,1,,2019-03-08 10:37:27 EET,,0,LOG,00000,"database system was interrupted while in recovery at log time 2019-03-08 10:29:57 EET",,"If this has occurred more than once some data might be corrupted and you might need to choose an earlier recovery target.",,,,,,,""
2019-03-08 10:37:27.856 EET,,,39153,,5c8229c7.98f1,2,,2019-03-08 10:37:27 EET,,0,LOG,00000,"starting archive recovery",,,,,,,,,""
2019-03-08 10:37:27.858 EET,,,39153,,5c8229c7.98f1,3,,2019-03-08 10:37:27 EET,,0,LOG,00000,"invalid checkpoint record",,,,,,,,,""
2019-03-08 10:37:27.858 EET,,,39153,,5c8229c7.98f1,4,,2019-03-08 10:37:27 EET,,0,FATAL,XX000,"could not locate required checkpoint record",,"If you are not restoring from a backup, try removing the file ""/var/lib/postgresql/11/main/backup_label"".",,,,,,,""
2019-03-08 10:37:27.859 EET,,,39151,,5c8229c6.98ef,2,,2019-03-08 10:37:26 EET,,0,LOG,00000,"startup process (PID 39153) exited with exit code 1",,,,,,,,,""
2019-03-08 10:37:27.859 EET,,,39151,,5c8229c6.98ef,3,,2019-03-08 10:37:26 EET,,0,LOG,00000,"aborting startup due to startup process failure",,,,,,,,,""
2019-03-08 10:37:27.861 EET,,,39151,,5c8229c6.98ef,4,,2019-03-08 10:37:26 EET,,0,LOG,00000,"database system is shut down",,,,,,,,,""

However:
.backup file says:

START WAL LOCATION: 0/2000060 (file 000000010000000000000002)
STOP WAL LOCATION: 0/2000168 (file 000000010000000000000002)
CHECKPOINT LOCATION: 0/2000098

And human readable wal (pg_waldump) says:
rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 0/02000028, prev 0/0165AF10, desc: RUNNING_XACTS nextXid 569 latestCompletedXid 568 oldestRunningXid 569
rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 0/02000060, prev 0/02000028, desc: RUNNING_XACTS nextXid 569 latestCompletedXid 568 oldestRunningXid 569
rmgr: XLOG        len (rec/tot):    106/   106, tx:          0, lsn: 0/02000098, prev 0/02000060, desc: CHECKPOINT_ONLINE redo 0/2000060; tli 1; prev tli 1; fpw true; xid 0:569; oid 13129; multi 1; offset 0; oldest xid 561 in DB 1; oldest multi 1 in DB 1; oldest/newest commit timestamp xid: 0/0; oldest running xid 569; online
rmgr: Standby     len (rec/tot):     50/    50, tx:          0, lsn: 0/02000108, prev 0/02000098, desc: RUNNING_XACTS nextXid 569 latestCompletedXid 568 oldestRunningXid 569
rmgr: XLOG        len (rec/tot):     34/    34, tx:          0, lsn: 0/02000140, prev 0/02000108, desc: BACKUP_END 0/2000060
rmgr: XLOG        len (rec/tot):     24/    24, tx:          0, lsn: 0/02000168, prev 0/02000140, desc: SWITCH

Why is the checkpoint invalid?
Thank you for any information on the subject.

Regards,
A Farkas

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Laurenz Albe 2019-03-08 09:44:49 Re: Backup restore question "could not locate required checkpoint record"
Previous Message soumitra bhandary 2019-03-08 05:27:40 Re: Installation of Postgres 10.0 and running application with HA without postgres user at OS