Dumped schema cannot be loaded

[zhulik]

Description Schemas produced with dbmate dump for PostgreSQL cannot be used to provision databases. This is especially annoying because I wanted to use the dump to provision test database as well as to generate code with bob and it requires loading the dump into a running database.

I understand that dbmate simply uses pg_dump under the the hood, but it could at least try to clean up the produced dumps and make sure they are loadable.

• Version: 2.27
• Database: PostgreSQL 17.x
• Operating System: Ubuntu 24.04

Steps To Reproduce

1. Create an empty database using dbmate create
2. Create a dump with dbmate dump
3. Try loading this dump with dbmate load
4. Observe

   Reading: ./db/schema.sql
   Error: pq: schema "public" already exists
   

   SET statements also cause problems when loading pg17 dumps in pg16.

Expected Behavior

1. No errors should be raised, dbmate should either filter out sql commands related to creating public schema and it's own schema_migrations table from the dump or ignore them when loading it.

I volunteer myself to prepare a fix if we agree on a solution.

[docapotamus]

For pg_dump and pg_restore there is a clean option -c or --clean.

https://www.postgresql.org/docs/current/app-pgrestore.html

Is it worth adding these to the as options to dump and load?

[dossy]

Also, according to this StackOverflow post, it looks like dumps from Postgres 11 onwards will produce a dump that can be restored as it won't try to recreate the public schema.

@zhulik states in their issue description that they're using PostgreSQL 17.x, so I'm not sure why their dump contains any statements that tries to create the public schema when it already exists.

I do see that dbmate's docker-compose.yml currently (v2.28.0) still tests against postgres:10 but that should only be used for CI/testing of dbmate, not by users in their own environments.