PostgreSQL

This page walks through how to connect Census with your PostgreSQL database.

🏃‍♀️ Getting Started

This guide will walk you through connecting to PostgreSQL as a destination.

If you are trying to use PostgresSQL as a data source (to query data from Postgres and sync to elsewhere), that process is documented separately here: Postgres Data Source

1. Visit the Destinations page and click + New Destination.

2. Select PostgreSQL from the menu.

3. Enter the requested database credentials:

Credential	Description
Hostname	Host name or IP address of database
Port	Port of database (5432 by default for Postgres)
Database Name	Name of database within Postgres to connect to
Username	Username Census will use to connect
Password	Password Census will use to connect
Number of Client Connections	Value between 1 and 8 (default is 1). This is the maximum number of concurrent connections Census will use to connect to database. The default should be fine in most cases, but increasing this value can increase throughput on very large syncs.
Use SSH Tunnel	Default: Off - Toggle on to indicate that Census should connect via an SSH Tunnel. For more information, see Network Access Controls
SSH Hostname	Hostname of the Census accessible SSH Tunnel bastion.
SSH Port	Port of SSH Tunnel bastion.
SSH Username	Username Census will use to connect to bastion.

🔑 Permissions

To use Postgres as a destination, Census requires permission to write to the desired destination tables, as well as read metadata about the table and database structures.

-- Note that creating a user may be redundant if you're already configured
-- Postgres as a source.

-- Give the census user the ability to sign in with a password
CREATE USER CENSUS WITH PASSWORD '<strong, unique password>';

-- Let the census user see this schema
GRANT USAGE ON SCHEMA "<your schema>" TO CENSUS;

-- Let the census user read all existing tables in this schema
-- Note: this can also be granted to specific tables as well
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA "<your schema>" TO CENSUS;

🗄️ Supported Objects and Behaviors

We support syncing data to Tables in PostgreSQL, but they must have a uniqueness constraint on a column. ​

Object Name	Supported?	Sync Keys	Behavior
Table	✅	Primary Keys or Columns with Uniqueness Constraints	Update or Create, Update Only, Append, Mirror

Contact us if you want Census to support more Sync behaviors for PostgreSQL.

🚦Advanced Network Configuration

Census can successfully connect to PostgreSQL instances that are using advanced networking controls including region constraints, IP address allow lists, or SSH Tunneling. For more information, see our Network Access Controls documentation.

❗️Common Troubleshooting Issues

You may be trying to sync to a table that does not have a uniqueness constraint. If possible, you need to add one to be able to sync to it. The syntax to do so is here.

🚑 Need help connecting to PostgreSQL?

Contact us via [e-mail] or start a conversation with us via the in-app chat.