Log database activity for one user (PostgreSQL)
Date: 2009/08/31
Keywords: log PostgreSQL psql postres log_statement database activity
Problem: You need to log all statements executed by a particular user.
Solution: Luckily, you can set a configuration_parameter for a particular role (aka user)
ALTER ROLE name SET log_statement = 'all'
Don't forget to turn it off when you are done:
ALTER ROLE name SET log_statement = 'none'
Be aware that logging has an overhead cost. That's one reason we wanted to only log one user.
Finally, the log file location is your "data directory" unless you configured it to be elsewhere. By default Postgres will prepend the lines you want with "LOG:" so you can grep those lines.
References:
