Support for external follower databases to Heroku Postgres

raoulalwani commented 1 year ago

Required Terms

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

What service(s) is this request for?

Heroku Postgres

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

We're generally happy using Heroku Postgres for our database, and we have logical backups being captured, but we would like to be able to specify an external postgres system to act as a follower to our main database.

Currently, we can set another Heroku Postgres DB as a follower, but that keeps everything on Heroku, which is not ideal in the case of an outage (extended or otherwise).

Right now our only alternative to support redundancy is to entirely not use Heroku Postgres, where we can specify an external main database server to our app and then build followers from there, but we would prefer staying on Heroku Postgres as our main DB with the external follower as a backup.

Thanks to the team for all your work on such a great platform, been a customer since the early days!

jbrown-heroku commented 4 months ago

Hi @raoulalwani thank you for logging. I think Logical Replication might work in your scenario, which is a feature we are currently actively researching: https://github.com/heroku/roadmap/issues/38

Please let me know if the proposed idea works; if so, please also provide your feedback/thumbs-up on the referenced issue. Thank you!

raoulalwani commented 4 weeks ago

@jbrown-heroku thanks and sorry for the delayed reply, didn't get a notification. Interestingly, found the other issue on Logical Replication while searching for the same solution, so yes, Logical Replication will do the job nicely I reckon. Is the timeline still before end of the year 2024?

jbrown-heroku commented 3 weeks ago

@raoulalwani thank you. We are focused on the new platform for Postgres, which we are including Logical Replication, but the release is currently targeted for mid-2025. I am still exploring ways to deliver Logical Replication, if at all possible, before the new platform release.

raoulalwani commented 3 weeks ago

@jbrown-heroku ok thanks for the update. Do you have any thoughts or opinions on using Bucardo to accomplish something similar for the time being? I understand there is some table locking during the initial sync, but running it off a Heroku Postgres Follower should eliminate any production impact, right? The follower will just lag behind prod until the initial sync and locking is done, then the follower will catch up, and Bucardo handles the delta without locking the tables again?

I've seen some articles written online, but nothing directly in Heroku's docs about Bucardo as a potential option:

https://www.porter.run/blog/migrating-postgres-from-heroku-to-rds https://medium.com/hellogetsafe/pulling-off-zero-downtime-postgresql-migrations-with-bucardo-and-terraform-1527cca5f989 https://medium.com/@benjichen16/5-29-bucardo-database-replication-produc-2a7b08dcc622

heroku / roadmap