How to do a point in time recovery in postgresql? I want to do a point in time recovery in postgresql. This is my recovery.conf

standby_mode = 'off'
primary_conninfo = 'host=localhost user=postgres port=5432 krbsrvname=postgres'
restore_command = 'cp /home/duc/backup/database/postgre_backupdb/%f %p'
recovery_target_time = '2017-06-16 9:00:00'


But when I start the server I always get this error in log

2017-06-17 08:01:53.679 ICT [6984] LOG:  database system was shut down at 2017-06-16 16:27:53 ICT
2017-06-17 08:01:53.679 ICT [6984] LOG:  starting point-in-time recovery to 2017-06-16 09:00:00+07
cp: cannot stat '/home/duc/backup/database/postgre_backupdb/00000002.history': No such file or directory
cp: cannot stat '/home/duc/backup/database/postgre_backupdb/000000020000000C00000042': No such file or directory
2017-06-17 08:01:53.682 ICT [6984] LOG:  invalid checkpoint record
2017-06-17 08:01:53.682 ICT [6984] FATAL:  could not locate required checkpoint record
2017-06-17 08:01:53.682 ICT [6984] HINT:  If you are not restoring from a backup, try removing the file "/var/lib/postgresql/9.6/main/backup_label".
2017-06-17 08:01:53.683 ICT [6983] LOG:  startup process (PID 6984) exited with exit code 1
2017-06-17 08:01:53.683 ICT [6983] LOG:  aborting startup due to startup process failure
2017-06-17 08:01:53.684 ICT [6983] LOG:  database system is shut down


I have try to change the recovery_target_time to other time but still get that error.

How do I do a point in time recovery ?
AFAIK in recovery.conf file apply only for the duration of the recovery
it, this file provide the parameters that PostgreSQL needs to

perform an archive recovery of a database, or to act as a log-streaming.

replication standby.


you need to turn on archive_log
the log show you there is no "cp: cannot stat '/home/duc/backup/database/postgre_backupdb/000000020000000C00000042': No such file or directory" have no that file it mean database couldn't make checkpoint to archive it.
use some tool to do backup such as PG_RMNA (ex: pg_rman restore --recovery-target-time '2017-06-16 9:00:00' -B /BACKUP/pgrman -D $PGDATA -U postgres)