Looking to duplicate a Postgres database in a Supabase project to another project? Using pg_dump and pg_restore won’t work because you only have superuser access through the Supabase dashboard. Here’s a workflow of duplicating a Supabase Postgres schema using pgAdmin and the Supabase dashboard.
Exporting Schema
You can use the pg_dump in a CLI as well.
Right-click on pgAdmin’s public schema and click Backup
Enter a filename and choose Plain format
Toggle “Only schema” dump option
If you need both the schema and full data, you can choose to dump pre-data, data, and post-data. If you only need the schema, the selections shown below will work.
Run the backup.
This should generate a file named my_backup.
Content of my_backup:
--
-- PostgreSQL database dump
--
-- Dumped from database version 13.3
-- Dumped by pg_dump version 13.3
-- Started on 2021-11-04 09:35:27
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
-- and so on...
Manually install extensions
If you have queries that require a Postgres extension that is not installed by default in Supabase, you will have to manually install those extensions through the Supabase dashboard before running dumped SQL code.
Importing Schema
This step must be done in the Supabase dashboard.
Create a New Query tab
Copy SQL code from my_dump
Select the CREATE/ALTER queries you’d like to run and copy them to the query pane.
Run once you’re ready.
That should duplicate your schema from the original Supabase project.