Using pg_dump and pg_restore for Supabase
When moving PostgreSQL data, two useful tools are pg_dump and pg_restore. They allow developers to create a database dump and restore it into another PostgreSQL environment.
Create a Backup
A typical workflow starts by exporting the source database. For a custom-format dump, you can use: pg_dump -Fc -d "$OLD_DATABASE_URL" -f app.dump
The resulting file contains the database information needed for restoration.
Restore the Database
The dump can then be restored into the target PostgreSQL server: pg_restore -d "$NEW_DATABASE_URL" app.dump
The exact options depend on your migration requirements.
Consider Ownership and Permissions
When moving between environments, ownership and access settings may not match. Options such as --no-owner and --no-acl can be useful in certain migration scenarios.
Test the Result
After restoring, check: Tables Indexes Functions Extensions RLS policies Row counts Application queries
Do Not Forget Other Supabase Services
A PostgreSQL dump does not automatically move every part of your Supabase project. Storage files, authentication configuration, and other services need separate planning.
Hostwares documents self-hosted Supabase as a full stack containing PostgreSQL, Auth, Storage, Realtime, and Studio. Database migration is one part of the process—not the entire migration.