How to Structure Terraform Modules for PostGIS: Drift-Safe Layout, State Reconciliation & Recovery

You inherited a 600-line main.tf that provisions a PostGIS cluster, and now every terraform plan reports a phantom postgis_version update or threatens to replace the instance outright. The remedy is structural: decompose the module along clean boundaries so state can be targeted surgically, drift can be isolated to a single file, and a routine security-group rotation never cascades into a database replacement. This guide is the implementation companion to PostGIS Cluster Provisioning and sits within the broader Geospatial Resource Provisioning framework; it extends the general Module Design Patterns with the geospatial-specific concerns — extension lifecycles, spatial parameter tuning, and private network isolation — that generic database modules ignore.

Symptom Identification and Triage

A poorly bounded PostGIS module announces itself through a small set of recurring, observable signals. Before refactoring, classify the failure so the fix targets the real layer rather than a downstream symptom:

• Phantom version churn: terraform plan reports ~ engine_version or ~ version (on a postgresql_extension resource) with (known after apply) despite zero configuration changes. This is almost always cloud-provider auto-minor-patching or an out-of-band ALTER EXTENSION postgis UPDATE executed directly against the database.
• Replace-on-touch: editing vpc_security_group_ids or db_subnet_group_name produces -/+ destroy and then create replacement. This means networking and the data plane share a resource boundary that forces destructive diffs.
• Extension provisioning failure: terraform apply errors with could not open extension control file or required extension "postgis" is not installed — a dependency-ordering gap between the instance, the postgresql provider connection, and CREATE EXTENSION.
• State lock contention: Error acquiring the state lock during a maintenance window, when a scheduled snapshot or replication job races a concurrent apply.
• Spatial query regression after promote: work_mem/maintenance_work_mem differ between environments, so a GIST index build or a ST_Intersects plan that passed in staging degrades in production.

Triage decision flow: confirm the diff is real with terraform plan -refresh-only, then attribute it to one of three boundaries — instance lifecycle, extension lifecycle, or network/parameter configuration — and route the fix to the file that owns that boundary.

Prerequisites and Environment Assumptions

This guide assumes an AWS RDS for PostgreSQL target, though the boundary model applies equally to Cloud SQL or a self-hosted cluster. You will need:

• Terraform >= 1.6 with pinned providers — hashicorp/aws ~> 5.40 for the instance and network, and cyrilgdn/postgresql ~> 1.23 for in-database extension management.
• Remote state with locking already configured (S3 + DynamoDB, GCS, or Azure Blob). State backend trade-offs are covered in State Backend Selection; the locking failure modes referenced below are detailed in Managing Terraform State Locks for Spatial Data.
• IAM permissions to run rds:ModifyDBInstance, rds:DescribeDBInstances, kms:Decrypt on the storage key, and the DynamoDB GetItem/PutItem/DeleteItem actions on the lock table.
• Network reachability from wherever Terraform runs to the database endpoint on TCP 5432, since the postgresql provider opens a real connection to manage extensions. In CI this typically means a runner inside the VPC or a bastion tunnel.

Step-by-Step Remediation

The fix is to split the monolith into files whose boundaries match the three drift sources identified in triage. Each file owns exactly one lifecycle so terraform plan diffs stay legible and -target operations stay safe.

1. Split by lifecycle boundary. Adopt the layout below. The split is not cosmetic: it lets a security-group change touch only networking.tf, and an extension upgrade touch only extensions.tf, without either forcing an instance replacement.

   modules/postgis/
   ├── versions.tf      # required_version + pinned providers
   ├── main.tf          # RDS instance + subnet group (data plane only)
   ├── networking.tf    # security groups, ingress rules, VPC endpoints
   ├── parameters.tf    # DB parameter group tuned for spatial workloads
   ├── extensions.tf    # idempotent CREATE EXTENSION lifecycle
   ├── variables.tf     # strictly typed, validated inputs
   └── outputs.tf       # endpoint, ARN, pooled DNS, extension status
   

2. Pin every provider. Version skew between cyrilgdn/postgresql releases changes how extension versions are diffed; pinning removes that class of phantom drift.

   # versions.tf
   terraform {
     required_version = ">= 1.6.0"
     required_providers {
       aws        = { source = "hashicorp/aws", version = "~> 5.40" }
       postgresql = { source = "cyrilgdn/postgresql", version = "~> 1.23" }
     }
   }

3. Isolate the data plane and stop minor-patch replacement. Keep the instance free of network detail and tell Terraform to ignore provider-driven minor engine bumps, which converts the “phantom version churn” symptom into a no-op.

   # main.tf
   resource "aws_db_instance" "postgis" {
     identifier              = "${var.env}-spatial-primary"
     engine                  = "postgres"
     engine_version          = var.engine_version      # e.g. "16.4"
     instance_class          = var.instance_class
     allocated_storage       = var.allocated_storage
     storage_type            = "gp3"
     iops                    = 3000
     storage_encrypted       = true
     kms_key_id              = var.kms_key_arn
     db_subnet_group_name    = aws_db_subnet_group.spatial.name
     vpc_security_group_ids  = [aws_security_group.postgis.id]
     parameter_group_name    = aws_db_parameter_group.spatial.name
     backup_retention_period = 35
     deletion_protection     = true
     skip_final_snapshot     = false
   
     lifecycle {
       ignore_changes = [engine_version]   # cloud minor-patching is not drift
     }
   }

