How does pgroll interact with PG replication?

[chris-braidwell]

Thank you for a brilliant piece of work.

Is pgroll aware of replication? For example, if DB1 has a PG publication and DB2 is a client for that publication. If I change a column in such a way that pgroll creates a new column that coexists with the original column for a time, then later the original column is dropped and the new column is renamed -- how would I coordinate that change with the replication client?

Similar question when renaming a table, adding a new column to a replicated table, many other scenarios.

Thanks

[tsg]

hi @chris-braidwell . I assume you are referring to logical replication, right?

Currently pgroll doesn't do anything special for logical replication, so doing the schema changes on the replica in sync with the primary is up to you.

I am thinking that the following process should work, though:

• do pgroll start on the replica first. Because pgroll start does the "expand" phase, this should keep replication flowing normally.
• do pgroll start on the primary. Now the two both have the "expanded" set of columns.
• do the application roll-out
• do pgroll complete on the primary first. This will "contract" the set of columns.
• do pgroll complete on the secondary.

It's not ideal because you need to manually orchestrate these steps, but actually we can use the expand/contract pattern of pgroll to our advantage here. Without it, you'd be forced to only do additive changes in the context of logical replication.

Logical replication/CDC is in general an area of interest for us already, because of https://github.com/xataio/pgstream to which we want to add Postgres->Postgres replication support. When we do that, an integration between pgroll and pgstream would be interesting, perhaps we find a more automatic solution to the above.

Let me know what you think, or if I misunderstood anything.

[gulcin]

Hello @chris-braidwell,

To summarize and add into what @tsg shared above:

Currently, pgroll is not replication-aware, it does not automatically coordinate or detect PostgreSQL logical replication (publications/subscriptions) when performing schema migrations. This means that when you use pgroll to make changes (such as adding, renaming, or dropping columns/tables), you must manually consider the impact on any logical replication clients.

pgroll often performs migrations in steps (e.g., creating a new column, migrating data, dropping or renaming the original column). Since logical replication only replicates data changes, not DDL/schema changes, you are responsible for ensuring that the subscriber schema matches what the publisher produces. If a write on the publisher sends data for a column that doesn't exist on the subscriber, replication will fail until you update the subscriber schema.

You must manually apply schema changes to both the publisher and subscriber databases and monitor for replication lag or failures after migrations. If you have a specific migration scenario or want advice on a workflow, feel free to describe it here and we can provide more detailed guidance!