How to inspect PostgreSQL queries

Sometimes I want to inspect and verify queries made by recently created application or hunt a bug that involves more complex queries. The essential part of this process requires altering PostgreSQL server configuration to log SQL statements.

You can view currently executed queries using technique described in How to terminate PostgreSQL sessions blog post, but this is not a case here as I want to use log file.

This information applies to PostgreSQL 9.1 and Debian Wheezy.

Log every SQL statement

You need to alter log_statement parameter inside PostgreSQL configuration file.

$ sudo vi /etc/postgresql/9.1/main/postgresql.conf
[..]
#log_lock_waits = off                   # log lock waits >= deadlock_timeout
log_statement = 'all'                   # none, ddl, mod, all
#log_temp_files = -1                    # log temporary files equal or larger
[..]

Possible values are presented in the following list.

  • none
    Log nothing. This is default value.
  • ddl
    Log data definition statements.
  • mod
    Log data definition and data modifying statements.
  • all
    Log every statement.
Beware, only executed queries will be logged. More detailed information can be found in documentation mentioned in the last section.

Reload database server configuration after modifying log_statement parameter.

$ sudo pg_lsclusters
Version Cluster   Port Status Owner    Data directory                     Log file
9.1     main      5432 online postgres /var/lib/postgresql/9.1/main       /var/log/postgresql/postgresql-9.1-main.log
$ sudo pg_ctlcluster 9.1 main reload

Inspect log file.

$ sudo tail /var/log/postgresql/postgresql-9.1-main.log
2014-11-24 22:10:28 CET LOG:  received SIGHUP, reloading configuration files
2014-11-24 22:10:28 CET LOG:  parameter "log_statement" changed to "all"
[..]
2014-11-24 22:17:02 CET LOG:  statement: CREATE ROLE milosz with login;
2014-11-24 22:17:15 CET LOG:  statement: CREATE DATABASE bookmarks;
2014-11-24 22:17:23 CET LOG:  statement: SELECT pg_catalog.quote_ident(datname) FROM pg_catalog.pg_database  WHERE substring(pg_catalog.quote_ident(datname),1,3)='boo'
	LIMIT 1000
2014-11-24 22:17:28 CET LOG:  statement: ALTER DATABASE bookmarks OWNER TO milosz;

Log SQL statements during current session

You can log SQL statements during active session, but it requires superuser privilege.

$ psql -U milosz bookmarks
Password for user milosz:
psql (9.1.14)
Type "help" for help.

bookmarks=# set log_statement TO 'all';
SET
bookmarks=# CREATE SCHEMA bookmarks;
CREATE SCHEMA
bookmarks=# SET search_path TO bookmarks, public;
SET
bookmarks=# \q

Inspect log file.

$ sudo tail /var/log/postgresql/postgresql-9.1-main.log
2014-11-24 22:34:11 CET LOG:  statement: CREATE SCHEMA bookmarks;
2014-11-24 22:35:08 CET LOG:  statement: SET search_path TO bookmarks, public;

Ending notes

I strongly suggest to read PostgreSQL 9.1 Documentation → Server Configuration → Error Reporting and Logging, especially When To Log and What To Log sections.

Milosz Galazka's Picture

About Milosz Galazka

Milosz is a Linux Foundation Certified Engineer working for a successful Polish company as a system administrator and a long time supporter of Free Software Foundation and Debian operating system.

Gdansk, Poland https://sleeplessbeastie.eu