0

Good Afternoon everyone, I've got a knotty problem, I have a mature Postgres database that has over 500 tables, and is too large to dump. When a product is added to the database a new line is created in many tables and I'd like to check what information is added in order to replicate the functionality in a new piece of software. The reason is the existing software interface is very old and unfriendly so we are replacing it. I can't very easily use the approach suggested here: Search entire postgres database? . Can anyone suggest another solution?

I should clarify i can't modify the database at all, until we have reliably replaced the existing interface and that won't be for months yet.

Thanks

DeadlyWonky
  • 11
  • 7
  • What does that mean? Do you know which tables get a new record created? Do you have some criteria by which you can reliably find it? – 404 Mar 29 '18 at 14:21
  • That's what i'm trying to find out, I need to check what information is added where, the existing system is closed source so i can't interrogate that – DeadlyWonky Mar 29 '18 at 14:23
  • 1
    If you have an isolated system where you can perform whatever action and all these records in unknown tables are created, I would say enable full logging in PG, and that should tell you what is happening. – 404 Mar 29 '18 at 14:25
  • I don't sadly, the only one i have access to is the production server – DeadlyWonky Mar 29 '18 at 14:34

1 Answers1

1

You can set log_statement = 'all' for the short time when you run your experiment. Make sure that your log_line_prefix contains %c. Then you can track all SQL statements that belong to one database session.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • If I do that, it'll grind to a halt. I've got several hundred transactions per second – DeadlyWonky Mar 29 '18 at 15:46
  • @DeadlyWonky You could setup [logical replication](https://www.postgresql.org/docs/current/static/logical-replication.html) and use a [custom plugin](https://www.postgresql.org/docs/current/static/logicaldecoding.html) to decode the statements (e.g. [using Java](https://jdbc.postgresql.org/documentation/head/replication.html#logical-replication)) on a different server. Logical replication adds very little overhead. –  Mar 29 '18 at 15:52
  • @a_horse_with_no_name True, the OP didn't mention the PostgreSQL version used, but I had assumed "a mature database" was something that's too old for logical decoding. – Laurenz Albe Mar 30 '18 at 06:21
  • @DeadlyWonky If you cannot track sessions by database user or something similar so that you can enable logging based on that, and on the other hand logging everything and picking it apart later is too invasive, you'll have to setup a test system. It is work, but it can be done. – Laurenz Albe Mar 30 '18 at 06:24