On 20 Dec 2017, at 13:48, Konstantin Knizhnik <k.knizh...@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 >>> <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
