cloudsql - postgres

[jonbcampos]

I've been trying to extend the cloudsql mysql example to include POSTGRES options. So what is the deal? For me what fails is the failover section of code. I've tried (as you can see) to set the locationPreference because I read that needs to be different from the original. I've also set the availabilityType: REGIONAL for the master.

{% if properties['failover']['enabled'] %}
- name: {{ ID }}-failover
  type: sqladmin.v1beta4.instance
  properties:
    backendType: SECOND_GEN
    instanceType: READ_REPLICA_INSTANCE
    databaseVersion: {{ properties['cloudsql']['databaseVersion'] }}
    region: {{ properties['cloudsql']['region'] }}
    masterInstanceName: $(ref.{{ ID }}-master.name)
    replicaConfiguration:
      failoverTarget: true
    settings:
      tier: {{ properties['cloudsql']['tier'] }}
      locationPreference:
        zone: {{ properties['failover']['zone'] }}
{% endif %}

Any idea what might be the problem? the error isn't very descriptive.

{"ResourceType":"sqladmin.v1beta4.instance","ResourceErrorCode":"400","ResourceErrorMessage":{"code":400,"errors":[{"domain":"global","message":"Invalid instance property.","reason":"invalidInstanceProperty"}],"message":"Invalid instance property.","statusMessage":"Bad Request","requestPath":"https://www.googleapis.com/sql/v1beta4/projects/wrkerz-test-3/instances","httpMethod":"POST"}}

[MrAlias]

@jonbcampos I just (frustratingly) ran into this same error, but think I found a solution.

From looking at the project audit logs I was trying to deploy to it looked like this was a 500 response from Google rather than what the error leads you to believe would be a 400. This led me to manually enable High availability directly on the master in the console.

Unlike what I have seen with MySQL CloudSQL failover replicas, the failover was reported in the console as Enabled rather than a separate instance listing. I was able to describe the master and notice that it was described with the following attributes:

failoverReplica:
  available: true

Upon removing the failover instance from the template and adding the above properties the deployment worked. E.g.

resources:
- name: {{ ID }}-master
  type: sqladmin.v1beta4.instance
  properties:
    backendType: SECOND_GEN
    instanceType: CLOUD_SQL_INSTANCE
    databaseVersion: {{ properties['cloudsql']['databaseVersion'] }}
    region: {{ properties['cloudsql']['region'] }}
    settings:
      tier: {{ properties['cloudsql']['tier'] }}
      dataDiskSizeGb: {{ properties['cloudsql']['dataDiskSizeGb'] }}
      dataDiskType: {{ properties['cloudsql']['dataDiskType'] }}
      storageAutoResize: true
      replicationType: SYNCHRONOUS
      {% if properties['failover'] %}
      failoverReplica:
        available: true
      {% endif %}
      locationPreference:
        zone: {{ properties['cloudsql']['zone'] }}
      {% if properties['databaseFlags'] %}
      databaseFlags: {{ properties['databaseFlags'] }}
      {% endif %}
      activationPolicy: ALWAYS
      backupConfiguration:
        enabled: true
        binaryLogEnabled: true
        startTime: {{ properties['cloudsql']['backupStartTime'] }}
      ipConfiguration:
        authorizedNetworks: {{ properties['cloudsql']['authorizedNetworks'] }}

{% for i in range(properties['readReplicas']) %}
- name: {{ ID }}-rr-{{ i }}
  type: sqladmin.v1beta4.instance
  properties:
...

Ideally the example could be updated to work, but until then hopefully this helps.

[ocsig]

Have you tried the Cloud Foundation Toolkit version of the CloudSQL:
https://github.com/GoogleCloudPlatform/deploymentmanager-samples/tree/master/community/cloud-foundation/templates/cloud_sql
Let me know if you have the same issue with that template.

[ryu1kn]

Maybe the contents have since changed, but I don't see an example for PostgreSQL in @ocsig 's link above 🙃 Maybe the example is valid for both MySQL and PostgreSQL, but valid value for a property can be different depending on the db (e.g. tier), and I feel it would be helpful if there's runnable example for postgres too.

There's a simple example on stackoverflow: Is it not possible to create a postgres cloudsql via deployment manager?

[ocsig]

Please take a look at our new repo where all the active development is going on.
Here is the Postgress example.

[ryu1kn]

Cool, thanks for the pointer @ocsig !