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?

Harleyharli

This is important: logging_collector = on – Phenocryst

Also, beware that on some GNU/Linux distributions (e.g. Debian Jessie) systemctl restart postgresql may not actually restart PostgreSQL service you have configured (I don't understand why yet), so changes in the configuration file won't be applied. It is safer to use pg_ctl (or pg_ctlcluster on Debian). – Wichern

I just tested this in Ubuntu 16.04 LTS, with PostgreSQL 9.5, and systemctl reload postgresql, systemctl restart postgresql, service postgresql reload and service postgresql restart all render configuration changes effective. – Sideman

In my case (Win 10 desktop, pg12) I had to explicitly enable logging for the actual database using ALTER DATABASE (as in this answer) – Policyholder

Answer 1

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.

Saddlebow

So just curious, does that mean PostgreSQL can't enable logging unless I restart the server? In MySQL, it is as simple as "SET GLOBAL general_log = 'ON';" – Cubicle

I myself don't know if there's a way to do it using a SQL statement like MySQL, but you can send a running server a command to reload the config with pg_ctl reload – Saddlebow

PostgreSQL doesn't have a way to change its parameters via SQL statements yet (as of 9.2). Most logging parameters can be changed without a full server restart, by just doing pg_ctl reload instead. However, it takes a restart to change logging_collector. – Seal

With Postgres 9.4 and the new ALTER SYSTEM command a superuser can set GUC params from SQL. – Hollandia

FWIW - If you do the above 3 things, it works. That is: logging_collector = on, log_destination = 'stderr', log_statement = 'all' I was missing log_destination, and it wasn't working. – Mauney

What about trigger functions? Can they be logged? – Dacoit

The data directory cited in the answer is not its literal name; it refers to the path assigned to the data_directory variable in the PostgreSQL configuration file. On Debian and Ubuntu GNU/Linux, this file usually resides at /etc/postgresql/$v/main/postgresql.conf, where $v is the server version. Also, on the aforementioned systems, when log_destination = 'stderr', the output is written to /var/log/postgresql/postgresql-$v-main.log, where $v is the server version (not to some location inside data_directory). – Sideman

In postgres 10, the folder is now called just "log" and postgres made it automatically for me (at least on macos) – Wagram

For those using Docker: if you don't want to create a configuration file just to enable logging then add this to your docker-compose.yml: command: postgres -c 'log_statement=all' – Cud

For the reload, SELECT pg_reload_conf() might work; the bundled postgresql.conf.sample says: If you edit the file on a running system, you have to SIGHUP the server for the changes to take effect, run "pg_ctl reload", or execute "SELECT pg_reload_conf()". Some parameters, which are marked below, require a server shutdown and restart to take effect. – Policyholder

Scratch SELECT pg_reload_conf() and pg_ctl reload; in my case only the service reload did the trick. It appears that the logging setting only applies to new connections (i.e. those that are already alive - such as from a connection pool - remain in non-logging state) so until you terminate and recreate them you are probably not going to see the logs that you want to see; which is kind of the whole point in enabling logging! – Policyholder

Isn't this kinda wrong ? It only logs FAULTY queries. I want to read EVERY query, including the ones that successfully ran. How do I do that ? – Frunze

Answer 2

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

Picoline

On debian stretch, I also had to uncomment # log_destination = 'stderr' in the configuration file before this worked. – Magree

I've following your step carefully and it doesn't works. Does it need restart? – Nubile

if you don't want to write a bash script but just want the logs to overwrite monthly do this: log_filename = 'postgresql-%d.log' and no it won't overwrite after each restart, it will append for each day and overwrite each month. Of course there are different days depending on month 28,29,30,31 -- but you get the idea. – Blackout

I discovered you can run select pg_reload_config(); since Postgres 9.0 rather than restarting the service in step 6. – Dagostino

It might be worth nothing that changing logging_collector = on requires a server restart - i.e. running SELECT pg_reload_conf(); will work with the other postgresql.conf parameters, but not that one - you can check this by running SELECT name, pending_restart FROM pg_settings WHERE name = 'logging_collector'; after the reload. It's a pity - I'd consider that a niggle at best or a bug at worst. – Georgiannageorgianne

Answer 3

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

Boatman

This should be the accepted answer - the only one that has practical value! – Policyholder

The quoted statements work in Postgres 9.4 or later, but the leading paragraph is false. Setting log_statement = all in postgresql.conf for the respective database cluster - like the accepted answer suggests - affects all databases of that cluster - which can be overruled by per-database settings. – Hollandia

reconnect psql to get effect. – Semimonthly

Answer 4

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.

Aeolotropic

It's generally cleaner to use SET log_statement = 'all' or (for transaction level) SET LOCAL log_statement = 'all'. You might also be interested in the client_min_messages and log_min_messages settings. – Williams

This is great, since I want to log just the messages of my connection. Unfortunately I get: permission denied to set parameter "log_statement" since my user is not superuser. – Mccubbin

You may ask DB administrator for grants executing the function. GRANT { EXECUTE | ALL [ PRIVILEGES ] } ON { FUNCTION function_name ( [ [ argmode ] [ arg_name ] arg_type [, ...] ] ) [, ...] | ALL FUNCTIONS IN SCHEMA schema_name [, ...] } TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ] – Aeolotropic

