Is there an official way of installing an extension on a GCP Postgress Cloud SQL instance via Terraform?
Closest I've found is this unofficial Postgres resource but it's not immediately clear how to link the two. This issue on their tracker sort of shows how, but far from a step by step guide.
if it makes any difference, I'm trying to provision a Postgres Cloud SQL instance with PostGIS.
Thanks.
You can use the postgresql_extension resource of the PostgreSQL Provider to manage extensions.
When you used Terraform to set up the DB instance, you can reuse the resource to "link" the two providers.
resource "google_sql_database_instance" "instance" {
name = random_id.instance_name.hex
region = var.region
project = var.project
database_version = var.database_version
}
resource "google_sql_user" "root" {
name = local.root_user
password = random_password.generated_password.result
project = var.project
instance = google_sql_database_instance.instance.name
depends_on = [google_sql_database_instance.instance]
}
provider "postgresql" {
scheme = "gcppostgres"
host = google_sql_database_instance.instance.connection_name
username = google_sql_user.root.name
password = google_sql_user.root.password
superuser = false
}
resource "postgresql_extension" "pgcrypto_extension" {
name = "pgcrypto"
depends_on = [google_sql_database_instance.instance, google_sql_user.root]
}
Terraform is a deployment tool, to create all your infrastructure. To install an extension on Postgres, you need a installation tool, because you need to connect to the database and to run a command.
It's the same thing if you want to create a user in the database and you want to grant some privileges on it.
In summary, you can't achieve that with Terraform. I recommend you to have an installation tool, such as Ansible to perform this action.
An alternative is to create, with Terraform, a micro VM with a startup script that connect the database, run the command and destroy itself at the end.
