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

Check if the required tools are installed:

pg_dump --version
psql --version

---

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

• 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

---

Step 3: Get Your Supabase Connection Details

1. Log in to Supabase and navigate to your project.
2. Go to Settings → Database.
3. Copy your PostgreSQL connection string, which will look like this:

postgresql://<username>:<password>@<host>:5432/<database>

---

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

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!