# PostgreSQL

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

To learn more about tap-postgres, refer to the repository at https://github.com/transferwise/pipelinewise-tap-postgres and documentation at https://transferwise.github.io/pipelinewise/connectors/taps/postgres.html.

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

# 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

  4. Run a data integration (EL) pipeline

# 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
    pip_url: pipelinewise-tap-postgres
    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