Error with logical replication
| От | Sabyasachi Mukherjee |
|---|---|
| Тема | Error with logical replication |
| Дата | |
| Msg-id | AS8PR10MB7474847CE39CB706BDD1EA148CE22@AS8PR10MB7474.EURPRD10.PROD.OUTLOOK.COM обсуждение исходный текст |
| Список | pgsql-admin |
Dear All,
I have machine that is hosting publishing DB and the mirror DB.
The data I see is replicated. However I see some errors in the log. Looks like there are some constraint error. I am not sure how to fix those.
They are as follows: I am kind of clueless
(BTW - I am not very experienced in DBA or Postgres)
2024-05-13 15:45:32.604 CEST [1980] STATEMENT: CREATE_REPLICATION_SLOT "pg_23568_sync_22182_7319873232440752272" LOGICAL pgoutput (SNAPSHOT 'use')
2024-05-13 15:45:32.623 CEST [8484] ERROR: duplicate key value violates unique constraint "bgw_job_stat_pkey"
2024-05-13 15:45:32.623 CEST [8484] DETAIL: Key (job_id)=(1) already exists.
2024-05-13 15:45:32.623 CEST [8484] CONTEXT: COPY bgw_job_stat, line 1
2024-05-13 15:45:33.701 CEST [7780] LOG: logical replication table synchronization worker for subscription "central_quality_data_rep_readonly_mirror_subscription", table "metadata" has started
2024-05-13 15:45:33.753 CEST [8804] LOG: logical replication table synchronization worker for subscription "central_quality_data_rep_readonly_mirror_subscription", table "compression_algorithm" has started
2024-05-13 15:45:33.782 CEST [13248] LOG: logical decoding found consistent point at 2B/72C129B8
2024-05-13 15:45:33.782 CEST [13248] DETAIL: There are no running transactions.
2024-05-13 15:45:33.782 CEST [13248] STATEMENT: CREATE_REPLICATION_SLOT "pg_23568_sync_22209_7319873232440752272" LOGICAL pgoutput (SNAPSHOT 'use')
2024-05-13 15:45:33.800 CEST [7780] ERROR: duplicate key value violates unique constraint "metadata_pkey"
2024-05-13 15:45:33.800 CEST [7780] DETAIL: Key (key)=(uuid) already exists.
2024-05-13 15:45:33.800 CEST [7780] CONTEXT: COPY metadata, line 1
2024-05-13 15:45:33.835 CEST [7840] LOG: logical decoding found consistent point at 2B/72C147F8
2024-05-13 15:45:33.835 CEST [7840] DETAIL: There are no running transactions.
2024-05-13 15:45:33.835 CEST [7840] STATEMENT: CREATE_REPLICATION_SLOT "pg_23568_sync_22294_7319873232440752272" LOGICAL pgoutput (SNAPSHOT 'use')
2024-05-13 15:45:33.852 CEST [8804] ERROR: duplicate key value violates unique constraint "compression_algorithm_pkey"
2024-05-13 15:45:33.852 CEST [8804] DETAIL: Key (id)=(0) already exists.
2024-05-13 15:45:33.852 CEST [8804] CONTEXT: COPY compression_algorithm, line 1
2024-05-13 15:45:33.896 CEST [11372] LOG: logical replication table synchronization worker for subscription "central_quality_data_rep_readonly_mirror_subscription", table "cache_inval_hypertable" has started
2024-05-13 15:45:33.987 CEST [7976] LOG: logical decoding found initial starting point at 2B/72C18FC0
2024-05-13 15:45:33.987 CEST [7976] DETAIL: Waiting for transactions (approximately 1) older than 191642079 to end.
2024-05-13 15:45:33.987 CEST [7976] STATEMENT: CREATE_REPLICATION_SLOT "pg_23568_sync_22386_7319873232440752272" LOGICAL pgoutput (SNAPSHOT 'use')
2024-05-13 15:45:33.989 CEST [7976] LOG: logical decoding found initial consistent point at 2B/72C19398
2024-05-13 15:45:33.989 CEST [7976] DETAIL: Waiting for transactions (approximately 1) older than 191642082 to end.
2024-05-13 15:45:33.989 CEST [7976] STATEMENT: CREATE_REPLICATION_SLOT "pg_23568_sync_22386_7319873232440752272" LOGICAL pgoutput (SNAPSHOT 'use')
2024-05-13 15:45:33.998 CEST [7976] LOG: logical decoding found consistent point at 2B/72C194B8
2024-05-13 15:45:33.998 CEST [7976] DETAIL: There are no running transactions.
2024-05-13 15:45:33.998 CEST [7976] STATEMENT: CREATE_REPLICATION_SLOT "pg_23568_sync_22386_7319873232440752272" LOGICAL pgoutput (SNAPSHOT 'use')
2024-05-13 15:45:34.018 CEST [7976] ERROR: syntax error at or near ")" at character 49
2024-05-13 15:45:34.018 CEST [7976] STATEMENT: COPY _timescaledb_cache.cache_inval_hypertable () TO STDOUT
2024-05-13 15:45:34.018 CEST [11372] ERROR: could not start initial contents copy for table "_timescaledb_cache.cache_inval_hypertable": ERROR: syntax error at or near ")"
LINE 1: COPY _timescaledb_cache.cache_inval_hypertable () TO STDOUT
^
2024-05-13 15:45:34.071 CEST [14784] LOG: logical replication table synchronization worker for subscription "central_quality_data_rep_readonly_mirror_subscription", table "bgw_job" has started
2024-05-13 15:45:34.149 CEST [5696] LOG: logical decoding found initial starting point at 2B/72C1E268
2024-05-13 15:45:34.149 CEST [5696] DETAIL: Waiting for transactions (approximately 1) older than 191642157 to end.
2024-05-13 15:45:34.149 CEST [5696] STATEMENT: CREATE_REPLICATION_SLOT "pg_23568_sync_22165_7319873232440752272" LOGICAL pgoutput (SNAPSHOT 'use')
2024-05-13 15:45:34.152 CEST [5696] LOG: logical decoding found consistent point at 2B/72C1E480
2024-05-13 15:45:34.152 CEST [5696] DETAIL: There are no running transactions.
2024-05-13 15:45:34.152 CEST [5696] STATEMENT: CREATE_REPLICATION_SLOT "pg_23568_sync_22165_7319873232440752272" LOGICAL pgoutput (SNAPSHOT 'use')
2024-05-13 15:45:34.170 CEST [14784] ERROR: duplicate key value violates unique constraint "bgw_job_pkey"
2024-05-13 15:45:34.170 CEST [14784] DETAIL: Key (id)=(2) already exists.
2024-05-13 15:45:34.170 CEST [14784] CONTEXT: COPY bgw_job, line 1
2024-05-13 15:45:34.171 CEST [9820] LOG: logical replication table synchronization worker for subscription "central_quality_data_rep_readonly_mirror_subscription", table "cache_inval_extension" has started
2024-05-13 15:45:34.249 CEST [5732] LOG: logical decoding found consistent point at 2B/72C21A08
2024-05-13 15:45:34.249 CEST [5732] DETAIL: There are no running transactions.
2024-05-13 15:45:34.249 CEST [5732] STATEMENT: CREATE_REPLICATION_SLOT "pg_23568_sync_22392_7319873232440752272" LOGICAL pgoutput (SNAPSHOT 'use')
2024-05-13 15:45:34.266 CEST [5732] ERROR: syntax error at or near ")" at character 48
2024-05-13 15:45:34.266 CEST [5732] STATEMENT: COPY _timescaledb_cache.cache_inval_extension () TO STDOUT
2024-05-13 15:45:34.267 CEST [9820] ERROR: could not start initial contents copy for table "_timescaledb_cache.cache_inval_extension": ERROR: syntax error at or near ")"
LINE 1: COPY _timescaledb_cache.cache_inval_extension () TO STDOUT
^
2024-05-13 15:45:37.369 CEST [9956] LOG: logical replication table synchronization worker for subscription "central_quality_data_rep_readonly_mirror_subscription", table "cache_inval_bgw_job" has started
2024-05-13 15:45:37.459 CEST [7500] LOG: logical decoding found consistent point at 2B/72C719E8
2024-05-13 15:45:37.459 CEST [7500] DETAIL: There are no running transactions.
2024-05-13 15:45:37.459 CEST [7500] STATEMENT: CREATE_REPLICATION_SLOT "pg_23568_sync_22389_7319873232440752272" LOGICAL pgoutput (SNAPSHOT 'use')
2024-05-13 15:45:37.475 CEST [7500] ERROR: syntax error at or near ")" at character 46
2024-05-13 15:45:37.475 CEST [7500] STATEMENT: COPY _timescaledb_cache.cache_inval_bgw_job () TO STDOUT
2024-05-13 15:45:37.476 CEST [9956] ERROR: could not start initial contents copy for table "_timescaledb_cache.cache_inval_bgw_job": ERROR: syntax error at or near ")"
LINE 1: COPY _timescaledb_cache.cache_inval_bgw_job () TO STDOUT
^
2024-05-13 15:45:37.508 CEST [1676] LOG: logical replication table synchronization worker for subscription "central_quality_data_rep_readonly_mirror_subscription", table "bgw_job_stat" has started
2024-05-13 15:45:37.596 CEST [13092] LOG: logical decoding found consistent point at 2B/72C76558
2024-05-13 15:45:37.596 CEST [13092] DETAIL: There are no running transactions.
2024-05-13 15:45:37.596 CEST [13092] STATEMENT: CREATE_REPLICATION_SLOT "pg_23568_sync_22182_7319873232440752272" LOGICAL pgoutput (SNAPSHOT 'use')
2024-05-13 15:45:37.615 CEST [1676] ERROR: duplicate key value violates unique constraint "bgw_job_stat_pkey"
2024-05-13 15:45:37.615 CEST [1676] DETAIL: Key (job_id)=(1) already exists.
2024-05-13 15:45:37.615 CEST [1676] CONTEXT: COPY bgw_job_stat, line 1
В списке pgsql-admin по дате отправления: