Back to destination list
Official
PostgreSQL
This destination plugin lets you sync data from a CloudQuery source to a PostgreSQL compatible database.
Price
Free
Overview #
PostgreSQL Destination Plugin
This destination plugin lets you sync data from a CloudQuery source to a PostgreSQL compatible database.
Supported database versions:
- PostgreSQL >= v10
- CockroachDB >= v20.2
Configuration #
Example #
This example configures a Postgresql destination using an environment variable called
POSTGRESQL_CONNECTION_STRING
: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`
The (top level) spec section is described in the Destination Spec Reference.
The PostgreSQL destination utilizes batching, and supports
batch_size
and batch_size_bytes
.PostgreSQL Spec #
This is the (nested) spec used by the PostgreSQL destination Plugin.
connection_string
(string
) (required)Connection string to connect to the database. This can be a DSN or a URI, as perpgxpool
"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
)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 usingwrite_mode: overwrite-delete-stale
.
Verbose logging for debug #
The PostgreSQL destination can be run in debug mode.
Note: This will use
pgx
built-in logging and might output data/sensitive information to logs so make sure to not use it in production but only for debugging.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"