There is a DB on PostgreSQL. It is necessary to transfer the state of some tables with a certain frequency, for example, once a day. In view of the fact that there may be a lot of data, it is necessary to transfer the so-called diff, and to make a complete snapshot of the database less often. How to implement this diff. Is it possible to make such a request on Postgres (or to do it using hibernate), which would allow checking whether there were any changes on this field, for example, per day and what kind of request is it? (everything is implemented in Java, orm hibernate, json (jackson), spring).

  • to give a difference, there must be something to compare with. Suppose you add a field with the date of the last change to all the tables, you can easily find rows that have been added or modified since a certain date. But here you will not see the deleted lines ... - Mike
  • And also, how should this diff be stored as a table? - Vasiliy Pumpkin
  • well, theoretically yes, you can have the same table as the main one, only with a column the type of change. in which there will be a sign that the record is deleted, added or changed. If you wish, you can lead it with a trigger on the main table, fixing all the changes. but if there are a lot of tables, this approach will be mildly complicated. then you still need to apply all these change logs on the target system - Mike
  • In general, I would suggest not reinventing bicycles. And use the full replication. Or use the oracle golden gate if standard replication doesn’t fit - Mike
  • @Mike, thanks for the help, I'll try to understand all this - Vasiliy Pumpkin

1 answer 1

If you need to keep something like a change log, you can try offline transactions. It will be possible to catch everything. Even if there was a rollback, you can see what they wanted to change. This is like an option http://blog.dalibo.com/2016/08/19/Autonoumous_transactions_support_in_PostgreSQL.html

And if the goal is backup, then yes replication. Although one does not interfere.