Page tree
Skip to end of metadata
Go to start of metadata

Backend server uses SQL DB to store stream history. It is recommended to use Postgresql. Embedded H2 DB is allowed for debug and testing purposes, but it should not be used in production.

DB structure

Backend server SQL DB contains the following tables which are created automatically on first launch:

FieldTypeNote
ALARM
idbigintPRIMARY KEY
namecharacter varying(255)UNIQUE CONSTRAINT
timebigint
typebigint
valuebigint
metric_idbigint

FOREIGN KEY  REFERENCES metric(id)

node_idbigintFOREIGN KEY REFERENCES node(id)
METRIC
idbigintPRIMARY KEY
enum_namecharacter varying(255)UNIQUE CONSTRAINT
namecharacter varying(255)UNIQUE CONSTRAINT
notecharacter varying(255)
NODE
idbigintPRIMARY KEY
hostcharacter varying(255)UNIQUE CONSTRAINT
NODE_PROFILE
node_idbigintFOREIGN KEY REFERENCES node(id)
profile_idbigintFOREIGN KEY REFERENCES profile(id)
PROFILE
idbigintPRIMARY KEY
namecharacter varying(255)UNIQUE CONSTRAINT
rateinteger
PROFILE_METRICS
profile_idbigintFOREIGN KEY REFERENCES profile(id)
metrics_idbigintFOREIGN KEY  REFERENCES metric(id)
PROFILE_RULE
profile_idbigintFOREIGN KEY REFERENCES profile(id)
rule_idbigintFOREIGN KEY REFERENCES rule(id)
RULE
idbigintPRIMARY KEY
namecharacter varying(255)UNIQUE CONSTRAINT
typebigint
valuecharacter varying(255)
STREAM
idbigintPRIMARY KEY
create_datetimestamp without time zone
durationbigint
media_idcharacter varying(255)
namecharacter varying(255)
node_idbigintFOREIGN KEY REFERENCES node(id)
publisher_idbigintFOREIGN KEY REFERENCES stream(id)
STREAM_STATUS
idbigintPRIMARY KEY
statuscharacter varying(255)
timestamptimestamp without time zone
stream_idbigintFOREIGN KEY REFERENCES stream(id)

Postgresql installation and setup

1. Install Postgresql (for example, on CentOS 7) with command

yum install postgresql-server

2. Initialize DB and start Postgresql

postgresql-setup initdb
systemctl enable postgresql
systemctl start postgresql

3. Enter Postgresql console

su - postgres
psql

4. Create database and user, grant privileges to the user

CREATE USER wcsoam WITH PASSWORD 'wcsoam';
CREATE DATABASE wcsoam;
\c wcsoam
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "wcsoam";

5. Exit Postgresql console

\q
exit

DB queries optimizing

In builds before 1.163, to speed up Postgresql and to decrease CPU load, the following indexed should be created after successfull launch of WCS OAM:

CREATE INDEX ON stream(name, media_id, node_id);
CREATE INDEX ON stream(node_id, name, status);
CREATE INDEX ON stream(publisher_id);

Data structure of stream table in wcsoam database should look as follows:

  • No labels