Access pg:backups from Heroku Scheduler

Question

I want to send my last heroku postgres backup to S3 to have it stored outside the heroku infrastructure, just in case I click 'delete' on wrong DB.

To achieve this I need an URL to the backup. I tried to run heroku pg:backups public-url -q, but it does not work on one-off dynos (I have tried on heroku run bash dyno). Whe It seems that heroku toolbelt is not available on dynos.

I thought I could use the platform-api gem, but it seems not to support pg:backups.

Do you know how I could get the pg backups data on heroku scheduler? Or maybe you have a better method of sending DB backups to S3?

Answer 1

A bit late, answering for those who have this question in the future.

There's nothing built into the Heroku platform that allows direct, automatic export of your Postgres backups to off-platform storage. There is a fairly straightforward way to manage this without too much effort.

Imagine that the application which has the database in question is named foo and you have a scheduled daily backup on that database. Periodically, you'd like to export the backup to S3 as described in the question. First, create a dummy app that will function similar to a service account. We'll call it bar for now.

Now, make sure to include the Heroku CLI buildpack with this application and follow the setup instructions. This way you can authorize and access the CLI directly from bar's application code. Now you can add the Heroku Scheduler add-on to bar and write some application code (like a rake task, since we're talking ruby) to complete the download and backup. Once you schedule that task to run at your preferred interval, you've got an app that can archive Postgres backups for you.