Thread: Partition, inheritance for storing syslog records.
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.
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.
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