The target-postgres loader sends data into PostgreSQL after it was pulled from a source using an extractor
Alternate Implementations
Getting Started
Prerequisites
If you haven't already, follow the initial steps of the Getting Started guide:
Dependencies
target-postgres requires the
libpq library to be available on your system.
If you've installed PostgreSQL, you should already have it, but you can also install it by itself using the
libpq-dev package on Ubuntu/Debian or the
libpq Homebrew formula on macOS.
Installation and configuration
-
Add the target-postgres loader to your
project using
:meltano add -
Configure the target-postgres
settings using
:meltano config
meltano add loader target-postgres --variant datamill-comeltano config target-postgres set --interactiveNext steps
Follow the remaining steps of the Getting Started guide:
If you run into any issues, learn how to get help.
Capabilities
This plugin currently has no capabilities defined. If you know the capabilities required by this plugin, please contribute!Settings
The
target-postgres settings that are known to Meltano are documented below. To quickly
find the setting you're looking for, click on any setting name from the list:
add_upsert_indexesafter_run_sqlbatch_detection_thresholdbefore_run_sqldisable_collectioninvalid_records_detectinvalid_records_thresholdlogging_levelmax_batch_rowsmax_buffer_sizepersist_empty_tablespostgres_databasepostgres_hostpostgres_passwordpostgres_portpostgres_schemapostgres_sslcertpostgres_sslcrlpostgres_sslkeypostgres_sslmodepostgres_sslrootcertpostgres_usernamestate_support
You can also list these settings using
with the meltano configlist
subcommand:
meltano config target-postgres list
You can
override these settings or specify additional ones
in your meltano.yml by adding the settings key.
Please consider adding any settings you have defined locally to this definition on MeltanoHub by making a pull request to the YAML file that defines the settings for this plugin.
Add Upsert Indexes (add_upsert_indexes)
-
Environment variable:
TARGET_POSTGRES_ADD_UPSERT_INDEXES -
Default Value:
true
Whether the Target should create column indexes on the important columns used during data loading.
These indexes will make data loading slightly slower but the deduplication phase much faster. Defaults to on for better baseline performance.
Configure this setting directly using the following Meltano command:
meltano config target-postgres set add_upsert_indexes [value]After Run SQL (after_run_sql)
-
Environment variable:
TARGET_POSTGRES_AFTER_RUN_SQL
Raw SQL statement(s) to execute as soon as the connection to Postgres is opened by the target. Useful for setup like SET ROLE or other connection state that is important.
Configure this setting directly using the following Meltano command:
meltano config target-postgres set after_run_sql [value]Batch Detection Threshold (batch_detection_threshold)
-
Environment variable:
TARGET_POSTGRES_BATCH_DETECTION_THRESHOLD
How often, in rows received, to count the buffered rows and bytes to check if a flush is necessary.
There's a slight performance penalty to checking the buffered records count or bytesize, so this controls how often this is polled in order to mitigate the penalty. This value is usually not necessary to set as the default is dynamically adjusted to check reasonably often.
Configure this setting directly using the following Meltano command:
meltano config target-postgres set batch_detection_threshold [value]Before Run SQL (before_run_sql)
-
Environment variable:
TARGET_POSTGRES_BEFORE_RUN_SQL
Raw SQL statement(s) to execute as soon as the connection to Postgres is opened by the target. Useful for setup like SET ROLE or other connection state that is important.
Configure this setting directly using the following Meltano command:
meltano config target-postgres set before_run_sql [value]Disable Collection (disable_collection)
-
Environment variable:
TARGET_POSTGRES_DISABLE_COLLECTION -
Default Value:
false
Include true in your config to disable Singer Usage Logging.
Configure this setting directly using the following Meltano command:
meltano config target-postgres set disable_collection [value]Invalid Records Detection (invalid_records_detect)
-
Environment variable:
TARGET_POSTGRES_INVALID_RECORDS_DETECT -
Default Value:
true
Include false in your config to disable target-postgres from crashing on invalid records
Configure this setting directly using the following Meltano command:
meltano config target-postgres set invalid_records_detect [value]Invalid Records Threshold (invalid_records_threshold)
-
Environment variable:
TARGET_POSTGRES_INVALID_RECORDS_THRESHOLD -
Default Value:
0
Include a positive value n in your config to allow for target-postgres to encounter at most n invalid records per stream before giving up.
Configure this setting directly using the following Meltano command:
meltano config target-postgres set invalid_records_threshold [value]Logging Level (logging_level)
-
Environment variable:
TARGET_POSTGRES_LOGGING_LEVEL -
Default Value:
INFO
The level for logging. Set to DEBUG to get things like queries executed, timing of those queries, etc.
Configure this setting directly using the following Meltano command:
meltano config target-postgres set logging_level [value]Max Batch Rows (max_batch_rows)
-
Environment variable:
TARGET_POSTGRES_MAX_BATCH_ROWS -
Default Value:
200000
The maximum number of rows to buffer in memory before writing to the destination table in Postgres
Configure this setting directly using the following Meltano command:
meltano config target-postgres set max_batch_rows [value]Max Buffer Size (max_buffer_size)
-
Environment variable:
TARGET_POSTGRES_MAX_BUFFER_SIZE -
Default Value:
104857600
The maximum number of bytes to buffer in memory before writing to the destination table in Postgres. Default: 100MB in bytes
Configure this setting directly using the following Meltano command:
meltano config target-postgres set max_buffer_size [value]Persist Empty Tables (persist_empty_tables)
-
Environment variable:
TARGET_POSTGRES_PERSIST_EMPTY_TABLES -
Default Value:
false
Whether the Target should create tables which have no records present in Remote.
Configure this setting directly using the following Meltano command:
meltano config target-postgres set persist_empty_tables [value]Database (postgres_database)
-
Environment variable:
TARGET_POSTGRES_POSTGRES_DATABASE
Configure this setting directly using the following Meltano command:
meltano config target-postgres set postgres_database [value]Host (postgres_host)
-
Environment variable:
TARGET_POSTGRES_POSTGRES_HOST -
Default Value:
localhost
Configure this setting directly using the following Meltano command:
meltano config target-postgres set postgres_host [value]Password (postgres_password)
-
Environment variable:
TARGET_POSTGRES_POSTGRES_PASSWORD
Configure this setting directly using the following Meltano command:
meltano config target-postgres set postgres_password [value]Port (postgres_port)
-
Environment variable:
TARGET_POSTGRES_POSTGRES_PORT -
Default Value:
5432
Configure this setting directly using the following Meltano command:
meltano config target-postgres set postgres_port [value]Schema (postgres_schema)
-
Environment variable:
TARGET_POSTGRES_POSTGRES_SCHEMA -
Default Value:
$MELTANO_EXTRACT__LOAD_SCHEMA
Note that $MELTANO_EXTRACT__LOAD_SCHEMA will expand to the value of the load_schema extra for the extractor used in the pipeline, which defaults to the extractor's namespace, e.g. tap_gitlab for tap-gitlab.
Name of the schema where the tables will be created. If schema_mapping
is not defined then every stream sent by the tap is loaded into this schema.
Configure this setting directly using the following Meltano command:
meltano config target-postgres set postgres_schema [value]SSL Cert (postgres_sslcert)
-
Environment variable:
TARGET_POSTGRES_POSTGRES_SSLCERT -
Default Value:
~/.postgresql/postgresql.crt
Only used if a SSL request w/ a client certificate is being made
Configure this setting directly using the following Meltano command:
meltano config target-postgres set postgres_sslcert [value]SSL CRL (postgres_sslcrl)
-
Environment variable:
TARGET_POSTGRES_POSTGRES_SSLCRL -
Default Value:
~/.postgresql/root.crl
Used for authentication of a server SSL certificate
Configure this setting directly using the following Meltano command:
meltano config target-postgres set postgres_sslcrl [value]SSL Key (postgres_sslkey)
-
Environment variable:
TARGET_POSTGRES_POSTGRES_SSLKEY -
Default Value:
~/.postgresql/postgresql.key
Only used if a SSL request w/ a client certificate is being made
Configure this setting directly using the following Meltano command:
meltano config target-postgres set postgres_sslkey [value]SSL Mode (postgres_sslmode)
-
Environment variable:
TARGET_POSTGRES_POSTGRES_SSLMODE -
Default Value:
prefer
Refer to the libpq docs for more information about SSL.
Configure this setting directly using the following Meltano command:
meltano config target-postgres set postgres_sslmode [value] (postgres_sslrootcert)
-
Environment variable:
TARGET_POSTGRES_POSTGRES_SSLROOTCERT -
Default Value:
~/.postgresql/root.crt
Used for authentication of a server SSL certificate
Configure this setting directly using the following Meltano command:
meltano config target-postgres set postgres_sslrootcert [value]Username (postgres_username)
-
Environment variable:
TARGET_POSTGRES_POSTGRES_USERNAME
Configure this setting directly using the following Meltano command:
meltano config target-postgres set postgres_username [value]State Support (state_support)
-
Environment variable:
TARGET_POSTGRES_STATE_SUPPORT -
Default Value:
true
Whether the Target should emit STATE messages to stdout for further
consumption.
In this mode, which is on by default, STATE messages are buffered in memory until all the records that occurred before them are flushed according to the batch flushing schedule the target is configured with.
Configure this setting directly using the following Meltano command:
meltano config target-postgres set state_support [value]Troubleshooting
Error: psycopg2.ProgrammingError: syntax error at or near "-"
This error message indicates that the extractor you are using this loader with generates
stream names that include the source database schema in addition to the table name: <schema>-<table>, e.g. public-accounts.
This is not supported by this variant of target-postgres.
Instead, use the transferwise variant which was made to be used with extractors that behave this way.
Error: pg_config executable not found or libpq-fe.h: No such file or directory
This error message indicates that the libpq dependency is missing.
To resolve this, refer to the "Dependencies" section above.
ld, clang, lssl, or linker Errors
If you have an error message like:
ld: library not found for -lsslclang: error: linker command failed with exit code 1error: command 'clang' failed with exit status 1
These error messages indicates that there is a problem installing OpenSSL.
This Stack Overflow answer
has specific recommendations on setting the LDFLAGS and/or CPPFLAGS environment variables.
Set those prior to running meltano add loader target-postgres.
Something missing?
This page is generated from a YAML file that you can contribute changes to.
Edit it on GitHub!Looking for help?
#plugins-general-brightgreen){kind=icon}
-78-c0c0c4){kind=icon}
-7-c0c0c4){kind=icon}
