Re: Logical Replication of Multiple Schema Versions

Thanks Peter for your answer.

I was hoping to keep the freedom of performing any change to my schema.
Since the entire upgrade might take more than a week, there will be a time in which I have two publisher nodes with different schema versions (which might include a column rename).
I was hoping that were was a way to somehow apply some logic on the subscriber to convert one schema to another. I also believe that limiting schema changes only to those that won't break replication should suffice.

Dan.

________________________________
From: Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com>
Sent: Wednesday, December 11, 2019 6:13 PM
To: Dan shmidt <dshmidt(at)hotmail(dot)com>; pgsql-general(at)lists(dot)postgresql(dot)org <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Re: Logical Replication of Multiple Schema Versions

On 2019-12-10 08:55, Dan shmidt wrote:
> What is the correct way to perform such an operation?
> Is there a way to keep constraint #1 or the only option is to not allow
> "breaking" schema changes between versions.

It all depends on the specific schema changes you want to make. You can
add columns on the subscriber and remove columns on the publisher
without breaking things (unless there are not-null constraints).
Renaming columns will break replication until you rename them
everywhere. Column type changes will usually just work as long as the
data fits into both the old and the new type.

You really need to carefully plan and test each class of scenarios
separately.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services