Postgresql – Database Schema design of server running status log

amazon ec2database-designpostgresqlschema

I am working with 4 AWS EC2 instances (servers). Each Instance has Instance ID and Instance Name. Instance ID is unique. Each of them having multiple application servers, like on of them is running PUMA server and another one is running NGINX and so on. I want to store the running status of the instances and their application servers in a database.

Like, If Instance A is running or not, that I can get by continuously hitting into the exposed port and store it in the database. I am scanning it in each minute. There will be 3 columns I can think of right now,

Instance A :-

Instance ID    Running Status   Time

Instance A        Running        10:10:04
Instance A        Running        10:11:04
Instance A        Running        10:12:04

Next, I want to store the status if Application Servers within Instance A running or not. There will be 3 columns I can think of right now,

Let's say PUMA and NGINX are running in Instance A

Instance A :- (Here also I am scanning it in each minute)

Application Name   Running Status   Time

PUMA                  Running       12:10:20
NGINX                 Running       12:10:20
PUMA                  Running       12:11:20
NGINX                 Running       12:11:20

I am using Postgresql. What will be the recommended schema design here ? Should I create a table for each instance and how will I map the applications with that particular Instance ? If I take Instance ID as a primary key then duplicate values in that column are not possible.

Best Answer

Time without date and timezone is pretty useless.

CREATE TABLE logz (
  server_inet inet,
  server_name text,
  app_name    text,
  is_running  bool,
  ts          timestamp with time zone
);

CREATE INDEX ON logz(server_name,ts);
CREATE INDEX ON logz(server_inet,ts);

Then to find the status of the server for each app.

CREATE VIEW running_status_by_name
AS
  SELECT DISTINCT ON (server_name,app_name) server_name, server_inet, app_name, is_running, ts
  FROM logz
  ORDER BY server_name, app_name, ts DESC;

CREATE VIEW running_status_by_inet
AS
  SELECT DISTINCT ON (server_inet,app_name) server_inet, server_name, app_name, is_running, ts
  FROM logz
  ORDER BY server_inet, app_name, ts DESC;

You could also use a pkid and easily do a CROSS JOIN LATERAL (OFFSET 0 LIMIT 1).

Related Question