Infrastructure as Code for Databases¶

★★★★★ Intermediate

Terraform provisions infrastructure (VMs, managed databases, networks), while Ansible configures software (PostgreSQL installation, replication setup, configuration management). Together they enable reproducible database deployments.

Terraform - Infrastructure Provisioning¶

# GCP PostgreSQL VM
resource "google_compute_instance" "postgres" {
  name         = "postgres-server"
  machine_type = "e2-medium"
  zone         = "us-central1-a"

  boot_disk {
    initialize_params {
      image = "ubuntu-os-cloud/ubuntu-2204-lts"
      size  = 50
      type  = "pd-ssd"
    }
  }

  network_interface {
    network = "default"
    access_config {}
  }
}

# Yandex Cloud Managed PostgreSQL
resource "yandex_mdb_postgresql_cluster" "pg" {
  name        = "pg-cluster"
  environment = "PRODUCTION"
  network_id  = yandex_vpc_network.net.id

  config {
    version = 15
    resources {
      resource_preset_id = "s2.micro"
      disk_type_id       = "network-ssd"
      disk_size          = 20
    }
  }
}

terraform init      # initialize providers
terraform plan      # preview changes
terraform apply     # create infrastructure
terraform destroy   # tear down

Ansible - Configuration Management¶

# inventory.yml
all:
  hosts:
    pg-primary:
      ansible_host: 10.0.0.1
    pg-replica:
      ansible_host: 10.0.0.2

# playbook.yml
- hosts: all
  become: yes
  tasks:
    - name: Install PostgreSQL
      apt:
        name: postgresql-15
        state: present
        update_cache: yes

    - name: Configure PostgreSQL
      template:
        src: postgresql.conf.j2
        dest: /etc/postgresql/15/main/postgresql.conf
      notify: restart postgresql

    - name: Configure pg_hba.conf
      template:
        src: pg_hba.conf.j2
        dest: /etc/postgresql/15/main/pg_hba.conf
      notify: reload postgresql

  handlers:
    - name: restart postgresql
      service: { name: postgresql, state: restarted }
    - name: reload postgresql
      service: { name: postgresql, state: reloaded }

Pre-built Ansible roles: galaxyproject.postgresql, geerlingguy.postgresql.

Cloud Managed PostgreSQL¶

Provider	Service	Versions	Features
GCP	Cloud SQL	10-15	Auto-backup, HA, read replicas
AWS	RDS / Aurora	10-15	Multi-AZ, auto-failover
Yandex Cloud	Managed PostgreSQL	10-15	PITR, connection pooler
VK Cloud	Cloud Database	10-14	Single/Master-Replica/Cluster

VK Cloud configurations: Single (dev/test), Master-Replica (production), Cluster (high reliability). Max SSD: 5120GB.

PostgreSQL Installation (Linux)¶

# Ubuntu/Debian (PGDG repo)
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" \
  > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt update && sudo DEBIAN_FRONTEND=noninteractive apt -y install postgresql-15

# AlmaLinux/CentOS/RHEL
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo yum install -y postgresql15-server postgresql15
sudo /usr/pgsql-15/bin/postgresql-15-setup initdb
sudo systemctl enable --now postgresql-15

Key Facts¶

Terraform manages infrastructure lifecycle, Ansible configures applications
DEBIAN_FRONTEND=noninteractive avoids prompts during automated installs
Managed services handle backups, HA, and patching - use for simpler ops
pg_lsclusters lists installed PostgreSQL clusters on Debian/Ubuntu
Ansible is agentless (connects via SSH)

Gotchas¶

Terraform destroy deletes all managed resources including data - protect with lifecycle rules
Managed PostgreSQL services may not support all extensions
Cloud VM disk resize may require instance restart
Ansible playbook idempotency: handlers only run if task reports "changed"
Always use DEBIAN_FRONTEND=noninteractive on non-LTS Ubuntu releases