DEV Community

Twilight
Twilight

Posted on

2

How to Transfer PostgreSQL Database from Local to Supabase on macOS

If you’ve been working with a PostgreSQL database locally and now want to move it to Supabase for production or sharing, this guide is for you. I’ll walk you through the process using terminal commands, focusing specifically on macOS users.


Why Supabase?

Supabase is a powerful, open-source alternative to Firebase. It provides a fully managed PostgreSQL database, making it an excellent choice for hosting your database in the cloud.


Prerequisites

Before you start, ensure you have:

  1. PostgreSQL installed locally (including pg_dump and psql).
  2. A Supabase account with a project created.
  3. Basic familiarity with the terminal.

Step 1: Install PostgreSQL on macOS (If Needed)

If you don’t already have PostgreSQL installed, follow these steps:

brew install postgresql
Enter fullscreen mode Exit fullscreen mode

Check if the required tools are installed:

pg_dump --version
psql --version
Enter fullscreen mode Exit fullscreen mode

Step 2: Export Your Local Database

Use pg_dump to create a backup file of your local database:

pg_dump -U <local_username> -h localhost -d <local_database_name> -F c -b -v -f backup.dump
Enter fullscreen mode Exit fullscreen mode
  • Replace <local_username> with your PostgreSQL username (usually postgres).
  • Replace <local_database_name> with the name of your database.
  • backup.dump is the file name where your database dump will be stored.

For example:

pg_dump -U postgres -h localhost -d my_local_db -F c -b -v -f my_local_db_backup.dump
Enter fullscreen mode Exit fullscreen mode

Step 3: Get Your Supabase Connection Details

  1. Log in to Supabase and navigate to your project.
  2. Go to SettingsDatabase.
  3. Copy your PostgreSQL connection string, which will look like this:
postgresql://<username>:<password>@<host>:5432/<database>
Enter fullscreen mode Exit fullscreen mode

Step 4: Import the Data into Supabase

Once you have your database dump, use pg_restore to load it into Supabase:

pg_restore -U <supabase_username> -h <supabase_host> -d <supabase_database> -p 5432 -v backup.dump

Enter fullscreen mode Exit fullscreen mode

Replace <supabase_username>, <supabase_host>, and <supabase_database> with the details from your Supabase connection string.


Conclusion

Congratulations! 🎉 You’ve successfully migrated your PostgreSQL database from local to Supabase using terminal commands on macOS. Supabase makes it easy to manage your database in the cloud while keeping full control.

Have questions or tips to share? Drop a comment below!

Image of Timescale

Timescale – the developer's data platform for modern apps, built on PostgreSQL

Timescale Cloud is PostgreSQL optimized for speed, scale, and performance. Over 3 million IoT, AI, crypto, and dev tool apps are powered by Timescale. Try it free today! No credit card required.

Try free

Top comments (0)

Sentry image

See why 4M developers consider Sentry, “not bad.”

Fixing code doesn’t have to be the worst part of your day. Learn how Sentry can help.

Learn more

👋 Kindness is contagious

Explore a sea of insights with this enlightening post, highly esteemed within the nurturing DEV Community. Coders of all stripes are invited to participate and contribute to our shared knowledge.

Expressing gratitude with a simple "thank you" can make a big impact. Leave your thanks in the comments!

On DEV, exchanging ideas smooths our way and strengthens our community bonds. Found this useful? A quick note of thanks to the author can mean a lot.

Okay