Installing Postgres

PostgreSQL is deployed with master-slave replication using Docker Compose. Schema setup includes essential tables and indexes.

PostgreSQL is used as the transactional database in HCL CDP. The setup includes master-slave replication, schema creation, and Prometheus monitoring integration.

Installing PostgreSQL on Master VM

To install PostgreSQL on master VM, follow the steps below:

  1. Install Docker and compose.
    yum update -y
yum install -y yum-utils
yum-config-manager --add-repo https://download.docker.com/linux/rhel/docker-ce.repo
yum install -y docker-ce docker-ce-cli containerd.io docker-compose-plugin
systemctl start docker
systemctl enable docker

curl -L "https://github.com/docker/compose/releases/download/v2.12.2/docker-compose-$(uname -s)-$(uname -m)" -o /usr/local/bin/docker-compose
mv /usr/local/bin/docker-compose /usr/bin/docker-compose
chmod +x /usr/bin/docker-compose
  2. Directory Structure.
    systemctl status docker
ln -s /data /disk1
mkdir -p /disk1/postgress
mkdir -p /disk1/postgress/data
  3. Create the Docker Compose File as shown below.
    -vi /home/kgunda/postgress/docker-compose.yaml
    version: "3"
services:
  postgres:
    image: postgres:15.3
    restart: always
    environment:
      POSTGRES_USER: <username>
      POSTGRES_PASSWORD: <password>
      POSTGRES_DB: analytics
    volumes:
      - db-data:/var/lib/postgresql/data
    ports:
      - 5432:5432
    shm_size: 1gb

  postgres-exporter:
    image: prometheuscommunity/postgres-exporter
    ports:
      - 9187:9187
    environment:
      DATA_SOURCE_NAME: "postgresql://<username>:<password>@<vmip>:5432/analytics?sslmode=disable"
    links:
      - postgres

volumes:
  db-data:
    driver: local
    driver_opts:
      type: none
      o: bind
      device: /disk1/postgress/data
  4. Start Services. 
    cd /home/kgunda/postgress
docker-compose up -d
docker ps

Installation on Slave Host

Repeat the same Docker install steps and create the same volume and directory structure. Then, use this docker-compose.yaml with adjusted passwords and memory.
postgres:
  shm_size: 58gb
postgres-exporter:
  DATA_SOURCE_NAME: "postgresql://<username>:<password>@<master-ip>:5432/analytics?sslmode=disable"
cd /home/kgunda/postgress
docker-compose up -d

Replication Configuration

On Master:

psql -U <username> -W -d analytics -h localhost -p 5432

On Slave

  1. System updates and dependencies.
    yum update -y
yum install -y yum-utils
  2. Add Docker repo and install Docker and Compose.
    yum-config-manager --add-repo https://download.docker.com/linux/rhel/docker-ce.repo
yum install -y docker-ce docker-ce-cli containerd.io docker-buildx-plugin docker-compose-plugin
  3. Start and verify Docker.
    systemctl start docker
systemctl enable docker
systemctl status docker
  4. Install Docker Compose.
    curl -L "https://github.com/docker/compose/releases/download/v2.12.2/docker-compose-$(uname -s)-$(uname -m)"  -o /usr/local/bin/docker-compose
mv /usr/local/bin/docker-compose /usr/bin/docker-compose
chmod +x /usr/bin/docker-compose
  5. Create directory structure for PostgreSQL.
    ln -s /data /disk1
mkdir -p /disk1/postgress
mkdir -p /disk1/postgress/data
  6. Create a docker composer file as shown below.
    vi /home/kgunda/postgress/docker-compose.yaml
    version: "3"
services:
 
  postgres:
    image: postgres:15.3
    restart: always
    environment:
      POSTGRES_USER: <username>
      POSTGRES_PASSWORD: wOH3S62YxZ7Y3E7PGdQwbA
      POSTGRES_DB: analytics
    volumes:
      - db-data:/var/lib/postgresql/data
    ports:
      - 5432:5432
    shm_size: 58gb
  postgres-exporter:
    image: prometheuscommunity/postgres-exporter
    ports:
      - 9187:9187
    environment:
      DATA_SOURCE_NAME: "postgresql://<username>:wOH3S62YxZ7Y3E7PGdQwbA@10.214.103.150:5432/analytics?sslmode=disable"
    links:
      - postgres
 
volumes:
  db-data:
    driver: local
    driver_opts:
      type: none
      o: bind
      device: /disk1/postgress/data
  7. Start PostgreSQL and Exporter.
    cd /home/kgunda/postgress/
