Solved Syslog and Postgresql

M

MassimoM

Good morning.
I use freebsd 12 and Postgresql 11.5.

I can't get the messages on the log.

here there is my postgresql.conf

Code: 
listen_addresses = 'localhost,*'
port = 5432
max_connections = 15
shared_buffers = 24MB

log_destination = 'syslog'
syslog_facility = 'local0'
syslog_ident = 'postgres'
log_min_messages = debug5
log_min_error_statement = info

datestyle = 'iso, ymd'
lc_messages = 'it_IT.UTF-8'
lc_monetary = 'it_IT.UTF-8'
lc_numeric = 'it_IT.UTF-8'
lc_time = 'it_IT.UTF-8'
default_text_search_config = 'pg_catalog.italian'

i tried to change local0 to LOCAL0, i set "debug 5" to have a huge amount of log, but nothing happens.


Here there is syslog.conf

Code: 
*.err;kern.warning;auth.notice;mail.crit                /var/log/messages

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

daemon.*                                        /var/log/daemon.log

local0.*                                        /var/log/local0.log

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

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

None of these log files are filled (except messages.log).

If i set postgres to put the messages on stderr, i see them.
The rc.conf contains the enable of syslogd (although it works even without).
Syslogs is loaded, and put messages on /var/log/messages.
All log files are present and with the right authorization (if i rename those files, i see error messages on /var/log/messages).
i tried to use the default and the freebsd-manual conf.

I followed the guides and i have seen https://forums.freebsd.org/threads/syslog-and-postgresql.46082/, i copied all the snippets in that thread, and still doesn't work.
and there is no log.
.
Can you help me?

thanks in advance
 
Syslog parses its config from top to bottom. And above your local0 statement you dump a lot of messages (despite their origin) in specific logfiles. Your first entry for example: *.err gets dumped into /var/log/messages, this would include everything of this severity that's sent out by Postgres.

The solution would be to make sure that your postgres origin gets ignored and then re-enabled in the right section. For example, this is what I'm using:

Code: 
!-pkg,pkg-static,pkg,named
*.notice;authpriv.none;kern.debug;lpr.info;mail.crit;news.err   /var/log/messages
...cut...
!pkg,pkg-static
*.*                                             /var/log/pkg.log
 
ShelLuser said:
Syslog parses its config from top to bottom. And above your local0 statement you dump a lot of messages (despite their origin) in specific logfiles. Your first entry for example: *.err gets dumped into /var/log/messages, this would include everything of this severity that's sent out by Postgres.
Click to expand...

Yes, and in fact I've controlled everything in /var/log, but there are no messages from pgsql


The solution would be to make sure that your postgres origin gets ignored and then re-enabled in the right section. For example, this is what I'm using:

Code: 
!-pkg,pkg-static,pkg,named
*.notice;authpriv.none;kern.debug;lpr.info;mail.crit;news.err   /var/log/messages
...cut...
!pkg,pkg-static
*.*                                             /var/log/pkg.log
Click to expand...


ok, this evening I will try.
Thank you! :)
 
It doesn't work.
The log is still not written.

i just tried to put
Code: 
*.*                                            /var/log/all.log

as first line.
still i didn't see postgres messages, while i read all the other messages.
I suspect that the postgres doesn't send messages at all.
 
Don't see an obvious error in Your config.
I have set only log_destination = 'syslog' and it works (FreeBSD 11.3 and postgres 10).

ShelLuser said:
Syslog parses its config from top to bottom. And above your local0 statement you dump a lot of messages (despite their origin) in specific logfiles. Your first entry for example: *.err gets dumped into /var/log/messages, this would include everything of this severity that's sent out by Postgres.
Click to expand...

That shouldn't matter. Syslog can log the same message to multiple destinations.

The destination should be uppercase, i.e. 'LOCAL0'. Otherwise there seem to be no more switches to twiddle.
 
PMc said:
Don't see an obvious error in Your config.
That shouldn't matter. Syslog can log the same message to multiple destinations.
The destination should be uppercase, i.e. 'LOCAL0'. Otherwise there seem to be no more switches to twiddle.
Click to expand...

Tried to switch uppercase "local0". Nothing happened
ok, now i think that the problem is postgres.
I'll wait a new version, and i will upgrade.
 
