Changing PK on replicated database

On 10/16/19 1:47 PM, PegoraroF10 wrote:

I have replication using Publication/Subscription and configured with REPLICA
IDENTITY DEFAULT, so it uses PK values to do the replication. Then, I´ve
imported data in a new schema and that has several tables with a record with
its PK = 0. Replication works but my application doesn´t because it needs to
see pk values > 0.

So, I have to change those records with 0 on their pk to any value, what is
the best way to do that ?
If i just change pk valued on master how will the data of that record be
replicated ?
That record will be sent to replica as update but that PK doesn´t exist on
replica server, so ...

I'm not following. You said above the replication worked with the
records where PK = 0, it was your application that could not find them.
If that is true then the records should be on the replica server,
correct? In that case it would just be an update.

Or do I need to update them manually on Master and Replicated servers ?

I didn´t find any info about this on Docs and because that I´m posting about
this.

--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

--
Adrian Klaver
adrian.klaver@aklaver.com

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 ?
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

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.klaver@aklaver.com

Very good, records were changed and everything is ok.
Well, sorry for this stupid question but some time ago we did something
similar to this and our replica stoped, so I was just afraid of that
happenning again.

--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html