Event Archiving script error. maint_events.sh

Hi!

Find out about this page Tuning
We have a lot of events that could be Archived, about 600 000 in total for now in Prod. We tried this first in our test-environment.

I have heard about “vacuumd” but not sure how it works.
Maybe it’s better to look at “vacuumd-configuration.xml” rather than this script?
Found this: vacuumd takes long time

First we had to change “PG_PASSWORD=” to "export PGPASSWORD=XXXX to make the script run.
Then we saw some other issues as follows:

postgres 12.8
opennms 28.1.1

./maint_events.sh
CREATE SEQUENCE tx_ids;
ERROR: relation “tx_ids” already exists

real 0m0.015s
user 0m0.006s
sys 0m0.003s
insert into events (eventid, eventuei, eventtime, eventsource, eventcreatetime, eventseverity, eventlog, eventdisplay, systemid) values (0, ‘uei.opennms.org/internal/archivedEvent’, now(), ‘Script’, ‘localhost’, now(), 1, ‘N’, ‘N’, ‘00000000-0000-0000-0000-000000000000’);
ERROR: INSERT has more expressions than target columns
LINE 1: …now(), ‘Script’, ‘localhost’, now(), 1, ‘N’, ‘N’, '00000000-…
^

real 0m0.013s
user 0m0.007s
sys 0m0.002s
UPDATE event_archives SET eventtime = now() WHERE eventid = 0
UPDATE 0

real 0m0.014s
user 0m0.005s
sys 0m0.004s
CREATE TABLE event_archives (LIKE events);
ERROR: relation “event_archives” already exists

real 0m0.015s
user 0m0.005s
sys 0m0.005s
ALTER TABLE event_archives ADD COLUMN txid bigint;
ERROR: column “txid” of relation “event_archives” already exists

real 0m0.014s
user 0m0.002s
sys 0m0.007s
CREATE INDEX event_archives_txid ON event_archives (txid);
ERROR: relation “event_archives_txid” already exists

real 0m0.014s
user 0m0.004s
sys 0m0.005s
CREATE UNIQUE INDEX event_archives_eventid ON event_archives (eventid);
ERROR: relation “event_archives_eventid” already exists

real 0m0.013s
user 0m0.006s
sys 0m0.002s
CREATE INDEX event_archives_eventid_txid ON event_archives (eventid,txid);
ERROR: relation “event_archives_eventid_txid” already exists

real 0m0.013s
user 0m0.005s
sys 0m0.003s
CREATE INDEX event_archives_eventtime ON event_archives (eventtime);
ERROR: relation “event_archives_eventtime” already exists

real 0m0.014s
user 0m0.004s
sys 0m0.004s
DELETE FROM events e WHERE e.eventid IN (SELECT o.svcregainedeventid FROM outages o WHERE o.svcregainedeventid IS NOT NULL AND (ifregainedservice - iflostservice)::interval < interval ‘35 seconds’);
DELETE 0

real 0m0.021s
user 0m0.006s
sys 0m0.004s
DELETE FROM notifications WHERE pagetime < now() - interval ‘3 months’;
DELETE 0

real 0m0.014s
user 0m0.004s
sys 0m0.005s
DELETE
FROM events
WHERE NOT EXISTS (
SELECT svclosteventid
FROM outages
WHERE svclosteventid = events.eventid
UNION
SELECT svcregainedeventid
FROM outages
WHERE svcregainedeventid = events.eventid
UNION
SELECT eventid
FROM notifications
WHERE eventid = events.eventid)
AND eventtime < now() - interval ‘6 weeks’;
DELETE 0

real 0m0.208s
user 0m0.005s
sys 0m0.003s
psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket “/var/run/postgresql/.s.PGSQL.5432”?
Next Value:
INSERT INTO event_archives SELECT *, FROM events WHERE eventtime < now() - interval ‘9 weeks’;
ERROR: syntax error at or near “FROM”
LINE 1: INSERT INTO event_archives SELECT *, FROM events WHERE even…
^

real 0m0.013s
user 0m0.007s
sys 0m0.001s
UPDATE outages SET svclosteventid = 0 FROM event_archives WHERE outages.svclosteventid = event_archives.eventid AND event_archives.txID = ;
ERROR: syntax error at or near “;”
LINE 1: …eventid = event_archives.eventid AND event_archives.txID = ;
^

real 0m0.013s
user 0m0.005s
sys 0m0.003s
UPDATE outages SET svcregainedeventid = 0 FROM event_archives WHERE outages.svcregainedeventid = event_archives.eventid AND event_archives.txID = ;
ERROR: syntax error at or near “;”
LINE 1: …eventid = event_archives.eventid AND event_archives.txID = ;
^

real 0m0.013s
user 0m0.007s
sys 0m0.002s
UPDATE notifications SET eventid = 0 FROM event_archives WHERE notifications.eventid = event_archives.eventid AND event_archives.txID = ;
ERROR: syntax error at or near “;”
LINE 1: …eventid = event_archives.eventid AND event_archives.txID = ;
^

real 0m0.013s
user 0m0.006s
sys 0m0.003s
UPDATE alarms SET lasteventid = 0 FROM event_archives WHERE alarms.lasteventid = event_archives.eventid AND event_archives.txID = ;
ERROR: syntax error at or near “;”
LINE 1: …eventid = event_archives.eventid AND event_archives.txID = ;
^

real 0m0.015s
user 0m0.005s
sys 0m0.004s
DELETE FROM events USING event_archives WHERE event_archives.eventid = events.eventid AND event_archives.txID = ;
ERROR: syntax error at or near “;”
LINE 1: …rchives.eventid = events.eventid AND event_archives.txID = ;
^

real 0m0.013s
user 0m0.006s
sys 0m0.003s

According to git, this tool hasn’t seen any updates since 2015. it’s pretty out of date amd I’m not surprised it doesn’t work. There’s been several database schema changes that may affect this since 2015.

I’ve also never seen it used in anger, or heard of anyone else using it, so that should tell you how widely used it is.

If you have old events you no longer find relevant, delete them.

Ok thanks!

Should we look into vacuumd? Or is it just better to manually remove events older than X and later put a script into crontab?