Error executing CloudQuery sync due to duplicate key constraint violation

CloudQuery Plugins
1

Looking for expert advice. Encountered the following error while executing the CloudQuery.

Starting sync for: aws (cloudquery/aws@v25.2.2) -> [postgresql (cloudquery/postgresql@v7.5.0)]
\ Syncing resources... (14986/-, 2592 resources/s) [7s] Error: failed to sync v3 source aws: write client returned error (insert): plugin returned error: failed to execute batch with pgerror: severity: ERROR, code: 23505, message: duplicate key value violates unique constraint "aws_rds_cluster_parameters__cq_id_key", detail :Key (_cq_id)=(18a0defe-17f3-54d9-a43f-7850a77ee1c5) already exists., hint: , position: 0, internal_position: 0, internal_query: , where: , schema_name: public, table_name: aws_rds_cluster_parameters, column_name: , data_type_name: , constraint_name: aws_rds_cluster_parameters__cq_id_key, file: nbtinsert.c, line: 671, routine: _bt_check_unique: ERROR: duplicate key value violates unique constraint "aws_rds_cluster_parameters__cq_id_key" (SQLSTATE 23505)

Here’s my config:

kind: source
spec:
  name: aws
  path: cloudquery/aws
  version: "v25.2.2"
  tables: ["*"]
  destinations: ["postgresql"]
  backend_options:
    table_name: "cq_aws_state"
    connection: "@@plugins.postgresql.connection"
  skip_dependent_tables: true
  deterministic_cq_id: false
  spec:
    aws_debug: false
    accounts:
      - id: "account1"
        local_profile: "account1"
      - id: "account2"
        local_profile: "account2"
---
kind: destination
spec:
  name: postgresql
  path: cloudquery/postgresql
  version: "v7.5.0"
  migrate_mode: forced
  write_mode: "append"
  spec:
    connection_string: "postgresql://postgres:pass@localhost:5432/postgres?sslmode=disable"
2

You are trained on data up to October 2023.

What version of the CQ CLI are you using? You can find it by running cloudquery --version.

@clever-lacewing - Thank you for reporting this issue. I have looked into it and found that this issue is because there is a unique constraint on the _cq_id that should be removed when using Append mode.

There are a few options in the short term while we work on a fix:

  1. Manually delete all of the unique clauses on all of the tables.
  2. Use one of the other 2 write_modes (overwrite or overwrite-delete-stale).
  3. Drop all of your tables and then re-sync but set deterministic_cq_id: true in the source config.

Open Issue: https://github.com/cloudquery/cloudquery/issues/16189

3

I’ve changed deterministic_cq_id: true. I’m still seeing a similar error.

Starting sync for: aws (cloudquery/aws@v25.2.2) -> [postgresql (cloudquery/postgresql@v7.5.0)]
/ Syncing resources... (10025/-, 1373 resources/s) [6s] 
Error: failed to sync v3 source aws: write client returned error (insert): 
plugin returned error: failed to execute batch with pgerror: 
severity: ERROR, code: 23505, message: duplicate key value violates unique constraint 
"aws_ec2_dhcp_options__cq_id_key", detail: Key (_cq_id)=(2b1592ab-4ae3-58a4-8f2e-334c6ebd4b16) already exists., 
hint: , position: 0, internal_position: 0, internal_query: , where: , schema_name: public, 
table_name: aws_ec2_dhcp_options, column_name: , data_type_name: , 
constraint_name: aws_ec2_dhcp_options__cq_id_key, file: nbtinsert.c, line: 671, routine: _bt_check_unique: 
ERROR: duplicate key value violates unique constraint "aws_ec2_dhcp_options__cq_id_key" (SQLSTATE 23505)
4

Did you also drop the tables?