Installing PostgreSQL

This section provides a step-by-step guide to installing PostgreSQL.

Procedure

Install PostgreSQL on the Master VM.

Mount an external file system disk to a VM at /data.
```
mount external file system disk to the VM at /data
```

Perform docker installation with below commands.

yum update -y
yum install -y yum-utils
yum-config-manager --add-repo https://download.docker.com/linux/rhel/docker-ce.repo
yum install docker-ce docker-ce-cli containerd.io docker-buildx-plugin docker-compose-plugin --allowerasing
wget https://github.com/opencontainers/runc/releases/download/v1.1.12/runc.amd64 -O /usr/local/sbin/runc
systemctl start 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
systemctl status docker
ln -s /data /disk1
mkdir -p /disk1/postgress
mkdir -p /disk1/postgress/data

Create a docker compose file as shown below.

- vi /home/user/postgres/docker-compose.yaml

version: "3"
services:
postgres:
image: postgres:15.3
restart: always
environment:
POSTGRES_USER: cdpadmin
POSTGRES_PASSWORD: ugGShnko8j5rNbsoA245Iw
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://cdpadmin:ugGShnko8j5rNbsoA245Iw@10.214.101.138:5432/analytics?sslmode=disable"
links:
- postgres
volumes:
db-data:
driver: local
driver_opts:
type: none
o: bind
device: /disk1/postgress/data

Deploy docker containers using a compose file and validate the containers are running.

cd /home/user/postgres
docker-compose up -d
docker ps
on inside postgres container run the below command
psql -U cdpadmin -W -d analytics -h localhost -p 5432

Install PostgreSQL on the Slave VM.

Mount an external file system disk to a VM at /data.
```
mount external file system disk to the VM at /data
```

Perform docker installation with below commands.

yum update -y
yum install -y yum-utils
yum-config-manager --add-repo https://download.docker.com/linux/rhel/docker-ce.repo
yum install docker-ce docker-ce-cli containerd.io docker-buildx-plugin docker-compose-plugin --allowerasing
wget https://github.com/opencontainers/runc/releases/download/v1.1.12/runc.amd64 -O /usr/local/sbin/runc
systemctl start 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
systemctl status docker
ln -s /data /disk1
mkdir -p /disk1/postgress
mkdir -p /disk1/postgress/data

Create a docker compose file.

- vi /home/user/postgress/docker-compose.yaml

version: "3"
services:
postgres:
image: postgres:15.3
restart: always
environment:
POSTGRES_USER: cdpadmin
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://cdpadmin: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

Deploy docker containers using a compose file and validate the containers are running.

cd /home/user/postgress
docker-compose up -d
docker ps
on inside postgres container run the below command
psql -U cdpadmin -W -d analytics -h localhost -p 5432

Whitelist ports to allow communication between Master and Slave nodes. Once the Postgress installation is completed on both Master and Slave, you need to white-list the 5432 and 9187 ports from Master and Slave hosts (Inbound and Outbound).

Source: Master and Slave Hosts

Destination : Kubernetes Pods.

Set up Replication on the Master.

Connect to database and create a user with privilege.

psql -U cdpadmin -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, update the below lines and save the file.
```
max_connections = 1000
wal_level = replica
max_wal_size = 100GB
max_wal_senders = 10
wal_keep_size = 3000
```
Similarly, edit the /disk1/postgress/data/pg_hba.conf file, add the below line and save the file.
```
host replication replication <Slave Host IP>/0 md5
```

After updating the postgresql.conf and pg_hba.conf, stop and start the postgress container.


cd /home/kgunda/postgress/
systemctl stop docker
systemctl start docker
cd /home/user/postgress
docker-compose down
docker-compose up -d
docker ps

Set up Replication on the Slave.

Similarly, perform replication on Slave using the below commands.

psql -U cdpadmin -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, update the below lines and save the 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, add the below line and save the file.
```
host replication replication <Master Host IP>/0 md5
cd /disk1/postgress/data
rm -rfv *
```

For pg_basebackup working, 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

Once postgressql-client installation completed, run the below command from Slave.
```
pg_basebackup -h <Master Host IP> -U replication -p 5432 -D /var/lib/postgresql/12/main/ -Fp -Xs -P -R
```

After pg_basebackup completed, restart the postgress DB on Slave.


cd /home/user/postgres
systemctl stop docker
systemctl start docker
docker-compose down
docker-compose up -d

Validate replication and create tables, indexes, and sequences.

Valid the replication on Master VM.

psql -U cdpadmin -W -d analytics -h localhost -p 5432
SELECT * FROM pg_stat_replication;

Once replication is successfully validated, create the following tables.

psql -U cdpadmin -W -d analytics -h localhost -p 5432

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 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 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 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 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 the following index.

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 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 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 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 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);

Similarly, create Sequence.

CREATE SEQUENCE source_outbound_id_seq;
CREATE SEQUENCE source_failure_id_seq;
CREATE SEQUENCE destination_outbound_id_seq;