Overall I agree with this. For dumping just the schema definitions, I'm not that happy with it. My primary use-case for a schema dump is to run it after applying migrations in dev/ci, so that if two engineers each open their own branch with incompatible schema migrations, someone gets a git conflict.
In this situation, pg_dump is really annoying because it will give you a schema-only dump, but that dump is not "stable". In the sense that
- dump the schema
- create a new database from the schema
- dump the schema again
often gives you two different files in the two different schema dumps, due to minor re-ordering of tables/statements.
The other use case I rely on is just having a single place where the schema is defined is actually super helpful for debugging, and can be an entrypoint for other tools. Sometimes it's nicer to be able to open that up in your editor rather than have to explore through `psql` or read through many smaller migration files.
Because pg_dump does not generate a stable dump (same schema == same file contents), you're constantly getting noise or irrelevant changes to the file, which makes it essentially unusable or at least very annoying if you keep that file committed.
> Now, I expect that somebody is going to point out that pg_dump does not solve the dependency problem 100% reliably in all cases. And that's correct. However, I think that complaint just shows how spoiled PostgreSQL users are. Recreating a complex database schema without a dependency-aware tool like pg_dump is an absolute nightmare.
If you're interested in a tool that is dependency-aware, AND allows you to explicitly specify dependencies for the cases that pg_dump doesn't handle (primarily between functions and other objects, iirc), check out my cli tool pgmigrate [0]. You don't need to use it to manage your migrations, you can just use the `dump` subcommand. It's a little under-documented, and it's missing some obvious improvements (can't handle multiple postgres schemas (namespaces) right now, only lets you dump one at a time), but I've been relying on it and using it with pleasure.
Classic problems in a state based vs migration based management of your database code. What's wrong with information schema and an order by? If you are using migrations then the creation dates should be generally in the right order as well.
I’m not sure I understand — can you elaborate on “what’s wrong with an information schema and an order by”? I don’t see how that’s connected to the problems I described with pg_dump or the solution offered by pgmigrate, but I’d be curious to learn another way of solving be problem.
Just the problem with ordering of state in a database not matching the other database after you ran migrations.
Information schema being the authoritative schema for all the information about the meta state in most database systems (or at least the cross platform one) means you can write a query against these views and (if I am not mistaken) re-assemble state that's identical by ordering the application of the objects in the order they were created originally.
In this situation, pg_dump is really annoying because it will give you a schema-only dump, but that dump is not "stable". In the sense that
often gives you two different files in the two different schema dumps, due to minor re-ordering of tables/statements.The other use case I rely on is just having a single place where the schema is defined is actually super helpful for debugging, and can be an entrypoint for other tools. Sometimes it's nicer to be able to open that up in your editor rather than have to explore through `psql` or read through many smaller migration files.
Because pg_dump does not generate a stable dump (same schema == same file contents), you're constantly getting noise or irrelevant changes to the file, which makes it essentially unusable or at least very annoying if you keep that file committed.
> Now, I expect that somebody is going to point out that pg_dump does not solve the dependency problem 100% reliably in all cases. And that's correct. However, I think that complaint just shows how spoiled PostgreSQL users are. Recreating a complex database schema without a dependency-aware tool like pg_dump is an absolute nightmare.
If you're interested in a tool that is dependency-aware, AND allows you to explicitly specify dependencies for the cases that pg_dump doesn't handle (primarily between functions and other objects, iirc), check out my cli tool pgmigrate [0]. You don't need to use it to manage your migrations, you can just use the `dump` subcommand. It's a little under-documented, and it's missing some obvious improvements (can't handle multiple postgres schemas (namespaces) right now, only lets you dump one at a time), but I've been relying on it and using it with pleasure.
[0]: https://github.com/peterldowns/pgmigrate