how to obtain a list of restore points?

i am implementing restore points using pg_create_restore_point, and will then have a set of scripts to enable my users to restore to one of these restore points. Clearly i have work to do with taking backups and archiving WALs etc, but this is all in hand.

my question is, is there a database table which lists all the available restore points?

cheers
dave

It doesn't look like it. pg_create_restore_point just calls XLogRestorePoint(...) after a bunch of sanity checking, to write the restore point record to the transaction logs. Restore point information is only referenced in recoveryStopsAfter(...), which is part of recovery.

I'm not really sure how PostgreSQL would expose restore points in the catalogs anyway. How would it know a restore point was obsolete and could be removed?

I suggest tracking this yourself using a regular table, where your system deletes from it when it purges old WAL. Your table should contain the restore point name and the LSN (xlog location) at which the point was created. That way you can delete old restore points when you purge the corresponding backups.

So, something like:

INSERT INTO my_restore_points (rpname, rplsn)
VALUES
('some_restore_point', pg_create_restore_point('some_restore_point'))

The other way to do it would be to use pg_xlogdump to scan xlogs for restore points, but that'll be slow and clumsy. Plus I'm not sure 9.2's pg_xlogdump is good enough.

StackExchange Database Administrators Q#118122, answer score: 4