Promote PostgreSQL® read-only replica to primary

On the Aiven console, if you use “service integrations” to create a read-only replica from an existing PostgreSQL or MySQL service, there is an option for the read-only replica service to promote to master using the Promote to master button under the Overview tab. While the Terraform documentation does not explicitly mention how to promote the read-only replica to master, you can remove the service integration between services to accomplish the task.

Let’s create Aiven for PostgreSQL® primary and a read-only replica using the following Terraform file:

resource "aiven_pg" "demo-postgresql-primary" {
  project                 = var.project_name
  service_name            = "demo-postgresql-primary"
  cloud_name              = "google-northamerica-northeast1"
  plan                    = "startup-4"
  maintenance_window_dow  = "sunday"
  maintenance_window_time = "10:00:00"
  termination_protection  = false
}

resource "aiven_pg" "demo-postgresql-read-replica" {
  project                 = var.project_name
  cloud_name              = "google-northamerica-northeast1"
  service_name            = "demo-postgresql-read-replica"
  plan                    = "startup-4"
  maintenance_window_dow  = "sunday"
  maintenance_window_time = "10:00:00"
  termination_protection  = false

  service_integrations {
    integration_type    = "read_replica"
    source_service_name = aiven_pg.demo-postgresql-primary.service_name
  }

  depends_on = [
    aiven_pg.demo-postgresql-primary,
  ]
}

resource "aiven_service_integration" "pg-readreplica" {
  project                  = var.project_name
  integration_type         = "read_replica"
  source_service_name      = aiven_pg.demo-postgresql-primary.service_name
  destination_service_name = aiven_pg.demo-postgresql-read-replica.service_name
}

You can get the read-only replica promoted to master by removing the resource aiven_service_integration, the code blocks service_integrations and depends_on under demo-postgresql-read-replica resource above. Once you remove these code blocks, your Terraform script will look something like this:

resource "aiven_pg" "demo-postgresql-primary" {
  project                 = var.project_name
  service_name            = "demo-postgresql-primary"
  cloud_name              = "google-northamerica-northeast1"
  plan                    = "startup-4"
  maintenance_window_dow  = "sunday"
  maintenance_window_time = "10:00:00"
  termination_protection  = false
}

resource "aiven_pg" "demo-postgresql-read-replica" {
  project                 = var.project_name
  cloud_name              = "google-northamerica-northeast1"
  service_name            = "demo-postgresql-read-replica"
  plan                    = "startup-4"
  maintenance_window_dow  = "sunday"
  maintenance_window_time = "10:00:00"
  termination_protection  = false
}

Run terraform apply to have the read-only replica promoted to master and both Aiven for PostgreSQL services will run as independent services.

Note

In order for the promotion to master to succeed, the resource aiven_service_integration must be used when creating the primary and read-only replica services and subsequently removed.