Date: Tue, 26 Jun 2018 14:30:35 +0900
From: Michael Paquier <michael@paquier.xyz>
To: "Yotsunaga, Naoki" <yotsunaga.naoki@jp.fujitsu.com>
Cc: Postgres hackers <pgsql-hackers@postgresql.org>
Subject: Re: automatic restore point
Message-ID: <20180626053035.GD31353@paquier.xyz>
References: <8E9126CB6CE2CD42962059AB0FBF7B0DBE3167@g01jpexmbkw23>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="vmttodhTwj0NAgWp"
Content-Disposition: inline
In-Reply-To: <8E9126CB6CE2CD42962059AB0FBF7B0DBE3167@g01jpexmbkw23>
Precedence: bulk


--vmttodhTwj0NAgWp
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On Tue, Jun 26, 2018 at 01:17:31AM +0000, Yotsunaga, Naoki wrote:
> The following is a description of "automatic restore point".
> =E3=80=90Background=E3=80=91
>   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.
>  =20
>   In order to solve the above problem,=20
>   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.

> =E3=80=90Setting file=E3=80=91
>   Set postgres.conf.
>   auto_create_restore_point =3D on # Switch on/off automatic recording
>   function of recovery point. The default value is 'off'.
>=20
> 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=E2=80=99m looking for ways to recover only specific tables. Do you =
have
> any ideas?=20

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

--vmttodhTwj0NAgWp
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEEG72nH6vTowiyblFKnvQgOdbyQH0FAlsxz3sACgkQnvQgOdby
QH3Geg//Z72GIHxcUqjYpEd01T/v2kLzmj+yqO2WX3B3oVOGtHzrjDklc2f2ADX0
FU1fJaUgtoj6iKdO7IjRHLwqqRKLWp7GLDAmP2vWGB2VIFC0fDzoP8RbnTgpmeQK
ZuCZ/Nl2rgYMqLdkUdpu2k7911rHDXM9hG70EPbdm1tY92SWGT4i7CPR01QTyGLn
8KKCVKaEZc7HHwM7+4P/MpZ+kax0gM/UFT28TMh8D1+HVlPILhWLsKdyuXkJbu7l
cGEAcN9we6b/n9QVBcOh+ewoOhbic0lIp79mGrZh/tNgg5Op7Rwm3R8FWdR3Mb6e
RJ0l5wMPzvzC6q0gpU4V4wGYPE1YWi0WFSua+87vaWSCg1kXby/mnB+YMQYYE4Av
Ca6pBgbutRG37JBTW01i9iIO1hfJEXAvbvJwp/jkaiGL4alYJmdGPmWiYDxgClDv
TRFq2/XJ1xyUQ1kMu0OBBXwV///2WdFVnn1c1OOKdSpTF+Y4ul+0jXiv+oMtH0/x
tyoLACeZbIdL/qlSbfAxv3R/XNp9MaIIJh8qcneoUwuG3FaCMiZXV29J/G8O44UC
JAB9/MxfzaX3OGMmgPCVu0BBbGlov4uHj+pLg2gfTqLMaOCoidfeYewYPBKiGqET
cmWYHm3q4F8LjnV9FT4jQelwQSQMEZhg+66W3iqEXmxXB/eQpyI=
=RMyU
-----END PGP SIGNATURE-----

--vmttodhTwj0NAgWp--