VRS PostgreSQL HA Deployment Guide

Internet should be available on each machine (to pull Docker images and Python packages).
All commands assume root. Trailing # in examples is not part of the command.

This guide deploys a 3-node PostgreSQL HA cluster for VRS metadata (vrs + keycloak_vrs) using PostgreSQL 17 + Patroni 4.1.3 + etcd v3.5.21 + HAProxy.

It does not cover CUCM SIP / recorder HA or audio file rsync. Those are separate from database HA.

Before You Begin

Follow sections 1 → 15 in order. Replace every <...> placeholder with your site values before you start the containers. The placeholder list is in section 1. Use the same Patroni passwords on all three hosts.

• This guide is for a first-time HA cluster bring-up (ETCD_INITIAL_CLUSTER_STATE: new).

• Create the same stack files on all three hosts; only the node env file differs (pgha1 / pgha2 / pgha3).

• If you edit entrypoint.sh on Windows, save it with LF line endings (not CRLF).

• <THIS_HOST_IP> is the IP of the host you are configuring right now (not always Node 1).

1. Overview

Recorder 1 / Recorder 2 / Replay (or DB node)
   VRS apps  →  <THIS_HOST_IP>:5000  →  HAProxy  →  current PRIMARY
   etcd + Patroni/Postgres on every node (one primary, two replicas)

Placeholders — replace these values in the files listed below before docker compose up. Search the folder for <; there must be no leftovers.

About the three Patroni passwords: invent them for this deployment and keep them identical on all three hosts. They protect cluster internals (postgres / replicator / rewind), not the Linux OS login. The application DB password is separate and is only needed when creating the app role and pointing applications at HA.

About <PG_SUBNET>: If all three nodes share a LAN like 10.0.0.11/12/13, use 10.0.0.0/24. If node IPs are on different subnets (for example 10.0.0.11, 10.1.0.22, 192.168.5.33), one /24 will not cover them; use a CIDR that truly includes all three, or add separate pg_hba lines for each node IP/subnet. Replication fails if a node IP is outside the allowed range.

Illustrative example values (do not use these in production; replace with your site values):

2. Prerequisites (Each of the 3 Hosts)

• Linux x86_64

• Docker Engine ≥ 24 and Docker Compose v2

• Time sync (NTP/chrony)

• Static IPs for all three nodes

• Enough RAM/disk for VRS apps and Postgres

• Open between the three nodes:

Optional firewall example:

firewall-cmd --add-port=2379/tcp --permanent
firewall-cmd --add-port=2380/tcp --permanent
firewall-cmd --add-port=15432/tcp --permanent
firewall-cmd --add-port=8008/tcp --permanent
firewall-cmd --add-port=5000/tcp --permanent
firewall-cmd --add-port=5001/tcp --permanent
firewall-cmd --reload

3. Create the Folder Structure (All 3 Hosts)

mkdir -p /root/vrs-pg-ha/{patroni,haproxy,nodes}
cd /root/vrs-pg-ha

Create the files below on all three hosts (same content except node env files in section 4).

3.1 docker-compose.yml

