Question
How to increase PostgreSQL log level so it has all database queries in it?
Resolution
Open PostgreSQL config file (on the database server):
vi /var/lib/pgsql/data/postgresql.conf
Set values for the following variables as specified below:
log_line_prefix = '[%p] [%c] [%m] [%x]: ' log_statement = 'all'
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