Missing schemas for branches created through the Dashboard UI due to enabled extensions

[k1ng-arthur]

Bug report

• I confirm this is a bug with Supabase, not with my own application.
• I confirm I have searched the Docs, GitHub Discussions, and Discord.

Describe the bug

Currently when using Branching via the UI, if you have enabled an extension such as PGMQ which produces additional schemas in your main database, the creation of the branch will fail as it is unable to find those migrations.

To Reproduce

Steps to reproduce the behavior, please provide code snippets or a repository:

1. In the dashboard, enable pgmq extension
2. Create a new branch via the UI and wait for setup to complete
3. Go the the 'manage branches' section under branches dropdown and view the logs of the new branch
4. See the following error below which shows the branch does not recognise the pgmq schema as the extension appears to create the schema (which is not turned on at branch initiation)

Expected behavior

The branch should be able to run all the migrations without issues and any schemas currently used on main should be found on the branch also.

Screenshots

System information

• Supabase
• PGMQ extension

Additional context

Add any other context about the problem here.

[kallebysantos]

Seems related with #4492

[ToJen]

@k1ng-arthur is it related to my issue that @kallebysantos referenced?

[ToJen]

Are you still experiencing it? I think the pgmq integration has issues. Even running pgmq on localhost Supabase has inconsistent behaviour with the pgmq in Supabase's cloud environment.

[k1ng-arthur]

Seems related with supabase/cli#4492

Yes i think it is related, i didn't create any queues but simply running on pgmq in prod meant that the branch does not have that schema though the migration command above of CREATE EXTENSION IF NOT EXISTS suggests it should pick it up. This was an issue last time i attempted this which was yesterday evening and i had to reset my branch after it was initially created to get it to work

[dumko2001]

Hey everyone, I've tracked down the root cause of this schema disappearing act during branch resets and creation.

Because PR #3043 excluded pgmq from pg_dump --schema-only (due to an old pgmq parsing segfault), the CREATE SCHEMA pgmq statement is missing from branch dumps. To "fix" this, PR #4391 added a script that uses sed to strip WITH SCHEMA "pgmq" from the extension initialization on Branches. This inadvertently installed pgmq into public, destroying any dependent user references that require pgmq.*!

Furthermore, drop.sql implicitly ignores pgmq so branch resets leave orphaned queue tables around, causing validation drops on migration re-applications (issue #4492).

I have submitted a fix in #4940 that cleanly resolves both of these issues by correcting the schema replacements and reverting the unintended branch reset exclusions. It ensures that the schema is created and extension runs correctly within pgmq.

[Hallidayo]

Transferred from the supabase/supabase repo.