migrating from supabase

hi. i'm wanting to migrate from supabase to neon, and i want to ideally do it with a live replica on neon (or some similar setup so i can progressively migrate). i'm thinking of using neon as a read replica for now with CDC and then moving all writes from neon -> supabase later. is this a valid approach? any advice?

6 Replies

eastern-cyan•7mo ago

Hey @oof2win2, What's your database size that you're looking to migrate?

automatic-azureOP•7mo ago

at this point 35-40gb?

eastern-cyan•7mo ago

Thanks for sharing that number. I believe that for now, you can use logical replication between Supabase and Neon to make sure that the data exists in both Neon and Supabase. Given the size, I believe in 1 (or 2 hours at maximum), all the data should be in sync. I'm in the process of updating the guide up there but here's what you'd want to do: 1. Enable IPv4 for your Supabase project in Settings > Add Ons > Dedicated IPv4 address section (1.1 Go to Add Ons in your project settings and click Change dedicated IPv4 address, 1.2 Select Dedicated IPv4 address (that comes at $4.00/month/database) and click Confirm) 2. Create a project in Neon. During that: - Select the relevant Postgres version during creation of the project - Select your desired Region - Make Compute Size fixed to the maximum Compute Units (say 16 vCPU, 64 GB RAM) - Uncheck the button which says “Scale to zero after 5 minutes of inactivity” 3. Once the project is created, go to Settings > Storage (in your newly created Neon project), set History Retention to 0 and click Save. 4. Ensure that all the tables (in Supabase) have Realtime enabled (this allows you to be able to stream changes to Neon as soon as new records are inserted into Supabase). 5. All tables in Supabase should either have a PRIMARY KEY or REPLICA IDENTITY FULL (a requirement for logical replication). If a table does not have a primary key, either that table can be altered to have one, or it can be altered to have the replica identity full via the SQL query ALTER TABLE public.table_name REPLICA IDENTITY FULL; in Supabase. 6. Create a schema only dump of your source database using your Supabase connection string (make sure it is Direct connection string OR Session pooled connection string [NOT Transaction pooled connection]) using the following command:

pg_dump --schema-only --no-privileges --no-owner "supabase-direct-or-session-connection-string" > pooler_schema_dump.sql

pg_dump --schema-only --no-privileges --no-owner "supabase-direct-or-session-connection-string" > pooler_schema_dump.sql

7. After changes in the .sql file, load the schema into Neon using Neon’s connection string (make sure it is Direct connection string and NOT pooled):

psql "neon-connection-string" < pooler_schema_dump.sql

psql "neon-connection-string" < pooler_schema_dump.sql

8. Create a role in Supabase dedicated to replication by executing the following SQL queries in Supabase:

CREATE ROLE replication_user WITH REPLICATION LOGIN PASSWORD 'your_secure_password';

GRANT USAGE ON SCHEMA public TO replication_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO replication_user;

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO replication_user;

ALTER ROLE replication_user SET statement_timeout = 0;

ALTER ROLE replication_user BYPASSRLS;

CREATE ROLE replication_user WITH REPLICATION LOGIN PASSWORD 'your_secure_password';

GRANT USAGE ON SCHEMA public TO replication_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO replication_user;

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO replication_user;

ALTER ROLE replication_user SET statement_timeout = 0;

ALTER ROLE replication_user BYPASSRLS;

9. Create a publication in Supabase using the following SQL Query:

CREATE PUBLICATION publication_neon_1 FOR TABLE public.a, public.b;

CREATE PUBLICATION publication_neon_1 FOR TABLE public.a, public.b;

10. Create a subscription in Neon using the following SQL query in Neon SQL Editor (make sure the connection string for Supabase is Direct connection string and NOT pooled [NOT even session pooled]). Make sure to obtain the Direct connection string for the replication user created in the steps earlier:

CREATE SUBSCRIPTION subscription_neon_1
CONNECTION 'supabase-replication-user-role-direct-connection-string'
PUBLICATION publication_neon_1;

CREATE SUBSCRIPTION subscription_neon_1
CONNECTION 'supabase-replication-user-role-direct-connection-string'
PUBLICATION publication_neon_1;

11. Wait for data to sync.

i'm thinking of using neon as a read replica for now with CDC and then moving all writes from neon -> supabase later. is this a valid approach? any advice?

Yes, once all the results of select * from pg_subscription_rel in Neon starts to show srsubstate as r you can perform the move over of writes to Neon.

automatic-azureOP•7mo ago

wait @Rishi Raj Jain will the replication "backdate" all of the data to neon, and then continue streaming updates? i know about the latter but had no idea the former existed as well if so that's amazing

eastern-cyan•6mo ago

will the replication "backdate" all of the data to neon, and then continue streaming updates

yes All the existing data would be copied to Neon, at that time select * from pg_subscription_rel in Neon starts to show srsubstate as d. Post srsubstate starts showing r, you are ready to cutover to Neon for writes. ikr! @oof2win2 how was your experience with the steps I shared?

automatic-azureOP•6mo ago

in the end it worked well - the only thing i had to fix and be aware of is the WAL size and timeout - had to increase WAL substantially and set timeout to 0 temporarily

Gaming

Programming

migrating from supabase

Did you find this page helpful?