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