Re: Creating complex track changes database - challenge!

Hi Guys,

I have idea already for creating this complex solution.

Please give your notes and tips if you have.

1. Keep all changes within table including:
-adding rows
-deleting
-editing

This can be managed by adding triggers and one additional table where you
can have sum up what was changed.

2. Changing DDL of tables:

I think that creating trigger for metadata should solve the problem. How
can i do it? I do not know already ...:)

3. Changing tables versioning.

It it is possible to save table (back up or something) to disc - i can
check the latest date of change and save table with this date and name.
And create table with all tables changes and version.
What do you think ?

4. Still problem with creating whole database versioning.
I found very interesting link but i not understand how it is works:

https://odetocode.com/blogs/scott/archive/2008/02/02/versioning-databases-change-scripts.aspx

Best,
Jacek

2018-02-26 12:16 GMT+01:00 Łukasz Jarych <jaryszek(at)gmail(dot)com>:

> Hi Manual,
> thank you very much!
>
> Regarding your tool - if it is not supported and it is for specific case
> - i will not use it but figure out something new. I do not even how to
> install this .hs files...
>
> I thought about creating triggers to have all changes to specific tables.
> And for each table name (or number) keep changes in one separate table.
> What do you think about it?
>
> If all you need is saving and restoring specific table states, logical
>> dumps with pg_dump should probably be enough for your needs.
>
>
> Can you explain in details how can i use it?
> What if user add new column? I can save ma table for example as version 3
> and come back to version 1 in the future? (without this new column?)
>
> Best,
> Jacek
>
>
>
> 2018-02-26 12:04 GMT+01:00 Manuel Gómez <targen(at)gmail(dot)com>:
>
>> On Mon, Feb 26, 2018 at 11:44 AM Łukasz Jarych <jaryszek(at)gmail(dot)com>
>> wrote:
>>
>>> I have to:
>>>
>>> 1. Keep all changes within table including:
>>> -adding rows
>>> -deleting
>>> -editing
>>>
>>> 2. Save table with specific state and recover specific state (so go back
>>> to previous table versions) including comparing tables.
>>>
>>> 3. Track all DLL and DML changes with possibility to ho back to previous
>>> version.
>>>
>>
>> Hi,
>>
>> I had similar needs long ago, so I wrote this tool I called Squealer,
>> which would transform a specification of a SQL database schema into some
>> PostgreSQL DDL to create a database that implements the same schema in a
>> logical sense, but actually stores all historical rows, and even permits
>> existing rows to have references to (soft-)deleted rows, all while
>> providing modifiable views that simulate the behavior of a regular table as
>> specified in the input schema through generous use of INSTEAD OF triggers.
>> It works somewhat like having version control for your database.
>>
>> You may find the source code here: https://github.com/mgomezch/squealer
>> Unfortunately, it has hardly any comments, it is completely unmaintained
>> and probably unused anywhere, I have no idea whether it builds with today's
>> libraries, and it does not necessarily break the tradeoffs in this space in
>> a way that fits your use case.
>>
>> Note there are major caveats with keeping all historical data around
>> forever, and the semantics of querying historical data can get complicated,
>> let alone having current data refer to deleted, historical data. I built
>> this for a very specific use case where this was the right design, but
>> please consider very carefully whether this is what you want.
>>
>> Storing your database history forever would take a lot of space.
>> Consider whether you can instead keep a record of changes stored outside
>> the database in some cheap cold storage. Also consider just keeping a set
>> of tables with dynamically structured event records (e.g. JSON fields)
>> partitioned by time ranges and retained only temporarily, perhaps even in a
>> separate database. Any such solution will have significant cost and
>> performance impact if your database bears a nontrivial load, so be careful.
>>
>> You could also just place your database on a PostgreSQL cluster by itself
>> and then keep all WAL segments archived forever, so you could just do
>> point-in-time recovery to any point in the history of your database. The
>> space required would grow very quickly, though, so if you don't really need
>> the full history forever, but only a fixed retention period, you can surely
>> use any of the well-known solutions for PostgreSQL backups that allow for
>> this through WAL archiving and periodic basebackups: e.g. WAL-E, WAL-G,
>> pgBackRest…
>>
>> If all you need is saving and restoring specific table states, logical
>> dumps with pg_dump should probably be enough for your needs.
>>
>
>