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",,"Futurelog 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
interruptedwhile in recovery at log time 2019-03-08 10:29:57 EET",,"If this has occurred more than once some data might
becorrupted 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
requiredcheckpoint 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
startupprocess 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
