-
Notifications
You must be signed in to change notification settings - Fork 1
01: The PostgreSQL database
The database used is PostgreSQL.
Tested versions PostgreSQL v9.6.x
The pgAdmin app works fine to administer PostgreSQL.
¤IPv4 local connections:
¤host all all 127.0.0.1/32 scram-sha-256
host all all 0.0.0.0/0 scram-sha-256
Mint: pg_hba.conf is found in /etc/postgresql/14/main
Change the port and listen_addresses in postgresql.conf in the data directory to allow more than one PostgreSQL DB (example)
port = 5432
listen_addresses = '*'
CREATE ROLE "Jvakt" LOGIN
ENCRYPTED PASSWORD 'md59591f073d2aff9d529feba7a60b3b8fd'
SUPERUSER INHERIT CREATEDB CREATEROLE REPLICATION;
CREATE ROLE console LOGIN
ENCRYPTED PASSWORD 'md57fb7257b8a1c9fc9b0e5d19b7d9dc073'
NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
CREATE DATABASE "Jvakt" WITH ENCODING='UTF8' OWNER="Jvakt" CONNECTION LIMIT=-1;
set PGPASSWORD=pw
Backup old DB
"C:\Program Files (x86)\PostgreSQL\9.6\bin\pg_dump" -h localhost -p 5433 -U postgres -Fc -f C:\temp\BU\PostgreJvakt.cus Jvakt
Restore into new DB
"C:\Program Files\PostgreSQL\13\bin\pg_restore" -U postgres -d Jvakt C:\temp\BU\PostgreJvakt.cus
CREATE TABLE IF NOT EXISTS Status (
state CHAR(1),
id varCHAR(255) not null,
prio integer,
type CHAR(1),
status varCHAR(255) not null,
body varCHAR(255),
rptdat timestamp,
chkday varCHAR(255),
chktim TIME,
errors integer,
accerr integer,
msg varCHAR(1),
msgdat timestamp,
console varCHAR(1),
condat timestamp,
info varCHAR(255),
plugin varCHAR(255),
agent varCHAR(255),
sms varCHAR(1),
smsdat timestamp,
msg30 character varying(1),
msgdat30 timestamp without time zone,
chktimto TIME,
recid varCHAR(255),
PRIMARY KEY ( id, prio, type ));
COMMENT ON TABLE status IS 'Status of the agents';
COMMENT ON COLUMN status.state IS 'A=Active I=Inactive D=Dormant';
COMMENT ON COLUMN status.id IS 'Descriptive key';
COMMENT ON COLUMN status.prio IS '1=7/24 2=not nigths 3=Office hours 4=Info';
COMMENT ON COLUMN status.type IS 'R=Repeated I=Immediate S=Scheduled';
COMMENT ON COLUMN status.status IS 'OKAY or not';
COMMENT ON COLUMN status.body IS 'Any text delivered by the agent';
COMMENT ON COLUMN status.rptdat IS 'The time of the agent report';
COMMENT ON COLUMN status.chkday IS '*ALL MON TUE... ';
COMMENT ON COLUMN status.chktim IS 'The start time for check';
COMMENT ON COLUMN status.errors IS 'Number of error status reported';
COMMENT ON COLUMN status.accerr IS 'Number of errors accepted before a msg is triggered';
COMMENT ON COLUMN status.msg IS 'Blank,M,R,S or T. To be handled by the msg routine';
COMMENT ON COLUMN status.msgdat IS 'Time the msg routine was triggered';
COMMENT ON COLUMN status.console IS 'C=tagged for to be shown/removed on/from the console';
COMMENT ON COLUMN status.condat IS 'Time the console was updated';
COMMENT ON COLUMN status.info IS 'Description of the checkpoint';
COMMENT ON COLUMN status.plugin IS 'The plugin to run when msg is triggered';
COMMENT ON COLUMN status.agent IS 'Description of the reporting agent';
COMMENT ON COLUMN status.sms IS 'Blank,M,R,S or T. To be handled by the SMS routine';
COMMENT ON COLUMN status.smsdat IS 'Time the SMS routine was triggered';
COMMENT ON COLUMN status.msg30 IS 'Blank,M,R,S or T. To be handled by the MSG30 routine';
COMMENT ON COLUMN status.msgdat30 IS 'Time the MSG30 routine was triggered';
COMMENT ON COLUMN status.chktimto IS 'The end time for check';
COMMENT ON COLUMN status.recid IS 'Field used by plugin 1';
GRANT select on status to "console";
GRANT update on status to "console";
GRANT insert on status to "console";
CREATE TABLE IF NOT EXISTS Console (
count integer,
id varCHAR(255) not null,
prio integer,
type CHAR(1),
condat timestamp,
credat timestamp,
status varCHAR(255) not null,
body varCHAR(255),
agent varCHAR(255),
recid varCHAR(255),
PRIMARY KEY ( id, prio, type, credat ));
COMMENT ON TABLE Console IS 'Status to operator';
COMMENT ON COLUMN Console.count IS '#Duplicates';
COMMENT ON COLUMN Console.id IS 'Id';
COMMENT ON COLUMN Console.prio IS '1=7/24 2=not nigths 3=Office hours 4=Info';
COMMENT ON COLUMN Console.type IS 'R=Repeated I=Immediate S=Scheduled';
COMMENT ON COLUMN Console.condat IS 'The time of latest console update';
COMMENT ON COLUMN Console.credat IS 'The time when row was added';
COMMENT ON COLUMN Console.status IS 'OKAY or not';
COMMENT ON COLUMN Console.body IS 'text by the agent';
COMMENT ON COLUMN Console.agent IS 'Description of the reporting agent';
COMMENT ON COLUMN Console.recid IS 'Field used by plugin 1';
GRANT select on console to "console";
CREATE TABLE IF NOT EXISTS ConsoleHst (
credat timestamp,
deldat timestamp,
count integer,
id varCHAR(255) not null,
prio integer,
type CHAR(1),
status varCHAR(255) not null,
body varCHAR(255),
agent varCHAR(255),
PRIMARY KEY ( credat, id, prio, type ));
COMMENT ON TABLE ConsoleHst IS 'Console history';
COMMENT ON COLUMN ConsoleHst.credat IS 'The time when row was added';
COMMENT ON COLUMN ConsoleHst.deldat IS 'The time when the row was deleted';
COMMENT ON COLUMN ConsoleHst.count IS '#Duplicates';
COMMENT ON COLUMN ConsoleHst.id IS 'Id';
COMMENT ON COLUMN ConsoleHst.prio IS '1=7/24 2=not nigths 3=Office hours 4=Info';
COMMENT ON COLUMN ConsoleHst.type IS 'R=Repeated I=Immediate S=Scheduled';
COMMENT ON COLUMN ConsoleHst.status IS 'OKAY or not';
COMMENT ON COLUMN ConsoleHst.body IS 'text by the agent';
COMMENT ON COLUMN ConsoleHst.agent IS 'Description of the reporting agent';
GRANT select on consoleHst to "console";
CREATE TABLE IF NOT EXISTS logs (
id varCHAR(255) not null,
origin varCHAR(255),
credat timestamp,
"row" varCHAR(2048),
line integer);
COMMENT ON TABLE Logs IS 'Imported logfiles';
GRANT select on logs to "console";
CREATE INDEX IF NOT EXISTS ix_credat
ON public.logs USING btree
(credat DESC NULLS FIRST)
WITH (deduplicate_items=True)
TABLESPACE pg_default;
ALTER TABLE console
ADD COLUMN recid varCHAR(255);
ALTER TABLE status
ADD COLUMN recid varCHAR(255);