The target-postgres Singer target sends data into PostgreSQL after it was pulled from a source using a Singer tap.

Alternative variants #

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

Alternative variants are:

Standalone usage #

Install the package using pip: 

pip install singer-target-postgres

For additional instructions, refer to the README in the repository.

Prerequisites #

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.

Usage with Meltano #

Meltano helps you manage your configuration, incremental replication, and scheduled pipelines.

View the Meltano-specific target-postgres instructions to learn more.

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) #

  • Default: localhost

Port (postgres_port) #

  • Default: 5432

Database (postgres_database) #

Username (postgres_username) #

Password (postgres_password) #

Schema (postgres_schema) #

  • Default: $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.

SSL Mode (postgres_sslmode) #

  • Default: prefer

Refer to the libpq docs for more information about SSL.

SSL Cert (postgres_sslcert) #

  • Default: ~/.postgresql/postgresql.crt

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

SSL Key (postgres_sslkey) #

  • Default: ~/.postgresql/postgresql.key

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

postgres_sslrootcert #

  • Default: ~/.postgresql/root.crt

Used for authentication of a server SSL certificate

SSL CRL (postgres_sslcrl) #

  • Default: ~/.postgresql/root.crl

Used for authentication of a server SSL certificate

Invalid Records Detection (invalid_records_detect) #

  • Default: true

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

Invalid Records Threshold (invalid_records_threshold) #

  • Default: 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.

Disable Collection (disable_collection) #

  • Default: false

Include true in your config to disable Singer Usage Logging.

Logging Level (logging_level) #

  • Default: INFO

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

Persist Empty Tables (persist_empty_tables) #

  • Default: false

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

Max Batch Rows (max_batch_rows) #

  • Default: 200000

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

Max Buffer Size (max_buffer_size) #

  • Default: 104857600

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

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.

State Support (state_support) #

  • Default: 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.

Add Upsert Indexes (add_upsert_indexes) #

  • Default: 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.

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.

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.

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 target 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!