Databases

PostgreSQL Instances

Both environments run a primary + read replica setup with streaming replication.

Role	Environment	Server	Port	Volume	Image
Primary	DEV	Azure (`4.180.181.86`)	`5432`	`dev_postgresql_data`	postgres:16-alpine
Replica	DEV	Azure (`4.180.181.86`)	—	`dev_postgresql_replica_data`	postgres:16-alpine
Primary	UAT	Contabo (`84.247.134.135`)	`5433`	`uat_uat_postgresql_data`	postgres:16-alpine
Replica	UAT	Contabo (`84.247.134.135`)	—	`uat_uat_postgresql_replica_data`	postgres:16-alpine

Replication

Both DEV and UAT use physical streaming replication with a hot standby replica.

How It Works

The primary streams WAL (Write-Ahead Log) records to the replica in real time
The replica continuously replays these records to stay in sync
The replica is read-only — it can serve read queries but cannot accept writes
Replication is asynchronous — the primary does not wait for the replica to confirm
A replication slot (replica_slot_1) ensures the primary retains WAL until the replica has consumed it

Primary Tuning (Write-Optimized)

Both DEV and UAT primaries are tuned with:

Parameter	Value	Purpose
`wal_level`	`logical`	Enables logical replication (also needed for Debezium CDC)
`shared_buffers`	`4 GB`	Main memory cache
`effective_cache_size`	`8 GB`	Planner hint for OS cache
`work_mem`	`16 MB`	Per-operation sort/hash memory
`maintenance_work_mem`	`512 MB`	For VACUUM, CREATE INDEX
`wal_buffers`	`64 MB`	WAL write buffer
`checkpoint_timeout`	`10 min`	Time between checkpoints
`max_wal_size`	`2 GB`	Max WAL before forced checkpoint
`min_wal_size`	`512 MB`	Min WAL to retain
`wal_compression`	`lz4`	Compress WAL to reduce I/O
`checkpoint_completion_target`	`0.9`	Spread checkpoint I/O
`random_page_cost`	`1.1`	Tuned for SSD

Replica Tuning (Read-Optimized)

Both DEV and UAT replicas are tuned with:

Parameter	Value	Purpose
`hot_standby`	`on`	Accept read queries on replica
`hot_standby_feedback`	`on`	Prevent primary from vacuuming rows replica needs
`max_standby_streaming_delay`	`30s`	Max delay before canceling conflicting queries
`shared_buffers`	`4 GB`	Same as primary
`effective_cache_size`	`12 GB`	Higher — more read-focused
`work_mem`	`64 MB`	4x primary — heavier read queries
`maintenance_work_mem`	`256 MB`	Lower — less write maintenance
`random_page_cost`	`1.0`	More aggressive index scan preference
`max_parallel_workers_per_gather`	`4`	Parallel query execution
`max_parallel_workers`	`8`	Total parallel workers
`huge_pages`	`try`	Use OS huge pages if available

Checking Replication Status

# On the primary — check replica is connected and streaming
docker exec -u postgres $(docker ps -q -f name=dev_postgresql -f status=running | head -1) \
  psql -U sseris -c "SELECT client_addr, state, sent_lsn, replay_lsn FROM pg_stat_replication;"

# On the replica — confirm it's in recovery (read-only standby)
docker exec -u postgres $(docker ps -q -f name=dev_postgresql-replica -f status=running) \
  psql -U sseris -c "SELECT pg_is_in_recovery();"
# Should return: t (true)

Important: pg_hba.conf

The primary must have this line in pg_hba.conf to allow replication connections:

host replication sseris all scram-sha-256

This is persisted in the primary’s volume. If the volume is ever recreated, this line must be added again and PostgreSQL reloaded:

docker exec $(docker ps -q -f name=dev_postgresql -f status=running | head -1) \
  sh -c "echo 'host replication sseris all scram-sha-256' >> /var/lib/postgresql/data/pg_hba.conf"
docker exec -u postgres $(docker ps -q -f name=dev_postgresql -f status=running | head -1) \
  pg_ctl reload -D /var/lib/postgresql/data

Connecting to a Database

The database user is sseris (not postgres). The replica uses the same credentials as its primary.

From the Server (via Docker)

# DEV primary
docker exec -it $(docker ps -q -f name=dev_postgresql -f status=running | head -1) psql -U sseris

# DEV replica (read-only)
docker exec -it $(docker ps -q -f name=dev_postgresql-replica -f status=running) psql -U sseris

# UAT primary
docker exec -it $(docker ps -q -f name=uat_postgresql -f status=running | head -1) psql -U sseris

# UAT replica (read-only)
docker exec -it $(docker ps -q -f name=uat_postgresql-replica -f status=running) psql -U sseris

From Outside (if port is exposed)

# DEV (primary only — replica port not exposed)
psql -h 4.180.181.86 -p 5432 -U sseris

# UAT (primary only — replica port not exposed)
psql -h 84.247.134.135 -p 5433 -U sseris

Common Database Operations

List All Databases

\l

Check Database Size

SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname))
FROM pg_database ORDER BY pg_database_size(pg_database.datname) DESC;

Check Active Connections

SELECT count(*) FROM pg_stat_activity;

Connection Pooling

PgBouncer sits in front of the primary PostgreSQL in both environments to manage connection pooling. Application services connect to PgBouncer (pgbouncer:6432), which manages connections to the actual database.

Other Data Stores

Store	Purpose	DEV	UAT
Redis	Caching, session storage	`dev_redis_data`	`uat_uat_redis_data`
Kafka	Event streaming	`dev_kafka_data`	`uat_uat_kafka_data`
RabbitMQ	Message queuing (notifications)	`dev_rabbitmq_data`	`uat_uat_rabbitmq_data`
ImmuDB	Immutable audit trail	`dev_immudb_data`	`uat_uat_immudb_data`
Garage S3	Object/file storage	`dev_garage_data`	`uat_uat_garage_data`