pg:wait, pg:info, or --fork using API

[quarterdome]

I am able to create a new Heroku Postgres addon using the Platform API. However, it is not clear how to provision a fork of an existing database and wait until the fork is complete.

I tried the following two options to provision the fork:

hk.addon.create('apartmentlist-production', { 'plan' => 'heroku-postgresql:standard-tengu', 'fork' => 'heroku-postgresql-black' } )
hk.addon.create('apartmentlist-production', { 'plan' => 'heroku-postgresql:standard-tengu', 'fork' => 'HEROKU_POSTGRESQL_BLACK_URL' } )

Both of the above provision an addon, but do not fork an existing DB.

Also, when I use the addon.info endpoint, I do not get any of the info that heroku pg:info command gives me. In particular I see no way to know if a newly provisioned DB has caught up and is ready for queries.

If Platform API is not the right tool for it, is there any other Heroku API that can help me accomplish forking a database and waiting until it is ready?

[brandur]

@quarterdome Hi Matt, thanks for writing in! The short answer is that there is no good way to do this right now. The platform API provides a generic way to interact with all add-ons, but one downside of that is that there isn't really any way for those add-ons to enrich the experience for users from an API perspective. Actions like pg:wait and pg:info run through a separate database service, so your best bet to get this functionality right away is to reverse engineer what the Toolbelt is doing in those situations, but even this isn't strongly recommended because those APIs have no stability guarantees.

We're working on a better long-term solution for this problem.

/cc @heroku/department-of-data We need you under the umbrella!

[msakrejda]

@quarterdome for forking, you should be able to use the config hash to include the fork flag. That is, something like this should work:

hk.addon.create('apartmentlist-production', {
  'plan' => 'heroku-postgresql:standard-tengu',
  'config' => { 'fork' => 'postgres://...' }
)

(disclaimer: I have not tried this and have not used platform-api)

The fork argument must be the full URL of a heroku postgres database.

Unfortunately, we don't really have a better way to access data for pg:info or other pg: endpoints beyond what Brandur suggested, but replication status is actually something you can figure out yourself. You can run SELECT txid_snapshot_xmax(txid_current_snapshot()) on both leader and follower and compare the values, or just run SELECT pg_last_xact_replay_timestamp() to get a rough idea of what point in time the follower has replayed to. I'd recommend this approach rather than trying to reverse-engineer the existing API--that will almost certainly change at some point as we eventually provide a proper, documented, user-facing API.

[quarterdome]

Thanks for your suggestion, @deafbybeheading. I will try this for forking.

Unfortunately, I don't think your suggestion for pg:wait will work. You can use it to see the status of replication, but not the status of db preparing. While db is in Preparing state, one can not connect to it using psql.

I ended up scripting what I needed using Heroku Toolbelt CLI, and parsing its stdout.

[gregnavis]

I'd also love to see PostgreSQL-specific API calls in platform-api. I have look at the docs and it seems they aren't there yet (but please correct me if I'm wrong). Is this something that you plan to do in the near future?

[geemus]

We've certainly heard this ask, but we don't have a timeline we can share on providing this.

[geemus]

closing in favor of #47 (just to reduce duplication a bit)

[jlerpscher]

@uhoh-itsmaciek this command works but the Heroku pg fork command is not available with hobby plan:

curl -n -X POST https://api.heroku.com/apps/my-app-name/addons \
  -d '{
  "config": {
    "fork": "postgres://..."
  },
  "plan": "...."
}' \
  -H "Content-Type: application/json" \
  -H "Accept: application/vnd.heroku+json; version=3"

I can't find any way to copy a database from another app (like heroku cli fork command) or just migrate + seed my new database (hobby plan).

[msakrejda]

@jlerpscher Right, hobby plans do not support forking. You may be able to reverse-engineer pg:copy to do what you want, but this is not a supported feature of the platform right now.