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

Re:Pgroll: zero-downtime, undoable, schema migrations for Po... by dboreham on Tuesday October 03, 2023 @12:55PM (#37754488)

For those curious, as I was, how this works, beyond the details in the readme and blog post, note that "schema" in this context is both a loose term (we changed the schema, old schema, new schema) AND a concrete thing in PostgreSQL[0]. It's helpful to know that pgroll implements the first one (let's change the schema) using the second one (make Schema objects in PG [1]). The magic is in creating different views in different Schemas that map appropriately to underlying tables (in a third Schema).

Presumably (didn't see this mentioned in the docs yet) the trick is that you re-deploy the app with a new connection string referencing the new schema (as in [3]), while an old app deployment can keep referencing the old schema with its connection string.

Hopefully I got that right.

[0] https://www.postgresql.org/docs/current/ddl-schemas.html [1] https://github.com/xataio/pgroll/blob/main/pkg/roll/execute.... [3] https://stackoverflow.com/a/67525360

[ Parent | on HN ]

Re:Pgroll: zero-downtime, undoable, schema migrations for Po... by surjection on Tuesday October 03, 2023 @01:19PM (#37754812)

You're right. I wish schema wasn't such an overloaded term :)

In order to access either the old or new version of the schema, applications should configure the Postgres `search_path`[0] which determines which schema and hence which views of the underlying tables they see.

This is touched on in the documentation here[1], but could do with further expansion.

[0] - https://www.postgresql.org/docs/current/ddl-schemas.html#DDL... [1] - https://github.com/xataio/pgroll/blob/main/docs/README.md#cl...

[ Parent | on HN ]

Re:Pgroll: zero-downtime, undoable, schema migrations for Po... by dkubb on Tuesday October 03, 2023 @04:29PM (#37757116)

I mentally change schema to namespace when thinking about the postgresql feature.

[ Parent | on HN ]

Re:Pgroll: zero-downtime, undoable, schema migrations for Po... by guffins on Tuesday October 03, 2023 @04:41PM (#37757256)

You’re not alone. That’s also how PostgreSQL itself thinks about schemas! https://www.postgresql.org/docs/current/catalog-pg-namespace...

[ Parent | on HN ]

Re:Pgroll: zero-downtime, undoable, schema migrations for Po... by fabianlindfors on Tuesday October 03, 2023 @01:34PM (#37755022)

You got it right! I wrote a blog post a few years back about how this technique works for anyone curious: https://fabianlindfors.se/blog/schema-migrations-in-postgres...

[ Parent | on HN ]