Migrating Large Heroku Postgres Instances to AWS Aurora Without Downtime
Might have been nice to see this post a year ago :).<p>My company migrated our ~2TB postgres db from heroku to AWS (originally aurora, we switched back to RDS postgres). Haven&#x27;t had time for a blog post, but figure I might post our learnings here, and the differences in our approach.<p>* We did the second step (ec2 -&gt; aurora) via AWS DMS (database migration service). This service may be a little more of a pain than it&#x27;s worth, but it&#x27;s what we settled on.<p>* we used wal-g rather than wal-e for the initial phase, it has slightly better performance, and we were happy with the tool.<p>* We wrote ansible playbooks to perform the heroku -&gt; ec2 migration. it&#x27;s definitely nice and makes you feel a little more secure having run the db migration multiple times over, and it would be a giant PITA to do all that manually. (hadn&#x27;t used ansible much before, I have to say, I&#x27;m a fan! had used chef&#x2F;puppet in bits and pieces before and was never that keen on them).<p>* Aurora wasn&#x27;t the right fit for us. Be careful about reading the pricing. On the AWS pricing calculator it asks you for how many million requests you have per month. This is _not_ how it&#x27;s charged. If you read the docs, the IO charges apply to AuroraReadIO and AuroraWriteIO, which is not per db request, it&#x27;s per db storage request, which is number of requests * number of db blocks that each request fetches.<p>* Our Aurora costs were through the roof, (and variable and spiky, never something you want!). We migrated again to RDS postgres, and bumping the provisioned IOPs up we had equivalent performance (possibly better?) for half the price (and the price was stable). We benchmarked everything else but the cost with the db migration, instead relying on the AWS simple pricing calculator. The difference between predicted and actual storage IO costs was over 10x out if my memory serves (making the entire DB double the price per month). BEWARE!<p>* Aws DMS is kinda buggy, last I checked the table selection editor UI didn&#x27;t work when you added&#x2F;modified multiple fields. You can switch to a json editor, so generate the table selection once, and check it in the json editor in case the UI got borked.<p>* Speed wasn&#x27;t great with AWS DMS as well, we had to flip BatchApplyEnabled on, and take a lot of care with it.<p>* There were definitely some shenanigans to do with LOBs, but I can&#x27;t fully remember them.<p>* DMS does not update id sequences, so if you have any tables with incrementing ids, make sure that you set the sequences manually before switching dbs.<p>* We used debian as our intermediate ec2 instance. We&#x27;ve historically used ubuntu for many ec2 instances, but the default ubuntu AMI image uses MBR for it&#x27;s partition (!!! in 2020!!!) and while we had some code to swap it to GPT while running, that felt a little too spicy for a db migration, so we used base debian which has a sane GPT partitioned disk for it&#x27;s AMI. (Note: we needed to create partitions larger than what MBR can support so this was a hard requirement).<p>* The last learning I should add, is that heroku support were actually really great during the whole process, very knowledgeable and gave some setup example files for the ec2 instance. After that they basically leave it to you, but I can&#x27;t speak highly enough about them (thanks jessie!)<p>* One more for you, be careful about the version of libc that you have. even if the postgres versions are identical, subsequent libc versions can have slightly different string sorting, which can corrupt your indexes that are migrated after copying the basebackup. (we needed to rebuild an index or two due to this, not a huge deal, but an interesting gotcha. I swear I checked our locale settings 5 times when it went wrong until I clocked on to it being the libc version.)<p>There were probably more learnings than that, but that&#x27;s what comes to mind.<p>(EDIT: added final bullet point).
(EDIT 2: added one more bullet)