Message-ID: <e99b306b8ea1904f2d4334f8852027f968a761b2.camel@cybertec.at>
Subject: Re: Backup restore question "could not locate required checkpoint
 record"
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: A Farkas <frks@tutanota.com>, pgsql-admin@lists.postgresql.org
In-Reply-To: <L_Ra1J3--3-1@tutanota.com>
References: <L_Ra1J3--3-1@tutanota.com>
Organization: Cybertec
Content-Type: text/plain; charset="UTF-8"
MIME-Version: 1.0
Date: Fri, 08 Mar 2019 10:44:49 +0100
User-Agent: Evolution 3.30.5 (3.30.5-1.fc29) 
Content-Transfer-Encoding: 7bit
Precedence: bulk

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