0

We are developing a large application and all our tests are based on a reference Test DB. This test DB is initially loaded by executing different scripts and you can add your own script to add more data.

I want to ease the following flow

  • I have loaded the test DB
  • I do some data modifications to the DB indirectly (e.g by just using the application)
  • I can generate a SQL script of the data modification

I am looking for the most efficient way to do it. So far my best solution is:

  1. I load the test DB under an other name
  2. I take a pg_dump in sql format of the vanilla test DB and the modified one
  3. I do a diff of both pg_dump output

This not really great because:

  • Loading test DB takes time and space
  • Test DB has some time information which will pollute the diff (e.g tables with created_date column)

Any better solution?

tibo
  • 5,326
  • 4
  • 37
  • 53
  • Does this answer your question? [How to log PostgreSQL queries?](https://stackoverflow.com/questions/722221/how-to-log-postgresql-queries) – nbk Aug 06 '23 at 15:04
  • It is not clear to me what you are trying to achieve? What are you going to do with the diff? – Adrian Klaver Aug 06 '23 at 15:55
  • It is an interesting approach that I didn't think about. Log statement could be indeed a starting point to generate a diff but also I want the result SQL to be somewhat compact. For example if a row is inserted and then deleted, I don't really want it. It doesn't feel like a silver bullet but it sure is an interesting angle. – tibo Aug 06 '23 at 15:56
  • @AdrianKlaver The diff is used to enrich the test DB, so we can write tests against newly created data. The script that creates the test DB takes 3 input: the schema generated from the source code of the app, some real world data from a live environment and some custom SQL script provided by the developers. – tibo Aug 06 '23 at 16:00

0 Answers0