docker-compose up -d
docker ps
  8. Access the PostgreSQL Container.
    psql -U <username> -W -d analytics -h localhost -p 5432
  9. After the Postgress installation is completed on both Master and Slave, whitelist the 5432 and 9187 ports from Master and Slave hosts (Inbound and Outbound).

PostgreSQL Replication and Schema Setup

  1. Replication Steps - Master.
     psql -U <username> -W -d analytics -h localhost -p 5432

        CREATE USER replication REPLICATION LOGIN CONNECTION LIMIT 1 ENCRYPTED PASSWORD
        'replicationpa55word';
 \du;
 ALTER ROLE replication CONNECTION LIMIT -1;

 # Edit the /disk1/postgress/data/postgresql.conf file:
 max_connections =  1000
 wal_level = replica
 max_wal_size = 100GB
 max_wal_senders = 10
 wal_keep_size = 3000

 # Edit the /disk1/postgress/data/pg_hba.conf file:
 host    replication    
          replication     <Slave Host IP>/0   md5

 cd /home/kgunda/postgress/
 systemctl stop docker; systemctl start docker

        docker-compose down
 docker-compose up -d
 docker ps
  2. Replication Steps - Slave.
     psql -U <username> -W -d analytics -h localhost -p 5432

        CREATE USER replication REPLICATION LOGIN CONNECTION LIMIT 1 ENCRYPTED PASSWORD
        'replicationpa55word';
 \du;
 ALTER ROLE replication CONNECTION LIMIT -1;

 # Edit the /disk1/postgress/data/postgresql.conf:
 max_connections = 1000
 wal_level = replica
 max_wal_size = 100GB
 max_wal_senders = 10
 wal_keep_size = 3000

 # Edit /disk1/postgress/data/pg_hba.conf:
 host    replication     replication     <Master Host IP>/0   md5

 cd /disk1/postgress/data
 rm -rfv *
  3. Install the postgressql-client on Slave Host.
    
 dnf update -y
 dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm

 dnf -qy module disable postgresql
 dnf install -y postgresql15

 pg_basebackup -h <Master Host IP> -U replication -p 5432 -D
          /var/lib/postgresql/12/main/  -Fp -Xs -P -R
  4. Restart the postgress DB on Slave.
    
 cd /home/kgunda/postgress/
 systemctl stop docker; systemctl start docker

 docker-compose down
 docker-compose up -d
  5. Validate Replication.
     psql -U
      <username> -W -d analytics -h localhost -p 5432
 SELECT * FROM pg_stat_replication;
  6. Create required tables and indexes.
    CREATE TABLE public.sst_events (
    apid character varying(128),
    hostname character varying(128),
    event_type character varying(64) NOT NULL,
    input_event text,
    cid character varying(64) NOT NULL,
    ds character varying(64) NOT NULL,
    ts bigint NOT NULL,
    reason character varying(1024),
    he character varying(256),
    hm character varying(256),
    vizid character varying(256),
    wspn character varying(256),
    subid character varying(1024),
    id character varying(1024),
    crmid character varying(256),
    ts_div_1000 bigint GENERATED ALWAYS AS (ts / 1000) STORED);

CREATE INDEX sst_events_apid ON public.sst_events (apid);
CREATE INDEX sst_events_cid ON public.sst_events (cid);
CREATE INDEX sst_events_crmid ON public.sst_events (crmid);
CREATE INDEX sst_events_ds ON public.sst_events (ds);
CREATE INDEX sst_events_event_type ON public.sst_events (event_type);
CREATE INDEX sst_events_he ON public.sst_events (he);
CREATE INDEX sst_events_hm ON public.sst_events (hm);
CREATE INDEX sst_events_id ON public.sst_events (id);
CREATE INDEX sst_events_subid ON public.sst_events (subid);
CREATE INDEX sst_events_ts ON public.sst_events (ts);
CREATE INDEX sst_events_ts_div_1000_temp ON public.sst_events (ts_div_1000 DESC);
CREATE INDEX sst_events_vizid ON public.sst_events (vizid);
CREATE INDEX sst_events_wspn ON public.sst_events (wspn);

CREATE TABLE public.sst_demo_events (
    apid        VARCHAR(128),
    hostname    VARCHAR(128),
    event_type  VARCHAR(64) NOT NULL,
    input_event TEXT,
    cid         VARCHAR(64) NOT NULL,
    ds          VARCHAR(64) NOT NULL,
    ts          BIGINT NOT NULL,
    reason      VARCHAR(1024),
    he          VARCHAR(256),
    hm          VARCHAR(256),
    vizid       VARCHAR(256),
    wspn        VARCHAR(256),
    subid       VARCHAR(1024),
    id          VARCHAR(1024),
    crmid       VARCHAR(256),
    ts_div_1000 BIGINT GENERATED ALWAYS AS (ts / 1000) STORED
);

