# PostgreSQL
The tap-postgres
extractor pulls data from a PostgreSQL database.
- Repository: https://github.com/transferwise/pipelinewise-tap-postgres
- Documentation: https://transferwise.github.io/pipelinewise/connectors/taps/postgres.html
- Maintainer: TransferWise
- Maintenance status: Active
# Getting Started
# Prerequisites
If you haven't already, follow the initial steps of the Getting Started guide:
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
Add the
tap-postgres
extractor to your project usingmeltano add
:meltano add extractor tap-postgres
Configure the settings below using
meltano config
.
# Using Meltano UI
Start Meltano UI using
meltano ui
:meltano ui
Open the Extractors interface at http://localhost:5000/extractors.
Click the "Add to project" button for "PostgreSQL".
Configure the settings below in the "Configuration" interface that opens automatically.
# Next steps
Follow the remaining steps of the Getting Started guide:
Choose how to replicate each entity
Supported replication methods:
LOG_BASED
,INCREMENTAL
,FULL_TABLE
Add a loader to send data to a destination
Note that this extractor is incompatible with the default
datamill-co
variants oftarget-postgres
andtarget-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
andtarget-snowflake
.
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
- Name:
host
- Environment variable:
TAP_POSTGRES_HOST
- Default:
localhost
# 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
- Name:
port
- Environment variable:
TAP_POSTGRES_PORT
- Default:
5432
# 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
- Name:
user
- Environment variable:
TAP_POSTGRES_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
- Name:
password
- Environment variable:
TAP_POSTGRES_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
- Name:
dbname
- Environment variable:
TAP_POSTGRES_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
- Name:
ssl
- Environment variable:
TAP_POSTGRES_SSL
- Default:
false
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
- Name:
filter_schemas
- Environment variable:
TAP_POSTGRES_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
- Name:
default_replication_method
- Environment variable:
TAP_POSTGRES_DEFAULT_REPLICATION_METHOD
- Options:
LOG_BASED
,INCREMENTAL
,FULL_TABLE
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
- Name:
max_run_seconds
- Environment variable:
TAP_POSTGRES_MAX_RUN_SECONDS
- Default:
43200
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
- Name:
break_at_end_lsn
- Environment variable:
TAP_POSTGRES_BREAK_AT_END_LSN
- Default:
true
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.