How to export and import a database inside supabase

[SandroMaglione]

Hi,

I would like to export a database I created in one project in supabase (scheme+data) and then import it into another project still in supabase.
I saw #666 and #175, but it is not clear to me where should I write the commands (e.g. pg_restore) in supabase, or even if it would be the correct approach.

How can I achieve that? Is it possible?

Great work by the way, the project is amazing 👍

[steve-chavez]

You have the database connection string on /settings/database, you copy it and then you can connect to your db from your terminal(psql <connection_string>) or any other client.

Then:

pg_dump <connection_string_from_db_1> -> extract the dump -> pg_restore -d <connection_string_from_db_2> dump

The databases share some tables/schemas, so a full dump could fail. An option would be to pg_dump specific tables that you want and only restore them on the other database.

[SandroMaglione]

Thanks @steve-chavez

I ended up downloading PostgreSQL. Then I used pgAdmin to connect to the server using the host URL from /settings/database from my account supabase, and the password of the project. Then I simply made a backup+restore of the database.

[SandroMaglione]

I also used this to remove the older database and restore the newest one.

[MentalGear]

Are custom functions/triggers restored as well that way?

[sagar-jani]

It seems like pg_restore needs the dump in compressed binary format otherwise it gives this error - pg_restore: error: input file appears to be a text format dump. Please use psql.

[jdcauley]

Extending this convo: Attempted a schema dump in command line and in pgAdmin based on the docs and then this discussion and got the following errors:

pg_dump: error: query failed: ERROR:  permission denied for table identities
pg_dump: error: query was: LOCK TABLE auth.identities IN ACCESS SHARE MODE

[awalias]

can you try this from the SQL editor in the dashboard:

GRANT ALL ON TABLE auth.identities TO postgres;

[jdcauley]

@awalias I replied via email, I got a permission denied error.

[gillkyle]

In order to get this to work I had to keep appending GRANT statements in the SQL editor. What finally worked was the following:

GRANT ALL ON TABLE auth.identities TO postgres;
GRANT ALL ON TABLE realtime.schema_migrations TO postgres;
GRANT ALL ON TABLE realtime.subscription TO postgres;
GRANT ALL ON SEQUENCE refresh_tokens_id_seq TO postgres;
GRANT ALL ON SEQUENCE realtime.subscription_id_seq TO postgres;

And then pg_dump <connection_string_from_db> > backup.sql

[riderx]

Even with that, i couldn't backup i had to add this:

GRANT ALL ON TABLE storage.migrations TO postgres;
GRANT ALL ON TABLE supabase_functions.migrations TO postgres;
GRANT ALL ON TABLE supabase_functions.hooks TO postgres;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO postgres;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA supabase_functions TO postgres;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA storage TO postgres;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA auth TO postgres;

And now export work but i can't restore i get :

Restore database 'postgres2'
Restoring...
restore_pg_13.0: while PROCESSING TOC:
restore_pg_13.0: from TOC entry 12; 2615 16468 SCHEMA auth supabase_admin
restore_pg_13.0: 
error: could not execute query: ERROR:  must be member of role "supabase_admin"
Command was: ALTER SCHEMA auth OWNER TO supabase_admin;

in new table and in existing table:

Restore database 'postgres'
Restoring...
restore_pg_13.0: while PROCESSING TOC:
restore_pg_13.0: from TOC entry 12; 2615 16468 SCHEMA auth supabase_admin
restore_pg_13.0: error: could not execute query: ERROR:  schema "auth" already exists
Command was: CREATE SCHEMA auth;

[riderx]

So i copied table by table it was looking good, just missing the RLS, but then sequence were broken and even i found a way to fix it now real-time feature fail to work.

[simonh1000]

I need to do this too. I know very little about SQL so what I did was go to Supabase SQL Editor on the web. Then, in the top section I wrote pg_dump /settings/database (based on the accepted answer) and clicked run. I got Failed to validate sql query: syntax error at or near "pg_dump". I have no doubt that that is completely wrong, but I don't really know where to begin

[logemann]

pg_dump is a CLI utility shipped with postgres, you cant run this as an SQL. You can install pg_dump on your machine together with pg_restore and then work with those commands.

[sunilguptasg]

the /settings/database is the menu path - you go there in your supabase admin console and copy the info...

[LucasKuhn]

Writing here in case it helps somebody else - I did a backup from a Heroku postgres and wanted to move it to Supabase. What worked for me was using PgAdmin.

1. Create Supabase project
2. Download PgAdmin, right click on servers -> Register -> Server
3. Open your Supabase project -> Project Settings -> Database
4. On PgAdmin, paste the Host and Password
5. Open your new server, right click on postgres database -> Restore
6. Select your sql dump
7. On Data/Object, do not save Owner and Privilege
8. Click Restore

[revskill10]

Why not adding a button in console to export json schema from a table (with child table) ?

[Snailedlt]

Would be very useful to have something like what pgAdmin has, where you can extract databases or tables to varying file formats.

in pgAdmin you can backup your db/table like this:

[simonh1000]

Can't help thinking this is a completely obvious thing for supabase users to want to do, and that there should be an out of the box way of making such a copy. Perhaps some would not be permitted by data protection rules to make copy of data but, for most, the ability to work safely with a copy of production data is useful. Perhaps the assumption is that we are all postgres cli experts? But there's a javascript SDK which means the audience is broader than postgres folk

[didier]

Need something like this!