Re: Backup restore question "could not locate required checkpoint record"

Hi Farkas,

It seems your are missing the latest archive while performing the point in
time and instead of recovery_target_timeline=1 in recovery.conf file you
must have set recovery_target_timeline='latest' and hot_standby=on.

Enabling these parameters will help in to get the latest timeline from the
archive.

Thanks & Regards,
*Shreeyansh DBA Team*
www.shreeyansh.com

On Fri, Mar 8, 2019 at 3:15 PM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
wrote:

> 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(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",,,,,,,,,""
>
> 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
>
>
>