← Blog

How to migrate from Supabase to RDS

Supabase is great for quickly spinning up a Postgres database that’s easy to connect to. But I’ve had to migrate off of it twice so far:

  • Performance was not good enough (despite choosing medium/large instance) for large data pipeline workloads.
  • Another time, I had abnormally high database egress traffic that I needed to debug.

Both times, it was difficult to debug what was going on. I only use Supabase for their database, not their Auth, API, real-time, nor storage offerings, so in each of these cases it just made sense to get my own RDS instance and have full control.

I use TablePlus for my database GUI, and made several mistakes that I’d like to help my future self (and anyone doing a similar migration) avoid.

”Export” is different from “Backup”

I initially started with TablePlus’ “export” function. I selected the SQL format, and went over the data. Looked good. Then imported. All the data was there. Great. I was even able to read the data from my application correctly.

But the moment I tried to add a new row to any table, I would see errors I never saw before about how an auto-increment ID could not be null.

A null ID? How?

It turns out that “exporting” does not export everything from the database, including auto-increment sequences. I’m sure there’s a bunch of other things it doesn’t export as well, but I think the auto-increment is the first issue devs are likely to face.

I found out that it was possible to fix the auto-increment columns with a separate script, but I wasn’t happy with this approach. What else was my database missing?

Backup flags

TablePlus offers a “backup” option, which is what you actually want when migrating from one database to another. I naively went through the default options, but ended up with errors on restore.

The reason? With a full default backup, it exports things like “supabase_admin” and all the extra schemas they use, such as auth, storage, and extensions. If you’re moving to RDS, you likely want to set up a new user and password. Further, if you’re only using the public schema, you may not need all of these other tables.

The solution? Flags. Thankfully, TablePlus offers a freeform text field where you can enter standard pgdump flags.

--format=custom --no-owner --schema=public --no-acl
  • --no-owner removes the owner of tables, which is important since we have a new user in our new database. However, due to the nature of Supabase, there may be other ownership pieces that sneak in. These will be handled in the restore step.
  • --schema=public specifies the name of the schema I want to export. If you have multiple schemas, you can add multiple --schema flags.
  • --no-acl will prevent all GRANT and ALTER DEFAULT PRIVILEGES statements from being added to the dump file.

Restore flags

TablePlus also offers flags when restoring from a .dump file. Here’s what worked for me:

--no-owner

Without the above, I was not able to restore due to extra owner data sneaking into the .dump file.

Now? Everything works, including auto-increments.

Conclusion

Note that this post only covers the simplest database migration, and you will encounter other snags when handling things like extensions.

A lot of this could have been avoided if I simply used the pg_dump CLI, but I honestly enjoy my GUIs for databases. After all, they already hold my connection string data, and allow for easy data browsing. If you use database GUIs, keep these notes in mind as the process is likely to be extremely similar across different software.

I’ll use this as a reference for future database migrations I do, and I hope others find this useful.