跟踪由单个PHP脚本调用引起的Postgres数据库活动

Question

Is it possible to track database activity from the beginning, until the end of a PHP script invocation?

For example, let's say I have a script which creates a new record for a user in a database. You fill in a form, hit submit and provided all the data is valid the script proceeds to affect multiple tables inserting data.

The script inserts data into a main user table; (name, address, tel, etc...) is returned a new userid and then also adds a record to a department table (userid, departmentid) and a salary amount to a payroll table (userid, salary).

Is there a way to see all the tables that were affected by the script, the order in which they were used and the data passed to them for a single invocation?

Extra info: I'm using Ubuntu 14.04 LTS with Apache 2.4.7, PHP 5.5.9 and Postgres 9.3.5. I am the sole user of this machine; Everything will be run locally.

Ultimately I want to set the script running and watch what it does in the Postgres DB.

Thank you.

Answer 1

You can do this server-side, by setting log_statement = 'all' and setting a suitable log_line_prefix to include the backend process ID, transaction ID, etc.

You might find it useful to set an application_name in the client connection string or SET application_name = 'blah' in your script, so it's easier to find in the logs too.

Note that you can set log_statement on a per-user basis with ALTER USER blah SET log_statement = 'all', or on a per-database basis with ALTER DATABASE blah SET log_statement = 'all'. You can clear the setting later with ALTER ... RESET log_statement.

Use of CSV format logs makes it easier too.