Unable to set high availability for Cloud SQL

Question

I am trying to create Cloud SQL using Deployment Manager.

Most of my configuration works apart from settings.availabilityType

jinja file -- That works

resources:
- name: dev-01
  type: gcp-types/sqladmin-v1beta4:instances
  properties:
    backendType: SECOND_GEN
    instanceType: CLOUD_SQL_INSTANCE
    region: europe-west1
    databaseVersion: POSTGRES_9_6
    settings: 
      tier: db-custom-1-3840
      storageAutoResize: true
      dataDiskSizeGb: PD_SSD
      dataDiskType: 10
      replicationType: SYNCHRONOUS
      failoverReplica:
        available: true
      backupConfiguration:
        enabled: true
      locationPreference:
        zone: europe-west1-b
      activationPolicy: ALWAYS

jinja file -- That doesn't work

resources:
- name: dev-01
  type: gcp-types/sqladmin-v1beta4:instances
  properties:
    backendType: SECOND_GEN
    instanceType: CLOUD_SQL_INSTANCE
    region: europe-west1
    databaseVersion: POSTGRES_9_6
    settings: 
      tier: db-custom-1-3840
      storageAutoResize: true
      dataDiskSizeGb: PD_SSD
      dataDiskType: 10
      replicationType: SYNCHRONOUS
      failoverReplica:
        available: true
      backupConfiguration:
        enabled: true
      locationPreference:
        zone: europe-west1-b
      activationPolicy: ALWAYS
      availabilityType: REGIONAL

I am getting error... Invalid API call...

EDIT#1

From the GUI I can add the HA with one click and without any existing failover instances.

Nikhil Gupta I have answered below. Does it work? If so please mark it as Answered. — Prashant, Nov 08 '19 at 05:15

score 2 · Answer 1 · answered Nov 07 '19 at 22:00

2

That is because you are trying to deploy a HA configuration with a locationPreference. The gcloud command to create HA for Cloud SQL HA instance just expects region and failover related details. See here

Follow this repo and you will find some good samples out there.

Specifically this part of the code gives you the template to follow

answered Nov 07 '19 at 22:00

Prashant

1,144
8
17
28

I understand the failover concept but my question is when i create a Cloud SQL instance via Deployment Manager without availabilityType setting it gets created successfully and then from the GUI I can simple enable High Availability without any failover instance. So why can't I do it via Deployment Manager. – niklodeon Nov 08 '19 at 09:19
The above answer has an example . Did you try? – Prashant Nov 08 '19 at 09:27
Prashant, I know the example in the git repo works... but that doesn't answer my question. In the example a failover is being created explicitly on the other hand once I create an DB instance via Deployment Manger I can enable HA from GUI without any failover and also the locationPreference preference is already set... so why is this deviation... – niklodeon Nov 08 '19 at 09:55
the deviation is because through the UI you are doing 2 operations (two API calls) whereas DM is making a single API call. As @Preshant mentioned, the API call for a failover SQL cluster should only have a specified region, not zone. The error returned should mention something along these lines – Patrick W Nov 18 '19 at 14:40

score 1 · Answer 2 · answered Nov 08 '19 at 17:30

Another user had a similar issue to yours in this thread. That worth to take a look, especially Jordi Miralles answer

For additional information, you should take a look into GCP docs.

score 1 · Answer 3 · answered Nov 19 '19 at 17:49

1

Regarding the edit note, the regional availability configuration (the one for PostgreSQL) does not require a failover instance, since it's based on regional persistent disks. More info on the docs.

Failover instances were only for MySQL instances, and it's now considered legacy (and the docs imply it's going to be deprecated in 2020) in favor of the same HA system as PostgreSQL: regional persistent disks.

answered Nov 19 '19 at 17:49

Jofre

3,718
1
23
31

Thanks Jofre for the info, do u also know why can't I use the availabilityType attribute in my jinja... – niklodeon Nov 19 '19 at 21:17

Unable to set high availability for Cloud SQL

3 Answers3