Logging Queries in Postgres

Postgres logs can be enabled by turning switches in postgresql.conf file. The location of

postgresql.conf file is at

Windows : <Installation Directory of Postgres>/PostgreSQL/<version>/data/postgresql.conf

Linux : /etc/postgresql/<version>/main/postgresql.conf

Look for the following line

#log_statement = 'none'         # none, ddl, mod, all

Uncomment the line by removing #

The various options that can be supplied:

none : No logging

ddl : Only DDL statements like Create, ALTER, DROP

mod : All ddl statements and INSERT, UPDATE, DELETE, TRUNCATE and COPY FROM.

all : All type of SQL statements including SELECT, CHECKPOINT and VACCUM

Statements that have syntax error are not logged. IF you want to log them than look for following

#log_min_error_statement = error    # values in order of decreasing detail:

uncomment #log_min_error_statement by removing #

You can provide a host of option which are mentioned in postgresql.conf file itself.

For latest current version see the details at 

http://www.postgresql.org/docs/current/static/runtime-config-logging.html

For other version replace current in the link with your version of database. Usually that works.

As the log can be hug in number you can do some finer control by setting log_min_duration_statement.

Be default it is disabled

#log_min_duration_statement = -1

0 : Logs all statement

n : n is a number. Only queries running more than n millisecond will be logged. So if you put 10, then only queries running more than 10 will be logged.

Restart the postgres service. In windows, you can do this on service window and in linux, you can look for the postgres server startup script in /etc/init.d

The log files are located in the location mentioned in postgresql.conf file.

In Linux this is usually at /var/log/postgresql.

In Windows it is at <Postgres Installation Directory>\PostgreSQL\9.3\data\pg_log