I have released an experimental shadow table management script at:
https://github.com/akaariai/pgsql_shadow_tables
The idea is simple: there are some plpgsql functions which create shadow
tables and insert/update/delete triggers by introspecting pg_catalog and
information_schema. There is very limited support for ALTER TABLE, too.
The usage is simple. For each schema you want to track:
select shadow_meta.update_shadow_schema('public');
After alter table/create table do again the above line and the shadow
tables/triggers should be updated, too.
The above will create a new schema 'shadow_public', and in that schema
shadow tables prefixed with __shadow_.
Now, there is a little trick which allows you to timetravel your
database. The shadow_public contains views which use a session variable
to select the correct snapshot from the shadow table. In short, you
should be able to timetravel by using:
set search_path = 'shadow_public, public';
set test_session_variable.view_time = 'wanted view timestamp';
-- of course, you need test_session_variable in
custom_variable_classes in postgresql.conf
And then you have the snapshot views available. The snapshot views are
named like the original tables, so you might be able to use your
original queries when timetraveling without any rewriting.
Now, for the warnings part: the script is _very_ experimental at the
moment. This is not intended for production use.
I wanted to tell you about the script for two reasons at this early
stage. First, if there is interest in the script, that gives me reason
to actually polish the project into better shape. Second, if there is
already something similar available, then there is not much point in
continuing development of the scripts.
I hope you find the scripts at least interesting if not directly useful,
- Anssi Kääriäinen