# syslog and PostgreSQL



## dvl@ (Apr 22, 2014)

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:


```
!postgres
*.*	/var/log/postgresql
```

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


```
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.


```
*.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:


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

Any suggestions for a different/better approach?


----------



## obsigna (Apr 23, 2014)

My settings are:

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

```
...
log_destination = 'syslog'
...
#syslog_facility = 'LOCAL0'
...
```

/etc/syslog.conf:

```
...
local0.*					/var/log/postgres.log
...
```

/etc/newsyslog.conf (for log rotation):

```
...
/var/log/postgres.log			640  7	   *	@T00  JC
...
```

This gives me pretty much what it seems that you want to achieve.


----------



## dvl@ (Apr 23, 2014)

With no duplicate entries?


----------



## obsigna (Apr 23, 2014)

Everything is logged only once into /var/log/postgres.log.


----------



## dvl@ (Apr 23, 2014)

I have no idea why I get duplicates and you do not.


----------



## obsigna (Apr 23, 2014)

Well, I don't understand the rational behind the following in your /etc/syslog.conf:

```
...
!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?


```
local0.*     /var/log/postgresql
```


----------



## dvl@ (Apr 23, 2014)

obsigna said:
			
		

> Well, I don't understand the rational behind the following in your /etc/syslog.conf:
> 
> ```
> ...
> ...



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?
> 
> 
> ```
> ...



No idea.

FWIW, the local0.none is still required.


----------



## obsigna (Apr 23, 2014)

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.


----------



## dvl@ (Apr 23, 2014)

It's not what you say, it's how you said it....


----------



## dvl@ (Apr 23, 2014)

Something differs.

My /etc/syslog:


```
# $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:


```
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:


```
[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:

```
*** /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
```


----------



## obsigna (Apr 23, 2014)

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:


```
...
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:

```
...
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.


----------



## dvl@ (Apr 23, 2014)

Mystery solved. Thank you.

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


----------

