Re: Changing PK on replicated database

On 10/17/19 3:13 AM, PegoraroF10 wrote:
> Correct, those records are on replica too. I´m just talking about the best
> way to update those keys and all their dependent tables.
> If I change them first on master they will not be replicated because it will
> be an update and their pk will not be found on replica, correct ?

https://www.postgresql.org/docs/11/sql-altertable.html#SQL-CREATETABLE-REPLICA-IDENTITY
"DEFAULT (the default for non-system tables) records the old values of
the columns of the primary key, if any."

AFAIK I know there is no restrictions on what you want to do(changing PK):

https://www.postgresql.org/docs/11/logical-replication-restrictions.html

To test and confirm this set up a test parent/child table combination
and change a PK. As to the FK, pretty sure that is handled by the
changes on the primary child tables being replicated to the standby
child tables.

> If so, do I need to update manually on replica and later on master ?
> And on replica server, all FK will be updated if I change that PK ? On
> replica triggers are not triggered, are foreign key cascade ?
> If I do this way, when I change that PK on master I´ll get a warning on
> replica server because that PK did not exist anymore ?
>
> The only question is, what are correct steps to do when you need to change a
> PK on replicated database, just that.
>
>
>
> --
> Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com