On 20 Dec 2017, at 13:48, Konstantin Knizhnik <
k.knizhnik@postgrespro.ru> wrote:
On 20.12.2017 16:12, Laurenz Albe wrote:
Konstantin Knizhnik wrote:
I wonder if Postgres community is interested in supporting time travel
queries in PostgreSQL (something like AS OF queries in Oracle:
https://docs.oracle.com/cd/B14117_01/appdev.101/b10795/adfns_fl.htm).
As far as I know something similar is now developed for MariaDB.
I think that would be a good thing to have that could make
the DBA's work easier - all the requests to restore a table
to the state from an hour ago.
Please notice that it is necessary to configure postgres in proper way in order to be able to perform time travels.
If you do not disable autovacuum, then old versions will be just cleaned-up.
If transaction commit timestamps are not tracked, then it is not possible to locate required timeline.
So DBA should make a decision
in advance whether this feature is needed or not.
It is not a proper instrument for restoring/auditing existed database which was not configured to keep all versions.
May be it is better to add special configuration parameter for this feature which should implicitly toggle autovacuum and track_commit_timestamp parameters).
I seem to recall that Oracle handles this by requiring tables that want the capability to live within a tablespace that supports flashback. That tablespace is obviously configured to retain redo/undo logs. It would be nice if the vacuuming process could be configured in a similar manner. I have no idea if it would make sense on a tablespace basis or not, though — I’m not entirely sure how analogous they are between Postgres & Oracle as I’ve never used tablespaces in Postgres.
-Joe