PostgreSQL is a powerful, open source object-relational database system that uses and extends the SQL language combined with many features that safely store and scale the most complicated data workloads. The origins of PostgreSQL date back to 1986 as part of the POSTGRES project at the University of California at Berkeley and has more than 30 years of active development on the core platform.

PostgreSQL has earned a strong reputation for its proven architecture, reliability, data integrity, robust feature set, extensibility, and the dedication of the open source community behind the software to consistently deliver performant and innovative solutions. PostgreSQL runs on all major operating systems, has been ACID-compliant since 2001, and has powerful add-ons such as the popular PostGIS geospatial database extender. It is no surprise that PostgreSQL has become the open source relational database of choice for many people and organizations.

Getting Started

Connections come in two flavors: source and sink. Source connections read from an external system and write to a Decodable stream, while sink connections read from a stream and write to an external system. Postgres connectors can only be used in the sink role.

Configure As A Sink

To create and configure a connector for Postgres, sign in to the Decodable Web Console, navigate to the Connections tab, click on New Connection, and follow the steps below. For examples using the command line tools or scripting, see the How To guides.

The connector type will default to sink, since that is the only option for Postgres connectors.
Specify the hostname or IP address of the database.
Optionally specify the port where your database accepts connections (default 5432).
Specify the name of the Postgres database.
Optionally provide the schema containing your database table (default public).
Provide the database username on whose behalf the connection is being made.
Provide the database user's password.
Specify the name of the database table to write to.
Optionally provide additional JDBC options to use for the connection.

For more detailed information about configuring Postgres, see the Postgres Getting Started guide and related documentation.

Reference


Connector name	`postgres`
Type	`sink`
Delivery guarantee	exactly once

The Postgres sink connects to a Postgres table through Postgres JDBC connections. Postgres sinks can materialize data from streams of both APPEND and CHANGE types.

When a Postgres sink is connected to a CHANGE stream, a primary key must be specified in the schema. A record with a key that already existed in the table will be updated with the new value.

Properties

The following properties are supported by the Postgres connector.

Property	Disposition	Description
`hostname`	required	IP address or hostname of the PostgreSQL database server.
`port`	optional	Integer port number of the PostgreSQL database server.
`database-name`	required	Database name of the PostgreSQL server to connect.
`schema-name`	optional	Schema name of the PostgreSQL database. Default `public`.
`table-name`	required	The name of the table the connection is writing to.
`username`	required	The username to connect to the database.
`password`	required	The password to authenticate the user to connect to the database.
`properties.jdbc.options`	optional	Additional jdbc options to use with the connection.

JDBC Connectivity
Currently the Postgres connector requires your JDBC connection to be public accessible and uses username/password to authenticate. If you'd like to have support for other authentication models, please contact [email protected] or join our Slack community and let us know!