pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedBackup restore question "could not locate required checkpoint record"
3+ messages / 3 participants
[nested] [flat]
* Backup restore question "could not locate required checkpoint record"
@ 2019-03-08 09:31 A Farkas <frks@tutanota.com>
0 siblings, 1 reply; 3+ messages in thread
From: A Farkas @ 2019-03-08 09:31 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Backup restore question "could not locate required checkpoint record"
@ 2019-03-08 09:44 Laurenz Albe <laurenz.albe@cybertec.at>
parent: A Farkas <frks@tutanota.com>
0 siblings, 1 reply; 3+ messages in thread
From: Laurenz Albe @ 2019-03-08 09:44 UTC (permalink / raw)
To: A Farkas <frks@tutanota.com>; pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Backup restore question "could not locate required checkpoint record"
@ 2019-03-08 13:34 Shreeyansh Dba <shreeyansh2014@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 3+ messages in thread
From: Shreeyansh Dba @ 2019-03-08 13:34 UTC (permalink / raw)
To: A Farkas <frks@tutanota.com>; +Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>; Laurenz Albe <laurenz.albe@cybertec.at>
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.albe@cybertec.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@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
>
>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2019-03-08 13:34 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-03-08 09:31 Backup restore question "could not locate required checkpoint record" A Farkas <frks@tutanota.com>
2019-03-08 09:44 ` Laurenz Albe <laurenz.albe@cybertec.at>
2019-03-08 13:34 ` Shreeyansh Dba <shreeyansh2014@gmail.com>
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