Sometimes I want to inspect and verify queries made by an 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 the technique described in How to terminate PostgreSQL sessions blog post, but this is not a case here as I want to use a 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 the 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 the 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 the current session

You can log SQL statements during an 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 reading PostgreSQL 9.1 Documentation → Server Configuration → Error Reporting and Logging, especially When To Log and What To Log sections.