PostgreSQL

​

Introduction

valmi.io can be used to sync data from a PostgreSQL database to any of the supported destinations. This document describes how to configure a PostgreSQL connection and how to use it to create a sync.

​

PostgreSQL Configuration

​

Required Permissions

valmi.io retrieves data from one or more tables in your database, which may be located in different schema, and transfers it to corresponding objects in supported destinations. To minimize the impact on your database’s performance and other applications’ APIs, valmi.io calculates a “diff” to identify the changes between each update. For this purpose, valmi.io generates and writes to a set of tables in a private bookkeeping schema (usually 2 or 3 tables per configured sync job).

We recommend creating a dedicated user account specifically for valmi.io, with a strong and unique password. This account is used by valmi.io to establish a connection with your PostgreSQL database. To ensure the correct functioning of the valmi.io connection, the valmi account must possess the following permissions:

• The account should have full administrative access to all tables within the bookkeeping schema, including the capability to create and delete tables, as well as read from and write to all tables.

• Read-only access to any tables and views in any schema that you wish valmi.io to publish to your service destinations.

Configuring PostgreSQL permissions for valmi can be intricate, as there are numerous ways to configure access. However, the script provided below has undergone testing with recent PostgreSQL versions and is known to function correctly:

Give the valmi user the ability to sign in with a password

CREATE USER valmi WITH PASSWORD '<strong password>';

Create a private bookkeeping schema where valmi can store sync state

CREATE SCHEMA valmi_scratch_computed_;

Give the valmi user full access to the bookkeeping schema

GRANT ALL ON SCHEMA valmi_scratch_computed_ TO valmi;

Ensure the valmi user has access to any objects that may have already existed in the bookkeeping schema

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA valmi_scratch_computed_ TO valmi;

Let the valmi user see this schema

GRANT USAGE ON SCHEMA "<your schema>" TO valmi;

Let the valmi user read all existing tables in this schema

GRANT SELECT ON ALL TABLES IN SCHEMA "<your schema>" TO valmi;

Let the valmi user read any new tables added to this schema

ALTER DEFAULT PRIVILEGES IN SCHEMA "<your schema>" GRANT SELECT ON TABLES TO valmi;

Let the valmi user execute any existing functions in this schema

GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA "<your schema>" TO valmi;

Let the valmi user execute any new functions added to this schema

ALTER DEFAULT PRIVILEGES IN SCHEMA "<your schema>" GRANT EXECUTE ON FUNCTIONS TO valmi;

​

Creating a PostgreSQL Connection on valmi.io

The picture below shows the configuration page for a Postgres connection. The following fields are required:

• Connection Name - A name for the connection.
• Host - The hostname of the Postgres server.
• Port - The port number of the Postgres server. The default port is 5432.
• Database - The name of the database to connect to.
• Username - The username to use to connect to the database.
• Password - The password to use to connect to the database.

​

Setting up a Sync

1. Selecting a table or a view to Sync

Once the connection is configured, sync creation involves selecting a source, a destination, a mapping and a schedule. Here we describe how to select a source. Selecting a postgres source while creating the sync requires the following steps:

• Select the Postgres connection you created earlier.
• Select a namespace.
• Select a database table or a view.

2. Supported Source Modes

After a source is selected, you will be asked to select a destination connection. Please refer to the specific destination documentation for more details on the destination configuration. Once the destination is selected, you will be asked to select mapping for the sync. The mapping page asks for a source mode that determines how the source connector extracts data from Postgres. There are two modes available:

• Full Table - This mode extracts the entire table from Postgres. This is the default mode.
• Incremental - This mode extracts only the rows that have changed since the last sync. The data to extract will be determined by comparing the current table with a snapshot of the table taken during the last sync.

3. Selecting a Primary Key

You will also be asked to select a primary key. The primary key has multiple uses.

• It can be used to identify an object on the destination. For example, if you are syncing a Postgres table to a Salesforce object, the primary key will be used to identify the Salesforce object.
• It can be used to identify valid and invalid rows. For example, null checks and duplicate checks are performed on the primary key.

The primary key must be a single column. If the table does not have a primary key, you can select a unique column instead.