CODEWITHSUNDEEP
sqldatabasepostgresqllogging
Paul
Paul

Reputation: 12819

How to log PostgreSQL queries?

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?

Upvotes: 515

Views: 668859

Answers (14)

Erik van Velzen
Erik van Velzen

Reputation: 7062

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

Upvotes: 11

Super Kai - Kazuya Ito
Super Kai - Kazuya Ito

Reputation: 1

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;

Upvotes: 8

Suresh
Suresh

Reputation: 495

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.

Upvotes: 4

Delirante
Delirante

Reputation: 929

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.

Upvotes: 6

Ser
Ser

Reputation: 2890

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://stackoverflow.com/a/41912295/2294168

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

Upvotes: 1

H.Ç.T
H.Ç.T

Reputation: 3569

You should also set this parameter to log every statement:

log_min_duration_statement = 0

Upvotes: 4

Jarret Hardie
Jarret Hardie

Reputation: 98042

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.

Upvotes: 565

A T
A T

Reputation: 13846

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

Upvotes: 118

vijay
vijay

Reputation: 11027

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

Upvotes: 155

Latif Premani
Latif Premani

Reputation: 431

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

Upvotes: 43

Zoltán
Zoltán

Reputation: 22196

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/

Upvotes: 14

Rix Beck
Rix Beck

Reputation: 1061

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.

Upvotes: 59

Shekhar
Shekhar

Reputation: 7245

+1 to above answers. I use following config

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

Upvotes: 22

Chad Birch
Chad Birch

Reputation: 74608

Set log_statement to all:

Error Reporting and Logging - log_statement

Upvotes: 29

Related Questions