Thread: Partition, inheritance for storing syslog records.

Partition, inheritance for storing syslog records.

From
Mimiko
Date:
Hello.

I beg for the community ideas on how to do in the best way.

I use centrally storing syslog messages in Postgres. All devices send syslog messages to a server (or could be several
infault tolerance) which use 
 
rsyslog to store the logs in the DB (currently I'm using mysql and postgre storing same entries).

In time obvious the table grows. While now I have about 50GB of data, I want to plan for future storing and retrieving
logswhen needed to investigate 
 
something.

I would not explain the columns of the syslog table as it is standard from the rsyslog provided info.

A while I ago on Postgre 8.4 (I know it old and planning to move to latest release) I implemented inheritance, when
creatingmain table and make 
 
inheritable tables using checks on syslogtag column to store logs from some specific applications (like dhcpd, smbd,
tftpd)in separate tables. And 
 
created table rules on main table to place the incoming entries in respective table.

CREATE TABLE syslog_dhcpd
(
   CONSTRAINT syslog_dhcpd_pkey PRIMARY KEY (id),
   CONSTRAINT syslog_dhcpd_message_unique UNIQUE (devicereportedtime, facility, priority, fromhost, message,
syslogtag),
   CONSTRAINT syslog_dhcpd_syslogtag_check CHECK (syslogtag::text ~~ 'dhcpd%'::text)
)
INHERITS (syslog);

CREATE OR REPLACE RULE syslog_dhcpd_insert_rule AS
     ON INSERT TO syslog
    WHERE new.syslogtag::text ~~ 'dhcpd%'::text DO INSTEAD  INSERT INTO syslog_dhcpd (customerid, receivedat,
devicereportedtime,facility, priority, 
 
fromhost, message, ntseverity, importance, eventsource, eventuser, eventcategory, eventid, eventbinarydata,
maxavailable,currusage, minusage, 
 
maxusage, infounitid, syslogtag, eventlogtype, genericfilename, systemid)
   VALUES (new.customerid, new.receivedat, new.devicereportedtime, new.facility, new.priority, new.fromhost,
new.message,new.ntseverity, 
 
new.importance, new.eventsource, new.eventuser, new.eventcategory, new.eventid, new.eventbinarydata, new.maxavailable,
new.currusage,new.minusage, 
 
new.maxusage, new.infounitid, new.syslogtag, new.eventlogtype, new.genericfilename, new.systemid);


As this is was the only way to make partitioning on the version 8.4 (alternatively is using triggers), but it is not
quitegood on expanding.
 

My goal is to make partitioning in such way:

Partition by syslogtag so every application will go to separate partition.
Then sub-partition each partition by fromhost (there will be about 10 different hosts that I want to be separate,
othersshould go on main).
 
Then sub-sub-partition by year.

There could be not sub-partition by fromhost. Only by syslogtag, then by year.

So how could be this accomplished both in version 8.4 and in version 12. Other ideas are welcome.

The main goal is to be able to quickly investigate logs from some applications and some host searching regex in
`message`column.
 




Re: Partition, inheritance for storing syslog records.

From
Mimiko
Date:
Any one can give some advice?

On 11.11.2019 13:45, Mimiko wrote:
> Hello.
> 
> I beg for the community ideas on how to do in the best way.
> 
> I use centrally storing syslog messages in Postgres. All devices send syslog messages to a server (or could be
severalin fault tolerance) which use 
 
> rsyslog to store the logs in the DB (currently I'm using mysql and postgre storing same entries).
> 
> In time obvious the table grows. While now I have about 50GB of data, I want to plan for future storing and
retrievinglogs when needed to investigate 
 
> something.
> 
> I would not explain the columns of the syslog table as it is standard from the rsyslog provided info.
> 
> A while I ago on Postgre 8.4 (I know it old and planning to move to latest release) I implemented inheritance, when
creatingmain table and make 
 
> inheritable tables using checks on syslogtag column to store logs from some specific applications (like dhcpd, smbd,
tftpd)in separate tables. And 
 
> created table rules on main table to place the incoming entries in respective table.
> 
> CREATE TABLE syslog_dhcpd
> (
>    CONSTRAINT syslog_dhcpd_pkey PRIMARY KEY (id),
>    CONSTRAINT syslog_dhcpd_message_unique UNIQUE (devicereportedtime, facility, priority, fromhost, message,
syslogtag),
>    CONSTRAINT syslog_dhcpd_syslogtag_check CHECK (syslogtag::text ~~ 'dhcpd%'::text)
> )
> INHERITS (syslog);
> 
> CREATE OR REPLACE RULE syslog_dhcpd_insert_rule AS
>      ON INSERT TO syslog
>     WHERE new.syslogtag::text ~~ 'dhcpd%'::text DO INSTEAD  INSERT INTO syslog_dhcpd (customerid, receivedat,
devicereportedtime,facility, priority, 
 
> fromhost, message, ntseverity, importance, eventsource, eventuser, eventcategory, eventid, eventbinarydata,
maxavailable,currusage, minusage, 
 
> maxusage, infounitid, syslogtag, eventlogtype, genericfilename, systemid)
>    VALUES (new.customerid, new.receivedat, new.devicereportedtime, new.facility, new.priority, new.fromhost,
new.message,new.ntseverity, 
 
> new.importance, new.eventsource, new.eventuser, new.eventcategory, new.eventid, new.eventbinarydata,
new.maxavailable,new.currusage, new.minusage, 
 
> new.maxusage, new.infounitid, new.syslogtag, new.eventlogtype, new.genericfilename, new.systemid);
> 
> 
> As this is was the only way to make partitioning on the version 8.4 (alternatively is using triggers), but it is not
quitegood on expanding.
 
> 
> My goal is to make partitioning in such way:
> 
> Partition by syslogtag so every application will go to separate partition.
> Then sub-partition each partition by fromhost (there will be about 10 different hosts that I want to be separate,
othersshould go on main).
 
> Then sub-sub-partition by year.
> 
> There could be not sub-partition by fromhost. Only by syslogtag, then by year.
> 
> So how could be this accomplished both in version 8.4 and in version 12. Other ideas are welcome.
> 
> The main goal is to be able to quickly investigate logs from some applications and some host searching regex in
`message`column.
 
> 


-- 
Mimiko desu.



Re: Partition, inheritance for storing syslog records.

From
David Rowley
Date:
On Tue, 12 Nov 2019 at 00:45, Mimiko <vbvbrj@gmail.com> wrote:
> There could be not sub-partition by fromhost. Only by syslogtag, then by year.
>
> So how could be this accomplished both in version 8.4 and in version 12. Other ideas are welcome.
>
> The main goal is to be able to quickly investigate logs from some applications and some host searching regex in
`message`column.
 

The documents in
https://www.postgresql.org/docs/12/ddl-partitioning.html go to lengths
to explain how to do this in v12.  You should have a read of that and
pay particular attention in areas that mention "sub-partitioning".  If
you're still uncertain after that, then ask here.

-- 
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services