services:
  etcd:
    image: quay.io/coreos/etcd:v3.5.21
    container_name: vrs-pgha-etcd
    restart: unless-stopped
    environment:
      ETCD_NAME: ${PATRONI_NAME}
      ETCD_DATA_DIR: /etcd-data
      ETCD_LISTEN_CLIENT_URLS: http://0.0.0.0:2379
      ETCD_ADVERTISE_CLIENT_URLS: http://${NODE_IP}:2379
      ETCD_LISTEN_PEER_URLS: http://0.0.0.0:2380
      ETCD_INITIAL_ADVERTISE_PEER_URLS: http://${NODE_IP}:2380
      ETCD_INITIAL_CLUSTER: pgha1=http://<NODE1_IP>:2380,pgha2=http://<NODE2_IP>:2380,pgha3=http://<NODE3_IP>:2380
      ETCD_INITIAL_CLUSTER_STATE: new
      ETCD_INITIAL_CLUSTER_TOKEN: vrs-pgha-etcd
      ETCD_HEARTBEAT_INTERVAL: 500
      ETCD_ELECTION_TIMEOUT: 5000
    volumes:
      - etcd-data:/etcd-data
    ports:
      - "2379:2379"
      - "2380:2380"
    networks:
      - pgha

  patroni:
    build:
      context: ./patroni
    image: vrs-pgha-patroni:17-4.1.3
    container_name: vrs-pgha-patroni
    restart: unless-stopped
    hostname: ${PATRONI_NAME}
    depends_on:
      - etcd
    environment:
      PATRONI_SCOPE: vrs-pgha
      PATRONI_NAME: ${PATRONI_NAME}
      PATRONI_RESTAPI_LISTEN: 0.0.0.0:8008
      PATRONI_RESTAPI_CONNECT_ADDRESS: ${NODE_IP}:8008
      PATRONI_POSTGRESQL_LISTEN: 0.0.0.0:5432
      PATRONI_POSTGRESQL_CONNECT_ADDRESS: ${NODE_IP}:15432
      # Do NOT use PATRONI_ETCD_* env names — those force etcd v2 API.
      LAB_ETCD_HOSTS: <NODE1_IP>:2379,<NODE2_IP>:2379,<NODE3_IP>:2379
      PATRONI_SUPERUSER_PASSWORD: ${SUPERUSER_PASSWORD}
      PATRONI_REPLICATION_PASSWORD: ${REPLICATION_PASSWORD}
      PATRONI_REWIND_PASSWORD: ${REWIND_PASSWORD}
    volumes:
      - ./patroni/patroni.yml.tpl:/etc/patroni/patroni.yml.tpl:ro
      - pg-data:/var/lib/postgresql/data
    ports:
      - "15432:5432"
      - "8008:8008"
    networks:
      - pgha

  haproxy:
    image: haproxy:2.9-alpine
    container_name: vrs-pgha-haproxy
    restart: unless-stopped
    depends_on:
      - patroni
    volumes:
      - ./haproxy/haproxy.cfg:/usr/local/etc/haproxy/haproxy.cfg:ro
    ports:
      - "5000:5000"
      - "5001:5001"
    networks:
      - pgha

volumes:
  etcd-data:
  pg-data:

networks:
  pgha:
    driver: bridge

Where to change: in this file, replace every <NODE1_IP>, <NODE2_IP>, and <NODE3_IP> (etcd cluster URLs and LAB_ETCD_HOSTS) with the three host IPs. Do this on all three hosts before starting.

3.2 patroni/Dockerfile

FROM postgres:17

USER root

