0

Is there any system table to check the changes which happened recently in a PostgreSQL DB? I have a PostgreSQL DB with just one table and once a day (time is unknown) updates happens and I want to know when the updates come. How can I check that without check the tables content? (The data is about 10GB+, comparisation is not a good solution, adding an extra column as a flag is not an option in the system, checking the last id of records is not also an option, the id is not increasing). May be it is a good solution if there is any log tracking system which I can see the queries like:

05.11.2021 09:47 - Insert into .....

05.11.2021 09:45 - Update table ...

I also need a tool which I can use via terminal to see differences of tables on Postgresql, have you ever use a tool like this?

Improve this question
1
  • There is nothing built into Postgres that would show you the difference over time. You could create e.g. a trigger that logs all modifications. Or turn on logging of all statements to the Postgres logfile
    – user1822
    Nov 5, 2021 at 7:35

2 Answers 2

Reset to default
1

Object Audit Logging (in the pgAudit extension) may be used to log updates to specific tables.

pgAudit can be installed on Ubuntu through the pgdg APT repository.

Improve this answer
0

For this you could use logical replication. A trigger on the replica can add the information you need to the replicated (or any other) table. The table definitions of the publisher and the subscriber are allowed to differ, as long as the statements on the publisher can be replicated on the replica it will work.

Don't forget to enable the trigger on the replica: ALTER TABLE ENABLE REPLICA TRIGGER See Alter TAble

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.