EASA Help Pages

User Tools

Site Tools

You are here: EASA 6.0 » What is an EASA system? » EASA Guides » Administrator's Guide » Install and Configure EASA on an Ubuntu system with a remote PostgreSQL database

Sidebar

Homepage | About EASA | Contact

Contents

Table of Contents

Install and Configure EASA on an Ubuntu system with a remote PostgreSQL database

Installation

  • Open a terminal
  • sudo apt-get update to update repository
  • sudo apt-get install postgresql postgresql-contrib → install postgresql from repo and any dependecies
  • By default postgresql creates a user postgres which we use to create the users and databases required for EASA.
  • Start psql client as postgres,
  • Configure an IP address for the postgres machine and enable remote connections,
    • Navigate to the 'main' installation directory of postgres here we use,
      • /etc/postgresql/9.5/main
        • With an editor modify one line in postgresql.conf → sudo nano postgresql.conf
          • Change,
            • #listen_addresses = ‘localhost’
          • To,
            • listen_addresses = ‘*’ ← uncomment line, add wildcard character
          • Save the file and close the editor
            • With nano this is CTRL-x <ENTER> <ENTER>
        • With an editor append the following two lines to the end of the file, pg_hba.conf
          • host all all 0.0.0.0/0 md5
          • host all all ::/0 md5
          • Save the file and close the editor
  • Restart the postgres service,
    • service postgresql restart
    • Start a postgres client
      • Run psql –h ip_address_of_machine -U sa easa → enter password when prompted
      • Run \include easa.sql
      • Exit → \q
      • Run psql –h ip_address_of_machine -U exceluser excel → enter password when prompted
      • Type \include excel.sql
      • Exit
      • Type psql –h ip_address_of_machine -U exceluser exceltesting → enter password when prompted
      • Type \include exceltesting.sql
      • Exit
      • Type psql –h ip_address_of_machine -U demo stockdata → enter password when prompted
      • Type \include stockdata.sql
      • Exit

EASA-postgres-init.sql

EASA-postgres-init.sql
CREATE USER sa WITH PASSWORD '​Temp1!+exceluser';​	 	 
CREATE USER exceluser WITH PASSWORD '​Temp2!+exceluser';​	 
CREATE USER demo WITH PASSWORD '​Temp3!+exceluser';​	 
 
CREATE DATABASE easa OWNER sa; 
CREATE DATABASE excel OWNER exceluser; 
CREATE DATABASE exceltesting OWNER exceluser;
CREATE DATABASE stockdata OWNER demo;

easa.sql

easa.sql
 DROP TABLE IF EXISTS tbl_job;
CREATE SEQUENCE tbl_job_seq;
 
CREATE TABLE tbl_job (
  JOB_ID INT NOT NULL DEFAULT NEXTVAL ('tbl_job_seq'),
  PARENT_JOB_ID INT DEFAULT NULL,
  PATH VARCHAR(255) DEFAULT NULL,
  USERNAME VARCHAR(45) DEFAULT NULL,
  MODE VARCHAR(45) DEFAULT NULL,
  LDIR VARCHAR(45) DEFAULT NULL,
  APPDIR VARCHAR(255) DEFAULT NULL,
  JOB_STATUS VARCHAR(255) DEFAULT NULL,
  NON_BATCH_SOFTWARE VARCHAR(45) DEFAULT NULL,
  DATE_SUBMITTED TIMESTAMP(0) DEFAULT NULL,
  STATE SMALLINT DEFAULT NULL,
  SIM_NUM INT DEFAULT NULL,
  APP_TITLE VARCHAR(255) DEFAULT NULL,
  NOTE VARCHAR(255) DEFAULT NULL,
  REMOTE_ID INT DEFAULT NULL,
  REMOTE_CS VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (JOB_ID)
 ,
  CONSTRAINT FKA5AC7BBC25D6D952 FOREIGN KEY (PARENT_JOB_ID) REFERENCES tbl_job (JOB_ID)
);
 
CREATE INDEX FKA5AC7BBC25D6D952 ON tbl_job (PARENT_JOB_ID);

excel.sql

excel.sql
DROP TABLE IF EXISTS easaps;
CREATE TABLE easaps (
  id int8 NOT NULL PRIMARY KEY,
  easap_name VARCHAR(255) DEFAULT NULL UNIQUE,
  easap_uniqueId VARCHAR(255) DEFAULT NULL UNIQUE
);
 
 
DROP TABLE IF EXISTS excel_file;
CREATE TABLE excel_file (
  id int8 NOT NULL PRIMARY KEY,
  last_modified TIMESTAMP(0) DEFAULT NULL,
  name VARCHAR(255) DEFAULT NULL,
  save_type VARCHAR(255) DEFAULT NULL,
  easap_fk int8 DEFAULT NULL,
  CONSTRAINT FKC85A522452EA222F FOREIGN KEY (easap_fk) REFERENCES easaps(id)
);
 
CREATE INDEX FKC85A522452EA222F ON excel_file (easap_fk);
 