4. Constrain inputs with validation. Strictly typed variables reject malformed instance classes and unsupported extension versions before a plan ever reaches the database cluster.

   # variables.tf
   variable "instance_class" {
     type        = string
     description = "Database instance class (e.g., db.r7g.large)"
     validation {
       condition     = can(regex("^db\\.(r|m|t)[0-9]+[a-z]*\\.[a-z0-9]+$", var.instance_class))
       error_message = "Instance class must follow cloud provider naming conventions."
     }
   }
   
   variable "postgis_version" {
     type        = string
     default     = "3.4"
     description = "Target PostGIS major.minor. Must match the provider support matrix."
   }

5. Tune the spatial parameter group in its own file. Spatial index builds and raster operations are memory-bound, so maintenance_work_mem and work_mem belong in a dedicated, environment-parameterized resource — never hand-edited in the console.

   AWS RDS memory-unit callout: RDS parameter-group memory values are expressed in 8 kB blocks as integers, not byte strings or percentages. 262144 blocks = 2 GB. The values below are sized for a db.r7g.large (16 GB RAM); scale them proportionally with instance_class.

   # parameters.tf
   resource "aws_db_parameter_group" "spatial" {
     name   = "${var.env}-postgis-pg"
     family = "postgres16"
   
     parameter {                          # 2 GB for GIST/BRIN index builds
       name  = "maintenance_work_mem"
       value = "262144"                   # 8 kB blocks
     }
     parameter {                          # 64 MB per sort/hash node
       name  = "work_mem"
       value = "8192"                     # 8 kB blocks
     }
     parameter {
       name         = "shared_preload_libraries"
       value        = "pg_stat_statements"
       apply_method = "pending-reboot"
     }
   }

6. Order the extension lifecycle explicitly. postgis_topology and postgis_raster depend on the base postgis extension; an explicit depends_on prevents the “extension not installed” race during a cold apply.

   # extensions.tf
   resource "postgresql_extension" "postgis" {
     name    = "postgis"
     version = var.postgis_version
     schema  = "public"
   }
   
   resource "postgresql_extension" "postgis_topology" {
     name       = "postgis_topology"
     version    = var.postgis_version
     schema     = "topology"
     depends_on = [postgresql_extension.postgis]
   }

If the drift is an already-upgraded extension, reconcile state before re-applying: terraform apply -refresh-only to sync the recorded version, or terraform state rm 'module.postgis.postgresql_extension.postgis' followed by a controlled re-import once the pinned var.postgis_version matches the live SELECT extversion FROM pg_extension WHERE extname = 'postgis';.

The network and identity boundaries this module exposes are consumed by the rest of the platform: the security group authored in networking.tf should follow Hardening Security Groups for PostGIS Ports, and the pooled outputs.tf endpoint is what every GeoServer Deployment Pattern consumes instead of hardcoding instance topology.

Verification

Confirm the refactor converged and the database is actually serving spatial queries before closing the change.

1. Plan is clean: a second terraform plan reports No changes. Your infrastructure matches the configuration. — the phantom version diff is gone because ignore_changes and the pinned provider now agree with reality.

2. Extensions match the pin: connect and run SELECT PostGIS_Full_Version();. The reported POSTGIS, GEOS, and PROJ revisions must equal what var.postgis_version and the engine declare in code.

3. Spatial path works: build and probe an index to prove the parameter group took effect:

   CREATE INDEX idx_parcels_geom ON parcels USING GIST (geom);
   EXPLAIN (ANALYZE, BUFFERS)
   SELECT id FROM parcels
   WHERE ST_DWithin(geom, ST_SetSRID(ST_MakePoint(-122.4, 37.8), 4326), 0.01);

   The plan should show an Index Scan using idx_parcels_geom, not a sequential scan, confirming maintenance_work_mem was sufficient for the build.

4. Boundary isolation holds: make a trivial security-group description change and run terraform plan; it must show an in-place update on the security group only, with no aws_db_instance replacement.

Preventing Recurrence

Encode the fix so the monolith cannot reassemble itself:

• Policy-as-code gate. Add an OPA/Conftest rule to the pre-merge pipeline that fails any aws_db_instance plan whose lifecycle.ignore_changes omits engine_version, and any postgresql_extension resource lacking an explicit version. This blocks the two highest-frequency drift sources before they merge.
• Scheduled drift detection. Run terraform plan -detailed-exitcode -refresh-only on a nightly cadence; exit code 2 signals out-of-band change and should page the owning team rather than wait for the next deploy window.
• Module interface contract. Treat variables.tf validation and the outputs.tf surface (cluster_endpoint, replica_endpoints, connection_pooler_dns, extension_versions) as the module’s public API. Consumers depend only on outputs, so internal file boundaries can evolve without breaking GeoServer Deployment Patterns or compute consumers downstream.
• State discipline. Keep mandatory locking on the remote backend and never apply from a workstation during a maintenance window; the lock-contention failure mode is otherwise inevitable.

Frequently Asked Questions

Related

• PostGIS Cluster Provisioning — the parent operational guide this module implements.
• Module Design Patterns — general interface-contract and boundary patterns for spatial modules.
• Managing Terraform State Locks for Spatial Data — resolving the lock-contention failure mode referenced above.
• Hardening Security Groups for PostGIS Ports — the network boundary the networking.tf file should enforce.