Hello. I have a web surveying system backed by PostgreSQL. A common
use case is for internal staff to make a series of changes to some
survey templates (questions, question order, calculations based on
answers, translations, etc...) through the web interface. Then those
changes are tested, and then manually reproduced through the web
interface on production servers. The manual reproduction wastes time
and causes errors, so instead I would like to capture all of the SQL
that affects the modified tables on development machines so we can run
it on the production servers.
I have come up with three approaches to do this:
1. Configure my persistence layer to log all of the prepared
statements and parameters it executes against the database on the
development machines. Write an application to trawl the logs and
capture all of the statements affecting the relevant tables and
convert them into regular SQL.
2. Patch the persistence layer to log the exact SQL I want.
3. For the development machines, attach triggers to all of the
relevant tables so that on update, delete, or insert it inserts an
equivalent SQL statement to a separate table along with an
incrementing primary key. Then just select the stored statements in
order by primary key to get SQL.
I can do any of the three, but they're all time consuming, and I
imagine what I am trying to do is not that rare. Is there an easier
way I missed?
Thank you in advance for any help.
-Mike