Docs · Guides

First Postgres in 10 minutes

Self-hosted Postgres 16 on a CloudNx VM. Production-grade defaults, a dedicated data volume, and a one-line backup cron. Until Managed Postgres reaches your region, this is the recommended path.

1. Provision

cloudnx instance create \
  --name pg-01 \
  --type cnx.m1.large \
  --image ubuntu-24.04 \
  --ssh-key my-key

cloudnx volume create --name pg-data --size 100
cloudnx volume attach pg-data --instance pg-01

cnx.m1.large (4 vCPU, 8 GiB) handles ~500 RPS of typical OLTP. A 100 GB data volume is detachable for restore drills.

2. Install Postgres on the new VM

ssh ubuntu@<your-vm-ip>
sudo apt-get update
sudo apt-get install -y postgresql-16 postgresql-contrib

3. Move data dir to the attached volume

# Format and mount the volume:
sudo mkfs.ext4 /dev/vdb
echo "/dev/vdb /var/lib/postgresql ext4 defaults,noatime 0 2" | sudo tee -a /etc/fstab
sudo systemctl stop postgresql
sudo rsync -av /var/lib/postgresql/ /tmp/pg-old/
sudo mount /var/lib/postgresql
sudo rsync -av /tmp/pg-old/ /var/lib/postgresql/
sudo chown -R postgres:postgres /var/lib/postgresql
sudo systemctl start postgresql

4. Harden access

# Listen on private network only (not the public IP):
sudo sed -i "s/^#listen_addresses.*/listen_addresses = '10.10.0.0\/24,localhost'/" \
  /etc/postgresql/16/main/postgresql.conf

# Force md5/scram passwords (not 'trust'):
echo "host all all 10.10.0.0/16 scram-sha-256" | sudo tee -a /etc/postgresql/16/main/pg_hba.conf
sudo systemctl reload postgresql

5. Create a non-superuser app user

sudo -u postgres psql <<EOF
CREATE USER myapp PASSWORD '<long-random-passphrase>';
CREATE DATABASE myapp_prod OWNER myapp;
REVOKE ALL ON SCHEMA public FROM PUBLIC;
GRANT ALL ON SCHEMA public TO myapp;
EOF

6. Firewall: only your app VMs can reach Postgres

cloudnx firewall add pg-01 tcp 5432 --cidr 10.10.0.0/24
# Nothing else — Postgres should never be reachable from the public internet.

7. Daily logical backup to Object Storage

See the full pattern in Backup strategy. Minimal version:

# /etc/cron.d/pg-daily-backup
0 2 * * * postgres pg_dumpall | gzip | \
  aws s3 --endpoint-url https://s3.cloudnx.in cp - \
  s3://my-backups/pg-$(date +%Y%m%d).sql.gz

8. Continuous WAL streaming (optional, recommended for > 10 GB databases)

For RPO-near-zero, run pgBackRest to ship WAL segments to Object Storage every minute. Point-in-time-recovery to any second in the last 7 days:

sudo apt-get install -y pgbackrest
sudo mkdir -p /etc/pgbackrest /var/lib/pgbackrest
# /etc/pgbackrest/pgbackrest.conf — see pgbackrest docs for the full template.
# Then on cron:  pgbackrest --stanza=main backup

9. Verify your runbook

Quarterly, run the restore drill on a throwaway VM. If you can’t restore in < 30 minutes, your backup strategy isn’t working — fix it before you need it.

When to switch to Managed Postgres

Self-host is great until one of these starts to hurt:

• Engineering hours spent on Postgres-ops > 1 hour/week.
• You need HA failover (primary + standby).
• You need point-in-time recovery without becoming a pgBackRest expert.

At that point, create a managed cluster. Migration is one pg_dump | psql.