I'm currently trying to setup pglogical replication between 2 GCP CloudSQL Postgres instances, in order to quickly migrate data between regions.
I've following the GCP docs[1] by creating a replicator user, installing teh pglogical extension as that user and performing the required config.
However whenever I create the subscription on the destination service, the status initially goes to initializing and then changes to down. Looking at the postgres` logs, I can see the following error on the destination side:
db=api,user=replicator ERROR: permission denied for function pg_replication_origin_session_setup
I can see the mentioned function in the destination DB, however whenever I try to GRANT EXECUTE on it, I get a warning as below.
postgres=> \df+ pg_replication_origin_session_setup
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Securi
ty | Access privileges | Language | Source code | Description
------------+-------------------------------------+------------------+---------------------+------+------------+----------+---------------+-------
---+-----------------------------------+----------+-------------------------------------+---------------------------------------------------------
pg_catalog | pg_replication_origin_session_setup | void | text | func | volatile | unsafe | cloudsqladmin | invoke
r | cloudsqladmin=X/cloudsqladmin +| internal | pg_replication_origin_session_setup | configure session to maintain replication progress track
ing for the passed in origin
| | | | | | | |
| cloudsqlsuperuser=X/cloudsqladmin | | |
(1 row)
postgres=> \du replicator
List of roles
Role name | Attributes | Member of
------------+-------------+---------------------
replicator | Replication | {cloudsqlsuperuser}
postgres=> GRANT EXECUTE ON FUNCTION pg_catalog.pg_replication_origin_session_setup TO replicator;
WARNING: no privileges were granted for "pg_replication_origin_session_setup"
GRANT
Has anyone been able to setup pglogical before on CloudSQL? Or seen the above permission denied error?
Thanks in advance.
Gavin
