amidala=# \d+ withuniqueindexĬolumn | Type | Collation | Nullable | Default | Storage | Stats target | Description Index name to be able to use it as a replica identity. HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.Īs often, Postgres is nice enough to help us solve the issue.
#POSTGRESQL APPILY 2 PRIMARY KEYS UPDATE#
But what if I try to update one data? amidala=# update withuniqueindex set c=4 where c =3 ĮRROR: cannot update table "withuniqueindex" because it does not have a replica Let’s do the same thing with the table that has a unique index: amidala=# insert into withuniqueindex default values Īmidala=# insert into withuniqueindex default values Īnd on the worker, we find: padme=# select * from withuniqueindex Įverything’s great, so far. … Everything works fine! Table with unique index (and not null column) If I try to update one, value… amidala=# update withprimarykey set c=4 where c =3 If I connect to my worker, then I should see the data there, and sure enough: padme=# select * from withprimarykey I first tried the table with a primary key: amidala=# insert into test default values Īmidala=# insert into test default values Inserting some data Table with primary key If you need help with the queen/princess/worker denomination, please read Dimitri’sīlog post. On the other side, the worker database isĬalled padme, so that should an SQL command begin with padme=#, t was issued create subscription mysubįor the rest of the article, you need to know that the queen database is calledĪmidala, so that should an SQL command begin with amidala=#, it was On another host, I created the 3 tables and the logical subscription. I then created a publication on those 3 tables: create publication mypub for all tables I granted all permissions on my tables to my replication user. Here’s the code: create table withprimarykey A table called withuniquekey with a unique key but without a primary key.A table called withprimarykey with a primary key.A table called badmodel without any uniqueness or primary key.I created 3 different tables for 3 uses cases: Please read theĭocumentation for more informations. Wal_level, max_wal_senders, max_replication_slots). Your primary instance (version higher or equal to 10, and some parameters like
You must also ensure you’ll be able to perform native logical replication from
#POSTGRESQL APPILY 2 PRIMARY KEYS PASSWORD#
Secondary host to the primary without password (using either a certificate,Ī password file or any other passwordless secured authentication method) with You must have two Postgres instances ready and be able to connect from the If you careįor performance, your data should be modelized (third normal form at least), so Performance reasons, you should avoid using it. It will use all the columns in the table as the replica key.
If you don’t have an index that satisfies all these conditions, you may use theįull keyword. Remember your index needs to be unique, not partial, not deferrable, and should
In case you don’t have a primary key, the using index clause can be used. Postgres what to use as a replica identity, using the alter table mytable replica identity. If there is no primary key, you need to tell Replica identityĪs the documentation states in the case, there is a primary key, it’s taken byĭefault as the replica identity. So, the next logical question here is: How does it work? How does it pickĪ row to update in case there is no primary key? The answer is “Replica Was confused with other logical replication systems like slony. I was even surprised that it’s never been mandatory. I was browsing through the logical replication restrictions when I noticed thatĪ primary key was not mandatory to be able to perform logical
0 kommentar(er)
