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

Visit the Destinations page and click + New Destination.
Select PostgreSQL from the menu.
Enter the requested database credentials:

Credential

Description

🔑 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;

Custom Field Permissions

To enable this in Postgres, Census needs to have the required permissions to add columns to your Postgres table.

The following instructions give you details on how to enable this with a new role, but you may want to reuse a similar existing role instead.

Run these commands to enable custom fields (in addition to those above).

-- Step 1: Create a role for managing custom field operations, including the ability to alter tables
CREATE ROLE census_table_manager;

-- Step 2: Allow the census_table_manager role to access the schema
GRANT USAGE ON SCHEMA "<your schema>" TO census_table_manager;

-- Step 3: Transfer ownership of the relevant table(s) to the census_table_manager role
-- This allows the role to manage table structures, including adding custom fields.
-- (Repeat this step for each table that will be managed)
ALTER TABLE <your_table> OWNER TO census_table_manager;

-- Step 4: Grant the census_table_manager role to the census user
GRANT census_table_manager TO CENSUS;

-- Step 5: Ensure the census user always uses the census_table_manager role by default
ALTER USER CENSUS SET ROLE census_table_manager;

️ Supported Objects and Sync Behaviors

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

Learn more about all of our sync behaviors in our Syncs documentation.

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?

PreviousPlanhat NextPostHog

Last updated 3 months ago

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

Visit the Destinations page and click + New Destination.

Select PostgreSQL from the menu.

Enter the requested database credentials:

Credential

Description

🔑 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;

Custom Field Permissions

Census allows you to create custom fields in your destination Postgres as a by-product of your sync (see ).

To enable this in Postgres, Census needs to have the required permissions to add columns to your Postgres table.

The following instructions give you details on how to enable this with a new role, but you may want to reuse a similar existing role instead.

Run these commands to enable custom fields (in addition to those above).

-- Step 1: Create a role for managing custom field operations, including the ability to alter tables
CREATE ROLE census_table_manager;

-- Step 2: Allow the census_table_manager role to access the schema
GRANT USAGE ON SCHEMA "<your schema>" TO census_table_manager;

-- Step 3: Transfer ownership of the relevant table(s) to the census_table_manager role
-- This allows the role to manage table structures, including adding custom fields.
-- (Repeat this step for each table that will be managed)
ALTER TABLE <your_table> OWNER TO census_table_manager;

-- Step 4: Grant the census_table_manager role to the census user
GRANT census_table_manager TO CENSUS;

-- Step 5: Ensure the census user always uses the census_table_manager role by default
ALTER USER CENSUS SET ROLE census_table_manager;