syslog and PostgreSQL

dvl@

Developer
This seems to be a recurring issue. I managed to get PostgreSQL logging to both /var/log/messages and /var/log/postgresql. I want only the latter. I'm using FreeBSD 9.2-RELEASE-p4.

Here are some of the entries from /etc/syslog.conf:

Code:
!postgres
*.*	/var/log/postgresql

From /usr/local/pgsql/data//postgresql.conf:

Code:
log_destination = 'syslog'

I suspect that the entries in /var/log/messages are picked up from the local0 facility, which is the default on PostgreSQL.

Reading syslog.conf(5), and trying many options, I settled upon this in /etc/syslog.conf.

Code:
*.notice;authpriv.none;kern.debug;lpr.info;mail.crit;news.err;local0.none       /var/log/messages
!postgres
*.*     /var/log/postgresql

NOTE the local0.none entry.

This seems to have the desired effect.

This entry in /etc/newsyslog.conf allows the log file to be rotated and signals the PostgreSQL daemon that it has been done:

Code:
/var/log/postgresql     pgsql:pgsql 640  7     *    $D0   GB  /usr/local/pgsql/data/postmaster.pid 30

Any suggestions for a different/better approach?
 
My settings are:

/usr/local/pgsql/data/postgresql.conf:
Code:
...
log_destination = 'syslog'
...
#syslog_facility = 'LOCAL0'
...

/etc/syslog.conf:
Code:
...
local0.*					/var/log/postgres.log
...

/etc/newsyslog.conf (for log rotation):
Code:
...
/var/log/postgres.log			640  7	   *	@T00  JC
...

This gives me pretty much what it seems that you want to achieve.
 
Well, I don't understand the rational behind the following in your /etc/syslog.conf:
Code:
...
!postgres
*.*     /var/log/postgresql

You told PostgreSQL to log everything by syslog using the log facility local0 (default). So, why do you tell syslog in addition to pick messages from the postgres process? Why not simply?

Code:
local0.*     /var/log/postgresql
 
obsigna said:
Well, I don't understand the rational behind the following in your /etc/syslog.conf:
Code:
...
!postgres
*.*     /var/log/postgresql

Because I was going based on what ppp was doing

obsigna said:
You told PostgreSQL to log everything by syslog using the log facility local0 (default). So, why do you tell syslog to pick messages from the postgres process? Why not simply?

Code:
local0.*     /var/log/postgresql

No idea.

FWIW, the local0.none is still required.
 
Let's analyze:

You command PostgreSQL to log everything via syslog into local0 which will end-up into /var/log/messages if not defined otherwise. So PostgreSQL does what you asked for, logging to /var/log/messages.

In addition, you command syslog to pick messages from PostgreSQL and log that into /var/log/postgresql, and syslog does too, what you have asked for, logging to /var/log/postgresql.

In my setup, I told syslog only to write everything coming-in on local0 to /var/log/postgresql.log instead of /var/log/messages, and therefore /var/log/messages is out of business only by this single definition.

Note, computers do what you command, which is not always what you want.
 
Something differs.

My /etc/syslog:

Code:
# $FreeBSD: release/9.2.0/etc/syslog.conf 238473 2012-07-15 10:55:43Z brueffer $
#
#	Spaces ARE valid field separators in this file. However,
#	other *nix-like systems still insist on using tabs as field
#	separators. If you are sharing this file between systems, you
#	may want to use only tabs as field separators here.
#	Consult the syslog.conf(5) manpage.
*.err;kern.warning;auth.notice;mail.crit		/dev/console
*.notice;authpriv.none;kern.debug;lpr.info;mail.crit;news.err	/var/log/messages


