PostgreSQL Destination Plugin

• PostgreSQL >= v10
• CockroachDB >= v20.2

Configuration #

Example #

kind: destination
spec:
  name: "postgresql"
  path: "cloudquery/postgresql"
  registry: "cloudquery"
  version: "v8.6.5"
  write_mode: "overwrite-delete-stale"
  # Learn more about the configuration options at https://cql.ink/postgresql_destination
  spec:
    # set the environment variable in DSN format like "user=postgres password=pass+0-[word host=localhost port=5432 dbname=postgres sslmode=disable"
    # you can also specify it in URI format like "postgres://postgres:pass@localhost:5432/postgres?sslmode=disable". any special URI characters need to be percent-encoded
    connection_string: "${POSTGRESQL_CONNECTION_STRING}"
    # Optional parameters:
    # pgx_log_level: error
    # batch_size: 10000 # 10K entries
    # batch_size_bytes: 100000000 # 100 MB
    # batch_timeout: 60s

    # create_performance_indexes: false #create indexes that help with performance when using `write_mode: overwrite-delete-stale`

PostgreSQL Spec #

• connection_string (string) (required)

  Connection string to connect to the database. This can be a DSN or a URI, as per pgxpool

  • "user=user password=pass+0-[word host=localhost port=5432 dbname=mydb sslmode=disable" DSN format
  • "postgres://user:pass@localhost:5432/mydb?sslmode=prefer" connect with tcp and prefer TLS
  • "postgres://user:pass@localhost:5432/mydb?sslmode=disable&application_name=pgxtest&search_path=myschema&connect_timeout=5" be explicit with all options
  • "postgres://localhost:5432/mydb?sslmode=disable" connect with os username cloudquery is being run as
  • "postgres:///mydb?host=/tmp" connect over unix socket
  • "dbname=mydb" unix domain socket, just specifying the db name - useful if you want to use peer authentication
• pgx_log_level (string) (optional) (default: error)

  Available: error, warn, info, debug, trace. Defines what pgx call events should be logged.
• batch_size (integer) (optional) (default: 10000)

  Maximum number of items that may be grouped together to be written in a single write.
• batch_size_bytes (integer) (optional) (default: 100000000 (= 100 MB))

  Maximum size of items that may be grouped together to be written in a single write.
• batch_timeout (duration) (optional) (default: 60s (= 60 seconds))

  Maximum interval between batch writes.
• create_performance_indexes (boolean) (optional) (default: false)

  Creates indexes on tables that help with performance when using write_mode: overwrite-delete-stale.

Verbose logging for debug #

kind: destination
spec:
  name: postgresql
  path: cloudquery/postgresql
  registry: cloudquery
  version: "v8.6.5"
  spec:
    connection_string: ${PG_CONNECTION_STRING}
    pgx_log_level: debug # Available: error, warn, info, debug, trace. Default: "error"