From: Laurenz Albe <laurenz.albe@cybertec.at>
To: A Farkas <frks@tutanota.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: Backup restore question "could not locate required checkpoint record"
Date: Fri, 08 Mar 2019 10:44:49 +0100
Message-ID: <e99b306b8ea1904f2d4334f8852027f968a761b2.camel@cybertec.at> (raw)
In-Reply-To: <L_Ra1J3--3-1@tutanota.com>
References: <L_Ra1J3--3-1@tutanota.com>
A Farkas wrote:
> 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",,,,,,,,,""
That means that either you didn't put "recovery.conf" in the data directory,
or you got the name wrong.
The message "starting archive recovery" is not issued if a "recovery_target_time" is set.
In that case, the database would say "starting point-in-time recovery to ...".
Since your "recovery.conf" is not read, PostgreSQL also has no "restore_command", and
in its "pg_xlog" is cannot find the WAL segment that contains the checkpoint from the
backup. That's why you get the error message.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: laurenz.albe@cybertec.at, frks@tutanota.com, pgsql-admin@lists.postgresql.org
Subject: Re: Backup restore question "could not locate required checkpoint record"
In-Reply-To: <e99b306b8ea1904f2d4334f8852027f968a761b2.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox