2

I'm trying to enable replication with pglogical on a database running Postgres 9.6.22, however I can't add one of my tables even though it has a unique constraint:

db=> SELECT pglogical.replication_set_add_table('default', 'public.tablename');
ERROR:  table tablename cannot be added to replication set default
DETAIL:  table does not have PRIMARY KEY and given replication set is configured to replicate UPDATEs and/or DELETEs
HINT:  Add a PRIMARY KEY to the table

The documentation says I should be able to add it with a not null unique constraint- I have that:

db=> \d+ tablename
                                         Table "public.tablename"
    Column     |           Type           | Collation | Nullable | Default | Storage  | Stats target | Description
---------------+--------------------------+-----------+----------+---------+----------+--------------+-------------
 col1          | character varying        |           | not null |         | extended |              |
 col2          | character varying        |           | not null |         | extended |              |
 col3          | timestamp with time zone |           | not null |         | plain    |              |
 col4          | jsonb                    |           | not null |         | extended |              |
Indexes:
    "idx1" btree (lower(col1::text))
    "unique" UNIQUE CONSTRAINT, btree (col1, col2)
Foreign-key constraints:
    "fkey" FOREIGN KEY (col1) REFERENCES anothertable(col1) ON DELETE CASCADE
Replica Identity: FULL

What am I missing? I can't add a primary key

2
  • May be you could try setting the replica identity to the unique index in question?
    – mustaccio
    Nov 4 '21 at 21:52
  • that did it, thanks!
    – afclear
    Nov 5 '21 at 14:51
1

alter table ... replica identity using index <unique index name> did the trick; replica identity full doesn't work.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.