How can we duplicate project DB?

[rphly]

I've read other discussion threads but have not found much success.
I can create an SQL dump of only public tables but am running into errors like must be member of role supabase_admin
due to lines like ALTER TABLE public.chats OWNER TO supabase_admin;.

As Supabase currently does not allow promotion to SUPERUSER, I am unable to get around this. Some help would be needed as I want to create a prod version of my current staging schema (without data).

[liaujianjie]

I believe that the SQL runner in the dashboard uses a Postgres user that's a superuser. You can run parts of your pg_dump dump that requires superuser access (e.g. ALTER TABLE, CREATE FUNCTION, and ALTER FUNCTION queries) in the dashboard. That's what worked for me.

[rphly]

My god why didn't I think of that 😅

[nstrelow]

@rphly If I understand correctly the only way to run commands like this is through the dashboard and is not possible if logged in as the user named postgres?

[liaujianjie]

@nstrelow If by "commands like this" you mean commands that are executable only by superusers, then yes. The postgres user is no longer a superuser for new supabase instances.

[rphly]

@nstrelow Can confirm that using the dashboard SQL editor works. Just create an SQL dump as you usually would and copy that into the SQL editor.