Answer 5

You also need add these lines in PostgreSQL and restart the server:

log_directory = 'pg_log'                    
log_filename = 'postgresql-dateformat.log'
log_statement = 'all'
logging_collector = on

Kipton

logging_collector=on is necessary – Teal

Works like a charm. I was confused by the comment on the config file saying Required to be on for csvlogs, thinking this option was to log query output and not just statements, but it's not the case. – Etra

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

Answer 6

Set log_statement to all:

Error Reporting and Logging - log_statement

Pyrenees

Answer 7

+1 to above answers. I use following config

log_line_prefix = '%t %c %u ' # time sessionid user
log_statement = 'all'

Cogan

Answer 8

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/

Wakeen

You don't need to restart - a pg_ctl reload is sufficient, and does not interrupt connections. Not convinced this answer adds anything to those already here. – Williams

@CraigRinger Thanks for the comment, that's quite an important fact. I will update the answer once I've tried your suggestion. I wrote this answer primarily for reference for myself, because at the time I had very little experience with UNIX, and I wanted to have all the necessary information in one place (e.g. the locations of postgresql.conf and the log files). – Anatolio

Answer 9

There is an extension in postgresql for this. It's name is "pg_stat_statements". https://www.postgresql.org/docs/9.4/pgstatstatements.html

Basically you have to change postgresql.conf file a little bit:

shared_preload_libraries= 'pg_stat_statements'
pg_stat_statements.track = 'all'

Then you have to log in DB and run this command:

create extension pg_stat_statements;

It will create new view with name "pg_stat_statements". In this view you can see all the executed queries.

Measles

Answer 10

You need to run the query below then restart PostgreSQL to enable logging persistently. *The parameter with ALTER SYSTEM SET is set to postgresql.auto.conf rather than postgresql.conf:

ALTER SYSTEM SET log_statement = 'all';

And, you need to run either of the queries below then restart PostgreSQL to disable logging persistently:

ALTER SYSTEM RESET log_statement;

Or:

ALTER SYSTEM SET log_statement = 'none';

You can also run the query below then need to restart PostgreSQL to enable logging persistently:

ALTER SYSTEM SET log_min_duration_statement = 0;

And, you can also run either of the queries below then need to restart PostgreSQL to disable logging persistently:

ALTER SYSTEM RESET log_min_duration_statement;

Or:

ALTER SYSTEM SET log_min_duration_statement = -1;

Be careful, these queries below cannot enable or disable logging persistently:

SET SESSION log_statement = 'all'
SET log_statement = 'all'
SET LOCAL log_statement = 'all'

SET SESSION log_min_duration_statement = 0;
SET log_min_duration_statement = 0;
SET LOCAL log_min_duration_statement = 0;

Or:

RESET log_statement;
SET SESSION log_statement = 'none'
SET log_statement = 'none'
SET LOCAL log_statement = 'none'

RESET log_min_duration_statement;
SET SESSION log_min_duration_statement = -1;
SET log_min_duration_statement = -1;
SET LOCAL log_min_duration_statement = -1;

Squawk

Answer 11

When using containers it is easiest to add -c log_statement=all as a command-line argument.

Docker example:

docker run \
    --detach \
    --name mypostgres \
    --env POSTGRES_PASSWORD=mypassword \
    postgres \
    -c log_statement=all
docker logs --follow mypostgres

Compose example:

services:
  mypostgres:
    image: postgres:16.0
    environment:
      POSTGRES_PASSWORD: mypassword
    command: "-c log_statement=all"

docker compose up --detach
docker compose logs --follow mypostgres

Buttons

Answer 12

You should also set this parameter to log every statement:

log_min_duration_statement = 0

Vial

Answer 13

Dynamically we can enable/disable the logging in 2 ways

1. Change the global variables in DB and reload the configuration a) Set log_statement = 'all'; or set log_min_duration_statement = 0; b) select pg_reload_conf();
2. From the Linux command line, edit the postgres configuration file, change the log related parameters log_min_duration_statement = 0 log_statement = 'all' Reload the configuration file su - postgres /usr/bin/pg_ctl reload

In both these cases, we should not be doing a Postgres restart. We can dynamically enable/disable logging with configuration reload.

I hope this should be helpful.

Cessionary

Answer 14

I was trying to set the log_statement in some postgres config file but in fact the file was not read by our postgres.

I confirmed that using the request :

select *
from pg_settings

[...]
log_statement   none # That was not the value i was expected for !!!

I use this way https://mcmap.net/q/75436/-how-to-customize-the-configuration-file-of-the-official-postgresql-docker-image

command: postgres -c config_file=/etc/postgresql.conf

Homiletics