Request for Support of session_replication_role Parameter in AlloyDB

[shubhzpatil45]

Dear Google Cloud Support Team,

I hope this message finds you well.

We are currently working on a PostgreSQL-to-AlloyDB data migration and replication pipeline, and have encountered a critical limitation: AlloyDB does not permit setting the session_replication_role parameter, even for privileged users.

This parameter is essential for tools like pgcopydb, pg_recvlogical, and others that rely on it to disable triggers and constraints while applying logical changes during replication.

We would like to request:

1. Clarification on whether there is any roadmap or possibility of enabling session_replication_role in AlloyDB.

2. Temporary workaround or alternate recommended approaches for logical replication and CDC (Change Data Capture) where this parameter is required.

3. If not supported, confirmation that this limitation is by design and non-negotiable due to security or platform constraints.

This capability is especially important for seamless migration and hybrid setups using tools like pgcopydb, which are standard in the PostgreSQL ecosystem.

Please advise on any alternatives, feature requests, or support plan escalation steps we can take to proceed with our project.

Looking forward to your guidance.

Thanks and Regards
Shubham, 

[nmagcalengjr]

Hi @shubhzpatil45,

Welcome to Google Cloud Community! 

Here’s a breakdown addressing your points:

1. Possibility of Enabling session_replication_role in AlloyDB: 

Currently, there are no confirmed plans to support session_replication_role in AlloyDB, and public documentation offers no indication that this capability will be added soon. AlloyDB is a fully-managed, PostgreSQL-compatible database for demanding transactional workloads, and Google Cloud often restricts direct access to certain PostgreSQL parameters that could affect its architecture or stability.

While there's no official roadmap to re-enable this setting, submitting a feature request through Google Cloud Support or your Customer Engineer may help prioritize it, especially if multiple customers express the same need.

2. Temporary Workaround or Alternative Approaches:
   
   • Use DMS (Database Migration Service): Using Google Cloud Database Migration Service (DMS) is the recommended method for migrating data to AlloyDB, as it supports both full data loads and Change Data Capture (CDC) without requiring manual use of session_replication_role. DMS abstracts the complexity of schema migration, data replication, and constraint handling by leveraging PostgreSQL's logical replication internally, allowing it to bypass triggers and constraints safely. 
   • Logical Replication with PostgreSQL: You can use native logical replication from your existing PostgreSQL database to AlloyDB, leveraging familiar tools and techniques. While this approach may require additional setup and manual handling, particularly when applying changes without access to session_replication_role. AlloyDB does support logical decoding and publications, making it feasible with some extra management effort.

3. Confirmation of Limitation (By Design/Non-Negotiable):

AlloyDB does not support the session_replication_role parameter, likely by design due to its fully managed, secure, and opinionated architecture. This restriction applies even to privileged roles like cloudsqlsuperuser, reflecting Google’s intent to abstract low-level configurations that could risk data integrity, stability, or security. 

You can also refer to these documentations, which might help for future reference.

• AlloyDB flags
• Supported database flags
• Supported database extensions
• About PostgreSQL users and roles

Additionally, consider consulting with our Google Cloud Support to help ensure your options about your migration process.

Was this helpful? If so, please accept this answer as “Solution”. If you need additional assistance, reply here within 2 business days and I’ll be happy to help.

[shubhzpatil45]

Does alloyDb allows to create the subscription on the RDS publisher ?

[abdullah2000]

Hey Shubham,

I see that its been 3 weeks since you posted the question but did not get it solved. So, I thought of giving you some suggestions only if my comment can help you. 

This is something a lot of teams run into when switching from PostgreSQL to AlloyDB. 

Right now, AlloyDB doesn’t support session_replication_role/. Google left it out on purpose to stop people from skipping triggers or constraints, which could mess with your data.

There’s no real hack around it either. What Google suggests is using logical replication tools like pgoutput or other CDC tools that don’t need to mess with the replication role.

If you really need to skip triggers, try loading the data into a normal PostgreSQL instance first. Do your cleanup and prep there, then move the clean data over to AlloyDB.

Also, let Google Cloud support know you want this feature. The more people ask, the more likely it’ll show up in the future.

In the meantime, Windsor.ai could be a solid workaround. It’s an ETL/ELT tool that is automatically syncing and transforming data without too much hassle. Using it, you can pull data from your PostgreSQL instance in a few minutes and then transfer it to AlloyDB. 

Hope that clears things up and helps you figure out your next move!