AFAIK the connection to syslog is per unix domain sockets.
And while I don't know how to debug these, it appears to be possible to have a look at them with sysutils/lsof:
Code: 
# lsof  | grep /var/run/log
syslogd    2267     root    7u    unix     0x8e735c60        0t0        /var/run/log
syslogd    2267     root    8u    unix     0x8e735a50        0t0        /var/run/logpriv
# lsof | grep 0x8e735c60
syslogd    2267     root    7u    unix     0x8e735c60        0t0        /var/run/log
postgres   2672 postgres    4u    unix     0x904af420        0t0        ->0x8e735c60
postgres   2674 postgres    4u    unix     0x904af420        0t0        ->0x8e735c60
postgres   2675 postgres    4u    unix     0x904af420        0t0        ->0x8e735c60
postgres   2676 postgres    4u    unix     0x904af420        0t0        ->0x8e735c60
postgres   2677 postgres    4u    unix     0x904af420        0t0        ->0x8e735c60
...

BTW: your postgres is allowed to write-access /var/run/log ?
 
Code: 
syslogd   22183     root    8u  unix 0xfffff80004a636d0                0t0        /var/run/log
syslogd   22183     root    9u  unix 0xfffff80004a63368                0t0        /var/run/logpriv


syslogd   22183     root    8u  unix 0xfffff80004a636d0                0t0        /var/run/log
postgres  65401 postgres    3u  unix 0xfffff80004aa5000                0t0        ->0xfffff80004a636d0
postgres  66484 postgres    3u  unix 0xfffff80004aa5000                0t0        ->0xfffff80004a636d0
postgres  66501 postgres    3u  unix 0xfffff80004aa5000                0t0        ->0xfffff80004a636d0
postgres  67024 postgres    3u  unix 0xfffff80004aa5000                0t0        ->0xfffff80004a636d0
postgres  67359 postgres    3u  unix 0xfffff80004aa5000                0t0        ->0xfffff80004a636d0
postgres  67557 postgres    3u  unix 0xfffff80004aa5000                0t0        ->0xfffff80004a636d0
postgres  67912 postgres    3u  unix 0xfffff80004aa5000                0t0        ->0xfffff80004a636d0

Code: 
ls -l /var/run/log
srw-rw-rw-  1 root  wheel  0 31 ago 21:32 /var/run/log

it seems ok
 
Hi there,

Like PMc said above, I also just use the default setting in postgresql.conf, but to see if I could replicate your problem
I just did this:

edit:
Code: 
/var/db/postgres/data11/postgresql.conf

added:
Code: 
syslog_facility = 'LOCAL0'
syslog_ident = 'postgres'
log_min_messages = debug5
log_min_error_statement = info

su to postgres to re-load the configuration information:

Code: 
$ su - postgres
$ cd data11
$ pg_ctl -D ${PWD} reload


Edit: /etc/syslog.conf
Code: 
!postgres
*.*                                             /var/log/postgresql.log

Created a blank file:

Code: 
touch /var/log/postgresql.log

Restarted syslog:

Code: 
service syslogd restart

And now I have a postgresql.log full of debug information:


Code: 
Sep  3 09:10:56 rpi2 postgres[950]: [15-1] 2019-09-03 09:10:56.312 AEST [950] DEBUG:  received inquiry for database 0
Sep  3 09:10:56 rpi2 postgres[950]: [16-1] 2019-09-03 09:10:56.313 AEST [950] DEBUG:  writing stats file "pg_stat_tmp/global.stat"
Sep  3 09:10:56 rpi2 postgres[950]: [17-1] 2019-09-03 09:10:56.313 AEST [950] DEBUG:  writing stats file "pg_stat_tmp/db_0.stat"
Sep  3 09:10:56 rpi2 postgres[43384]: [12-1] 2019-09-03 09:10:56.334 AEST [43384] DEBUG:  InitPostgres
Sep  3 09:10:56 rpi2 postgres[43384]: [13-1] 2019-09-03 09:10:56.335 AEST [43384] DEBUG:  my backend ID is 3
Sep  3 09:10:56 rpi2 postgres[43384]: [14-1] 2019-09-03 09:10:56.339 AEST [43384] DEBUG:  StartTransaction(1) name: unnamed; blockState: DEFAULT; state: INPROGRESS, xid/subid/cid: 0/1/0

So, it works.
What is different to your setup?

Regards
Mark
 
You must log in or register to reply here.
Back
Top