Heroku Review Apps: copy DB to review app

Question

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?

Chipper

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. – Siret

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 ;-) – Chipper

Looking forward to a solution for this. – Ginnifer

@Meltemi: Any update on this? – Klockau

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 devcenter.heroku.com/articles/… – Derna

I just used EpiphanyMachine's solution and it worked perfectly: https://mcmap.net/q/384395/-heroku-review-apps-copy-db-to-review-app. Worth an upvote/green check mark – Schwing

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 – Hindrance

Answer 1

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"
    }
  ]
}

Swelling

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? – Skilled

@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 ! – Maul

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) – Stimulus

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 – Emigrate

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. – Machination

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. – Maddis

Adding to the above - You can now add scripts for specific environments: devcenter.heroku.com/articles/… – Haematosis

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 – Saturday

Answer 2

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"
  }
}

Kaftan

Answer 3

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.

Arsyvarsy

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? – Wedgwood

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. – Arsyvarsy

Could you provide a code snippet on how to do this? – Barbet

@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 devcenter.heroku.com/articles/… – Arsyvarsy

Answer 4

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

Jepum

Answer 5

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?

Hayes

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. devcenter.heroku.com/changelog-items/994 – Saturday