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 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.

Prerequisites#

You need:

• A Quake AI account with application credentials
• OpenTofu 1.6.0 or later (installation guide)
• OpenStack credentials sourced into the shell (source openrc.sh). See the OpenStack CLI guide.
• An SSH key pair already uploaded to the project. See Add an SSH key.
• A copy of the self-managed-postgres template from the template reference page
• 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.

Step 1: Configure variables#

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

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

Defaults for flavor, volume size, PostgreSQL version, and network CIDR are documented on the Self-Managed PostgreSQL reference page.

Step 2: Apply the template#

From the template directory, run:

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:

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:

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:

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:

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
• Restore PostgreSQL from backup
• Three-Tier Application template
• WordPress + MySQL template

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.