The target-postgres loader loads extracted data into a PostgreSQL database.

Alternative variants

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

Alternative options are transferwise (default) and datamill-co.

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 meltano variant of the target-postgres loader to your project using meltano add:

     meltano add loader target-postgres --variant meltano
    
  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 arrow next to the “Add to project” button for “PostgreSQL”.
  4. Choose “Add variant ‘meltano’”.
  5. Configure the settings below in the “Configuration” interface that opens automatically.

Next steps

Follow the remaining step of the Getting Started guide:

  1. Run a data integration (EL) pipeline

If you run into any issues, refer to the “Troubleshooting” section below or learn how to get help.

Settings

target-postgres requires the configuration of the following settings:

A URL setting is also available that can be used as an alternative to setting User, Password, Host, Port, and DBname separately.

These and other supported settings are documented below. To quickly find the setting you’re looking for, use the Table of Contents in the sidebar.

Minimal configuration

A minimal configuration of target-postgres in your meltano.yml project file will look like this:

plugins:
  loaders:
  - name: target-postgres
    variant: meltano
    config:
      user: my_user
      host: postgres.example.com
      port: 5432
      dbname: my_database
      # schema: my_schema   # override if default (see below) is not appropriate

Sensitive values are most appropriately stored in the environment or your project’s .env file:

export TARGET_POSTGRES_PASSWORD=my_password

User

  • Name: user
  • Environment variable: TARGET_POSTGRES_USER, alias: PG_USERNAME, POSTGRES_USER
  • Default: warehouse

How to use

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

meltano config target-postgres set user <user>

export TARGET_POSTGRES_USER=<user>

Password

  • Name: password
  • Environment variable: TARGET_POSTGRES_PASSWORD, alias: PG_PASSWORD, POSTGRES_PASSWORD
  • Default: warehouse

How to use

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

meltano config target-postgres set password <password>

export TARGET_POSTGRES_PASSWORD=<password>

Host

  • Name: host
  • Environment variable: TARGET_POSTGRES_HOST, alias: PG_ADDRESS, POSTGRES_HOST
  • Default: localhost

How to use

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

meltano config target-postgres set host <host>

export TARGET_POSTGRES_HOST=<host>

Port

  • Name: port
  • Environment variable: TARGET_POSTGRES_PORT, alias: PG_PORT, POSTGRES_PORT
  • Default: 5502

How to use

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

meltano config target-postgres set port 5432

export TARGET_POSTGRES_PORT=5432

DBname

  • Name: dbname
  • Environment variable: TARGET_POSTGRES_DBNAME, alias: PG_DATABASE, POSTGRES_DBNAME
  • Default: warehouse

How to use

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

meltano config target-postgres set dbname <database>

export TARGET_POSTGRES_DBNAME=<database>

URL

Lets you set User, Password, Host, Port, and DBname in one go using a postgresql:// URI.

Takes precedence over the other settings when set.

How to use

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

meltano config target-postgres set url postgresql://<username>:<password>@<host>:<port>/<database>

export TARGET_POSTGRES_URL=postgresql://<username>:<password>@<host>:<port>/<database>

Schema

  • Name: schema
  • Environment variable: TARGET_POSTGRES_SCHEMA, alias: PG_SCHEMA, POSTGRES_SCHEMA
  • Default: $MELTANO_EXTRACT__LOAD_SCHEMA, which 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.

How to use

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

meltano config target-postgres set schema <schema>

export TARGET_POSTGRES_SCHEMA=<schema>

Troubleshooting

Error: ld: library not found for -lssl or clang: error: linker command failed with exit code 1 or error: command 'clang' failed with exit status 1

This error message 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 --variant meltano.