Populate Database

Question

I am using Terraform Cloud to build the infrastructure. One of the resources is a postgresql database.

What is the best way to populate the database by creating tables, multiple schema etc after the database gets created.

Answer 1

For RDS, the simplest would probably be to have a template RDS snapshot that you create instances off of, just like you would an AMI.

However if later updates to the database structure will be frequent/complex enough that the added complexity of a CD migration tool will be worthwhile, then you may be better off starting with that for the initial deployment as well. I don't know how mature they are in the postgres world, but https://dbmstools.com/categories/version-control-tools/postgresql might be a good place to start.

Answer 2

I ended up using the cyrilgdn/postgresql provider which allowed be to create/destroy db as needed

terraform {
  required_providers {
    hashiaws = {
      source  = "hashicorp/aws"
      version = "~> 3.4.0"
    }
    postgresql = {
      source  = "cyrilgdn/postgresql"
      version = "1.7.2"
    }
  }
  required_version = ">= 0.13"
}

provider "postgresql" {
  host             = local.db_url
  port             = local.db_port
  database         = local.db_name
  username         = local.db_usr
  password         = local.db_pwd
  sslmode          = "disable"
  connect_timeout  = 15
  expected_version = local.db_eng_ver
}

The only downside to this solution is that the server you are running TF on will need to have access to the RDS manage instance