PostgreSQL Setup Guide

This guide helps your team provision, configure, and validate PostgreSQL for a SuprSend self-hosted deployment.
You can use a managed PostgreSQL (recommended for ease of maintenance) or deploy it on your own VMs/Kubernetes.

Version Requirement: PostgreSQL v17 or newer
Required Extensions: pg_cron, pgcrypto, pg_stat_statements, btree_gin, btree_gist
Required Parameters (in postgresql.conf or DB parameter group):
shared_preload_libraries = 'pg_stat_statements,pg_cron'
cron.database_name = 'defaultdb'
cron.max_running_jobs = 1
hot_standby_feedback = on
max_standby_archive_delay = -1
max_standby_streaming_delay = -1
wal_level = logical

Topology & Sizing

Component	Recommended Configuration
Topology	1 primary + 1–2 hot standbys (streaming replication)
Compute	4 vCPU / 16–32 GB RAM
Storage	SSD, 100–200 GB to start
Connections	Use PgBouncer for pooling
Backups	Automated daily + PITR (WAL archival)

Managed PostgreSQL (Recommended)

Managed services simplify upgrades, backups, and high availability.

AWS RDS / Aurora PostgreSQL

Create a PostgreSQL 17+ instance or cluster.
In the parameter group, apply the configuration parameters above.
Ensure wal_level=logical and configure replication if needed.

Google Cloud SQL for PostgreSQL

Create a PostgreSQL 17+ instance.
Add the required parameters in Flags.
Enable all required extensions.

Azure Database for PostgreSQL – Flexible Server

Create a PostgreSQL 17+ flexible server.
Apply the parameters above.
Enable required extensions.

pg_cron is mandatory for SuprSend. Verify that your managed provider supports it before proceeding. If unavailable, consider a self-hosted PostgreSQL deployment.

Self-Hosted on VMs

Install PostgreSQL 17+ (from PGDG or distro repos).

Edit postgresql.conf:

shared_preload_libraries = 'pg_stat_statements,pg_cron'
cron.database_name = 'defaultdb'
cron.max_running_jobs = 1
hot_standby_feedback = on
max_standby_archive_delay = -1
max_standby_streaming_delay = -1
wal_level = logical

Restart PostgreSQL.
Configure replication and backup scripts as needed.

Self-Hosted on Kubernetes

You can deploy PostgreSQL using a Postgres Operator or Helm chart. Example ConfigMap fragment:

apiVersion: v1
kind: ConfigMap
metadata:
  name: postgres-conf
data:
  postgresql.conf: |
    shared_preload_libraries = 'pg_stat_statements,pg_cron'
    cron.database_name = 'defaultdb'
    cron.max_running_jobs = 1
    hot_standby_feedback = on
    max_standby_archive_delay = -1
    max_standby_streaming_delay = -1
    wal_level = logical

Mount this ConfigMap into your container or operator-managed config.

Create Required Databases

Run the following SQL on your instance:

CREATE DATABASE suprsend;
CREATE DATABASE defaultdb;
CREATE DATABASE pronto;
CREATE DATABASE svix;
CREATE DATABASE temporal;
CREATE DATABASE temporal_visibility;

Enable Required Extensions

Connect to the defaultdb database and enable pg_cron. This extension can only be installed in one database, which must be defaultdb:

\c defaultdb
CREATE EXTENSION IF NOT EXISTS pg_cron;

Run the following commands in each of the six databases:

CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS btree_gin;
CREATE EXTENSION IF NOT EXISTS btree_gist;

cron.database_name = 'defaultdb' means scheduled jobs run by default in the defaultdb database.

Database Access

SuprSend requires a superuser role to operate correctly. Use the superuser credentials (username and password) when configuring SuprSend’s database connection.

For high concurrency, use PgBouncer in transaction pooling mode.

Replication, HA & Backups

Enable streaming replication with 1–2 replicas.
Ensure hot_standby_feedback=on and wal_level=logical.

Recommended replication parameters:

max_wal_senders = 10
max_replication_slots = 10

Use WAL archiving for point-in-time recovery.
On managed platforms, enable automated backups.

Observability & Maintenance

pg_stat_statements: helps analyze query performance.
Monitor:
- CPU, memory, IOPS
- Connection usage
- Replication lag
- Autovacuum activity

Example pg_cron job:

SELECT cron.schedule('analyze-all', '0 2 * * *', $$VACUUM (ANALYZE);$$);

Security & Networking

Enforce TLS for connections.
Restrict access to VPC/VNet or private subnets.
Rotate credentials regularly.
Store secrets in a managed secret manager (for example, AWS Secrets Manager).

