Re: automatic restore point

On Tue, Jun 26, 2018 at 01:17:31AM +0000, Yotsunaga, Naoki wrote:
> The following is a description of "automatic restore point".
> 【Background】
> When DBA's operation failure, for example DBA accidently drop table,
> the database is restored from the file system backup and recovered by
> using time or transaction ID. The transaction ID is identified from
> WAL.
>
> In order to solve the above problem,
> I'd like propose a feature to implement automatic recording function
> of recovery point.

There is also recovery_target_lsn which is new as of v10. This
parameter is way better than having to track down time or XID, which is
a reason why I developped it. Please note that this is also one of the
reasons why it is possible to delay WAL replays on standbys, so as an
operator has room to fix such operator errors. Having of course cold
backups with a proper WAL archive and a correct retention policy never
hurts.

> 【Setting file】
> Set postgres.conf.
> auto_create_restore_point = on # Switch on/off automatic recording
> function of recovery point. The default value is 'off'.
>
> So what do you think about it? Do you think is it useful?

So basically what you are looking for here is a way to enforce a restore
point to be created depending on a set of pre-defined conditions? How
would you define and choose those?

> Also, when recovering with the current specification, tables other
> than the returned table also return to the state of the specified
> recovery point.
> So, I’m looking for ways to recover only specific tables. Do you have
> any ideas?

Why not using the utility hook which filters out for commands you'd
like to forbid, in this case TRUNCATE or a DROP TABLE on a given
relation? Or why not simply using an event trigger at your application
level so as you can actually *prevent* the error to happen first? With
the last option you don't have to write C code, but this would not
filter TRUNCATE. In short, what you propose looks over-complicated to
me and there are options on the table which allow the problem you are
trying to solve to not happen at all. You could also use the utility
hook to log or register somewhere hte XID/time/LSN associated to a given
command and then use it as your restore point. This could also happen
out of core.
--
Michael