3 ms·

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

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 https://www.postgresql.org/docs/current/ddl-schemas.html [1] https://github.com/xataio/pgroll/blob/main/pkg/roll/execute.go#L64 https://github.com/xataio/pgroll/blob/main/pkg/roll/execute.... [3] https://stackoverflow.com/a/67525360 https://stackoverflow.com/a/67525360

surjection 3y ago
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-SCHEMAS-PATH https://www.postgresql.org/docs/current/ddl-schemas.html#DDL... [1] - https://github.com/xataio/pgroll/blob/main/docs/README.md#client-applications https://github.com/xataio/pgroll/blob/main/docs/README.md#cl...
- dkubb 3y ago
  I mentally change schema to namespace when thinking about the postgresql feature.
  - guffins 3y ago
    You’re not alone. That’s also how PostgreSQL itself thinks about schemas! https://www.postgresql.org/docs/current/catalog-pg-namespace.html https://www.postgresql.org/docs/current/catalog-pg-namespace...
fabianlindfors 3y ago
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/ https://fabianlindfors.se/blog/schema-migrations-in-postgres...

surjection 3y ago

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-SCHEMAS-PATH https://www.postgresql.org/docs/current/ddl-schemas.html#DDL... [1] - https://github.com/xataio/pgroll/blob/main/docs/README.md#client-applications https://github.com/xataio/pgroll/blob/main/docs/README.md#cl...

dkubb 3y ago
I mentally change schema to namespace when thinking about the postgresql feature.
- guffins 3y ago
  You’re not alone. That’s also how PostgreSQL itself thinks about schemas! https://www.postgresql.org/docs/current/catalog-pg-namespace.html https://www.postgresql.org/docs/current/catalog-pg-namespace...

fabianlindfors 3y ago

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/ https://fabianlindfors.se/blog/schema-migrations-in-postgres...