Filebeat Module Postgresql

Elastic Stack Beats
1

Hello everyone,

Please I need your help, I have problems with the Postgresql module filbeat, at the time of viewing the log I see that I get the following error message:

[2023-04-26 09:20:02.534 -05 [2828024] u_sistema@scp LOG: statement: insert into monitor.log(host,swap,menlibre,cargacpu,disco90,disco95,ip10,aswap,amen,uptime,llast)values('dnsserv2','36','86','0,01,','-','-','10. 1.1.96','974','976','up 18 weeks, 3 days, 11 hours, 58 minutes','reboot system boot 5.4.0-105-generi Sat Dec 17 21:21 still running')]]

I have already checked all my configuration of both filebeat and Postgresql module is in "true".

image
image1336×570 56 KB

If you could help me it would be of great help.

Thanks.

2

hi @Giancarlo_Huapaya_Ra Welcome to the community!

What version of the Stack are you on...

and did you run

filebeat setup -e

Before you started to ingest the logs?

And can you share a few lines from the raw postgres logs please.

Quick look ... it looks like your log lines are not the postgres standard logs and that they have been customized in some way.

3

Hello,
I am using version 8.7.3
If I run filebeat setup -e
The log lines are as follows, I get error message with GROK

image

4

I don't mean the logs inside elastic ...

I want to see some of the raw lines from the postgres.log itself

Also per the docs

The postgresql module using .log was tested with logs from versions 9.5 on Ubuntu, 9.6 on Debian, and finally 10.11, 11.4 and 12.2 on Arch Linux 9.3.

My quick analysis is that your raw postgress logs do not adhere to the supported postgres log format.

Please take a look at this supported log formats

That not to say we can not fix this ... but that is why it is not working

it looks to me like you logs are of the format which is not standard... one part that appears to be not standard is the -05 timezone which looks like a non standard pattern.

2023-04-26 09:20:02.534 -05 [2828024] u_sistema@scp LOG: statement: insert into monitor.log(host,swap,menlibre,cargacpu,disco90,disco95,ip10,aswap,amen,uptime,llast)values('dnsserv2','36','86','0,01,','-','-','10. 1.1.96','974','976','up 18 weeks, 3 days, 11 hours, 58 minutes','reboot system boot 5.4.0-105-generi Sat Dec 17 21:21 still running')

So first can you check what version ...
and 2nd I suspect there has been some customizations to the postgressql log format
and 3rd can you changes to a standard format...

If not any of those we will need to create a custom version of the parsing pipeline....

Which is not too bad but it is something you will need to maintain and the timezone may not be correct

Also please do NOT paste images of text, that is not helpful at all ... please past text and format it with the </> button or just put ``` 3 back ticks the line before and after the text

5

BTW I just stood up a quick postgres with docker no changes and ran it created a few tables etc.

2023-04-26 19:01:02.525 UTC [1] LOG:  starting PostgreSQL 15.2 (Debian 15.2-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit

Screenshot 2023-04-26 at 12.14.35 PM
Screenshot 2023-04-26 at 12.14.35 PM1920×994 94.2 KB

6

Thanks for the information provided I will do what you tell me, I just have one more question, in the doc you shared I see that make changes to postgresql.conf, I have to make these changes or I can leave it by default?

7

I am not a postgress guru...

The docs say

Log lines should be preffixed with the timestamp in milliseconds, the process id, the user id and the database name. This uses to be the default in most distributions, and is translated to this setting in the configuration file:

I suspect it should work with the defaults... you will need to test.

BUT if you want more advanced like duration follow the docs

PostgreSQL server can be configured to log statements and their durations and this module is able to collect this information. To be able to correlate each duration with their statements, they must be logged in the same line. This happens when the following options are used:

log_duration = 'on'
log_statement = 'none'
log_min_duration_statement = 0

Setting a zero value in log_min_duration_statement will log all statements executed by a client. You probably want to configure it to a higher value, so it logs only slower statements. This value is configured in milliseconds.

When using log_statement and log_duration together, statements and durations are logged in different lines, and Filebeat is not able to correlate both values, for this reason it is recommended to disable log_statement.

8

Thanks for the support, and I will review the docs sent.

9

Hello again,

To update my case which has already been solved thanks to your comments.

As an addition the change I applied was the following:

log_timezone = 'Etc/UTC'.

Before it was as America-Latin.

Again thank you very much for your support.

1 Like
10

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.