Обсуждение: Changing PK on replicated database
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 ... 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
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