security.*					/var/log/security
auth.info;authpriv.info				/var/log/auth.log
mail.info					/var/log/maillog
lpr.info					/var/log/lpd-errs
ftp.info					/var/log/xferlog
cron.*						/var/log/cron
*.=debug					/var/log/debug.log
*.emerg						*
# uncomment this to log all writes to /dev/console to /var/log/console.log
# touch /var/log/console.log and chmod it to mode 600 before it will work
#console.info					/var/log/console.log
# uncomment this to enable logging of all log messages to /var/log/all.log
# touch /var/log/all.log and chmod it to mode 600 before it will work
#*.*						/var/log/all.log
# uncomment this to enable logging to a remote loghost named loghost
#*.*						@loghost
# uncomment these if you're running inn
# news.crit					/var/log/news/news.crit
# news.err					/var/log/news/news.err
# news.notice					/var/log/news/news.notice


!ppp
*.*						/var/log/ppp.log
!*

local0.*     /var/log/postgresql

Entries from PostgreSQL 9.2.8 /usr/local/pgsql/data/postgresql.conf:

Code:
checkpoint_completion_target = 0.7	# checkpoint target duration, 0.0 - 1.0
checkpoint_segments = 512		# in logfile segments, min 1, 16MB each
client_min_messages = warning		# values in order of decreasing detail:
datestyle = 'iso, mdy'
default_text_search_config = 'pg_catalog.english'
lc_messages = 'C'			# locale for system error message
lc_monetary = 'C'			# locale for monetary formatting
lc_numeric = 'C'			# locale for number formatting
lc_time = 'C'				# locale for time formatting
listen_addresses = '127.0.0.1,10.55.0.73'		# what IP address(es) to listen on;
log_checkpoints = on
log_destination = 'syslog'
log_lock_waits = on			# log lock waits >= deadlock_timeout
log_min_messages = warning		# values in order of decreasing detail:
log_timezone = 'UTC'
maintenance_work_mem = 1GB		# min 1MB
max_connections = 100			# (change requires restart)
shared_buffers = 3GB			# min 128kB
timezone = 'UTC'
update_process_title = off
work_mem = 1GB				# min 64kB

Make sure the settings are active:

sudo kill -SIGHUP `sudo cat /var/run/syslog.pid `

and watch the files:

sudo xtail /var/log/messages /var/log/postgresql

Run a simple few test in one session:

Code:
[dan@slocum:~] $ logger test
[dan@slocum:~] $ echo 'things' | psql bacula
ERROR:  syntax error at or near "things"
LINE 1: things
        ^
[dan@slocum:~] $

From the other session:
Code:
*** /var/log/messages ***
Apr 23 10:50:31 slocum dan: test
Apr 23 10:50:35 slocum postgres[89524]: [5-1] ERROR:  syntax error at or near "things" at character 1
Apr 23 10:50:35 slocum postgres[89524]: [5-2] STATEMENT:  things

*** /var/log/postgresql ***
Apr 23 10:50:35 slocum postgres[89524]: [5-1] ERROR:  syntax error at or near "things" at character 1
Apr 23 10:50:35 slocum postgres[89524]: [5-2] STATEMENT:  things
 
dvl@ said:
...
FWIW, the local0.none is still required.

You are right, local0.none is required in order to have no postgres logging at all into /var/log/messages. My tests yesterday did not trigger the *.notice level of /var/log/messages, and so I saw logging into /var/log/postgres.log only.

In /usr/local/pgsql/data/postgresql.conf I have:

Code:
...
log_min_messages = log
log_min_error_statement = log
...
This is because the clients that I use show me statement errors right away, and I saw no advantage in logging them. Therefore, I don't see statement errors in either log file. In my /var/log/postgres.log I see mostly shutting down, starting up, and autovacuum events which don't appear in /var/log/messages.

With the following settings:
Code:
...
log_min_messages = warning
log_min_error_statement = error
...
..., I see the statement errors in both log files, and again you are right, in this case, in order to keep /var/log/messages totally silent on postgres events, local0.none is needed.
 
Mystery solved. Thank you.

I log errors so I have them. Not all my database interactions occur while I'm watching (e.g.FreshPorts).
 
Back
Top