Re: Partition, inheritance for storing syslog records. - Mailing list pgsql-general

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.