RUN apt-get update \
 && apt-get install -y --no-install-recommends \
      python3 \
      python3-pip \
      python3-psycopg2 \
      python3-venv \
      curl \
      ca-certificates \
      gosu \
 && python3 -m venv /opt/patroni-venv \
 && /opt/patroni-venv/bin/pip install --no-cache-dir "patroni[etcd3]==4.1.3" psycopg2-binary \
 && ln -sf /opt/patroni-venv/bin/patroni /usr/local/bin/patroni \
 && ln -sf /opt/patroni-venv/bin/patronictl /usr/local/bin/patronictl \
 && rm -rf /var/lib/apt/lists/*

COPY entrypoint.sh /entrypoint.sh
RUN chmod +x /entrypoint.sh

ENV PATH="/opt/patroni-venv/bin:${PATH}"

ENTRYPOINT ["/entrypoint.sh"]

Where to change: no site placeholders in this file. Keep it identical on all three hosts.

3.3 patroni/entrypoint.sh

#!/bin/bash
set -euo pipefail

TEMPLATE="/etc/patroni/patroni.yml.tpl"
CONFIG="/var/run/postgresql/patroni.yml"

if [[ ! -f "$TEMPLATE" ]]; then
  echo "Missing $TEMPLATE"
  exit 1
fi

mkdir -p /var/lib/postgresql/data /var/run/postgresql
chown -R postgres:postgres /var/lib/postgresql/data /var/run/postgresql
chmod 700 /var/lib/postgresql/data || true

rm -f "$CONFIG" /tmp/patroni.yml
sed \
  -e "s|__SCOPE__|${PATRONI_SCOPE}|g" \
  -e "s|__NAME__|${PATRONI_NAME}|g" \
  -e "s|__RESTAPI_CONNECT_ADDRESS__|${PATRONI_RESTAPI_CONNECT_ADDRESS}|g" \
  -e "s|__RESTAPI_LISTEN__|${PATRONI_RESTAPI_LISTEN}|g" \
  -e "s|__POSTGRESQL_CONNECT_ADDRESS__|${PATRONI_POSTGRESQL_CONNECT_ADDRESS}|g" \
  -e "s|__POSTGRESQL_LISTEN__|${PATRONI_POSTGRESQL_LISTEN}|g" \
  -e "s|__ETCD_HOSTS__|${LAB_ETCD_HOSTS}|g" \
  -e "s|__SUPERUSER_PASSWORD__|${PATRONI_SUPERUSER_PASSWORD}|g" \
  -e "s|__REPLICATION_PASSWORD__|${PATRONI_REPLICATION_PASSWORD}|g" \
  -e "s|__REWIND_PASSWORD__|${PATRONI_REWIND_PASSWORD}|g" \
  "$TEMPLATE" > "$CONFIG"

chown postgres:postgres "$CONFIG"
chmod 600 "$CONFIG"

unset PATRONI_ETCD_HOSTS PATRONI_ETCD_HOST PATRONI_ETCD_URL PATRONI_ETCD_PROXY || true

exec gosu postgres patroni "$CONFIG"

chmod +x /root/vrs-pg-ha/patroni/entrypoint.sh

Where to change: no site placeholders in this file. If you edit it on Windows, save with LF line endings before copying to Linux. Keep it identical on all three hosts.

3.4 patroni/patroni.yml.tpl

scope: __SCOPE__
namespace: /vrs/
name: __NAME__

restapi:
  listen: __RESTAPI_LISTEN__
  connect_address: __RESTAPI_CONNECT_ADDRESS__

etcd3:
  hosts: __ETCD_HOSTS__

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    retry_timeout: 10
    maximum_lag_on_failover: 1048576
    synchronous_mode: true
    synchronous_mode_strict: false
    synchronous_node_count: 1
    postgresql:
      use_pg_rewind: true
      parameters:
        max_connections: 100
        shared_buffers: 128MB
        wal_level: replica
        hot_standby: "on"
        max_wal_senders: 10
        max_replication_slots: 10
        wal_keep_size: 256MB
  initdb:
    - encoding: UTF8
    - data-checksums

postgresql:
  listen: __POSTGRESQL_LISTEN__
  connect_address: __POSTGRESQL_CONNECT_ADDRESS__
  data_dir: /var/lib/postgresql/data/pgdata
  pgpass: /tmp/pgpass
  authentication:
    replication:
      username: replicator
      password: __REPLICATION_PASSWORD__
    superuser:
      username: postgres
      password: __SUPERUSER_PASSWORD__
    rewind:
      username: rewind
      password: __REWIND_PASSWORD__
  parameters:
    unix_socket_directories: /var/run/postgresql
  pg_hba:
    - local all all trust
    - host all all 127.0.0.1/32 md5
    - host all all ::1/128 md5
    - host all all 0.0.0.0/0 md5
    - host replication replicator 127.0.0.1/32 md5
    - host replication replicator ::1/128 md5
    - host replication replicator <PG_SUBNET> md5

tags:
  nofailover: false
  noloadbalance: false
  clonefrom: false
  nosync: false

Where to change: only the pg_hba line ending with <PG_SUBNET> md5. Use a CIDR that includes all three node IPs. Apply the same file on all three hosts.

3.5 haproxy/haproxy.cfg

global
  maxconn 200
  log stdout format raw local0

defaults
  log global
  mode tcp
  retries 3
  timeout client 30m
  timeout connect 5s
  timeout server 30m
  option clitcpka

listen postgres_rw
  bind *:5000
  option httpchk GET /primary
  http-check expect status 200
  default-server inter 3s fall 3 rise 2 on-marked-down shutdown-sessions
  server pgha1 <NODE1_IP>:15432 maxconn 100 check port 8008
  server pgha2 <NODE2_IP>:15432 maxconn 100 check port 8008
  server pgha3 <NODE3_IP>:15432 maxconn 100 check port 8008

listen postgres_ro
  bind *:5001
  option httpchk GET /replica
  http-check expect status 200
  default-server inter 3s fall 3 rise 2
  server pgha1 <NODE1_IP>:15432 maxconn 100 check port 8008
  server pgha2 <NODE2_IP>:15432 maxconn 100 check port 8008
  server pgha3 <NODE3_IP>:15432 maxconn 100 check port 8008

Where to change: in every server pghaN <NODEx_IP>:15432 line (RW and RO sections), replace the placeholders with the three host IPs. Keep the same file on all three hosts.

4. Node Environment Files

Create one env file per host. Do not mix them. Use the same passwords on all nodes.

Where to change: on each host, set that node’s NODE_IP and the three Patroni passwords. Example for node 1: NODE_IP=10.0.0.11 plus your chosen secrets.

Node 1 — /root/vrs-pg-ha/nodes/pgha1.env

PATRONI_NAME=pgha1
NODE_IP=<NODE1_IP>
SUPERUSER_PASSWORD=<SUPERUSER_PASSWORD>
REPLICATION_PASSWORD=<REPLICATION_PASSWORD>
REWIND_PASSWORD=<REWIND_PASSWORD>

Node 2 — /root/vrs-pg-ha/nodes/pgha2.env

PATRONI_NAME=pgha2
NODE_IP=<NODE2_IP>
SUPERUSER_PASSWORD=<SUPERUSER_PASSWORD>
REPLICATION_PASSWORD=<REPLICATION_PASSWORD>
REWIND_PASSWORD=<REWIND_PASSWORD>

Node 3 — /root/vrs-pg-ha/nodes/pgha3.env

PATRONI_NAME=pgha3
NODE_IP=<NODE3_IP>
SUPERUSER_PASSWORD=<SUPERUSER_PASSWORD>
REPLICATION_PASSWORD=<REPLICATION_PASSWORD>
REWIND_PASSWORD=<REWIND_PASSWORD>

5. Start the Cluster

Risk: ETCD_INITIAL_CLUSTER_STATE: new is only for a first-time cluster. Do not reuse this bring-up on hosts that already have etcd/Patroni data from a previous cluster unless you intentionally rebuild.

On each host (use the matching env file):

cd /root/vrs-pg-ha
cp nodes/pgha1.env .env    # on node 1; use pgha2.env / pgha3.env on other nodes
docker compose build
docker compose up -d
docker compose ps

Start all three hosts within a short window so etcd can form the initial cluster.

docker ps --format 'table {{.Names}}\t{{.Status}}\t{{.Ports}}'

Expected on each host: vrs-pgha-etcd, vrs-pgha-patroni, vrs-pgha-haproxy.

6. Verify Cluster Health

Run these checks on any node after all three hosts are up. Do not continue to cutover until they pass.

Health check 1 — containers on each host

docker ps --format 'table {{.Names}}\t{{.Status}}\t{{.Ports}}'

Expected on each host: vrs-pgha-etcd, vrs-pgha-patroni, vrs-pgha-haproxy.

docker exec vrs-pgha-patroni patronictl -c /var/run/postgresql/patroni.yml list

Health check 2 — Patroni membership

Expected: 1 Leader, 1 Sync Standby, 1 Replica, lag 0.

Health check 3 — HAProxy primary port (:5000)

Docker-based client

docker run --rm --network host \
  -e PGPASSWORD='<SUPERUSER_PASSWORD>' postgres:17 \
  psql -h 127.0.0.1 -p 5000 -U postgres -d postgres \
  -c "SELECT inet_server_addr(), pg_is_in_recovery();"

Service/package-based client (psql installed on the host)

export PGPASSWORD='<SUPERUSER_PASSWORD>'
psql -h 127.0.0.1 -p 5000 -U postgres -d postgres \
  -c "SELECT inet_server_addr(), pg_is_in_recovery();"

pg_is_in_recovery() must return f. Application containers must use the host IP for DB_HOST, not localhost.

If Patroni is restarting:

docker logs --tail 80 vrs-pgha-patroni
docker logs --tail 40 vrs-pgha-etcd

7. Acceptance Test (Before VRS Cutover)

Use either a Docker-based client (docker run ... postgres:17) or a service/package-based client (psql installed on the host). Both talk to the same HA ports. For each step, pick either Docker or service/package — not both, and not in parallel. Use the same style consistently.

7.1 Write via HAProxy (Any Node)

Docker-based client

docker run --rm --network host \
  -e PGPASSWORD='<SUPERUSER_PASSWORD>' postgres:17 \
  psql -h 127.0.0.1 -p 5000 -U postgres -d postgres \
  -c "CREATE TABLE IF NOT EXISTS ha_test(id int primary key, note text);
      INSERT INTO ha_test VALUES (1,'hello-from-primary')
      ON CONFLICT (id) DO UPDATE SET note=EXCLUDED.note;
      SELECT * FROM ha_test;"

Service/package-based client (psql installed on the host)

export PGPASSWORD='<SUPERUSER_PASSWORD>'
psql -h 127.0.0.1 -p 5000 -U postgres -d postgres \
  -c "CREATE TABLE IF NOT EXISTS ha_test(id int primary key, note text);
      INSERT INTO ha_test VALUES (1,'hello-from-primary')
      ON CONFLICT (id) DO UPDATE SET note=EXCLUDED.note;
      SELECT * FROM ha_test;"

7.2 Read on Each Node (Local Postgres Port)

Docker-based client

docker run --rm --network host \
  -e PGPASSWORD='<SUPERUSER_PASSWORD>' postgres:17 \
  psql -h 127.0.0.1 -p 15432 -U postgres -d postgres \
  -c "SELECT * FROM ha_test;"

Service/package-based client (psql installed on the host)

export PGPASSWORD='<SUPERUSER_PASSWORD>'
psql -h 127.0.0.1 -p 15432 -U postgres -d postgres -c "SELECT * FROM ha_test;"

Same row must appear on all three hosts.

7.3 Failover Test

Risk: stopping Patroni on the Leader causes a short write outage while failover runs. Use a maintenance window. Do not delete Docker volumes.

On the current Leader host:

cd /root/vrs-pg-ha
docker compose stop patroni

On another host:

docker exec vrs-pgha-patroni patronictl -c /var/run/postgresql/patroni.yml list

Wait until a new Leader appears, then write again via :5000 on a surviving host (same Docker or service/package write as in 7.1).

Bring the stopped node back:

cd /root/vrs-pg-ha
docker compose up -d --force-recreate patroni
sleep 8
docker exec vrs-pgha-patroni patronictl -c /var/run/postgresql/patroni.yml list

Former Leader should rejoin as Replica (streaming, lag 0).

8. Create / Migrate Application Databases on HA

Use this after the HA cluster is healthy (sections 6–7). Choose Option A for a brand-new empty database with no data to keep, or Option B when the customer already has VRS/Keycloak data in Postgres (Option B includes the cutover steps).

Before running any command in this section: substitute real values for <SUPERUSER_PASSWORD>, <APP_DB_USER>, <APP_DB_PASSWORD>, <APP_DB_NAME>, and <KEYCLOAK_DB_NAME>. Example: vrs / keycloak_vrs. Do not leave angle brackets in SQL.

You can run SQL with a Docker-based client or a service/package-based client (psql/pg_dump/pg_restore installed on the host or jump host, pointed at 127.0.0.1:5000 on an HA node, or <ANY_HA_HOST_IP>:5000 from another machine). For each step, pick either Docker or service/package — not both, and not in parallel. Use the same style consistently. If client packages are missing, install PostgreSQL client tools or use a postgres:17 container.

Option A — New Empty Databases + Schema

Remember: run these create/schema steps once against HA :5000. Recorder B and Replay receive the same objects through replication (section 9).

Option A loads schema into empty databases. For existing customer data, skip this option and use Option B.

Step 1 — Create role and empty databases. Pick Docker or service/package below — not both.

Docker-based client

# Where to run: any HA node or jump host that can reach port 5000.
# Risk: CREATE DATABASE fails if the name already exists (safe). Do not drop databases.

docker run --rm -i --network host \
  -e PGPASSWORD='<SUPERUSER_PASSWORD>' postgres:17 \
  psql -h 127.0.0.1 -p 5000 -U postgres -d postgres <<'SQL'
DO $$
BEGIN
  IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = '<APP_DB_USER>') THEN
    CREATE ROLE <APP_DB_USER> LOGIN PASSWORD '<APP_DB_PASSWORD>';
  ELSE
    ALTER ROLE <APP_DB_USER> WITH LOGIN PASSWORD '<APP_DB_PASSWORD>';
  END IF;
END$$;
SELECT datname FROM pg_database WHERE datname IN ('<APP_DB_NAME>','<KEYCLOAK_DB_NAME>');
SQL

# If the SELECT returned no rows, create the databases:
docker run --rm -i --network host \
  -e PGPASSWORD='<SUPERUSER_PASSWORD>' postgres:17 \
  psql -h 127.0.0.1 -p 5000 -U postgres -d postgres <<'SQL'
CREATE DATABASE <APP_DB_NAME> OWNER <APP_DB_USER>;
CREATE DATABASE <KEYCLOAK_DB_NAME> OWNER <APP_DB_USER>;
SQL

Service/package-based client (psql installed on the host)

# Where to run: any HA node or jump host that can reach port 5000.
# Risk: CREATE DATABASE fails if the name already exists (safe). Do not drop databases.

export PGPASSWORD='<SUPERUSER_PASSWORD>'
psql -h 127.0.0.1 -p 5000 -U postgres -d postgres <<'SQL'
DO $$
BEGIN
  IF NOT EXISTS (SELECT FROM pg_roles WHERE rolname = '<APP_DB_USER>') THEN
    CREATE ROLE <APP_DB_USER> LOGIN PASSWORD '<APP_DB_PASSWORD>';
  ELSE
    ALTER ROLE <APP_DB_USER> WITH LOGIN PASSWORD '<APP_DB_PASSWORD>';
  END IF;
END$$;
SELECT datname FROM pg_database WHERE datname IN ('<APP_DB_NAME>','<KEYCLOAK_DB_NAME>');
SQL

# If the SELECT returned no rows, create the databases:
psql -h 127.0.0.1 -p 5000 -U postgres -d postgres <<'SQL'
CREATE DATABASE <APP_DB_NAME> OWNER <APP_DB_USER>;
CREATE DATABASE <KEYCLOAK_DB_NAME> OWNER <APP_DB_USER>;
SQL

Step 2 — Load schema (empty database only). Pick Docker or service/package below — not both. Check tables first. If application tables already exist and data is valid, skip schema load. Only load schema into an empty database.

Risk: loading postgres.sql into a DB that already has objects may error or duplicate objects.

Docker-based client

# Check existing tables / load schema only if empty (adjust path).

docker run --rm --network host \
  -e PGPASSWORD='<SUPERUSER_PASSWORD>' postgres:17 \
  psql -h 127.0.0.1 -p 5000 -U postgres -d <APP_DB_NAME> -c '\dt'

docker run --rm -i --network host \
  -v /path/to/recording-solution/data/scripts/postgres.sql:/postgres.sql:ro \
  -e PGPASSWORD='<SUPERUSER_PASSWORD>' postgres:17 \
  psql -h 127.0.0.1 -p 5000 -U postgres -d <APP_DB_NAME> -f /postgres.sql

Service/package-based client (psql installed on the host)

# Check existing tables / load schema only if empty (adjust path).

export PGPASSWORD='<SUPERUSER_PASSWORD>'
psql -h 127.0.0.1 -p 5000 -U postgres -d <APP_DB_NAME> -c '\dt'
psql -h 127.0.0.1 -p 5000 -U postgres -d <APP_DB_NAME> \
  -f /path/to/recording-solution/data/scripts/postgres.sql

Step 3 — Grant privileges. Pick Docker or service/package below — not both (same style as the earlier steps).

Docker-based client

docker run --rm -i --network host \
  -e PGPASSWORD='<SUPERUSER_PASSWORD>' postgres:17 \
  psql -h 127.0.0.1 -p 5000 -U postgres -d <APP_DB_NAME> <<'SQL'
GRANT ALL ON SCHEMA public TO <APP_DB_USER>;
GRANT ALL ON ALL TABLES IN SCHEMA public TO <APP_DB_USER>;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO <APP_DB_USER>;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO <APP_DB_USER>;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO <APP_DB_USER>;
SQL

Service/package-based client (psql installed on the host)

export PGPASSWORD='<SUPERUSER_PASSWORD>'
psql -h 127.0.0.1 -p 5000 -U postgres -d <APP_DB_NAME> <<'SQL'
GRANT ALL ON SCHEMA public TO <APP_DB_USER>;
GRANT ALL ON ALL TABLES IN SCHEMA public TO <APP_DB_USER>;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO <APP_DB_USER>;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO <APP_DB_USER>;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO <APP_DB_USER>;
SQL

Option B — Migrate from an Existing Postgres

Use this when the customer already has data in Postgres. Follow the cutover steps below, then the dump/restore command blocks in this option.

Step — Final dump from source. Pick Docker or service/package below — not both. Dependency: pg_dump on the source side (OS package or postgres:17 container). On the source Postgres host:

Service/package-based client (pg_dump installed on the host)

pg_dump -U <source_user> -d <APP_DB_NAME> -Fc -f /tmp/appdb.dump
pg_dump -U <source_user> -d <KEYCLOAK_DB_NAME> -Fc -f /tmp/keycloak.dump

Docker-based client

docker run --rm -v /tmp:/dump -e PGPASSWORD='<source_password>' postgres:17 \
  pg_dump -h <SOURCE_PG_HOST> -p 5432 -U <source_user> -d <APP_DB_NAME> -Fc -f /dump/appdb.dump
# Repeat with -d <KEYCLOAK_DB_NAME> -f /dump/keycloak.dump as needed.

Step — Create empty role/DBs on HA, then restore. Create empty role/DBs first (Option A Step 1 only — do not load postgres.sql). Target databases should be empty before restore. Pick Docker or service/package for restore below — not both.

Risk: pg_restore into a database that already has tables can mix or break schemas. If \dt shows existing application tables, stop and decide: keep them, or restore into a new empty database. This guide does not drop databases.

Docker-based client

docker run --rm --network host \
  -v /path/to/dumps:/migrate:ro \
  -e PGPASSWORD='<SUPERUSER_PASSWORD>' postgres:17 \
  pg_restore -h 127.0.0.1 -p 5000 -U postgres -d <APP_DB_NAME> --no-owner --role=<APP_DB_USER> /migrate/appdb.dump

docker run --rm --network host \
  -v /path/to/dumps:/migrate:ro \
  -e PGPASSWORD='<SUPERUSER_PASSWORD>' postgres:17 \
  pg_restore -h 127.0.0.1 -p 5000 -U postgres -d <KEYCLOAK_DB_NAME> --no-owner --role=<APP_DB_USER> /migrate/keycloak.dump

Service/package-based client (pg_restore installed on the host)

export PGPASSWORD='<SUPERUSER_PASSWORD>'
pg_restore -h 127.0.0.1 -p 5000 -U postgres -d <APP_DB_NAME> --no-owner --role=<APP_DB_USER> /path/to/dumps/appdb.dump
pg_restore -h 127.0.0.1 -p 5000 -U postgres -d <KEYCLOAK_DB_NAME> --no-owner --role=<APP_DB_USER> /path/to/dumps/keycloak.dump

Step — Health check after migration (primary via :5000 and each node via :15432). Pick Docker or service/package below — not both. Adjust the count query to a table that exists in your schema.

Docker-based client

docker run --rm --network host \
  -e PGPASSWORD='<SUPERUSER_PASSWORD>' postgres:17 \
  psql -h 127.0.0.1 -p 15432 -U postgres -d <APP_DB_NAME> \
  -c '\dt' \
  -c 'SELECT COUNT(*) FROM mixed_sessions;'

Service/package-based client (psql installed on the host)

export PGPASSWORD='<SUPERUSER_PASSWORD>'
psql -h 127.0.0.1 -p 15432 -U postgres -d <APP_DB_NAME> \
  -c '\dt' \
  -c 'SELECT COUNT(*) FROM mixed_sessions;'

9. How Schema and Data Reach All Three Hosts

Recorder 1, Recorder 2, and Replay share one logical PostgreSQL cluster (one primary + two replicas). They are not three independent databases you build separately.

Create or restore databases, roles, and tables only once on the current primary (connect through any host HAProxy port 5000). Do not run CREATE DATABASE, load postgres.sql, or recreate roles on Recorder B and Replay by hand.

After the HA cluster is healthy and streaming:

1. Apply Option A (new empty) or Option B (existing data) once through :5000.

2. Patroni streaming replication copies roles, databases, schema, and rows to the other two nodes automatically.

3. Verify on each host using local Postgres port 15432 (same objects and counts; Patroni lag 0).

Does data on Recorder A replicate to B and Replay when all nodes are up?

• If that data is already in the HA primary (created or restored via :5000): yes — it replicates to the other nodes automatically.

• If that data is only on an old standalone Postgres (before cutover): no — use Option B (dump/restore into HA) first; then it replicates.

10. Point VRS Applications to HA Postgres

Update VRS config.env on each VRS application host.

If Keycloak uses the same cluster:

Important: Many VRS Compose files load env_file as ${PWD}/docker/config.env. Run Compose from the parent recording-solution directory (or edit the nested file Compose actually uses). After changing env, force-recreate the application containers.

Risk: force-recreate restarts application containers (brief service interruption). It does not delete Postgres data volumes when you recreate only application services.

cd /path/to/recording-solution
docker compose -f docker/docker-compose-cisco.yml --env-file docker/config.env up -d --force-recreate vrs-apis mixer jtapi-connector
docker exec backend printenv | grep -E '^DB_'

Confirm inside the container:

DB_ENGINE=postgres
DB_HOST=<THIS_HOST_IP>
DB_PORT=5000

If the UI proxy returns 502 after recreate:

docker restart proxy

11. Live Call Validation (Recommended)

1. Note baseline count of mixed_sessions via :5000.

2. Place a test recorded call on an active recorder.

3. Confirm the new row exists on all three DB nodes.

4. Optional: stop Patroni on the current Leader, place another call, confirm insert via surviving nodes, then restore Patroni.

12. Ongoing Cluster Operations

docker exec vrs-pgha-patroni patronictl -c /var/run/postgresql/patroni.yml list

cd /root/vrs-pg-ha
docker compose stop patroni
docker compose up -d --force-recreate patroni

docker logs --tail 100 vrs-pgha-patroni
docker logs --tail 100 vrs-pgha-etcd
docker logs --tail 50 vrs-pgha-haproxy

13. Notes and Common Issues

14. Backup, Final Dump, and Disaster Recovery (Optional)

These are different protections. Do not treat them as the same thing.

14.1 Final dump (cutover)

Use the Option B dump during the maintenance window (section 8, Option B cutover checklist) so existing customer data is copied into HA before apps switch. Keep the old Postgres intact for rollback. That final dump is for cutover safety, not the only backup you will ever need.

14.2 Scheduled backup (recommended after go-live)

On a schedule, dump both application databases from the HA primary endpoint (:5000) to storage outside the three HA hosts (NFS, backup server, object storage). Periodically test-restore a copy.

Service/package-based client (from an HA node or jump host; store files OFF the three HA hosts)

export PGPASSWORD='<SUPERUSER_PASSWORD>'
STAMP=$(date +%F)
pg_dump -h 127.0.0.1 -p 5000 -U postgres -d <APP_DB_NAME> -Fc -f /path/to/offbox/backup/appdb-${STAMP}.dump
pg_dump -h 127.0.0.1 -p 5000 -U postgres -d <KEYCLOAK_DB_NAME> -Fc -f /path/to/offbox/backup/keycloak-${STAMP}.dump

Docker-based client

docker run --rm --network host -v /path/to/offbox/backup:/backup \
  -e PGPASSWORD='<SUPERUSER_PASSWORD>' postgres:17 \
  pg_dump -h 127.0.0.1 -p 5000 -U postgres -d <APP_DB_NAME> -Fc -f /backup/appdb.dump
# Repeat for <KEYCLOAK_DB_NAME> /backup/keycloak.dump as needed.

14.3 Recovery steps

A. One HA node or primary down (cluster still has quorum)

Use Patroni failover (section 7.3 and ongoing operations). Confirmed data should already be on the surviving replicas. Do not restore from dump unless the database is corrupted.

B. Bad cutover / roll back before old Postgres is retired

Point VRS apps back to the old Postgres (still kept ≥ 7 days). Do not drop the old database as part of HA bring-up.

# Rollback cutover — on EACH VRS application host, point config back to the OLD Postgres,
# then recreate app containers. Example docker/config.env values:
#   DB_HOST=<OLD_PG_HOST>
#   DB_PORT=5432
#   DB_NAME=<APP_DB_NAME>
#   DB_USER=<APP_DB_USER>
#   DB_PASSWORD=<APP_DB_PASSWORD>
#   KC_DB_URL=jdbc:postgresql://<OLD_PG_HOST>:5432/<KEYCLOAK_DB_NAME>

cd /path/to/recording-solution
docker compose -f docker/docker-compose-cisco.yml --env-file docker/config.env \
  up -d --force-recreate vrs-apis mixer jtapi-connector
docker exec backend printenv | grep -E '^DB_'
# Confirm DB_HOST / DB_PORT show the OLD Postgres, not HA :5000.

C. Disaster — HA cluster lost or all Postgres volumes wiped

1. Rebuild an empty HA cluster (sections 1–7). Use ETCD_INITIAL_CLUSTER_STATE: new only for a true first-time empty bring-up on clean hosts.

2. Create empty roles/databases on the new primary (Option A create steps only — do not load schema if you will restore dumps).

3. Restore the latest off-box dumps into those empty databases via :5000 (same pg_restore pattern as Option B).

4. Verify on all three nodes (:15432, lag 0, row counts).

5. Point applications to HA (section 10).

# Disaster recovery C — after you rebuild HA (sections 1–7) and create EMPTY role/DBs
# (Option A create only; do NOT load postgres.sql if restoring dumps):

# Service/package restore from OFF-BOX backup files:
export PGPASSWORD='<SUPERUSER_PASSWORD>'
pg_restore -h 127.0.0.1 -p 5000 -U postgres -d <APP_DB_NAME> \
  --no-owner --role=<APP_DB_USER> /path/to/offbox/backup/appdb-YYYY-MM-DD.dump
pg_restore -h 127.0.0.1 -p 5000 -U postgres -d <KEYCLOAK_DB_NAME> \
  --no-owner --role=<APP_DB_USER> /path/to/offbox/backup/keycloak-YYYY-MM-DD.dump

# Verify on each HA host (local Postgres port):
psql -h 127.0.0.1 -p 15432 -U postgres -d <APP_DB_NAME> \
  -c '\dt' \
  -c 'SELECT COUNT(*) FROM mixed_sessions;'

# Then point apps to HA (section 10). Docker restore alternative: same as Option B.

Note: HA + backups protect database metadata (vrs / keycloak_vrs). Recording audio/WAV files need the site media/rsync process separately.

15. References

• VRS Installation Guide (Postgres schema / postgres.sql)

• Stack: PostgreSQL 17, Patroni 4.1.3, etcd v3.5.21, HAProxy 2.9