Import/export postgres database endpoint

[MatthiasRMS]

Hi,

While reading through your API documentation, I couldn't find a way to import and export a postgres database. I can do that from the command line using pg backups/pg restore (https://devcenter.heroku.com/articles/heroku-postgres-import-export) but is there an equivalent for the ruby client (or any other client) ?

[mikehale]

Unfortunately we don't have a published API for some of the postgres commands. If you are feeling ambitious you could use HEROKU_DEBUG=1 and look at the cli implementation: https://github.com/heroku/heroku-pg/blob/master/commands/backups/capture.js, https://github.com/heroku/heroku-pg/blob/master/commands/backups/download.js

[MatthiasRMS]

If I'm really ambitious, can I look at the implementation and make a pull request for the ruby client ? :D

[mikehale]

It might have to be a new client/project as currently the ruby client is generated based of the public api schema, and we don't have a published schema for the postgres endpoints.

/cc @heroku/dod-core

[gregburek]

@MatthiasRMS could you provide more detail about what problem you are trying to solve here?

[MatthiasRMS]

Hi @gregburek !

I have a clean database dump stored on AWS S3. I usually restore it on my Heroku app from the CLI using: heroku pg:backups:restore 'https://s3.amazonaws.com/me/items/3H0q/mydb.dump' DATABASE_URL However, I'd like to reset it to the clean database every night, using a cron job (and the Heroku scheduler with Rails' rake tasks). I can't use the CLI in a cron job, so I need to use the ruby client, which apparently doesn't support this action.

If it seems overcomplicated, and you have a simple solution, I'm listening :-) If you have another client that supports this action, I could spin up another app in this language and setup a cron job with this client.

To sum this up: I'm just looking to import the same database in my app every night without doing it manually.

If this doesn't sound clear, let me know.

[gregburek]

Thanks for providing this detail.

The API for pg:backups is not documented because it may change at any time, as necessary to support it as a free service. Using pg:backups for these regular db resets, could fail with little visibility due to normal operational problems as well from changes to the service, so I think it is not a great fit.

If you are using Standard or Premium databases, I recommend creating a rollback database every 24 hours with a target of created_at of the db, which will restore you dataset to what it was when forked. Once available, the new db may be promoted and the old db may be destroyed.

For a hobby tier database, I recommend using an s3 client (such as s3gof3r) piped to pg_restore, which is available in the Heroku 16 stack image. It is not well documented but in the version of pg_restore in the stack image, you can use -d $DATABASE_URL and the binary will parse host, user, password and db information from the connection string.

This can run from a scheduler dyno or from an app dyno which is triggered from scheduler.

I recognize that this is not the answer you wanted, but the operational and scale problems with this service make it difficult to recommend or to extend functionality. However, I will take your use case into our planning as we figure out a way to an open and supportable API.

[MatthiasRMS]

Thanks for your answer @gregburek. I have a Standard postgresql database. I found the option to rollback, but it is manual. I couldn't find where the periodic option is (which is what I'm looking for). Where can I find it?

[MatthiasRMS]

@gregburek @mikehale Hi, any update on this ? I still can't find how to make it periodic

[gregburek]

You can use Heroku Scheduler to run code periodically. In cli form it could be:

`` heroku addons:create --rollback DATABASE_URL --by "1 day" heroku addons:wait heroku addons:destroy DATABASE_URL heroku pg:promote $(heroku addons | grep HEROKU_POSTGRESQL | awk '{print $NF}')

But I recommend using the Heroku API to do something similar.

[pmichna]

@mikehale I tried running heroku CLI with HEROKU_DEBUG=1. It runs two requests. The first one is /client/v11/apps/{app}/transfers. I guess the host is https://postgres-api.heroku.com. How should I authenticate to get results when running GET at https://postgres-api.heroku.com/client/v11/apps/{app}/transfers?

[mikehale]

@pmichna unfortunately there is no published api for postgres-api.heroku.com. You will need to either use the CLI, or reverse engineer the CLI to script the process.