Thread: Point in time recovery

Search the log file for errors?

Query the database(s) to verify that the latest data s there?

Hello Team,

 

I want to know the best way to ensure/verify that the Point in time recovery has done successfully after the crash and the restore.

 

Thanks,

 

Greetings,

Please don't top-post.

* Ron (ronljohnsonjr@gmail.com) wrote:
> Search the log file for errors?

Sadly, this doesn't actually work if you're really doing PITR- you need
to look for the specific message saying "recovery stopping before
commit" or similar, eg:

2020-08-18 12:55:31.240 UTC [16149] LOG:  recovery stopping before commit of transaction 495, time 2020-08-18
12:55:17.227359+00

If you don't see a message along those lines, and you actually specified
a recovery target, chances are good that, for whatever reason, PG didn't
find that recovery target and instead just replayed to the end of WAL,
which isn't what you want if you're specifying a recovery target.

You can see how to take backups, perform restores, and see messages like
the above in the PG log here:

https://learn.crunchydata.com/pg-administration/courses/basic-postgresql-for-dbas-12/pgbackrest/

(which is what I just did to get at the above message, hence the rather
recent timestamp ;)

> Query the database(s) to verify that the latest data s there?

I do generally recommend regular backup and restore procedures that
export the data from the database and then re-import it into a clean
system to verify that everything is consistent.

> On 8/18/20 5:10 AM, Daulat Ram wrote:
> >I want to know the best way to ensure/verify that the Point in time
> >recovery has done successfully after the crash and the restore.

If you're coming from a crash, then that's an entirely different thing
and you need to make sure you replay all WAL that existed at the time of
the crash- you can't do PITR to a point before then or the database
system won't be consistent.

Thanks,

Stephen