# Deploy the self-managed PostgreSQL template with OpenTofu

Source: https://docs.quake.ai/resources/deployments/deploy-self-managed-postgres-template
Markdown: https://docs.quake.ai/resources/deployments/deploy-self-managed-postgres-template.md
> Stand up PostgreSQL 16 on a private subnet with a dedicated Cinder data volume using the self-managed-postgres OpenTofu template.

---

# Deploy the self-managed PostgreSQL template with OpenTofu

Stand up PostgreSQL 16 on a private subnet with a dedicated Cinder data volume using the [validated OpenTofu template](/docs/platform/validation#how-infrastructure-templates-are-checked) `self-managed-postgres`. The database listens on a private address only; cloud-init formats the data volume at `/var/lib/postgresql`, enables WAL archiving, and installs a scheduled `pg_dumpall` backup script.

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

<Figure size="md" caption="Self-managed PostgreSQL topology: private network, PostgreSQL instance on a dedicated data volume, router to PublicStatic, no floating IP">

```d2
direction: right

cloud: Quake AI {
  router: Router\nto PublicStatic
  private: Private network\n192.168.40.0/24 {
    pg: PostgreSQL 16\nport 5432
  }
  vol: Block volume\n50 GB\n/var/lib/postgresql
  sg: Security group\nSSH + PostgreSQL
}

cloud.router -> cloud.private
cloud.vol -> cloud.pg: /dev/sdb
cloud.sg -> cloud.pg
```

</Figure>

## Prerequisites

You need:

- A Quake AI account with [application credentials](/docs/tools/generate-app-credentials)
- OpenTofu 1.6.0 or later ([installation guide](https://opentofu.org/docs/intro/install/))
- OpenStack credentials sourced into the shell (`source openrc.sh`). See [the OpenStack CLI guide](/docs/tools/openstack-cli).
- An SSH key pair already uploaded to the project. See [Add an SSH key](/docs/tools/add-ssh-key).
- A copy of the `self-managed-postgres` template from [the template reference page](/resources/iac-templates/self-managed-postgres)
- Enough project quota for one `m2a.xlarge` instance and one 50 GB block volume (defaults)
- A host that routes to the private subnet for SSH after apply (bastion, VPN, or another instance on the same network). See [SSH bastion access into a private subnet](/docs/network/how-to/ssh-bastion-access).

## Step 1: Configure variables

Copy `terraform.tfvars.example` to `terraform.tfvars` and set:

```hcl
key_name      = "YOUR_KEY_NAME"
allowed_cidrs = ["192.168.40.0/24"]
db_password   = "CHOOSE_A_STRONG_PASSWORD"
```

| Variable | Purpose |
| --- | --- |
| `key_name` | Existing SSH key pair name Nova uses at boot |
| `allowed_cidrs` | IPv4 ranges that may connect to PostgreSQL on port 5432 |
| `db_password` | Password cloud-init assigns to `appuser` |

Defaults for flavor, volume size, PostgreSQL version, and network CIDR are documented on the [Self-Managed PostgreSQL](/resources/iac-templates/self-managed-postgres) reference page.

## Step 2: Apply the template

From the template directory, run:

```bash
tofu init
tofu plan
tofu apply
```

Type `yes` when prompted. Provisioning takes several minutes while Nova builds the boot volume, Cinder provisions the data volume, and cloud-init formats `/dev/sdb` and installs PostgreSQL.

When the run finishes, note `private_ip` from the outputs.

## Step 3: Verify PostgreSQL and the data volume

Cloud-init can take a few more minutes after `tofu apply` returns. SSH to the instance private IP from a host that routes to the subnet:

```bash
PRIVATE=$(tofu output -raw private_ip)
ssh -i ~/.ssh/YOUR_KEY ubuntu@${PRIVATE}
```

On the instance, confirm the data volume is mounted and PostgreSQL is running:

```bash
df -h /var/lib/postgresql
sudo systemctl is-active postgresql
sudo -u postgres psql -c "\l" | grep appdb
```

Connect as the application user and run a test query:

```bash
PGPASSWORD='CHOOSE_A_STRONG_PASSWORD' psql -h 127.0.0.1 -U appuser -d appdb -c "CREATE TABLE deploy_check (id int); INSERT INTO deploy_check VALUES (1); SELECT * FROM deploy_check;"
```

The query returns one row, which confirms PostgreSQL stores data on the attached volume.

Optional: confirm WAL archiving and the backup cron:

```bash
sudo -u postgres psql -c "SHOW archive_mode;"
ls -l /usr/local/bin/pg-backup.sh /etc/cron.d/pg-backup
```

## Next steps

- [Self-Managed PostgreSQL template](/resources/iac-templates/self-managed-postgres)
- [Restore PostgreSQL from backup](/docs/automation/how-to/restore-postgres-from-backup)
- [Three-Tier Application template](/resources/iac-templates/three-tier-app)
- [WordPress + MySQL template](/resources/iac-templates/wordpress-mysql)

## Clean up

Run `tofu destroy` from the project directory when finished. Type `yes` to confirm. Verify in the Console that the instance and data volume are gone.