The target-postgres Meltano loader sends data into PostgreSQL after it was pulled from a source using an extractor.

Alternative variants #

Multiple variants of target-postgres are available. This document describes the datamill-co variant.

Alternative variants are:

Getting Started #

Prerequisites #

If you haven't already, follow the initial steps of the Getting Started guide:

  1. Install Meltano
  2. Create your Meltano project
  3. Add an extractor to pull data from a source

Installation and configuration #

Using the Command Line Interface #

  1. Add the target-postgres loader to your project using meltano add : 

    meltano add loader target-postgres --variant datamill-co
  2. Configure the settings below using meltano config .

Next steps #

Follow the remaining steps of the Getting Started guide:

  1. Run a data integration (EL) pipeline

If you run into any issues, learn how to get help.

Capabilities #

Settings #

target-postgres requires the configuration of the following settings:

These and other supported settings are documented below. To quickly find the setting you're looking for, use the Table of Contents at the top of the page.

Host (postgres_host) #

How to use #

Manage this setting using meltano config or an environment variable: 

meltano config target-postgres set postgres_host <postgres_host>

export TARGET_POSTGRES_POSTGRES_HOST=<postgres_host>

Port (postgres_port) #

How to use #

Manage this setting using meltano config or an environment variable: 

meltano config target-postgres set postgres_port 5432

export TARGET_POSTGRES_POSTGRES_PORT=5432

Database (postgres_database) #

How to use #

Manage this setting using meltano config or an environment variable: 

meltano config target-postgres set postgres_database <postgres_database>

export TARGET_POSTGRES_POSTGRES_DATABASE=<postgres_database>

Username (postgres_username) #

How to use #

Manage this setting using meltano config or an environment variable: 

meltano config target-postgres set postgres_username <postgres_username>

export TARGET_POSTGRES_POSTGRES_USERNAME=<postgres_username>

Password (postgres_password) #

How to use #

Manage this setting using meltano config or an environment variable: 

meltano config target-postgres set postgres_password <postgres_password>

export TARGET_POSTGRES_POSTGRES_PASSWORD=<postgres_password>

