PostgreSQL

Connect Foundry to PostgreSQL ↗ to read and sync data between PostgreSQL databases and Foundry. This connector uses the official PostgreSQL driver ↗ on major version 42, which is compatible with all versions of PostgreSQL 8.2 and above.

Supported capabilities

Setup

1. Open the Data Connection application and select + New Source in the upper right corner of the screen.
2. Select PostgreSQL from the available connector types.
3. Choose to use a direct connection over the Internet or to connect through an agent runtime.
4. Follow the additional configuration prompts to continue the setup of your connector using the information in the sections below.

Learn more about setting up a connector in Foundry.

Authentication

The Foundry PostgreSQL connector requires the use of a username and password for authentication. We recommend the use of service credentials rather than individual user credentials.

Username and password authentication may be used in conjunction with client and/or server certificates and SSL modes requiring verification of these certificates.

You must ensure that the provided user has the necessary privileges on the target database, as well as permission to read from or write to the target table(s). For change data capture, the user may also require CREATE and REPLICATION permissions on the target database.

Networking

The PostgreSQL connector requires network access to the database instance that you wish to connect to. PostgreSQL connections will normally use a hostname or IP to connect on port 5432.

To enable direct connections out of Foundry to PostgreSQL, the appropriate egress policies must be added when setting up the source in the Data Connection application.

For cloud-hosted PostgreSQL instances accessible over the Internet, such as PostgreSQL in Amazon Relational Database Service (RDS), you must add an egress policy for the hostname of the database or the IP address if you are not using a hostname. Review the official documentation for the provider of your managed, cloud-hosted PostgreSQL instance for more details on the required networking configuration.

You will need to ensure that you have allowed inbound traffic from Foundry to your PostgreSQL instance. You can view the egress IPs where traffic from Foundry will originate in the Network egress page in Control Panel. Review the documentation for your hosting provider to learn how to allow traffic from these IPs to your database instance.

If you are connecting with an agent runtime, you must ensure that the agent host has firewalls open to the hostnames, IP addresses, and ports required to connect to your PostgreSQL database.

PostgreSQL in Amazon RDS

If you are connecting to a managed instance of PostgreSQL hosted in Amazon RDS, you can use a direct connection runtime with the necessary egress policies.

• To find the hostname for your PostgreSQL instance hosted in Amazon RDS, navigate to your instance in the AWS console. An example hostname-based egress policy for RDS PostgreSQL is <your-database-name>.<unique-identifier>.<region>.rds.amazonaws.com (port 5432)

When connecting to an RDS PostgreSQL instance, you may need to add the RDS root Certificate Authority (CA) as a server certificate in the source configuration panel. Download the rds-ca-2019-root.pem from the Amazon S3 site ↗, then copy the certificate details into Foundry to trust connections to Amazon RDS. For more information on connecting to RDS database instances using SSL/TLS, review the official AWS documentation ↗.

Connection details

Change data capture [Beta]

The PostgreSQL source supports change data capture (CDC) syncs.

Since PostgreSQL supports logical replication, change data capture can stream changes to configured tables in near realtime. According to the PostgreSQL documentation ↗:

Logical replication is a method of replicating data objects and their changes, based upon their replication identity (usually a primary key).

Foundry change data capture syncs from PostgreSQL function by using an existing replication slot or creating a new replication slot and publication on the target database. Only one replication slot and publication may be configured per data connection source, however any number of tables may be streamed to Foundry over a single connection. If you wish to use multiple replication slots or publications, you can create multiple data connection sources that connect to your database.

Before setting up a change data capture sync, first ensure that you have a working PostgreSQL source connection. Then, navigate to the CDC syncs tab and provide the additional required configuration for change data capture.

Once the required settings for change data capture are configured, you can navigate to the Overview page or stay on the CDC syncs page and select + Create CDC sync to create a new change data capture sync.

For more information on using CDC with PostgreSQL, review the official documentation ↗ on logical replication for the version of PostgreSQL in use.