Pgroll: zero-downtime, undoable, schema migrations for Postgres

• pgroll

  14 2,504 9.4 Go

  PostgreSQL zero-downtime migrations made easy

  

An example would make this more concrete.

This migration[0] adds a CHECK constraint to a column.

When the migration is started, a new column with the constraint is created and values from the old column are backfilled using the 'up' SQL from the migration. The 'up' SQL rewrites values that don't meet the constraint so that they do.

The same 'up' SQL is used 'on the fly' as data is written to the old schema by applications - the 'up' SQL is used (as part of a trigger) to copy data into the new column, rewriting as necessary to ensure the constraint on the new column is met.

As the sibling comment makes clear, it is currently the migration author's responsibility to ensure that the 'up' SQL really does rewrite values so that they meet the constraint.

[0] - https://github.com/xataio/pgroll/blob/main/examples/22_add_c...

• reshape

  16 1,621 7.4 Rust

  An easy-to-use, zero-downtime schema migration tool for Postgres

Cool stuff! Do you have any thoughts about how this compares to https://github.com/fabianlindfors/reshape?

• InfluxDB

  www.influxdata.com sponsored

  Power Real-Time Data Analytics at Scale. Get real-time insights from all types of time series data with InfluxDB. Ingest, query, and analyze billions of data points in real-time with unbounded cardinality.

  

• pg-osc

  6 477 7.9 Ruby

  Easy CLI tool for making zero downtime schema changes and backfills in PostgreSQL

Great to see more innovation in this space! How does this compare to?

https://github.com/shayonj/pg-osc

• quantumdb

  1 60 0.0 Java

  Zero-downtime schema evolution for PostgreSQL

  

Very cool! Congratulations to the authors on the release! I'm the author of a similar (zero-downtime migration) tool for PG called QuantumDB[0]. It was the first (to my knowledge at least) tool to support foreign keys, by creating table copies (keeping those in sync using triggers), and exposing multiple schemas through a custom database driver. I never got to production-ready version unfortunately, but I'm happy this one did. I'm seeing a lot of familiar concepts, and it looks well thought out.

[0] https://github.com/quantumdb/quantumdb

• atlas

  67 4,945 9.8 Go

  Manage your database schema as code (by ariga)

  

Check out: https://github.com/ariga/atlas

(I'm one of the authors of this project).

• migra

  25 2,865 0.0 Python

  Like diff but for PostgreSQL schemas

Forr postgres, how does the schema diffing aspect compare to migra?

https://github.com/djrobstep/migra

I'm asking because, although migra is excellent and there are multiple migrations tools based on it (at least https://github.com/bikeshedder/tusker and https://github.com/blainehansen/postgres_migrator), issues are piling up but development seem to be slowing down

• tusker

  9 197 5.8 Python

  PostgreSQL migration management tool

Forr postgres, how does the schema diffing aspect compare to migra?

https://github.com/djrobstep/migra

I'm asking because, although migra is excellent and there are multiple migrations tools based on it (at least https://github.com/bikeshedder/tusker and https://github.com/blainehansen/postgres_migrator), issues are piling up but development seem to be slowing down

• WorkOS

  workos.com sponsored

  The modern identity platform for B2B SaaS. The APIs are flexible and easy-to-use, supporting authentication, user identity, and complex enterprise features like SSO and SCIM provisioning.

  

• postgres_migrator

  7 77 5.1 Rust

  A postgres migration generator and runner that uses raw declarative sql.

Forr postgres, how does the schema diffing aspect compare to migra?

https://github.com/djrobstep/migra

I'm asking because, although migra is excellent and there are multiple migrations tools based on it (at least https://github.com/bikeshedder/tusker and https://github.com/blainehansen/postgres_migrator), issues are piling up but development seem to be slowing down

Suggest a related project