Terraform-provider-google: Unable to create postgres failover replica.

[stefanthorpe]

Hi,

I'm unable to create a Postgres failover replica.
I have a module with the following code:

resource "google_sql_database_instance" "postgres" {
  count            = "${var.master_instance_name == "" ? 1 : 0}"
  name             = "${local.name}"
  database_version = "POSTGRES_9_6"
  region           = "${var.region}"
  depends_on       = ["null_resource.start"]

  settings = {
    tier      = "${var.instance_size[terraform.workspace]}"
    disk_size = "${var.disk_size[terraform.workspace]}"
    disk_type = "PD_SSD"

    availability_type = "REGIONAL"
    disk_autoresize   = true

    ip_configuration = {
      authorized_networks = "${var.whitelist}"
      require_ssl         = true
    }

    backup_configuration = {
      binary_log_enabled = "${var.master_instance_name == "" ? true : false}"
      enabled            = "${var.master_instance_name == "" ? true : false}"
      start_time         = "01:00"
    }
  }
}

resource "google_sql_database_instance" "postgres_replica" {
  count = "${var.master_instance_name == "" ? 0 : 1}"
  name  = "${local.name}"

  database_version     = "POSTGRES_9_6"
  region               = "${var.region}"
  master_instance_name = "${var.master_instance_name}"
  depends_on           = ["null_resource.start"]

  replica_configuration = {
    connect_retry_interval = "30"
    failover_target        = "${var.failover_target}"
  }

  settings = {
    tier = "${var.instance_size[terraform.workspace]}"

    disk_size = "${var.disk_size[terraform.workspace]}"
    disk_type = "PD_SSD"

    #availability_type = "REGIONAL"
    disk_autoresize = true

    ip_configuration = {
      authorized_networks = "${var.whitelist}"
      require_ssl         = true
    }
  }
}

Calling the above module I have:

module "postgres_db" {
  source                 = "./cloud-sql"
  project_id_map         = "${var.project_id_map}"
  name                   = "${local.primary_db_name}-test5"
  kms_key_id             = "${module.kms.key_id}"
  master_password_cipher = "${local.master_password_cipher[terraform.workspace]}"

  whitelist = "${local.whitelist}"
}

module "postgres_api_replica_db" {
  source                 = "./cloud-sql"
  project_id_map         = "${var.project_id_map}"
  name                   = "${local.api_replica_db_name}-st-126"
  kms_key_id             = "${module.kms.key_id}"
  master_password_cipher = "${local.master_password_cipher[terraform.workspace]}"

  whitelist            = "${local.whitelist}"
  master_instance_name = "cherre-sandbox-pg-sandbox-master-test5-db"
  dependency_id        = "${module.postgres_db.completion_id}"
}

module "postgres_failover_replica_db" {
  source                 = "./cloud-sql"
  project_id_map         = "${var.project_id_map}"
  name                   = "${local.failover_replica_db_name}-st1"
  kms_key_id             = "${module.kms.key_id}"
  master_password_cipher = "${local.master_password_cipher[terraform.workspace]}"

  whitelist = "${local.whitelist}"

  master_instance_name = "${module.postgres_db.instance_id[0]}"
  dependency_id        = "${module.postgres_db.completion_id}"

  failover_target = true
}

Terraform Version

Terraform v0.11.7
+ provider.google v1.8.0

Affected Resource(s)

Please list the resources as a list, for example:

• google_sql_database_instance

If this issue appears to affect multiple resources, it may be an issue with Terraform's core, so please mention this.

Debug Output

https://gist.github.com/stefanthorpe/72187efb98e1bbbea7ebbf5b56cbca16

Expected Behavior

3 DB's should be created.

Actual Behavior

postgres_db and postgres_api_replica_db create just fine. postgres_failover_replica_db fails to create. API response is vague. I've tried a variety of combs but still unable to get any deploy with failover_replica set to true.
The error message is:

1 error(s) occurred:

* module.postgres_failover_replica_db.google_sql_database_instance.postgres_replica: 1 error(s) occurred:

* google_sql_database_instance.postgres_replica: Error, failed to failover-replica-st1-db: googleapi: Error 400: Invalid instance property., invalidInstanceProperty