CREATE INDEX sst_demo_events_apid ON public.sst_demo_events (apid);
CREATE INDEX sst_demo_events_cid ON public.sst_demo_events (cid);
CREATE INDEX sst_demo_events_crmid ON public.sst_demo_events (crmid);
CREATE INDEX sst_demo_events_ds ON public.sst_demo_events (ds);
CREATE INDEX sst_demo_events_event_type ON public.sst_demo_events (event_type);
CREATE INDEX sst_demo_events_he ON public.sst_demo_events (he);
CREATE INDEX sst_demo_events_hm ON public.sst_demo_events (hm);
CREATE INDEX sst_demo_events_id ON public.sst_demo_events (id);
CREATE INDEX sst_demo_events_subid ON public.sst_demo_events (subid);
CREATE INDEX sst_demo_events_ts ON public.sst_demo_events (ts);
CREATE INDEX sst_demo_events_ts_div_1000_temp ON public.sst_demo_events (ts_div_1000) DESC;
CREATE INDEX sst_demo_events_vizid ON public.sst_demo_events (vizid);
CREATE INDEX sst_demo_events_wspn ON public.sst_demo_events (wspn);

CREATE SEQUENCE source_outbound_id_seq;

CREATE TABLE public.source_outbound (
    input_event TEXT,
    key         VARCHAR,
    event       VARCHAR,
    cid         VARCHAR,
    rid         VARCHAR(255),
    id          INTEGER NOT NULL DEFAULT nextval('source_outbound_id_seq'::regclass),
    ts          BIGINT,
    srcid       INTEGER,
    PRIMARY KEY (id)
);

CREATE INDEX idx_cid ON public.source_outbound (cid);
CREATE INDEX idx_event ON public.source_outbound (event);
CREATE INDEX idx_key ON public.source_outbound (key);
CREATE INDEX idx_rid ON public.source_outbound (rid);
CREATE INDEX idx_srcid ON public.source_outbound (srcid);
CREATE INDEX idx_ts ON public.source_outbound (ts);

CREATE SEQUENCE source_failure_id_seq;

CREATE TABLE public.source_failure (
    input_event  TEXT,
    key          VARCHAR,
    event        VARCHAR,
    cid          VARCHAR,
    errorcode    VARCHAR,
    errordetails TEXT,
    rid          VARCHAR(255),
    id           INTEGER NOT NULL DEFAULT nextval('source_failure_id_seq'::regclass),
    ts           BIGINT,
    srcid        INTEGER,
    PRIMARY KEY (id)
);

CREATE INDEX idx_cid_source_failure ON public.source_failure (cid);
CREATE INDEX idx_errorcode_source_failure ON public.source_failure (errorcode);
CREATE INDEX idx_errordetails ON public.source_failure (errordetails);
CREATE INDEX idx_errordetails_source_failure ON public.source_failure (errordetails);
CREATE INDEX idx_event_source_failure ON public.source_failure (event);
CREATE INDEX idx_key_source_failure ON public.source_failure (key);
CREATE INDEX idx_rid_source_failure ON public.source_failure (rid);
CREATE INDEX idx_srcid_source_failure ON public.source_failure (srcid);
CREATE INDEX idx_ts_source_failure ON public.source_failure (ts);

CREATE SEQUENCE destination_outbound_id_seq;

CREATE TABLE public.destination_outbound (
    input_event           TEXT,
    key                   VARCHAR,
    event                 VARCHAR,
    cid                   VARCHAR,
    rid                   VARCHAR(255),
    id                    INTEGER NOT NULL DEFAULT nextval('destination_outbound_id_seq'::regclass),
    ts                    BIGINT,
    destinationinstanceid INTEGER,
    srcid                 INTEGER,
    PRIMARY KEY (id)
);

CREATE INDEX idx_cid_destination_outbound ON public.destination_outbound (cid);
CREATE INDEX idx_destinationinstanceid_destination_outbound ON public.destination_outbound (destinationinstanceid);
CREATE INDEX idx_event_destination_outbound ON public.destination_outbound (event);
CREATE INDEX idx_key_destination_outbound ON public.destination_outbound (key);
CREATE INDEX idx_rid_destination_outbound ON public.destination_outbound (rid);
CREATE INDEX idx_srcid_destination_outbound ON public.destination_outbound (srcid);
CREATE INDEX idx_ts_destination_outbound ON public.destination_outbound (ts);

RDS DB Configuration

This step sets up the MySQL database vrm on your RDS instance (or VM-based MySQL running on port 3307).
# Connect to MySQL using admin credentials
mysql -u admin -h <vm_ip> -P 3307

# Create the database
create database vrm;