0

I find myself facing a challenge, and I'm reaching out for some guidance.

My current project involves a rebuild for a client, and we're considering adopting the Supabase stack. However, our existing MySQL database houses a substantial amount of data, and I'm a bit uncertain about the migration process.

I've already exported the data in a .sql file, which includes table creation, relations, and the data itself. While I've come across the Supabase documentation on migrating from MySQL here, I admit there are aspects that I'm struggling to grasp.

Has anyone here successfully migrated from MySQL/MariaDB to Supabase before? I would greatly appreciate any insights, tips, or resources that could help me navigate this process more smoothly. If you've encountered similar challenges or have valuable experience with Supabase migrations, your advice would be gold to me right now.

Thanks a bunch in advance!

Update Nov 14, 2023:

So i used my mac, instead of windows to be able to use the pgloader.

What i did was to install pgloader using brew install pgloader (since it automatically install dependencies).

So i have created a folder and set up a file config.load and use this:

load database
  from mysql://user:@host/source_db
  into postgres://postgres:[email protected]:XXXX/postgres
alter schema 'public' owner to 'postgres';
set wal_buffers = '64MB', max_wal_senders = 0, statement_timeout = 0, work_mem to '2GB';

mysql's password is null since it's from xampp mysql and it is default to null while on postgres i got it from my database settings on supabase. take note that this is online and not local setup supabase.

and i am encountering an error at Line 4

Note: I have moved this post from StackOverflow to here since it's related to a database, but I can't seem to tag Supabase here because I can't add tags due to the 300 reputation requirement. They told me to post it here, so please don't hate me for using the Postgres tag, but Supabase is built on Postgres, and this is the only tag I can use. I've also added the MySQL tag to increase visibility for my post.

enter image description here

3
  • What do you hope that Supabase will provide you over the underlying RDBMS?
    – Rick James
    Nov 15, 2023 at 16:40
  • Sorry, I didn't quite understand the question fully. From what I understand, it's about expectations regarding Supabase compared to RDBMS. Well, Supabase is essentially an RDBMS since it utilizes PostgreSQL. Using Supabase benefits me in several ways: it provides a free and straightforward database setup (except for the initial data migration), offers authentication/user management, facilitates storage, ensures easy setup for real-time functionality, and the added bonus of free hosting is especially beneficial for development environments. Nov 15, 2023 at 21:49
  • Thank you for adding supabase tag Nov 15, 2023 at 21:50

1 Answer 1

0

to proceed what i did is to keep it simple like this:

    load database
      from mysql://user:pass@host/source_db
      into postgres://postgres:[email protected]:XXXX/postgres;

Notes

  • mysql database name should be named as public
  • postgres should be granted with permission before doing this, so open your supabase and go to sql query and run: (i got this code from supabase mod at discord named garyaustin)
grant usage on schema public to postgres, anon, authenticated, service_role;

grant all privileges on all tables in schema public to postgres, anon, authenticated, service_role;
grant all privileges on all functions in schema public to postgres, anon, authenticated, service_role;
grant all privileges on all sequences in schema public to postgres, anon, authenticated, service_role;

alter default privileges in schema public grant all on tables to postgres, anon, authenticated, service_role;
alter default privileges in schema public grant all on functions to postgres, anon, authenticated, service_role;
alter default privileges in schema public grant all on sequences to postgres, anon, authenticated, service_role;

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.