Database provisioning with Terraform (IaC)

Symptom

Goal: Define database infrastructure (instance, parameter group, network) as code (Infrastructure as Code) with Terraform; set up the plan/apply flow, remote state and drift management.

1. Choose and configure the provider (aws / google / azurerm)

Define the provider for the cloud you run on and pin its version. Lock the hashicorp/aws provider for AWS, hashicorp/google for GCP and hashicorp/azurerm for Azure in the required_providers block. Version pinning ensures consistent behavior across the team.

PostgreSQL

terraform {
  required_providers {
    aws = {
      source  = "hashicorp/aws"
      version = "~> 5.0"
    }
  }
}

provider "aws" {
  region = "eu-central-1"
}

2. Set up remote state and locking

Do not keep the state file locally; use a remote backend for teamwork and security. Common choices are the S3 backend + a DynamoDB lock table on AWS, the gcs backend on GCP and the azurerm backend (Storage Account) on Azure. Remote state prevents concurrent applies through locking and makes the state the single source of truth. Because passwords may sit in the state in plain text, keep the backend encrypted and access-restricted.

PostgreSQL

terraform {
  backend "s3" {
    bucket         = "company-tfstate"
    key            = "db/terraform.tfstate"
    region         = "eu-central-1"
    dynamodb_table = "tf-lock"
    encrypt        = true
  }
}

3. Define the database resource

Write the main resource for your engine and cloud: aws_db_instance on AWS (or aws_rds_cluster for Aurora), google_sql_database_instance on GCP, azurerm_mssql_database on Azure (for SQL Server, under an azurerm_mssql_server). Specify engine, version, class, storage and backup window here.

PostgreSQL

resource "aws_db_instance" "pg" {
  identifier        = "app-db"
  engine            = "postgres"
  engine_version    = "16"
  instance_class    = "db.t3.medium"
  allocated_storage = 50
  db_name           = "appdb"
  username          = "pgadmin"
  password          = var.db_password
  backup_retention_period = 7
}

SQL Server

resource "azurerm_mssql_database" "db" {
  name      = "appdb"
  server_id = azurerm_mssql_server.srv.id
  sku_name  = "S1"
}

4. Write the parameter group as code

Define database settings (max_connections, work_mem, character set) with a parameter resource, not manually in the console. Use aws_db_parameter_group on AWS, settings.database_flags inside google_sql_database_instance on GCP, and the server configuration resource on Azure. Settings are then versioned and reproducible.

PostgreSQL

resource "aws_db_parameter_group" "pg" {
  name   = "app-pg16"
  family = "postgres16"

  parameter {
    name  = "max_connections"
    value = "200"
  }
}
# inside aws_db_instance:
# parameter_group_name = aws_db_parameter_group.pg.name

5. Pass secrets via variables and a secrets manager

Do not hard-code the password in a .tf file; define it as a sensitive variable (variable ... sensitive = true) and supply the value via a TF_VAR_ environment variable, a secrets manager (AWS Secrets Manager, Vault) or a CI secret. Remember: the value is written to the state file, so remote state must be encrypted. The cleanest approach is never to pass the password into Terraform and read it from a secrets manager resource instead.

PostgreSQL

variable "db_password" {
  type      = string
  sensitive = true
}

# CI/shell:
export TF_VAR_db_password="$(vault kv get -field=pw secret/db)"

6. Run the plan and apply flow

First prepare the provider/backend with terraform init, see what will change with terraform plan, approve, and apply with terraform apply. Do not apply without reviewing the plan output; the safe flow in CI is to post the 'plan' as a PR comment and apply after human approval. Catch destructive changes (e.g. instance re-creation) by the -/+ marker in the plan output.

PostgreSQL

terraform init
terraform plan -out=plan.tfplan
terraform apply plan.tfplan

7. Detect and fix drift

If someone makes a manual change in the console, the real infrastructure and the state diverge (drift). Differences that appear in terraform plan 'when nothing should have changed' reveal drift; the difference is read with terraform refresh/plan. Either reflect the change back into code or pull the infrastructure back to the code with apply. Ban manual changes as a rule; code should be the single source of truth.

PostgreSQL

# drift check (when no changes are expected):
terraform plan
# expect 'No changes'; any diff means drift
# bring an existing resource into state with import:
terraform import aws_db_instance.pg app-db