aiven
Tool attempts to logically replicate system tables provided by extensions
After successfully setting up logical replication, we are encountering the following error over and over again in our target database:
[webapp-db-sbx-1]2020-11-10T23:21:01.098136[postgresql-12][85-1] user=,db=,app=,client= LOG: logical replication table synchronization worker for subscription "aiven_db_migrate_dafhp6nhpahgqn_sub", table "spatial_ref_sys" has started
[webapp-db-sbx-1]2020-11-10T23:21:01.145297[postgresql-12][86-1] user=,db=,app=,client= ERROR: duplicate key value violates unique constraint "spatial_ref_sys_pkey"
[webapp-db-sbx-1]2020-11-10T23:21:01.145522[postgresql-12][86-2] user=,db=,app=,client= DETAIL: Key (srid)=(3819) already exists.
[webapp-db-sbx-1]2020-11-10T23:21:01.145589[postgresql-12][86-3] user=,db=,app=,client= CONTEXT: COPY spatial_ref_sys, line 1
[webapp-db-sbx-1]2020-11-10T23:21:01.148562[postgresql-12][85-1] user=,db=,app=,client= LOG: background worker "logical replication worker" (PID 2368) exited with exit code 1
The table in question here, spatial_ref_sys, is one of the system tables from the postgis extension. The table is populated when the extension is created, so it makes sense that trying to logically replicate the contents of this table is failing because the data is already there.
Since it is not currently possible to specify include/exclude lists of tables, there is no way to avoid this from happening when postgis is one of the extensions that is replicated. I imagine the same issue exists for other extensions that have their own built-in/system tables as well.
Unfortunately, it's not possible to manually fix this after the fact, as the tool makes use of FOR ALL TABLES in the initial publication. In order to address this, I believe the tool will need to generate the list of all tables for publication individually
dafhp6nhpahgqn=# ALTER PUBLICATION aiven_db_migrate_dafhp6nhpahgqn_pub DROP TABLE spatial_ref_sys;
ERROR: publication "aiven_db_migrate_dafhp6nhpahgqn_pub" is defined as FOR ALL TABLES
DETAIL: Tables cannot be added to or dropped from FOR ALL TABLES publications.
@nickgsc Support has been added for skipping or specifying a subset of tables and for skipping extension tables. Please let us know if this fixes your issue, and also be aware that the new features require the latest version of https://github.com/aiven/aiven-extras running on the source cluster
Thanks @gabriel-tincu, we appreciate you putting in the work to add this capability to the tool. We hope to test it in the coming days.
Cheers