pgroll

Just wanted to appreciate the open-source work by Xata. I’ve been eyeing pgroll [1] for schema migrations after Liquibase license shenanigans (the only barrier for me is json-based migration instead of sql-based migrations) [1] https://github.com/xataio/pgroll

What do you think of the approach that pg_roll takes? Seems like the top contenders, at least for Postgres, are: sqldef: https://news.ycombinator.com/item?id=46845239 pgschema: https://github.com/pgschema/pgschema pg_roll: https://github.com/xataio/pgroll atlas: https://github.com/ariga/atlas grate (minimal SQL-based migrations): https://grate-devs.github.io/grate/ pg-schema-diff: https://github.com/stripe/pg-schema-diff results: https://github.com/djrobstep/results https://david.rothlis.net/declarative-schema-migration-for-s...

It's Postgres specific but there is https://github.com/xataio/pgroll which takes the automation a step further.

For a tooling solution for this problem, and many others, pgroll (https://github.com/xataio/pgroll) automates the steps from the blog post in a single higher-level operation. It can do things like adding a hidden column, backfill it with data, then adds the constraint, and only then expose it in the new schema.

We use event triggers both in pgroll [0] and pgstream [1] to capture DDL changes. It's a feature of pgroll that it captures the schema migration history, regardless if you do the changes via pgroll or with another tool. And pgstream uses it to put the schema changes in the logical replication stream. One thing to be aware is that on many Postgres DBaaS EVENT TRIGGERS are not allowed, because they generally require superuser. But RDS and Aurora do support them, we (Xata) support them of course, and I think Supabase is working on adding support for them. [0]: https://github.com/xataio/pgroll [1]: https://github.com/xataio/pgstream

(xata employee) I totally agree with schema migrations being painful, have you seen the open-source tool we developed to tackle this problem? It is called pgroll: https://github.com/xataio/pgroll Any feedback is appreciated!

There is something we built for the schema management aspect of Vitess for PostgreSQL. It is called pgroll, open-source, aiming to minimize potential downtime risks associated with DDL changes, offering multi-schema view, instant rollbacks, and more high-level options like backfilling in batches. You can check the repo here: https://github.com/xataio/pgroll

I see you're using pg-schema-diff for schema diffing, hadn’t come across it before, so thanks for mentioning it! Have you seen pgroll? https://github.com/xataio/pgroll It is a Postgres schema migration tool for achieving zero downtime, minimal locking schema changes. Thought it might be interesting for you. I also checked the unsupported operations in pg-schema-diff, and from a quick look, pgroll seems to cover more migration types: https://pgroll.com/docs/v0.8.0/getting-started

https://github.com/xataio/pgroll/issues/281 may be worth a :+1: even though it seems from tudorg's comment that they're really wedded to .json :-(

Yes, but we're working on a converter from DDL sql to pgroll json. The reason for JSON is because the pgroll migrations are "higher level". For example, let's say that you are adding a new unique column that should infer its data from an existing column (e.g. split `name` into `first_name` and `last_name`). The pgroll migration contains not only the info that new columns are added, but also about how to backfill the data. The sql2pgroll converter is creating the higher level migration files, but leaves placeholder for the "up" / "down" data migrations. The issue where sql2pgroll is tracked is this one: https://github.com/xataio/pgroll/issues/504

My thoughts exactly. It's surprising that external online schema change tools for Postgres have only become a thing fairly recently! The only two I'm aware of are: * pgroll: Written in Golang, first commits June 2023. https://github.com/xataio/pgroll * pg-osc: Written in Ruby, first commits Dec 2021. https://github.com/shayonj/pg-osc Meanwhile over in the MySQL and MariaDB ecosystem, external OSC tools have been around for quite some time, starting with oak-online-alter-table over 15 years ago. The most popular options today are pt-online-schema-change or gh-ost, but other battle-tested solutions include fb-osc, LHM, and the latest entry Spirit.

I thought I recognized xataio - they submitted pgroll a few months back https://news.ycombinator.com/item?id=37752366 (https://github.com/xataio/pgroll - Apache 2)