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.