Validation Checklist

-- Check version
SELECT version();

-- Verify parameters
SHOW shared_preload_libraries;
SHOW cron.database_name;
SHOW wal_level;

-- Confirm databases
SELECT datname FROM pg_database
WHERE datname IN ('suprsend','defaultdb','pronto','svix','temporal','temporal_visibility');

-- Confirm extensions
SELECT extname FROM pg_extension
WHERE extname IN ('pg_cron','pgcrypto','pg_stat_statements','btree_gin','btree_gist');

All checks should return expected values.

SuprSend Helm Configuration

Once your PostgreSQL instance is configured with all required databases and extensions, configure SuprSend to use it.

This section shows only the PostgreSQL-specific configuration. You must also configure other required secrets and values for SuprSend to work properly. See the complete configuration guide: SuprSend Installation Guide

Kubernetes Secret Configuration

First, add the PostgreSQL-specific secrets to your suprsend-secrets.yaml:

# ============================================
# PostgreSQL Configuration (this guide)
# ============================================
# username of the PostgreSQL database (superuser)
pgDbUserKey: "YOUR_SUPERUSER_USERNAME"
# password of the PostgreSQL database
pgDbPasswordKey: "YOUR_PASSWORD"

Helm Values Configuration

Then add the following to your suprsend-values.yaml (along with other required configuration):

# hostname of PostgreSQL database instance
pgDbHost: "your-postgres-host.region.rds.amazonaws.com"
# port for the PostgreSQL database instance (e.g: 5432)
pgDbPort: "5432"
# SSL mode for the connection: 'disable', 'require', 'verify-ca', or 'verify-full'
pgDbSslmode: "require"
# postgres connection pool size within application (e.g: 10)
pgMaxPoolSize: "10"
# hostname of read-only PostgreSQL replica instance (optional)
pgDbReplicaHost: ""

SuprSend will automatically connect to the required databases (suprsend, pronto, svix, defaultdb) using the same host and credentials. Ensure all databases are created on the same PostgreSQL instance. The above configuration goes under global.config section.

Final Steps

✅ Provision PostgreSQL 17+
✅ Apply configuration parameters
✅ Create six databases
✅ Enable required extensions
✅ Configure superuser access
✅ Configure replication & backups
✅ Run validation checklist

Next Steps:
Once PostgreSQL is ready, proceed with deploying the rest of the SuprSend stack using the Helm charts provided in the deployment documentation.

FAQ

Do we need all extensions in every database?

Yes. To ensure consistent functionality across services, enable them in all databases.

Why wal_level=logical?

Required for CDC, logical replication, and certain SuprSend data sync features.

What if a managed DB doesn't support pg_cron?

pg_cron is mandatory for SuprSend - there is no fallback. If your managed provider does not support pg_cron, you will need to use a self-hosted PostgreSQL deployment instead.

Getting Started

Installation

Post-Installation

CLI & Automation

Updates and Versioning

Topology & Sizing

Managed PostgreSQL (Recommended)

AWS RDS / Aurora PostgreSQL

Google Cloud SQL for PostgreSQL

Azure Database for PostgreSQL – Flexible Server

Self-Hosted on VMs

Self-Hosted on Kubernetes

Create Required Databases

Enable Required Extensions

Database Access

Replication, HA & Backups

Observability & Maintenance

Security & Networking

Validation Checklist

SuprSend Helm Configuration

Kubernetes Secret Configuration

Helm Values Configuration

Final Steps

FAQ

​Topology & Sizing

​Managed PostgreSQL (Recommended)

​AWS RDS / Aurora PostgreSQL

​Google Cloud SQL for PostgreSQL

​Azure Database for PostgreSQL – Flexible Server

​Self-Hosted on VMs

​Self-Hosted on Kubernetes

​Create Required Databases

​Enable Required Extensions

​Database Access

​Replication, HA & Backups

​Observability & Maintenance

​Security & Networking

​Validation Checklist

​SuprSend Helm Configuration

​Kubernetes Secret Configuration

​Helm Values Configuration

​Final Steps

​FAQ

Topology & Sizing

Managed PostgreSQL (Recommended)

AWS RDS / Aurora PostgreSQL

Google Cloud SQL for PostgreSQL

Azure Database for PostgreSQL – Flexible Server

Self-Hosted on VMs

Self-Hosted on Kubernetes

Create Required Databases

Enable Required Extensions

Database Access

Replication, HA & Backups

Observability & Maintenance

Security & Networking

Validation Checklist

SuprSend Helm Configuration

Kubernetes Secret Configuration

Helm Values Configuration

Final Steps

FAQ