Version control for databases | Postgres.FM 018 | #PostgreSQL #Postgres podcast

Why not Liquibase? It's rather more useful then only git as it was described there

Guys, great stuff... This needs to be covered. Now I am new to PG, but for DECADES we have extracted DDL for all objects and committed them to source control. We all have our own branch, and production and staging have their own branch. This is used to DIFF against for conflicts and other changes.

We use SCRIPTS to publish our changes. The individual files can be included to grab the latest code. But we use it more as a RECORD of changes. Not completely to deploy.
Although it allows you to easily grab something and use it via the script.

Our process is to use pg_dump -s --file=xxx.sql
And then we have a routine that parses the SQL into a directory structure: DB_NAME\SCHEMA\OBJ_TYPE\OBJ_NAME.ddl
It also deletes old/removed files.
And it deals with function overloading!
It also uses CREATE OR REPLACE type syntax as appropriate.
It also creates a few useful files in the root of the DB_NAME folder... (all_objects.tsv for loading into excel, quickly extract DROP statements or \ir <...> statements (of course, filterable by schema, object_type, etc), all_constraints.sql and all_indexes.sql which simply does a \ir <..> on the complete list of corresponding files, because after loading fresh data, we need to re-constrain/index everything)

Again, this is source control has a HISTORY Management System. Not to push code changes out. Just to save/protect code. Trust me, after having 20+ years of this, it's really nice to see a piece of code was written ORIGINALLY, never modified and never used.

But it does help when you change a table, and have to basically drop ALL VIEWS, and then we can re-include all views.

You have talked about/touched on the core problems. It's hard to turn that into a migration script, although we can/do use some of these details.
The hardest thing for Upgrading stuff in PG is simply if it has dependencies... REALLY Curious how you do this?

Also, curious (After we lost ALL COMMENTS in our views), what is the SOURCE OF TRUTH for views in PG? Is it simply the rewritten version in the Database?
I was wondering if people used tools to maintain an external version of a view (that, GASP, might have comments, or be formatted for a different purpose than PG likes)???