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.