You may need to allowlist Hightouch's IP addresses to let our systems connect to your Postgres instance. Reference our networking docs to determine which IP addresses you need to allowlist.
Go to the Destinations overview page and click the Add destination button. Select PostgreSQL and click Continue. If you're not using a tunnel, you can then authenticate Hightouch to PostgreSQL by entering the following fields:
Host: The hostname or IP address of your PostgreSQL server. You don't need to include the https://.
Port: The port number of your PostgreSQL server. The default port number is 5432, but yours may be different.
Database: This specifies the database to use when Hightouch executes queries in PostgreSQL. This is different than the host, but your host address might contain your database name.
User: The user that has access through Hightouch to the database and tables you want to sync to. It's best to create a new user specifically for Hightouch access. Do not use the root user.
Password: The password for the user specified above.
To ensure your credentials are correct, click Test connection. This confirms if Hightouch can connect to your database by running a basic SELECT query.
Hightouch can connect directly to PostgreSQL over the public internet or via an SSH tunnel. Since data is encrypted in transit via TLS, a direct connection is suitable for most use cases. You may need to set up a tunnel if your PostgreSQL instance is on a private network or virtual private cloud (VPC).
Hightouch supports both standard and reverse SSH tunnels. To learn more about SSH tunneling, refer to Hightouch's tunneling documentation.
Once you've set up your PostgreSQL destination and have a model to pull data from, you can set up your sync configuration to begin syncing data. Go to the Syncs overview page and click the Add sync button to begin. Then, select the relevant model and the PostgreSQL destination you want to sync to.
Hightouch supports Upsert mode, with the option to delete removed rows, and Insert mode using the COPY FROM STDINstatement. This statement loads queried rows into your table faster than upsert mode. However, using this statement, PostgreSQL rejects the entire batch if any row contains a primary key or unique value that already exists.
Use insert mode if you are only inserting rows and want to sync your data faster.
Hightouch requires you select a unique identifier in the table you are syncing to. The model column you select must match a UNIQUE, PRIMARY KEY column within PostgreSQL. If a field that satisfies these conditions still doesn't appear in the dropdown menu, ensure that you assigned the required permissions to the Postgres user.
You can see columns that fit this criteria as available options in record matching section.
If there are no fields in the dropdown, you must add a unique type column to your PostgreSQL table. Then, click the refresh icon to access the newly created column.
Hightouch supports arrays of standard JSON objects (JSON or JSONB) but doesn't support arrays of USER-DEFINED type JSON objects.
Because insert mode uses the COPY FROM STDIN statement, it doesn't support the array of JSONB column type (jsonb[]). Instead, use a JSONB column type, which natively supports arrays.
You can tune the number of rows Hightouch upserts or inserts per query based on your needs and database threshold. The default is 1000 rows per batch. You can also optionally enter a custom sleep interval in milliseconds (ms) between batches. This default is 100 ms.
If you want to increase the sync's speed, you can increase the batch size and lower the sleep interval. Keep in mind that PostgreSQL fails the entire batch of rows if it detects any erroneous row. If you suspect that you will have many bad rows, don't use a high batch size. To avoid locks, ensure you account for your database's capacity when increasing the batch size.
This error occurs when the required execution time of your PostgreSQL query exceeds the timeout limit for the database.
To address the error, increase the timeout by executing the following query:
set statement_timeout = '300 s'; -- 300 seconds,5 minutes
Be sure to adjust the time to be as long as it takes for your query to execute.
Hightouch provides complete visibility into the API calls made during each of your sync runs. We recommend reading our article on debugging tips and tricks to learn more.