4 ms·

Wish PostgresQL has built-in version control for every DDL changes. For example, i want a history of changes when i run command like CREATE OR REPLACE FUNCTION

Wish PostgresQL has built-in version control for every DDL changes. For example, i want a history of changes when i run command like CREATE OR REPLACE FUNCTION... Else programming inside database is still a pain.

manojlds 5y ago
EdgeDB seems like a good way to approach this https://www.edgedb.com/ https://www.edgedb.com/
grzm 5y ago
One way I've approached this in the past is to dump the database schema after each schema modification and use version control on those schema. There's a python project out there (apologies: I can't recall the name of it right now) now that leverages pg_dump and its options to make this easier. Not perfect, but useable. And better than nothing! Found it: https://github.com/omniti-labs/pg_extractor https://github.com/omniti-labs/pg_extractor
jayd16 5y ago
You can use DB versioning tools like FlywayDB or Liquibase. Makes it consistent at least.
vincnetas 5y ago
You dont write code directly in db as you dont write code directly in python web app. Even though you can edit production live files, you dont do this. Instead you do changes in version control and deploy. Same goes for DB. Change fluway scripts, version control them and do deployment.
- peterth3 5y ago
  Come here to say this, here’s a link to flyway if anyone is curious - https://github.com/flyway/flyway https://github.com/flyway/flyway. Many ORMs come with migration systems. For example, SQLAlchemy’s migration tool is Alembic - https://github.com/sqlalchemy/alembic https://github.com/sqlalchemy/alembic.
- laurent92 5y ago
  But it would be much better if Postgres (or any SQL DBMS) were a .git recipient where you push to deploy the triggers and stored procedures. There are hundreds of “schema upgraders”, all based on their flavor of XML, JSON, text files and their associated name comventions, precisely because the canonical SQL way of doing it is limping.
  - Cthulhu_ 5y ago
    Why add it as a concern to a database engine when it's down to the individual use case? I mean, there's hundreds of database migration tools out there, all you need to do - as a developer, that's your job - is to add some automation to your git repository and database server's master to run a migration. Set up a cron job to pull every X minutes, set up a post-merge hook to run those migrations. Simples. Just use straight SQL files for these migrations.
  - necovek 5y ago
    I'd rather say that there are "hundreds of schema upgraders" because there are hundreds of ORMs instead: it's usually a database migration tool (or two) per ORM. SQL DBMS come with an obvious text-only schema management tool called SQL-in-text-files. If you don't need anything esoteric, number them in order of application (001-base.sql, 002-add-customer-model.sql, 003-trigger-on-name-update.sql...), and you are good to go. Any VCS can deal with those properly. I've used plenty of saner version control systems than Git, and I wouldn't want a database to restrict me to it (even though I am usually "forced" to use Git nowadays).
    - mhitza 5y ago
      > If you don't need anything esoteric, number them in order of application (001-base.sql, 002-add-customer-model.sql, 003-trigger-on-name-update.sql...) I would just suggest people use database migration tools from the start. This "autoincrement" method breaks down easily when there are multiple developers working on the same project.
      - necovek 5y ago
thejosh 5y ago
What is your application, and does it not handle migrations?
guyromm 5y ago
that's actually precisely what i implemented in my postgrest-svelte starter template (https://github.com/guyromm/svelte-postgrest-template https://github.com/guyromm/svelte-postgrest-template). ./pg_schema_dump.sh breaks down the schema into an entity-per-file structure at ./sql/schema, while. ./db_init.sh knows how to create a fresh database schema from this dump. the per-file breakdown allows to nicely version the schema in git.
michelpp 5y ago
I'm not sure why you're getting downvoted, this is a fine question. One approach is to use Point In Time Recovery. When you run your migration, you take note of the LSN (the point in the WAL stream) just before you make your changes. Then you can roll back to the point in time right before you applied the migration. Note that you will need some other mechanism for restoring any data you added after the migration point.
Dystopian 5y ago
While general best practice is that you version your changes in git and deploy migrations with something like FlyWay I've also run into the problem of needing some form of 'audit log' to make sure no DDL changes are being made in prod (and if they are, moving them over into version control). I have a migration which is something like this one: https://www.alibabacloud.com/help/doc-detail/169290.html https://www.alibabacloud.com/help/doc-detail/169290.html which uses a trigger on `ddl_command_end` in PG to copy over the query which made the DDL change from `pg_stat_activity` to a new audit schema to stash. Can definitely help with maintenance and finding out what happened when.
cultofmetatron 5y ago
I do this with migrations. its not perfect but it works well enough.

manojlds 5y ago

EdgeDB seems like a good way to approach this https://www.edgedb.com/ https://www.edgedb.com/

grzm 5y ago

One way I've approached this in the past is to dump the database schema after each schema modification and use version control on those schema. There's a python project out there (apologies: I can't recall the name of it right now) now that leverages pg_dump and its options to make this easier. Not perfect, but useable. And better than nothing! Found it: https://github.com/omniti-labs/pg_extractor https://github.com/omniti-labs/pg_extractor

jayd16 5y ago

You can use DB versioning tools like FlywayDB or Liquibase. Makes it consistent at least.

vincnetas 5y ago

You dont write code directly in db as you dont write code directly in python web app. Even though you can edit production live files, you dont do this. Instead you do changes in version control and deploy. Same goes for DB. Change fluway scripts, version control them and do deployment.

peterth3 5y ago
Come here to say this, here’s a link to flyway if anyone is curious - https://github.com/flyway/flyway https://github.com/flyway/flyway. Many ORMs come with migration systems. For example, SQLAlchemy’s migration tool is Alembic - https://github.com/sqlalchemy/alembic https://github.com/sqlalchemy/alembic.
laurent92 5y ago
But it would be much better if Postgres (or any SQL DBMS) were a .git recipient where you push to deploy the triggers and stored procedures. There are hundreds of “schema upgraders”, all based on their flavor of XML, JSON, text files and their associated name comventions, precisely because the canonical SQL way of doing it is limping.
- Cthulhu_ 5y ago
  Why add it as a concern to a database engine when it's down to the individual use case? I mean, there's hundreds of database migration tools out there, all you need to do - as a developer, that's your job - is to add some automation to your git repository and database server's master to run a migration. Set up a cron job to pull every X minutes, set up a post-merge hook to run those migrations. Simples. Just use straight SQL files for these migrations.
- necovek 5y ago
  I'd rather say that there are "hundreds of schema upgraders" because there are hundreds of ORMs instead: it's usually a database migration tool (or two) per ORM. SQL DBMS come with an obvious text-only schema management tool called SQL-in-text-files. If you don't need anything esoteric, number them in order of application (001-base.sql, 002-add-customer-model.sql, 003-trigger-on-name-update.sql...), and you are good to go. Any VCS can deal with those properly. I've used plenty of saner version control systems than Git, and I wouldn't want a database to restrict me to it (even though I am usually "forced" to use Git nowadays).
  - mhitza 5y ago
    > If you don't need anything esoteric, number them in order of application (001-base.sql, 002-add-customer-model.sql, 003-trigger-on-name-update.sql...) I would just suggest people use database migration tools from the start. This "autoincrement" method breaks down easily when there are multiple developers working on the same project.
    - necovek 5y ago

thejosh 5y ago

What is your application, and does it not handle migrations?

guyromm 5y ago

that's actually precisely what i implemented in my postgrest-svelte starter template (https://github.com/guyromm/svelte-postgrest-template https://github.com/guyromm/svelte-postgrest-template). ./pg_schema_dump.sh breaks down the schema into an entity-per-file structure at ./sql/schema, while. ./db_init.sh knows how to create a fresh database schema from this dump. the per-file breakdown allows to nicely version the schema in git.

michelpp 5y ago

I'm not sure why you're getting downvoted, this is a fine question. One approach is to use Point In Time Recovery. When you run your migration, you take note of the LSN (the point in the WAL stream) just before you make your changes. Then you can roll back to the point in time right before you applied the migration. Note that you will need some other mechanism for restoring any data you added after the migration point.

Dystopian 5y ago

While general best practice is that you version your changes in git and deploy migrations with something like FlyWay I've also run into the problem of needing some form of 'audit log' to make sure no DDL changes are being made in prod (and if they are, moving them over into version control). I have a migration which is something like this one: https://www.alibabacloud.com/help/doc-detail/169290.html https://www.alibabacloud.com/help/doc-detail/169290.html which uses a trigger on `ddl_command_end` in PG to copy over the query which made the DDL change from `pg_stat_activity` to a new audit schema to stash. Can definitely help with maintenance and finding out what happened when.

cultofmetatron 5y ago

I do this with migrations. its not perfect but it works well enough.