RE: automatic restore point

Hi. Thanks for comments.

>There is also recovery_target_lsn which is new as of v10.
In this method, it is necessary to look at a lsn position before operating.
But I assume the user who did not look it before operating.
So I think that this method is not appropriate.

> 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?
I understand that I was asked how to set up a command to apply this function.
Ex) DROP = on
TRUNCATE = off
Is my interpretation right?
If my interpretation is correct, all the above commands will be applied.
When this function is turned on, this function works when all the above commands are executed.

-------
Naoki Yotsynaga
-----Original Message-----
From: Michael Paquier [mailto:michael(at)paquier(dot)xyz]
Sent: Tuesday, June 26, 2018 2:31 PM
To: Yotsunaga, Naoki/四ツ永 直輝 <yotsunaga(dot)naoki(at)jp(dot)fujitsu(dot)com>
Cc: Postgres hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: 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