PostgreSQL Alert Log
Error Reporting and Logging is controlled by multiple parameters available in PostgreSQL's configuration file postgresql.conf
--As postgres
psql
postgres=# select name, setting, short_desc from pg_settings where name in ('logging_collector', 'log_directory', 'log_filename', 'log_rotation_age', 'log_line_prefix', 'log_statement','data_directory');
name | setting | short_desc
-------------------+---------------------+-------------------------------------------------------------------------
data_directory | /var/lib/pgsql/data | Sets the server's data directory.
log_directory | pg_log | Sets the destination directory for log files.
log_filename | postgresql-%a.log | Sets the file name pattern for log files.
log_line_prefix | %t %h %d %u %a | Controls information prefixed to each log line.
log_rotation_age | 1440 | Automatic log file rotation will occur after N minutes.
log_statement | all | Sets the type of statements logged.
logging_collector | on | Start a subprocess to capture stderr output and/or csvlogs into log files
-bash-4.2$ ls -lt /var/lib/pgsql/data/pg_log
total 24
-rw------- 1 postgres postgres 19450 Jan 4 10:41 postgresql-Fri.log
-rw------- 1 postgres postgres 2249 Jan 3 13:26 postgresql-Thu.log
-rw------- 1 postgres postgres 0 Jan 2 00:00 postgresql-Wed.log
-rw------- 1 postgres postgres 0 Jan 1 00:00 postgresql-Tue.log
-rw------- 1 postgres postgres 0 Dec 31 00:00 postgresql-Mon.log
-rw------- 1 postgres postgres 0 Dec 30 00:00 postgresql-Sun.log
-rw------- 1 postgres postgres 0 Dec 29 00:00 postgresql-Sat.log
Use following procedure If parameter need to be changed
--As postgres
-bash-4.2$ cd /var/lib/pgsql/data
-bash-4.2$ vi postgresql.conf
log_line_prefix = '%t %h %d %u %a' <-------- change according to this
log_statement = 'all' <-------- none (off), ddl, mod, all
pg_ctl restart -m fast <------------ restart instance