[Postgres]: Support logical replication

[nicoburns]

Required Terms

• I agree to follow this project's Code of Conduct
• I have read and accept the Salesforce Program Agreement

What service(s) is this request for?

Postgres

Tell us about what you're trying to solve. What challenges are you facing?

Logical replication is currently blocking us in two ways:

*Downtime when upgrading Postgres*
Our biggest problem with using Heroku has been upgrading Postgres versions. It is currently impossible to upgrade heroku-hosted Postgres without downtime on the order of several minutes. the recommended way to do this with minimal downtime is:

(1) Provision a Postgres instance with the new postgres version
(2) Logically replicate from the old instance to the new
(3) Very briefly shut everything down, just long enough for any in-flight requests to finish
(4) Wait for follower to catch up (this should be fast) 
(5) Promote follower to master
(6) Turn everything back on

Unfortunately (2) is not possible without logical replication.

*"Realtime" subscriptions*

Projects like https://github.com/supabase/realtime offer realtime subscriptions on top of Postgres. But they require a logical replication slot in order to function.

[afawcett]

Excellent feedback @nicoburns and its clear others here feel the same given the upvotes. As managed solution our goal is to provide abstractions and tools that take away or reduce friction like this - this is something I'll ask the team to consider in our future roadmap for Heroku Postgres. We may have a some follow up questions here as well. Thank you again!

[massens]

To my knowledge this is needed as well for Airbyte CDC replication.

[chuck-alt-delete]

Logical replication is also required to create a Postgres source in Materialize. We’d love to be able to offer Materialize to Heroku Postgres users

[noahtf13]

Any update here? This is affecting our ability to properly handle deletes without full historical loads of tables.

[bubbaspaarx]

Is there any update on the progress of this request. Would really help working with clickhouse's expermiental materialize engine

[usiegj00]

Would love to have this. Heroku states they are blocking access to replication for "security," but they allow their own follower databases to access the role. This, plus the reality that database url's have been shared in cleartext by default and databases presented to the whole wide internet, makes it hard to appreciate the security focus on the one area that would make it easier for folks to migrate their data.

From the KB article:

Third-party tools, such as Amazon DMS, may require SUPERUSER or REPLICATION roles to be set on a user accessing the database. Heroku Postgres does not give customers access to the superuser role, or allow customers to grant other users the replication role, due to these roles being very privileged.

Therefore, it is not possible to use these third-party tools to replicate your Heroku Postgres database to a non-Heroku database.

Makes it seem like this should be better articulated on the front end. Maybe database addons could indicate whether they allow for replication or not. This is a major distinction.

Would love an update on this issue.

[mhubadam]

Logical replication is also necessary to sync data into data warehouses. We use Fivetran and are forced to use XMIN to detect row changes. A major limitation of XMIN is that you can't detect deletions. So the data warehouse gets out of data fairly quickly and the only solution is to resync the entire database. This is easily solved with logical replication and access to the WAL logs.

[karlbecker]

Please add support for this. Many other Postgres providers have it, and this feature is going to save us days if not weeks of development time on a new feature we're making, intended to build up our resiliency and allow us to have mostly zero-downtime deployments.

If this existed even within Postgres databases hosted at Heroku, that would be helpful. Hopefully you'd allow the replication to happen outside of Heroku, too, but even within Heroku would be helpful.

[jbrown-heroku]

Thank you all for your patience. It is clear that logical replication is an important feature addition. We are continuing with our research to deliver more flexibility to our database fleet and we are reviewing how we can deliver this. I will share more updates soon.

[noah-vetcove]

@jbrown-heroku any update on this or any idea/estimates on when this will be supported? For context we are looking to use CDC to replicate data into snowflake

[jbrown-heroku]

Originally targeting for early 2025 with many other features planned for release, but I am working to expedite this capability ahead of this schedule to possibly within 2024. I understand the importance of this feature and we will do what we can to move this up on timing.

Edited above: changed to "... possibly within 2024", as we work to try to move this up. This change was made to avoid confusion with target date. I will update as soon as we have the new target date.

[nightpool]

This would be a big help for us at Genius too!

[kobiebotha]

Had a customer ask for this, looking forward to the release.

[internets]

We are also looking forward to this, and the timeline may affect some important architectural choices. Please let us know if there is any update on the timeline.

[gzzwmwtZjQDsPDqN]

Can I use logical replication from Heroku Database (deployed in Private Spaces) to AWS RDS?

[jbrown-heroku]

We currently do not support Logical Replication on Heroku, including to AWS RDS, unfortunately. @gzzwmwtZjQDsPDqN, we do offer PrivateLink to connect Heroku Postgres from AWS env, though it may not be what you're looking for..

We will support Logical Replication with the new platform we are building.
As for the timeline, it's best to assume the next generation data platform delivery, targeted for mid next year, however, if we can support this feature sooner, I will be sure to update.

[takahiro-yonei]

I'm running CDataSync on Heroku. (common-runtime)
This is needed as well for CDataSync CDC.

[jbrown-heroku]

Thank you for mentioning @takahiro-yonei ; noted.

[jeffbax]

This is making Fivetran very painful for us as well, as the XMIN and Teleport options are significantly more expensive on the postgres instance -- we regularly get warning emails about risks running out of temporary disk space in the DB related to this... without a lot of obvious ways to alleviate it given the size of some of our tables.

[brickpack]

@jbrown-heroku are there any updates on support for logical replication? We specifically would like to be able to replicate to AWS Aurora.