Monitorizando servicios systemd
24 de marzo de 2023
Cliente – Servidor perl socket
29 de marzo de 2023
1.- Creamos la función y el trigger sobre la tabla que se quiere monitorizar
CREATE OR REPLACE FUNCTION t_notification_func()
RETURNS trigger AS
'
DECLARE
BEGIN
INSERT INTO notification(notification_id, sent)
VALUES(DEFAULT, 0);
RETURN NEW;
END;
'
LANGUAGE 'plpgsql';
2.- El trigger
CREATE TRIGGER t_notification_insert
AFTER INSERT OR UPDATE
ON database_name.table
FOR EACH ROW
EXECUTE PROCEDURE t_notification_func()
3.- La tabla de registros de notificaciones
CREATE TABLE notification (
notification_id int(10) unsigned NOT NULL AUTO_INCREMENT,
tablename VARCHAR(20) NULL,
sent tinyint(1) NULL,
PRIMARY KEY (notification_id)
);
Borrar el trigger y la función:
DROP FUNCTION t_notification_func() CASCADE;Poner cron en /etc/cron.d/ddbb-monitor:
* * * * * root /opt/scripts/ddbb-monitor.sh >/dev/null 2>&1El script que realiza la acción:
#!/bin/bash
DB_USER='user'
DB_PASS='pass'
DB_NAME='dbname'
DB_HOST='dbhost'
ID=`PGPASSWORD=$DB_PASS psql -U $DB_USER -d $DB_NAME -h $DB_HOST -c "SELECT notification_id FROM <dbname>.notification_queue WHERE sent=0 ORDER BY notification_id DESC LIMIT 1;" | awk -F '-' '{print $1}' | cut -d' ' -f3- | tr -d " \t\n\r"`
if [[ ! -z $ID ]]
then
RESULT=`PGPASSWORD=$DB_PASS psql -U $DB_USER -d $DB_NAME -h $DB_HOST -c "UPDATE <dbname>.notification_queue SET sent=1 WHERE notification_id = $ID;"`
date=$(date '+%d-%m-%Y %H:%M:%S')
echo "WARNING the table <dbname>.user has been modified the $date" >> /var/log/ddbb-monitor.log
fi
