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 .

Using Meltano UI

  1. Start Meltano UI using meltano ui:

    meltano ui
  2. Open the Loaders interface at http://localhost:5000/loaders.
  3. Click the “Add to project” button for “PostgreSQL”.
  4. Choose "Add variant 'datamill-co'".
  5. Configure the settings below in the “Configuration” interface that opens automatically.

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.

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.

postgres_host

  • Environment variable: TARGET_POSTGRES_HOST, alias: TARGET_POSTGRES_POSTGRES_HOST PG_ADDRESS
  • Default: localhost

How to use

Manage this setting using Meltano UI, meltano config, or an environment variable:

meltano config target-postgres set postgres_host <postgres_host>

export TARGET_POSTGRES_HOST=<postgres_host>

postgres_port

  • Environment variable: TARGET_POSTGRES_PORT, alias: TARGET_POSTGRES_POSTGRES_PORT PG_PORT
  • Default: 5432

How to use

Manage this setting using Meltano UI, meltano config, or an environment variable:

meltano config target-postgres set postgres_port 5432

export TARGET_POSTGRES_PORT=5432

postgres_database

  • Environment variable: TARGET_POSTGRES_DATABASE, alias: TARGET_POSTGRES_POSTGRES_DATABASE PG_DATABASE

How to use

Manage this setting using Meltano UI, meltano config, or an environment variable:

meltano config target-postgres set postgres_database <postgres_database>

export TARGET_POSTGRES_DATABASE=<postgres_database>

postgres_username

  • Environment variable: TARGET_POSTGRES_USERNAME, alias: TARGET_POSTGRES_POSTGRES_USERNAME PG_USERNAME

How to use

Manage this setting using Meltano UI, meltano config, or an environment variable:

meltano config target-postgres set postgres_username <postgres_username>

export TARGET_POSTGRES_USERNAME=<postgres_username>

postgres_password

  • Environment variable: TARGET_POSTGRES_PASSWORD, alias: TARGET_POSTGRES_POSTGRES_PASSWORD PG_PASSWORD

How to use

Manage this setting using Meltano UI, meltano config, or an environment variable:

meltano config target-postgres set postgres_password <postgres_password>

export TARGET_POSTGRES_PASSWORD=<postgres_password>

postgres_schema

  • Environment variable: TARGET_POSTGRES_SCHEMA, alias: TARGET_POSTGRES_POSTGRES_SCHEMA PG_SCHEMA
  • Default: $MELTANO_EXTRACT__LOAD_SCHEMA

How to use

Manage this setting using Meltano UI, meltano config, or an environment variable:

meltano config target-postgres set postgres_schema <postgres_schema>

export TARGET_POSTGRES_SCHEMA=<postgres_schema>

postgres_sslmode

  • Environment variable: TARGET_POSTGRES_SSLMODE, alias: TARGET_POSTGRES_POSTGRES_SSLMODE
  • Default: prefer

Refer to the libpq docs for more information about SSL: https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-PARAMKEYWORDS

How to use

Manage this setting using Meltano UI, meltano config, or an environment variable:

meltano config target-postgres set postgres_sslmode <postgres_sslmode>

export TARGET_POSTGRES_SSLMODE=<postgres_sslmode>

postgres_sslcert

  • Environment variable: TARGET_POSTGRES_SSLCERT, alias: TARGET_POSTGRES_POSTGRES_SSLCERT
  • Default: ~/.postgresql/postgresql.crt

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

How to use

Manage this setting using Meltano UI, meltano config, or an environment variable:

meltano config target-postgres set postgres_sslcert <postgres_sslcert>

export TARGET_POSTGRES_SSLCERT=<postgres_sslcert>

postgres_sslkey

  • Environment variable: TARGET_POSTGRES_SSLKEY, alias: TARGET_POSTGRES_POSTGRES_SSLKEY
  • Default: ~/.postgresql/postgresql.key

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

How to use

Manage this setting using Meltano UI, meltano config, or an environment variable:

meltano config target-postgres set postgres_sslkey <postgres_sslkey>

export TARGET_POSTGRES_SSLKEY=<postgres_sslkey>

postgres_sslrootcert

  • Environment variable: TARGET_POSTGRES_SSLROOTCERT, alias: TARGET_POSTGRES_POSTGRES_SSLROOTCERT
  • Default: ~/.postgresql/root.crt

Used for authentication of a server SSL certificate

How to use

Manage this setting using Meltano UI, meltano config, or an environment variable:

meltano config target-postgres set postgres_sslrootcert <postgres_sslrootcert>

export TARGET_POSTGRES_SSLROOTCERT=<postgres_sslrootcert>

postgres_sslcrl

  • Environment variable: TARGET_POSTGRES_SSLCRL, alias: TARGET_POSTGRES_POSTGRES_SSLCRL
  • Default: ~/.postgresql/root.crl

Used for authentication of a server SSL certificate

How to use

Manage this setting using Meltano UI, meltano config, or an environment variable:

meltano config target-postgres set postgres_sslcrl <postgres_sslcrl>

export TARGET_POSTGRES_SSLCRL=<postgres_sslcrl>

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 UI, 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

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 UI, meltano config, or an environment variable:

meltano config target-postgres set invalid_records_threshold 0

export TARGET_POSTGRES_INVALID_RECORDS_THRESHOLD=0

disable_collection

Include true in your config to disable Singer Usage Logging: https://github.com/datamill-co/target-postgres#usage-logging

How to use

Manage this setting using Meltano UI, meltano config, or an environment variable:

meltano config target-postgres set disable_collection true

export TARGET_POSTGRES_DISABLE_COLLECTION=true

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 UI, meltano config, or an environment variable:

meltano config target-postgres set logging_level DEBUG

export TARGET_POSTGRES_LOGGING_LEVEL=DEBUG

persist_empty_tables

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

How to use

Manage this setting using Meltano UI, 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

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 UI, 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

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 UI, 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

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 UI, meltano config, or an environment variable:

meltano config target-postgres set batch_detection_threshold 1234

export TARGET_POSTGRES_BATCH_DETECTION_THRESHOLD=1234

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 UI, meltano config, or an environment variable:

meltano config target-postgres set state_support false

export TARGET_POSTGRES_STATE_SUPPORT=false

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 UI, 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

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 UI, 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

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 UI, 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>

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!