Schema (postgres_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.

How to use #

Manage this setting using meltano config or an environment variable: 

meltano config target-postgres set postgres_schema <postgres_schema>

export TARGET_POSTGRES_POSTGRES_SCHEMA=<postgres_schema>

SSL Mode (postgres_sslmode) #

Refer to the libpq docs for more information about SSL.

How to use #

Manage this setting using meltano config or an environment variable: 

meltano config target-postgres set postgres_sslmode <postgres_sslmode>

export TARGET_POSTGRES_POSTGRES_SSLMODE=<postgres_sslmode>

SSL Cert (postgres_sslcert) #

Only used if a SSL request w/ a client certificate is being made

How to use #

Manage this setting using meltano config or an environment variable: 

meltano config target-postgres set postgres_sslcert <postgres_sslcert>

export TARGET_POSTGRES_POSTGRES_SSLCERT=<postgres_sslcert>

SSL Key (postgres_sslkey) #

Only used if a SSL request w/ a client certificate is being made

How to use #

Manage this setting using meltano config or an environment variable: 

meltano config target-postgres set postgres_sslkey <postgres_sslkey>

export TARGET_POSTGRES_POSTGRES_SSLKEY=<postgres_sslkey>

postgres_sslrootcert #

Used for authentication of a server SSL certificate

How to use #

Manage this setting using meltano config or an environment variable: 

meltano config target-postgres set postgres_sslrootcert <postgres_sslrootcert>

export TARGET_POSTGRES_POSTGRES_SSLROOTCERT=<postgres_sslrootcert>

SSL CRL (postgres_sslcrl) #

Used for authentication of a server SSL certificate

How to use #

Manage this setting using meltano config or an environment variable: 

meltano config target-postgres set postgres_sslcrl <postgres_sslcrl>

export TARGET_POSTGRES_POSTGRES_SSLCRL=<postgres_sslcrl>

Invalid Records Detection (invalid_records_detect) #

Include false in your config to disable target-postgres from crashing on invalid records

How to use #

Manage this setting using meltano config or an environment variable: 

meltano config target-postgres set invalid_records_detect false

export TARGET_POSTGRES_INVALID_RECORDS_DETECT=false

Invalid Records Threshold (invalid_records_threshold) #

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.

How to use #

Manage this setting using meltano config or an environment variable: 

meltano config target-postgres set invalid_records_threshold 0

export TARGET_POSTGRES_INVALID_RECORDS_THRESHOLD=0

Disable Collection (disable_collection) #

Include true in your config to disable Singer Usage Logging.

How to use #

Manage this setting using meltano config or an environment variable: 

meltano config target-postgres set disable_collection true

export TARGET_POSTGRES_DISABLE_COLLECTION=true

Logging Level (logging_level) #

  • Environment variable: TARGET_POSTGRES_LOGGING_LEVEL
  • Options: DEBUG INFO WARNING ERROR CRITICAL
  • Default: INFO

The level for logging. Set to DEBUG to get things like queries executed, timing of those queries, etc.

How to use #

Manage this setting using meltano config or an environment variable: 

meltano config target-postgres set logging_level DEBUG

export TARGET_POSTGRES_LOGGING_LEVEL=DEBUG

Persist Empty Tables (persist_empty_tables) #

Whether the Target should create tables which have no records present in Remote.

How to use #

Manage this setting using meltano config or an environment variable: 

meltano config target-postgres set persist_empty_tables true

export TARGET_POSTGRES_PERSIST_EMPTY_TABLES=true

Max Batch Rows (max_batch_rows) #

The maximum number of rows to buffer in memory before writing to the destination table in Postgres

How to use #

Manage this setting using meltano config or an environment variable: 

meltano config target-postgres set max_batch_rows 200000

export TARGET_POSTGRES_MAX_BATCH_ROWS=200000

Max Buffer Size (max_buffer_size) #

The maximum number of bytes to buffer in memory before writing to the destination table in Postgres. Default: 100MB in bytes

How to use #

Manage this setting using meltano config or an environment variable: 

meltano config target-postgres set max_buffer_size 104857600

export TARGET_POSTGRES_MAX_BUFFER_SIZE=104857600

Batch Detection Threshold (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.

How to use #

Manage this setting using meltano config or an environment variable: 

meltano config target-postgres set batch_detection_threshold 1234

export TARGET_POSTGRES_BATCH_DETECTION_THRESHOLD=1234

State Support (state_support) #

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.

How to use #

Manage this setting using meltano config or an environment variable: 

meltano config target-postgres set state_support false

export TARGET_POSTGRES_STATE_SUPPORT=false

Add Upsert Indexes (add_upsert_indexes) #

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.

How to use #

Manage this setting using meltano config or an environment variable: 

meltano config target-postgres set add_upsert_indexes false

export TARGET_POSTGRES_ADD_UPSERT_INDEXES=false

Before Run SQL (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.

How to use #

Manage this setting using meltano config or an environment variable: 

meltano config target-postgres set before_run_sql <before_run_sql>

export TARGET_POSTGRES_BEFORE_RUN_SQL=<before_run_sql>

After Run SQL (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.

How to use #

Manage this setting using meltano config or an environment variable: 

meltano config target-postgres set after_run_sql <after_run_sql>

export TARGET_POSTGRES_AFTER_RUN_SQL=<after_run_sql>

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 -lssl
  • clang: error: linker command failed with exit code 1
  • error: 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.

Looking for help? #

If you're having trouble getting the target-postgres loader to work, look for an existing issue in its repository, file a new issue, or join the Meltano Slack community and ask for help in the #plugins-general channel.

Found an issue on this page? #

This page is generated from a YAML file that you can contribute changes to. Edit it on GitHub!