PostgreSQL

PostgreSQL (the distro's current release — 16 on Ubuntu 24.04) with optional streaming replication, automated backups, and slow query logging.

Configuration

services:
  db:
    package: postgresql
    config:
      admin_password: "${PG_ADMIN_PASSWORD}"
      replication_password: "${PG_REPL_PASSWORD}"

To add read replicas:

services:
  db:
    package: postgresql
    replicas: 2
    config:
      admin_password: "${PG_ADMIN_PASSWORD}"
      replication_password: "${PG_REPL_PASSWORD}"
      max_connections: 200
      shared_buffers: "512MB"

Parameters

Parameter	Required	Default	Description
admin_password	Yes		Superuser password for the postgres user
replication_password	Yes		Password for the repl_user replication role
replicas	No	0	Number of read replicas
max_connections	No	100	Maximum concurrent connections
shared_buffers	No	auto	Shared buffer pool size (e.g. 256MB), or auto to use 25% of RAM
work_mem	No	4MB	Memory per sort/hash operation
log_min_duration_statement	No	5000	Log queries slower than this (milliseconds, -1 to disable)
keep_daily_backups	No	5	Number of daily full backups to retain
keep_weekly_backups	No	1	Number of weekly backups to retain
keep_monthly_backups	No	1	Number of monthly backups to retain

Replication

When replicas is set, managed creates a primary-replica topology:

• Primary, handles all writes, streams WAL to replicas
• Replicas, read-only hot standbys that follow the primary via streaming replication

Replicas are created using pg_basebackup to clone the primary's data directory, then connect as streaming standbys. Replication uses scram-sha-256 authentication; connections are encrypted with SSL when both sides support it (the default sslmode=prefer).

Checking replication status

From the primary, check connected replicas and lag with a query in the built-in REPL (or the dashboard's replication panel):

managed repl db
db=# SELECT client_addr, state, sent_lsn, replay_lsn FROM pg_stat_replication;

Failover

If the primary fails, promote a replica by connecting to it and running:

SELECT pg_promote();

Then update managed.yaml to point to the new primary.

Backups

Full backups run every 6 hours via pg_dumpall, compressed with gzip and uploaded to object storage.

Retention policy

Backups follow a daily/weekly/monthly retention scheme:

• Daily: keep the most recent backup per calendar day (default: 5 days)
• Weekly: keep the oldest daily per week-of-year bucket (default: 1 week)
• Monthly: keep the oldest daily per calendar month (default: 1 month)

Manual backup

managed service db backup

List backups

managed service db backup-list

Restore from backup

managed service db restore --arg confirm=yes

To restore a specific backup:

managed service db restore --arg backup=backups/db-a3f2/20260404-120000.sql.gz --arg confirm=yes

Commands

Command	Description
managed service <name> backup	Create a full backup now
managed service <name> backup-list	List available backups in storage
managed service <name> restore	Restore from the latest (or specified) backup

Connection

PostgreSQL listens on port 5432, open to private networks only (the firewall allows RFC1918 sources, not the public internet). The easiest way in:

managed repl <service-name>

It opens psql with the password passed through the environment — never on a command line, where it would land in your shell history and be visible to other users on your machine in the process list.

To connect manually, let psql prompt for the password instead of typing it into the command:

psql -U postgres -h <server-private-ip> -p 5432 -W   # from a host on the same private network

After running managed apply, connection strings are printed automatically. Use --expose to reveal passwords.

How it works

The service installs PostgreSQL from the default Ubuntu repositories. The primary is configured with:

• wal_level = replica and GTID-equivalent WAL streaming for replication
• scram-sha-256 authentication for all remote connections
• shared_buffers auto-tuned to 25% of RAM (or set manually)
• pg_stat_statements extension for slow query tracking
• SSL enabled by default
• Logging with daily rotation and configurable slow query threshold
• pg_hba.conf configured for remote password auth and replication