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

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.