41

Trying to fully automate Heroku's Review Apps (beta) for an app. Heroku wants us to use db/seeds.rb to seed the recently spun up instance's DB.

We don't have a db/seeds.rb with this app. We'd like to set up a script to copy the existing DB from the current parent (staging) and use that as the DB for the new app under review.

This I can do manually:

heroku pg:copy myapp::DATABASE_URL DATABASE_URL --app myapp-pr-1384 --confirm myapp-pr-1384

But I can't get figure out how to get the app name that Heroku creates into the postdeploy script.

Anyone tried this and know how it might be automated?

Meltemi
  • 37,979
  • 50
  • 195
  • 293
  • Did you ever figure this out? I'm in the same boat and have not had luck trying to embed ENV['HEROKU_APP_NAME'] in the command. – Eric M. Nov 18 '15 at 23:19
  • 4
    Yeah, word from my contact @ Heroku is that it's currently impossible for an app to be aware of itself so you can't copy a DB to your app name because it can't know it. They expect to have a fix for this "in a few week". I'll post back here when they do (if I remember ;-) – Meltemi Nov 19 '15 at 20:20
  • Looking forward to a solution for this. – Paul Watson Dec 14 '15 at 12:15
  • @Meltemi: Any update on this? – zupo Jan 23 '16 at 14:17
  • Having real data in a test environment is something desirable, isn't it? Why Heroku gives such an advice: "Copying production data to test apps means risk of data leaks or other programming mistakes operating on recent customer data. For those reasons, we instead recommend seeding databases comprehensively with non-production data using seed scripts run with the postdeploy command." More on https://devcenter.heroku.com/articles/github-integration-review-apps#the-postdeploy-script – borjagvo Mar 11 '16 at 17:22
  • 1
    I just used EpiphanyMachine's solution and it worked perfectly: http://stackoverflow.com/a/36784098/337903. Worth an upvote/green check mark – Nick May 11 '16 at 15:13
  • Surprised this was nowhere to be found in Heroku's otherwise good docs. Most apps will need the database data as well as the add-on provisioned – Dan Garland Nov 08 '18 at 12:09

5 Answers5

47

I ran into this same issue and here is how I solved it.

  1. Set up the database url you want to copy from as an environment variable on the base app for the pipeline. In my case this is STAGING_DATABASE_URL. The url format is postgresql://username:password@host:port/db_name.

  2. In your app.json file make sure to copy that variable over.

  3. In your app.json provision a new database which will set the DATABASE_URL environment variable.

  4. Use the following script to copy over the database pg_dump $STAGING_DATABASE_URL | psql $DATABASE_URL

Here is my app.json file for reference:

