Question

How to increase PostgreSQL log level so it has all database queries in it?

Resolution

  1. Open PostgreSQL config file (on the database server):

    vi /var/lib/pgsql/data/postgresql.conf
    
  2. Set values for the following variables as specified below:

    log_line_prefix = '[%p] [%c] [%m] [%x]: '
    log_statement = 'all'
    
  3. Reload PostgreSQL configuration:

    bash-4.1$ su postgres
    bash-4.1$ cd /var/lib/pgsql/data/
    bash-4.1$ /usr/bin/pg_ctl -D /var/lib/pgsql/data/ reload
    server signaled
    

Note

  • Paths can slightly differ for different PostgreSQL versions
  • PBA services are not affected, no need to stop PBA
  • PostgreSQL logs will be available at:

    # cd /var/lib/pgsql/data/pg_log/
    # ls
    postgresql-Fri.log  postgresql-Sat.log  postgresql-Thu.log  postgresql-Wed.log
    postgresql-Mon.log  postgresql-Sun.log  postgresql-Tue.log
    

Internal content