DROP TABLE IF EXISTS cases;
CREATE TABLE cases (
  id int8 NOT NULL PRIMARY KEY,
  created TIMESTAMP(0) DEFAULT NULL,
  created_by VARCHAR(255) DEFAULT NULL,
  pricing_id VARCHAR(255) DEFAULT NULL,
  data_file_image BYTEA,
  last_modified TIMESTAMP(0) DEFAULT NULL,
  last_modified_by VARCHAR(255) DEFAULT NULL,
  STATUS VARCHAR(255) DEFAULT NULL,
  excel_file_fk int8 DEFAULT NULL,
  CONSTRAINT FK3CEF323277DA6BE FOREIGN KEY (excel_file_fk) REFERENCES excel_file (id)
);
 
CREATE INDEX FK3CEF323277DA6BE ON cases (excel_file_fk);
 
DROP TABLE IF EXISTS named_range;
CREATE TABLE named_range (
  name VARCHAR(255) NOT NULL,
  excel_file_fk int8 NOT NULL,
  VALUE text,
  PRIMARY KEY (excel_file_fk,name),
  CONSTRAINT FKB0810C77277DA6BE FOREIGN KEY (excel_file_fk) REFERENCES excel_file (id)
);
 
CREATE INDEX FKB0810C77277DA6BE ON named_range (excel_file_fk);
 
DROP TABLE IF EXISTS DATA;
CREATE TABLE DATA (
  TYPE INT DEFAULT NULL,
  VALUE text,
  excel_file_fk int8 NOT NULL,
  named_range_fk VARCHAR(255) NOT NULL,
  case_fk int8 NOT NULL,
  PRIMARY KEY (case_fk,excel_file_fk,named_range_fk),
  CONSTRAINT FK1FE7AA6F969779 FOREIGN KEY (case_fk) REFERENCES cases (id),
  CONSTRAINT FK1FE7AA7DAE806 FOREIGN KEY (excel_file_fk, named_range_fk) REFERENCES named_range (excel_file_fk, name)
);
 
CREATE INDEX FK1FE7AA6F969779 ON DATA (case_fk);
CREATE INDEX FK1FE7AA7DAE806 ON DATA (excel_file_fk,named_range_fk);

exceltesting.sql

exceltesting.sql
DROP TABLE IF EXISTS easaps;
CREATE TABLE easaps (
  id int8 NOT NULL PRIMARY KEY,
  easap_name VARCHAR(255) DEFAULT NULL UNIQUE,
  easap_uniqueId VARCHAR(255) DEFAULT NULL UNIQUE
);
 
 
DROP TABLE IF EXISTS excel_file;
CREATE TABLE excel_file (
  id int8 NOT NULL PRIMARY KEY,
  last_modified TIMESTAMP(0) DEFAULT NULL,
  name VARCHAR(255) DEFAULT NULL,
  save_type VARCHAR(255) DEFAULT NULL,
  easap_fk int8 DEFAULT NULL,
  CONSTRAINT FKC85A522452EA222F FOREIGN KEY (easap_fk) REFERENCES easaps(id)
);
 
CREATE INDEX FKC85A522452EA222F ON excel_file (easap_fk);
 
DROP TABLE IF EXISTS cases;
CREATE TABLE cases (
  id int8 NOT NULL PRIMARY KEY,
  created TIMESTAMP(0) DEFAULT NULL,
  created_by VARCHAR(255) DEFAULT NULL,
  pricing_id VARCHAR(255) DEFAULT NULL,
  data_file_image BYTEA,
  last_modified TIMESTAMP(0) DEFAULT NULL,
  last_modified_by VARCHAR(255) DEFAULT NULL,
  STATUS VARCHAR(255) DEFAULT NULL,
  excel_file_fk int8 DEFAULT NULL,
  CONSTRAINT FK3CEF323277DA6BE FOREIGN KEY (excel_file_fk) REFERENCES excel_file (id)
);
 
CREATE INDEX FK3CEF323277DA6BE ON cases (excel_file_fk);
 
DROP TABLE IF EXISTS named_range;
CREATE TABLE named_range (
  name VARCHAR(255) NOT NULL,
  excel_file_fk int8 NOT NULL,
  VALUE text,
  PRIMARY KEY (excel_file_fk,name),
  CONSTRAINT FKB0810C77277DA6BE FOREIGN KEY (excel_file_fk) REFERENCES excel_file (id)
);
 
CREATE INDEX FKB0810C77277DA6BE ON named_range (excel_file_fk);
 
DROP TABLE IF EXISTS DATA;
CREATE TABLE DATA (
  TYPE INT DEFAULT NULL,
  VALUE text,
  excel_file_fk int8 NOT NULL,
  named_range_fk VARCHAR(255) NOT NULL,
  case_fk int8 NOT NULL,
  PRIMARY KEY (case_fk,excel_file_fk,named_range_fk),
  CONSTRAINT FK1FE7AA6F969779 FOREIGN KEY (case_fk) REFERENCES cases (id),
  CONSTRAINT FK1FE7AA7DAE806 FOREIGN KEY (excel_file_fk, named_range_fk) REFERENCES named_range (excel_file_fk, name)
);
 
CREATE INDEX FK1FE7AA6F969779 ON DATA (case_fk);
CREATE INDEX FK1FE7AA7DAE806 ON DATA (excel_file_fk,named_range_fk);

stockdata.sql

stockdata.sql
TBD

Page Tools