Backup restore question "could not locate required checkpointrecord"

Поиск
Список
Период
Сортировка
От A Farkas
Тема Backup restore question "could not locate required checkpointrecord"
Дата
Msg-id L_Ra1J3--3-1@tutanota.com
обсуждение исходный текст
Ответы Re: Backup restore question "could not locate required checkpointrecord"
Список 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@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@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

В списке pgsql-admin по дате отправления:

Предыдущее
От: soumitra bhandary
Дата:
Сообщение: Re: Installation of Postgres 10.0 and running application with HAwithout postgres user at OS
Следующее
От: Laurenz Albe
Дата:
Сообщение: Re: Backup restore question "could not locate required checkpointrecord"