Meltano

# PostgreSQL

The tap-postgres extractor pulls data from a PostgreSQL database.

# 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

Then, follow the steps in the "Setup requirements" section of the documentation.

If you'd like to use log-based incremental replication, also follow the "Existing replication slot" step in the repository's README, since Meltano does not create a replication slot automatically.

# Dependencies

tap-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

# Using the Command Line Interface

  1. Add the tap-postgres extractor to your project using meltano add:

    meltano add extractor tap-postgres

  2. Configure the settings below using meltano config.

# Using Meltano UI

  1. Start Meltano UI using meltano ui:

    meltano ui

  2. Open the Extractors interface at http://localhost:5000/extractors.

  3. Click the "Add to project" button for "PostgreSQL".

  4. Configure the settings below in the "Configuration" interface that opens automatically.

# Next steps

Follow the remaining steps of the Getting Started guide:

  1. Select entities and attributes to extract

  2. Choose how to replicate each entity

    Supported replication methods: LOG_BASED, INCREMENTAL, FULL_TABLE

  3. Add a loader to send data to a destination

    Note that this extractor is incompatible with the default datamill-co variants of target-postgres and target-snowflake, because they don't support stream names that include the source schema in addition to the table name: <schema>-<table>, e.g. public-accounts.

    Instead, use the transferwise variants that were made to be used with this extractor: target-postgres and target-snowflake.

  4. 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

tap-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 in the sidebar.

# Minimal configuration

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





 
 
 
 
 

plugins:
  extractors:
  - name: tap-postgres
    variant: transferwise
    config:
      host: postgres.example.com
      port: 5432
      user: my_user
      dbname: my_database

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

export TAP_POSTGRES_PASSWORD=my_password

# Host

# How to use

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

meltano config tap-postgres set host <host>

export TAP_POSTGRES_HOST=<host>

# Port

# How to use

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

meltano config tap-postgres set port 5502

export TAP_POSTGRES_PORT=5502

# User

# How to use

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

meltano config tap-postgres set user <user>

export TAP_POSTGRES_USER=<user>

# Password

# How to use

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

meltano config tap-postgres set password <password>

export TAP_POSTGRES_PASSWORD=<password>

# DBname

# How to use

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

meltano config tap-postgres set dbname <database>

export TAP_POSTGRES_DBNAME=<database>

# SSL

Using SSL via postgres sslmode='require' option.

If the server does not accept SSL connections or the client certificate is not recognized the connection will fail.

# How to use

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

meltano config tap-postgres set ssl true

export TAP_POSTGRES_SSL=true

# Filter Schemas

Scan only the specified comma-separated schemas to improve the performance of data extraction

# How to use

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

meltano config tap-postgres set filter_schemas <schema1>,<schema2>

export TAP_POSTGRES_FILTER_SCHEMAS=<schema1>,<schema2>

# Default Replication Method

Default replication method to use for tables that don't have replication-method stream metadata specified.

# How to use

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

meltano config tap-postgres set default_replication_method <LOG_BASED|INCREMENTAL|FULL_TABLE>

export TAP_POSTGRES_DEFAULT_REPLICATION_METHOD=<LOG_BASED|INCREMENTAL|FULL_TABLE>

# Max Run Seconds

Stop running the tap after certain number of seconds

# How to use

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

meltano config tap-postgres set max_run_seconds 100000

export TAP_POSTGRES_MAX_RUN_SECONDS=100000

# Logical Poll Total Seconds

  • Name: logical_poll_total_seconds
  • Environment variable: TAP_POSTGRES_LOGICAL_POLL_TOTAL_SECONDS
  • Default: 10800

Stop running the tap when no data received from wal after certain number of seconds

# How to use

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

meltano config tap-postgres set logical_poll_total_seconds 100000

export TAP_POSTGRES_LOGICAL_POLL_TOTAL_SECONDS=100000

# Break At End LSN

Stop running the tap if the newly received lsn is after the max lsn that was detected when the tap started

# How to use

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

meltano config tap-postgres set break_at_end_lsn false

export TAP_POSTGRES_BREAK_AT_END_LSN=false

# Troubleshooting

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