Unable to use pglogical replication in AlloyDB

[zortax]

I am trying to replicate an AlloyDB instance to CloudSQL for Postgres. After setting up logical replication on  both instances as you'd usually do (enable extension, creating nodes, susbscriptions), the CloudSQL instance fails to replicate, giving only this error:

ERROR:  BEGIN on target node failed: ERROR:  permission denied for function pg_replication_origin_session_setup

Privileges should be setup correctly on both instance, with the roles in questions being member of alloydbsuperuser (or cloudsqlsuperuser respectively) and having the replication privilege. I have tried granting "execute" on this function to my replication user with

GRANT EXECUTE ON FUNCTION pg_catalog.pg_replication_origin_session_setup TO replication_user;

but it seems like this privilege can't be granted unless the current role actually is a superuser role. As it is impossible to login as true superuser ("alloydbadmin"), I am stuck at this point.

Is it in any way possible to replicate from an AlloyDB instance at this time?

[robertcarlos]

Hi @zortax,

Welcome to Google Cloud Community!

Please check out this documentation on replicating data between AlloyDB Omni and other databases as pglogical extension is installed and AlloyDB Omni server is installed and configured. Access to your non-AlloyDB PostgreSQL cluster should be a superuser as well.

If the documentation above didn't work, you may file a bug so that our engineers could take a look at this. We don't have a specific ETA for this one but you can keep track of its progress once the ticket has been created.

Hope this helps.

[zortax]

Hi.

I am sorry if my post was unclear. I am not using AlloyDB Omni, but GCP's managed AlloyDB for Postgres. While I obviously can't configure that instance other than setting the flags needed for pglogical and logical decoding, I am pretty much doing what is described in the documentation you linked. The one line that doesn't seem to work is

ALTER USER pglogical_replication WITH superuser;

but that seems to be intended with GCP's managed databases. Replication doesn't seem to work because my replication user lacks execute permissions on pg_replication_origin_session_setup. I am not able to grant execute permissions to any user, nor am I able to grant "superuser" using the adlloydbadmin role. That role in on itself also doesn't seem to be able to to call said function, so to me it seems like it is simply impossible to use pglogical to replicate to an external Postgres instance (or to CloudSQL for Postgres for that matter) right now, which is very unfortunate.

For now I have settled with pg_dump for data migration, even though that requires long downtimes for data integrity reasons, so it would be nice if this would become a possibility in the future. To be honest, being locked in this way with no way of migrating away seamlessly is kind of a big reason for never using managed AlloyDB again...

I will file an issue if I find the time for writing up exact steps to reproduce.

[diegosucaria]

I faced this issue as well. I think It is related to copying users and roles before starting the replication.
I dropped everything and started over, following this lab step-by-step https://partner.cloudskillsboost.google/paths/129/course_templates/832/labs/511249