{
  "name": "app-name",
  "scripts": {
    "postdeploy": "pg_dump $STAGING_DATABASE_URL | psql $DATABASE_URL && bundle exec rake db:migrate"
  },
  "env": {
    "STAGING_DATABASE_URL": {
      "required": true
    },
    "HEROKU_APP_NAME": {
      "required": true
    }
  },
  "formation": {
    "web": {
      "quantity": 1,
      "size": "hobby"
    },
    "resque": {
      "quantity": 1,
      "size": "hobby"
    },
    "scheduler": {
      "quantity": 1,
      "size": "hobby"
    }
  },
  "addons": [
    "heroku-postgresql:hobby-basic",
    "papertrail",
    "rediscloud"
  ],
  "buildpacks": [
    {
      "url": "heroku/ruby"
    }
  ]
}
EpiphanyMachine
  • 698
  • 1
  • 10
  • 8
  • 1
    This worked well for me going from a staging app to a review app, but if app.json were used in deploys to production, doesn't this run the risk of overwriting production data with a dump from staging? – Jamie Folsom Aug 11 '16 at 13:13
  • 1
    @Jamie Had a chat with a Herokai about this. Basically, you're gonna use this app.json to deploy into production only if you set up your app automatically through their API. My prod app si live and is not gonna pop up through their API, so it won't be of importance. Hope I helped you ! – Stan Sep 22 '16 at 13:18
  • 3
    This is a brilliant solution! I reached out to Heroku to see if there is a way to run pg_restore directly. There is but it requires a curl command so your solution is better imho. @Jamie to be clear, app.json only runs on review apps and never further up the pipeline (staging or production) – Lee Apr 13 '17 at 23:28
  • Looking at the script logs, I get loads of things like: `ALTER SEQUENCE ERROR: relation "addresses" already exists ERROR: role "ckoeyocnvasjns" does not exist ERROR: relation "addresses_id_seq" already exists ERROR: role "ckoeyocnvasjns" does not exist` – Chris Edwards Mar 01 '18 at 16:48
  • 1
    I had the similar issue which @ChrisEdwards had. I've changed the pg_dump command to `pg_dump -Ox $STAGING_DATABASE_URL` to remove the owner and all the privileges and it fixed the problem. – Alireza Oct 11 '18 at 16:23
  • This looks great, could someone explain how this doesn't impact production deployments. Is there a separate app.json in the master branch or some other switch that prevents the postDeploy script from running after a production deployment and copying the staging database into production. --- Nevermind: Postdeploy is run only once after the app has been created. Note that postdeploy is run only once, after the app has been created and deployed for the first time. It is not run when subsequent changes are deployed. – stujo Nov 16 '18 at 22:55
  • Adding to the above - You can now add scripts for specific environments: https://devcenter.heroku.com/articles/github-integration-review-apps#environments-in-app-json – RawKnee Mar 31 '21 at 18:40
  • I've used the above command and it almost worked, there were some `constraints` errors because I've had `Admins` table referencing to `Users` table when `Users` aren't yet created. I was able to resolve the problem by using `pg_restore` instead of `psql`: `pg_dump $STAGING_DATABASE_URL -F c -b -v -f db.sql | pg_restore -d $DATABASE_URL db.sql && bundle exec rake db:migrate` – Jan Matuszewski Oct 25 '22 at 12:34
4

An alternative is to share the database between review apps. You can inherit DATABASE_URL in your app.json file.

PS: This is enough for my case which is a small team, keep in mind that maybe is not enough for yours. And, I keep my production and test (or staging, or dev, whatever you called it) data separated.

sad parrot
  • 71
  • 1
  • 3
  • 1
    How do you account for cases where one person made changes to the database in their branch? Wouldn't that break it for other apps running on the same database? – Drenmi Oct 21 '17 at 14:05
  • 1
    Yes, it will. That's why I made it clear it was enough for my case, which at that time was a team of only two devs. So we used to let the other know if we were about to do something like that and take the responsibility do a rollback or anything else if it was the case. – sad parrot Oct 23 '17 at 14:31
  • Could you provide a code snippet on how to do this? – ricks Mar 20 '19 at 14:30
  • 1
    @RickS I don't have access to this project anymore, but I do remember we did this through config vars inheritance. If you have a DATABASE_URL or something like this you can specify it as inherited from the "main app" that way it will share the database. You can see code + more info about this here https://devcenter.heroku.com/articles/github-integration-review-apps#the-app-json-file – sad parrot Mar 21 '19 at 15:10
4

Alternatively: Another solution using pg_restore, thanks to https://gist.github.com/Kalagan/1adf39ffa15ae7a125d02e86ede04b6f

{
  "scripts": {
    "postdeploy": "pg_dump -Fc $DATABASE_URL_TO_COPY | pg_restore --clean --no-owner -n public -d $DATABASE_URL && bundle exec rails db:migrate"
  }
}
Jonathan Haar
  • 574
  • 4
  • 10
2

I ran into problem after problem trying to get this to work. This postdeploy script finally worked for me:

pg_dump -cOx $STAGING_DATABASE_URL | psql $DATABASE_URL && bundle exec rails db:migrate
kid_drew
  • 3,857
  • 6
  • 28
  • 38
0

I see && bundle exec rails db:migrate as part of the postdeploy step in a lot of these responses.

Should that actually just be bundle exec rails db:migrate in the release section of app.json?

AwesomeBobX64
  • 327
  • 2
  • 4
  • I believe the thing is that `postdeploy` script is run after `release` phase so if you'd put `rails db:migrate` at the release phase it won't make much sense as the database won't be there yet. https://devcenter.heroku.com/changelog-items/994 – Jan Matuszewski Oct 25 '22 at 10:26