[Chupaka]

• provider.google v1.8.0

Could you check with current version (1.16)?

Also, I tried to reproduce this, but your config is not full, it throws dozens of errors about unknown resources and variables.

[stefanthorpe]

Yep, I'll test it with that version.
There is a fair chunk of stuff that isn't directly related so I kept it out if the version test produces the same issues. I trim the fat and submit a version so you can replicate.

[stefanthorpe]

postgres-failover-error.zip
I'm still getting the same issue with version 1.16.
I've trimmed the fat of the files so it's easy to replicate. you probably just need to change the project.

[mudrii]

I am getting same error as above.
by removing "replica_configuration" all seems to work.
Some how "failover_target" is not working with POSTGRESQL

[stefanthorpe]

Just to confirm who are we waiting for a response form?

[ndmckinley]

Waiting for it to get picked up by someone. :) I'll take a look.

[ndmckinley]

Whew, that was some digging, but I've got it.

The error here is working as intended, but I know how to solve your issue. Your goal is to create a high-availability postgres database with a read replica. You can absolutely do that! But postgres databases work differently than mysql ones in a couple ways.

First, it's not ever valid to have a separate failover replica for a postgres database. https://cloud.google.com/sql/docs/mysql/high-availability#differences-mysql-postgres. Instead, you just need to configure the postgres database for regional availibility, which you have already done (availability_type = "REGIONAL"). You can just delete your third module instantiation - you already have failover capacity. You can test this by going into the Cloud Console and clicking 'failover' on your regional postgres instance - it'll work, even though you only see the one instance in the Cloud Console.

Second, read replicas cannot be regional. If you want the ability to serve reads from multiple zones, you'll just need to create a read replica in each zone. You can just increase the count to 2 or 3. Remember that they'll still create serially, because Cloud SQL can only create one replica at a time, but Terraform will handle that for you.

Here's the config I used to show that this works, modified from yours:

variable "region" {
  default = "us-east1"
}

provider "google" {
  project = "project-name"
  region  = "${var.region}"
  credentials = "${file("~/.gcloud/service-account.json")}"
}

provider "random" {}

resource "random_id" "r" {
  byte_length = 8
}

resource "google_sql_database_instance" "postgres_main_db" {
  name             = "main-database-${random_id.r.hex}"
  database_version = "POSTGRES_9_6"
  region           = "${var.region}"

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

    availability_type = "REGIONAL"
    disk_autoresize   = true

    ip_configuration = {
      require_ssl         = true
    }

    backup_configuration = {
      binary_log_enabled = true
      enabled            = true
      start_time         = "01:00"
    }
  }
}

variable "zones" {
  type = "list"
  default = ["us-east1-d", "us-east1-b", "us-east1-c"]
}

resource "google_sql_database_instance" "postgres_replica" {
  count = 3
  name  = "replica-database-${random_id.r.hex}-${count.index}"

  database_version     = "POSTGRES_9_6"
  region               = "${var.region}"
  master_instance_name = "${google_sql_database_instance.postgres_main_db.name}"

  replica_configuration = {
    connect_retry_interval = "30"
    failover_target        = "false"
  }

  settings = {
    tier = "db-f1-micro"

    disk_size = "20"
    disk_type = "PD_SSD"

    disk_autoresize = true

    ip_configuration = {
      require_ssl         = true
    }
    location_preference = {
      zone = "${element(var.zones, count.index)}"
    }
  }
}

resource "google_sql_database" "db-master" {
  name     = "core_db"
  instance = "${google_sql_database_instance.postgres_main_db.name}"
}

output "ip" {
  value = "${google_sql_database_instance.postgres_main_db.*.first_ip_address}"
}

output "database" {
  value = "${google_sql_database.db-master.*.name}"
}

output "instance_id" {
  value = "${google_sql_database_instance.postgres_main_db.*.id}"
}

[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 feel this issue should be reopened, we encourage creating a new issue linking back to this one for added context. If you feel I made an error 🤖 🙉 , please reach out to my human friends 👉 [email protected]. Thanks!