Google Cloud SQL - Export API - PostgreSQL : Error 400 The requested operation is not valid for a read-replica instance

Question

I have two Cloud SQL PostgreSQL instances : one master and one read-only replica.

When I try to export the datas from the read-only replica to a given bucket using the Cloud SQL v1beta4 Export API (https://cloud.google.com/sql/docs/postgres/admin-api/v1beta4/instances/export), with the following request body :

{
  "exportContext": {
    "kind": "sql#exportContext",
    "fileType": "CSV",
    "uri": "gs://bucket_uri",
    "databases": [
      "db_name"
    ],
    "csvExportOptions": {
      "selectQuery": "SELECT * FROM db_name.table_name"
    }
  }
}

I get the following error :

{
  "error": {
    "code": 400,
    "message": "The requested operation is not valid for a read-replica instance.",
    "errors": [
      {
        "message": "The requested operation is not valid for a read-replica instance.",
        "domain": "global",
        "reason": "errorReadReplicaInvalidOperation"
      }
    ]
  }
}

But when I use the master instance, everything works fine.

I also did the same thing but with a Cloud SQL MySQL instance, and the export API was working well on the read-only replica, that's why I'm surprised it's not working on a PostgreSQL read-only replica instance.

I don't want to plug my export on the master instance as the instance is already busy enough. Do you have encountered the same kind of issue ? Any idea how to solve this ?

Answer 1

According to oficial documentation link.

Note: You cannot export to a CSV file from a read replica instance. The export operation creates an export user and grants that user select permissions on the database the user wants to export. Since read replica instances run in read only mode, these operations fail.

Since this instance is a replica, any transactions to it will be considered as read-only. Basically, with the read-only constraint the permission granting will fail.The current workaround is to do the exports in MASTER instance instead of REPLICA as you mentioned.