Terraform: Enable support for Google Cloud SQL Postgres

[samhamilton]

Hi there,

Thank you for opening an issue. Please note that we try to keep the Terraform issue tracker reserved for bug reports and feature requests. For general usage questions, please see: https://www.terraform.io/community.html.

Terraform Version

Terraform v0.8.8

Affected Resource(s)

• google_sql_database_instance

Terraform Configuration Files

resource "google_sql_database_instance" "prod-us-east1-postgres-01" {
  name    = "prod-us-east1-postgres-01"
  region  = "us-east1"

  settings {
    database_version = "POSTGRES_9_6"
    tier = "db-f1-micro"
    disk_size = "10"
    disk_type = "PD_SSD"

  }
}

Debug Output

I don't believe this is a bug, more a feature request

Panic Output

N/A

Expected Behavior

Create a new Google Cloud managed postgres instance

Actual Behavior

I got an error: google_sql_database_instance.prod-us-east1-postgres-01: settings.0: invalid or unknown key: database_version

Steps to Reproduce

1. terraform apply

Important Factoids

Nope

References

Could not find any other open Google Cloud Postgres issues.

[samhamilton]

Sorry - please ignore

[nodesocket]

Is PostgreSQL supported using google_sql_database_instance?

[samhamilton]

Yes just set database_version = "POSTGRES_9_6"

[ctavan]

Just to be clear, the correct config has to look like this (notice that database_version is defined on the top level):

resource "google_sql_database_instance" "prod-us-east1-postgres-01" {
  name             = "prod-us-east1-postgres-01"
  region           = "us-east1"
  database_version = "POSTGRES_9_6"

  settings {
    tier      = "db-f1-micro"
    disk_size = "10"
    disk_type = "PD_SSD"
  }
}

[ctavan]

I proposed to add this to the official documentation in #14028

[davejlong]

This only works with shared CPU instances. Google Cloud SQL requires a custom machine instance or shared-core for PostgreSQL. For example, the following resource:

resource "google_sql_database_instance" "development" {
  name = "development-instance"
  region = "${var.region}"
  database_version = "POSTGRES_9_6"

  settings {
    tier = "db-n1-standard-1"
    backup_configuration {
      enabled = true
      start_time = "03:00"
    }
  }
}

Will fail with the following error:

Error applying plan:

1 error(s) occurred:

* google_sql_database_instance.development: 1 error(s) occurred:

* google_sql_database_instance.development: Error, failed to create instance development-instance: googleapi: Error 400: Invalid parameter: Only custom machine instance type and shared-core instance type allowed for PostgreSQL database., invalid

[ctavan]

This is interesting. When I tried it last week it worked with db-n1-standard-1 without any problems. But back then the cloud console UI also still allowed me to choose the machine types… So I guess this is what "beta" status means then :/.

Maybe we should reopen this issue then?

[ctavan]

And does anyone have a clue how to specify custom machine instance types or where to find documentation on that? https://cloud.google.com/sql/docs/postgres/admin-api/v1beta4/instances isn't really helpful at the moment.

[ctavan]

I see that the Cloud SDK has --cpu and --memory options:

gcloud beta sql instances create --help

     --cpu=CPU
        A whole number value indicating how many cores are desired in the
        machine. Both --cpu and --memory must be specified if a custom machine
        type is desired, and the --tier flag must be omitted.

     --memory=MEMORY
        A whole number value indicating how much memory is desired in the
        machine. A size unit should be provided (eg. 3072MiB or 9GiB) - if no
        units are specified, GiB is assumed. Both --cpu and --memory must be
        specified if a custom machine type is desired, and the --tier flag must
        be omitted.

That's about all I could find… The latest Go api doesn't seem to support these settings yet: https://godoc.org/google.golang.org/api/sqladmin/v1beta4

[ctavan]

I could find out that cloud console uses a custom tier called db-custom-2-5888.

{
  "name": "testest",
  "region": "europe-west1",
  "project": "my-testing-project-1234567",
  "databaseVersion": "POSTGRES_9_6",
  "backendType": "SECOND_GEN",
  "instanceType": "CLOUD_SQL_INSTANCE",
  "replicaNames": [],
  "settings": {
    "dataDiskType": "PD_SSD",
    "tier": "db-custom-2-5888",
    "locationPreference": {},
    "ipConfiguration": {
      "authorizedNetworks": []
    },
    "backupConfiguration": {
      "enabled": true,
      "binaryLogEnabled": false,
      "startTime": "20:00"
    },
    "activationPolicy": "ALWAYS",
    "pricingPlan": "PER_USE",
    "storageAutoResize": true,
    "maintenanceWindow": {
      "day": null,
      "hour": 0
    },
    "databaseFlags": [],
    "dataDiskSizeGb": 10
  },
  "failoverReplica": {},
  "rootPassword": "***"
}

No clue though where these custom tiers come from. Probably the major lesson learned here is that it doesn't make too much sense to implement terraform resources against Google cloud services that are still in beta.

[ctavan]

Haha, I just figured out that the custom tier actually encodes the amount of CPU and memory. The first number is the number of CPUs, the second number is the amount of memory expressed in MiB.

I found it out by creating several instances through the web interface and observed the payload assembled by cloud console:

• 2 CPU, 13GB (=13*1024=13312MiB) ram -> db-custom-2-13312
• 1 CPU, 4GB (=4*1024=4096MiB) ram -> db-custom-1-4096

Just tried it out with terraform and it seems to work well!

So the solution for now is to manually calculate the correct tier as:

db-custom-{NUMBER_OF_CPUS}-{MEMORY_IN_MIB}

Details on which combinations of number CPUs and memory are allowed are described in: https://cloud.google.com/compute/docs/instances/creating-instance-with-custom-machine-type#create

[davejlong]

That is both genius and terrifying... I think I'll just stick db-g1-small for now until I need to upgrade capacity.

[ppwfx]

@ctavan awesome, thanks man!

[dodizzle]

@ctavan you are a genius hacker. I scoured the google sql documentation and this appears no where.
Thanks

[sereeth]

Thanks @ctavan , the more I work with gcloud the more I'm of the opinion that AWS is far > than gcloud. This is pretty nonsensical, having to write out a tier like that.

[hashibot[bot]]

I'm going to lock this issue because it has been closed for _30 days_ ⏳. This helps our maintainers find and focus on the active issues.

If you have found a problem that seems similar to this, please open a new issue and complete the issue template so we can capture all the details necessary to investigate further.