How to log PostgreSQL queries?

Question

How to enable logging of all SQL executed by PostgreSQL 8.3?

Edited (more info) I changed these lines :

log_directory = 'pg_log'                    
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_statement = 'all'

And restart PostgreSQL service... but no log was created... I'm using Windows Server 2003.

Any ideas?

Answer 1

SELECT set_config('log_statement', 'all', true);

With a corresponding user right may use the query above after connect. This will affect logging until session ends.

Answer 2

You should also set this parameter to log every statement:

log_min_duration_statement = 0

Answer 3

In your data/postgresql.conf file, change the log_statement setting to 'all'.

---

Edit

Looking at your new information, I'd say there may be a few other settings to verify:

• make sure you have turned on the log_destination variable
• make sure you turn on the logging_collector
• also make sure that the log_directory directory already exists inside of the data directory, and that the postgres user can write to it.

Answer 4

FYI: The other solutions will only log statements from the default database—usually postgres—to log others; start with their solution; then:

ALTER DATABASE your_database_name
SET log_statement = 'all';

Ref: https://serverfault.com/a/376888 / log_statement

Answer 5

Edit your /etc/postgresql/9.3/main/postgresql.conf, and change the lines as follows.

Note: If you didn't find the postgresql.conf file, then just type $locate postgresql.conf in a terminal

1. #log_directory = 'pg_log' to log_directory = 'pg_log'

2. #log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' to log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

3. #log_statement = 'none' to log_statement = 'all'

4. #logging_collector = off to logging_collector = on

5. Optional: SELECT set_config('log_statement', 'all', true);

6. sudo /etc/init.d/postgresql restart or sudo service postgresql restart

7. Fire query in postgresql select 2+2

8. Find current log in /var/lib/pgsql/9.2/data/pg_log/

The log files tend to grow a lot over a time, and might kill your machine. For your safety, write a bash script that'll delete logs and restart postgresql server.

Thanks @paul , @Jarret Hardie , @Zoltán , @Rix Beck , @Latif Premani

Answer 6

Just to have more details for CentOS 6.4 (Red Hat 4.4.7-3) running PostgreSQL 9.2, based on the instructions found on this web page:

1. Set (uncomment) log_statement = 'all' and log_min_error_statement = error in /var/lib/pgsql/9.2/data/postgresql.conf.
2. Reload the PostgreSQL configuration. For me, this was done by running /usr/pgsql-9.2/bin/pg_ctl reload -D /var/lib/pgsql/9.2/data/.
3. Find today's log in /var/lib/pgsql/9.2/data/pg_log/