Re: Partition, inheritance for storing syslog records.

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 several in 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 retrieving logs 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 creating main 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 quite good 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, others should 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.