PostgreSQL + Smart Ingest

Smart Ingest lets you pull data stored in your PostgreSQL database (sometimes called Postgres) and push it to Iterable.

# In this article

# Connection requirements

To connect to PostgreSQL, you need the following details:

• A connection type - Smart Ingest connects to PostgreSQL using a direct connection or an SSH tunnel. To learn more about SSH tunneling and how to set up standard and reverse tunnels for Smart Ingest, read SSH Tunneling for Smart Ingest.

• You can optionally provide your own SSL certificate. (This is uncommon.)

• Host - The hostname or IP address of your PostgreSQL server.

• Port - The port number of your PostgreSQL server. The default port number is 5432, but yours may be different.

• Database - The name of the database to use when Smart Ingest executes queries in PostgreSQL.

• User - This can be your personal PostgreSQL login or a dedicated user for Smart Ingest. Permissions for this user depend on the sync engine. To learn more, read the next section on minimum permissions.

• Password - The password for the user specified above.

Before you connect Smart Ingest to PostgreSQL, make sure to allow Smart Ingest IPs to connect to your database.

# Minimum permissions

The sync engine for Smart Ingest can be set to either Lightning or Basic. This determines where the sync schema is created and how the sync is managed and can have significant impact on performance. To learn more about sync engines, read Optimizing Smart Ingest Sync Performance.

Based on the sync engine you select, you need to provide the following permissions to the database user:

• Basic Sync Engine - The database user must have read access to the tables you want to sync. The user must also have the ability to create and drop tables in the schema where the sync tables are created.

• Lightning Sync Engine - The database user must have write access. Smart Ingest provides a pregenerated SQL snippet that you can provide to your PostgreSQL administrator. This SQL snippet contains the permissions necessary for Smart Ingest to create and manage the sync schema, and are customized based on the other inputs you provide in the source setup form.

  To view the SQL snippet, select the Lightning Sync Engine in the setup wizard. Provide the pregenerated SQL snippet to your PostgreSQL administrator.

# Using a custom SSL/TLS certificate

If you use a custom SSL/TLS certificate with your PostgreSQL server, you can provide the certificate and key to Smart Ingest. This is optional, and most users can connect without providing a custom certificate.

Smart Ingest supports the following formats for certificates:

• PEM - A common format for certificates and keys. You can provide the certificate and key in separate files, or in a single file with the certificate first and the key second.

• CRT - A common format for certificates. You can provide the certificate in a single file.

To use your own SSL/TLS certificate with Smart Ingest, you need to provide the following details:

• CA Certificate - A CA certificate file in PEM or CRT format, max size 10MB. You can disable verification if you are setting up a connection with a self-signed CA certificate.

• SSL Key - The key file in PEM or KEY format, max size 10MB.

• SSL Certificate - A SSL certificate file in PEM or CRT format, max size 10MB.

• Passphrase - The password for the key file, if it is encrypted.

# Connecting PostgreSQL to Smart Ingest

To connect Smart Ingest with your PostgreSQL database:

1. Log in to Iterable as a user with the Manage Integrations project permission and open the project you’re working on.

2. Go to Integrations > Smart Ingest.

3. Click Connect a New Source.

4. Select Postgres and click Continue.

5. In Step 1 select the connection type your sync should use to connect:

   • Connect directly to PostgreSQL. This creates a direct connection with no additional setup needed.

   • Connect via SSH Tunnel. This connects using SSH. Add a standard or reverse tunnel, or select an existing tunnel if you’ve already set one up.

     To learn more about SSH tunneling and how to set up standard and reverse tunnels for Smart Ingest, read SSH Tunneling for Smart Ingest.

6. In Step 2, enter your PostgreSQL database details: Host, Port, and Database.

7. If you have a custom SSL/TLS certificate, turn on the setting Use custom SSL/TLS Certificate and provide the certificate and key details in Step 2.

8. In Step 3, choose from the Lightning or Basic sync engine.

9. In Step 4, enter your user credentials for PostgreSQL: User and Password

   This user must have the necessary permissions to use the sync engine you selected in Step 3.

10. Click Continue. Smart Ingest automatically tests the connection.

11. When the connection test is successful, click Continue. (If there are problems connecting, click Back and review the connection details for accuracy.)

12. Add a name for your data source to display in Iterable.

13. Click Finish.

# Next steps

You've now connected your data warehouse to Smart Ingest. The next thing you can do is create a sync.

# Troubleshooting

# Canceling statement due to statement timeout

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.

# Terminating connection due to conflict with recovery

This error can happen if the max_standby_archive_delay and max_standby_streaming_delay values in your PostgreSQL configuration are too low. Try increasing them (as suggested in the AWS Knowledge Center).