# Self-Managed PostgreSQL

Source: https://docs.quake.ai/resources/iac-templates/self-managed-postgres
Markdown: https://docs.quake.ai/resources/iac-templates/self-managed-postgres.md

---

# Self-managed PostgreSQL

This pattern composes Compute, Network, and Block Storage.

## What this template does

Provisions a self-managed PostgreSQL server with private networking and WAL archiving:

- Dedicated compute instance optimized for database workloads
- Block storage volume formatted and mounted at `/var/lib/postgresql` (on `/dev/sdb`) for the PostgreSQL data directory
- Cloud-init installs PostgreSQL `pg_version`, creates the `db_name` database and `db_user` role, and enables local WAL archiving to `/var/lib/postgresql/wal_archive`
- Automated `pg_dumpall` backup script on `backup_schedule` (writes to `/var/lib/postgresql/backups`)
- Private network: no public access to the database port
- Security group allowing PostgreSQL traffic only from `allowed_cidrs`

WAL segments archive locally on the data volume by default. To ship them to object storage, point the archive command at a bucket using `wal_archive_bucket` and your S3 credentials.

Set `db_password` to a strong value when you apply; the template ships no default password.

## Parameters

| Parameter | Description | Default |
| --- | --- | --- |
| `key_name` | SSH keypair name (must already exist in your project) | required |
| `db_flavor` | Database instance size | `m2a.xlarge` |
| `volume_size` | Data volume in GB | `50` |
| `pg_version` | PostgreSQL major version | `16` |
| `backup_schedule` | Cron expression for backups | `0 2 * * *` |
| `allowed_cidrs` | CIDRs allowed to connect | No default |
| `wal_archive_bucket` | Object storage bucket for WAL | `null` |
| `db_name` | Application database created on first boot | `appdb` |
| `db_user` | Application database user created on first boot | `appuser` |
| `db_password` | Password for the database user (required, no default) | _none_ |
| `image_name` | Operating system image | `Ubuntu-24.04` |
| `external_network` | External network for router gateway | `PublicStatic` |
| `private_cidr` | Address range for the private subnet | `192.168.40.0/24` |
| `instance_name` | Compute instance display name | `postgres` |

## When to use this pattern

Run PostgreSQL on a single compute instance with a data volume and private network access. Choose [MySQL/MariaDB Database](/resources/iac-templates/mysql-database) for the sibling relational-database option when the workload speaks the MySQL wire protocol, [WordPress + MySQL on Compute](/resources/iac-templates/wordpress-mysql) for a CMS plus MySQL pair, or [Full-Stack Application](/resources/iac-templates/full-stack-app) when the database sits behind separate web and app tiers.

## Estimated cost

<PricingCompanion
  components={[
    { kind: "template", slug: "self-managed-postgres", required: true },
  ]}
/>

## Template source

<TemplateSource slug="self-managed-postgres" />

<TemplateVariations base="self-managed-postgres" />

<TemplateResourceMap template="self-managed-postgres" format="opentofu" />

## Customize this pattern

- [Customize a template's image and flavor](/docs/automation/how-to/customize-template-image-flavor)
- [Add a block volume to a template](/docs/automation/how-to/add-volume-to-template)
- [Parameterize a template with a tfvars file](/docs/automation/how-to/parameterize-template-tfvars)

## See also

- [Deploy the self-managed PostgreSQL template with OpenTofu](/resources/deployments/deploy-self-managed-postgres-template)
- [How to restore PostgreSQL from a self-managed-postgres backup](/docs/automation/how-to/restore-postgres-from-backup)
- [How to emulate a branchable Postgres workflow](/docs/automation/how-to/branchable-postgres-workflow)
- [WordPress + MySQL template](/resources/iac-templates/wordpress-mysql)
- [Object Storage overview](/docs/object)