PostgreSQL on Heroku

Heroku Postgres is a managed SQL database service provided by Heroku. It is a reliable and powerful database service that is easy to set up and use.

Features

Feature nameSupported
Column HashingTrueColumn level
BlockingTrueColumn level
IncrementalTrue
Custom dataTrue
HistoryFalse
ReSyncTrueTable level
TemplatesFalse

πŸ”§ Setup Guide

Read replicas

With high throughput systems, running a high frequency sync with a production database can put unnecessary load onto the running services. One mitigation technique is to create a read replica.

Whitelist Weld's IP pool

Requests from Weld will always come from the following IP pool:

  • 3.64.84.139
  • 3.65.119.169
  • 35.156.133.78

Make sure to whitelist all three of these IP's within your network policies, SSH gateway server or the DB itself. If any updates to the list are ever scheduled to happen, you will be contacted by Weld via email.

Step 1 - Connector Configuration

From your PostgreSQL Database, access and paste the required fields:

  • Name (The name you want to refer to regarding this connection in the Weld app)
  • Host (IP Address of your server)
  • Database (Name of the database you want to connect to)

The host can be found using PSQL commands such as:

SELECT *
FROM pg_settings
WHERE name = 'port';

DB inside private network or dynamic host/IP

Our PostgreSQL connector operates with industry standard access architecture in mind. Weld > SSH > DB

The preferred way of connecting to a PostgreSQL database source is for the user to provide a public hostname or a static IP towards an SSH server Weld can connect to.

The SSH server would be a part of the user's internal network, and/or be able to resolve the database DNS alias or IP internally. This approach is not invasive and leaves the user with full control over their own infrastructure.

Step 2 - Data To Sync

Tables can always be updated in the configuration settings of the sync.

  1. Select the tables you wish to include in the sync.
    You can view the schema, remove columns or hash sensitive information.
  2. Update Sync mode, which can be set to:
    • Incremental Sync
    • Full Sync at Midnight

Incremental Sync

Once the initial sync is complete, Weld performs incremental updates of any new or modified data from your source database.

The hidden PostgreSQL system column xmin can be used to select only the new or changed rows since the last update.

The XMIN method has the following limitations:

  • XMIN can't track row deletions because it relies on a updated_at column. There is no way to tell which rows in the destination are no longer present in the source database. It Requires a full table scan to detect and remove deleted rows.
  • The xmin column is updated on a row basis. Therefore, regardless of whether you selected that column in the connector schema, if any value of a column in a row changes, the xmin value also changes which would cause the connector to sync that row.

Full Sync at Midnight

A limitation of incremental syncs are that they do not capture deleted rows. If you expect rows to be deleted often from your tables then you can set a table to run a full sync at midnight which will remove those deleted rows.

Step 3 - Configure Sync

  1. Select how often you would like the data to sync.
  2. Provide a unique destination table name.

Weld will take over from here and commence syncing data from your PostgreSQL database.

Was this page helpful?