or How to migrate Postgres Databases with Heroku.
Last week I discussed some highlights from Johannes Wagener and my Battle Born campaign for The Killers. As mentioned in that post, we also simultaneously launched a campaign for Muse called The Social Connectome. Using the Human Connectome Project as inspiration, we developed a Chrome experiment that mapped and visualized Muse social connections (rather that the brains neural network) in an organized effort to unlock The Isolated System, a track off the new album, The 2nd Law. You can read an overview of that build on Jo’s blog.
Today I’d like to tell you about something that transpired right in the middle of that launch and serves as a testament to Heroku‘s excellent Postgres database infrastructure.
The campaign itself was quite viral for two reasons:
- Muse has a shit ton of fans: over 13 million Likes and 1 million Twitter followers
- We created personalized entry links for some of the biggest media outlets such as NME and Rolling Stone so they could track their contributions to the Connectome. In other words, gamifying the press.
This led to several thousand concurrent visitors for the first few hours. All in all, the site held together nicely (after scaling up our Heroku dynos) because it was a single page client side app cached to all hell.
However, there were…
Johannes foresaw an issue that was going to arise because of how we created the unique media specific links. We generated these initially in our development database and serviced them out one by one to each outlet.
Once we were ready to push the site live, we wiped the database of all test data, including the media links, and regenerated them to the precise ID they were given before. This led to a slightly out of sync database index:
- ID 1-100 == Empty
- ID 101-125 == Press links
- ID 126 > == Empty
Given our setup, the first 100 users that created their own unique link wouldn’t see any issues, but user 101 would run right into the first press link causing the database to throw an error due to duplication. In reality this point of contingency was more like 5,000 (not 101) so we had some time to react, and the fix was easy enough: shift the database index pass the press links.
However, we quickly realized that we weren’t going to be able to do this because we had already hit the concurrent user connection limit on Heroku’s Postgres development database: 20. In other words, the mass of activity on the site was preventing us from accessing our database as an admin. Doh.
The only way to raise the database connection limit was to upgrade to a higher tier Postgres configuration offered by Heroku. We chose the base option, but the upgrade only got us half of the way there. We also needed to migrate all the current rows over to the new database.
Given the fact that the inspiration of this campaign was neural mappings, I penned this migration of data a brain transplant.
Johannes operated on our patient while I watched nervously from the operation deck. He began but putting the patient to sleep:
$ heroku maintenance:on
Then he captured a copy of the patient’s memories:
$ heroku pgbackups:capture --expire
and transplanted them into our new brain:
$ heroku pgbackups:restore HEROKU_POSTGRESQL_FRANKENSTEIN
I watched him place the new brain into our patient:
$ heroku pg:promote HEROKU_POSTGRESQL_FRANKENSTEIN
and flip the switch.
$ heroku maintenance:off
He then whisper something, a prayer perhaps, and waited…
$ heroku restart
Please! Remain in your seats, I beg you! We are not children here, we are scientists! I assure you there is nothing to fear!
While I do love the theater of this occurrence, I should also mention that this could have all been easily prevented if we generated those links differently or started with an upgraded database. However, then we wouldn’t have found out that Jo looks a lot like Gene Wilder.