PostgreSQL / Redshift

PostgreSQL/Redshift integration lets you import customer data, events, catalogs, and vouchers directly from your PostgreSQL or Amazon Redshift database into Bloomreach. You can schedule regular imports for ongoing data synchronization or run one-time imports for historical data migration.

Benefits

• Automation: Schedule regular imports to eliminate manual file exports and uploads.
• Accuracy: Direct database connections minimize manual errors and keep data synchronized.
• Flexibility: Use standard SQL syntax to import precisely the data you need.
• Security: Store credentials securely and control access at the database level.

PostgreSQL / Redshift integration use cases

• Import customer profiles for personalization and segmentation.
• Sync product catalogs for accurate recommendations.
• Pull transaction events to build customer journeys.
• Import voucher codes for targeted campaigns.

How the integration works

1. Set up database connection: Configure connection details in Bloomreach.
2. Choose data selection method: Write custom SQL queries or select entire tables.
3. Map columns: Map database columns to Bloomreach attributes.
4. Schedule imports: Run one-time imports or set recurring schedules.

Set up integration

1. Go to Data & Assets > Integrations and click + Add new integration in the top right corner.
2. In the Available integrations dialog, enter PostgreSQL / Redshift in the search box, and select + Add integration.

3. Enter connection details (hostname, port, database name, credentials).

4. Optional security configuration:
   • Static IPs: Enable static IP usage, then configure your PostgreSQL server firewall to allow connections from Bloomreach's IP addresses.
   • SSL encryption: Enable to encrypt data during transfer between your database and Bloomreach.
   • SSH tunnel: Enable for an additional security layer when connecting through intermediate servers.

     ❗

     Warning

     You can't enable SSL encryption and an SSH tunnel at the same time. If you save an integration with both enabled, the save fails. If your database sits behind an SSH bastion and requires SSL, Bloomreach can't currently connect to it—there's no supported way to enable both. Contact your Technical Consultant to explore adjusting your network setup, for example, allow-listing Bloomreach's static IPs so you can connect over SSL directly.

     • If your database sits behind an SSH bastion but doesn't require SSL, the connection between Bloomreach and the bastion is encrypted, but the hop from the bastion to your database isn't. Secure that hop within your own infrastructure—for example, with a VPN or TLS termination at the bastion.
   • MD5 password authentication: If you encounter authentication issues, create a dedicated database user with MD5 password storage for reliable connections:
   • SQL

      -- Connect as an admin
       SET password_encryption = 'md5';
       CREATE ROLE br_integration LOGIN;
       ALTER ROLE br_integration PASSWORD 'a-strong-random-password';
       -- Password will be stored as MD5 for this user only

   • Security recommendations for the integration user:
     • Strong passwords: Use long, random passwords and rotate them regularly.
     • Minimal privileges: Grant only read-only access to required schemas and tables.
     • Network restrictions: Limit connections to Bloomreach static IPs or your SSH tunnel.
     • TLS encryption: Keep SSL enabled in the integration settings.
     • Low connection limits: Restrict concurrent connections, for example, with ALTER ROLE br_integration CONNECTION LIMIT 3;
5. Give your integration a name and Save integration for use during data imports.

Health checks and compute cost

Bloomreach runs automatic health checks on this integration to confirm your connection and credentials are still valid. These checks run about every 3 hours, independent of your import activity, and don't send customer, campaign, or other business data.

If you're using Redshift Serverless, health checks can prevent the workgroup from pausing and add to your compute costs even when no import is running. This doesn't apply to standard PostgreSQL or provisioned Redshift clusters, which bill continuously regardless of activity.

Create data import

1. Go to Data & Assets > Imports > + Create new
2. Choose the type of data to import:
   • Customers (columns mapped to attributes)
   • Events (with customer IDs, timestamps, and event attributes)
   • Catalogs (general or product-specific)
   • Vouchers

3. Select Database as your data source and pick your PostgreSQL / Redshift connection from the SQL integration dropdown menu.
4. Choose your data selection method:
   1. Query mode: Write custom SELECT statements to pull specific data.
   2. Table mode: Select entire tables without custom query options.

5. For Query mode: Write your SQL SELECT statement.

For Table mode: Select the table to import from in the Source Table dropdown.

6. Select Test query or Preview data to verify your data source is working.
7. Click Next to proceed to mapping.
8. Map your PostgreSQL/Redshift columns to Bloomreach attributes by dragging source columns to the corresponding target fields, then click Next.
9. Configure your schedule execution and click Finish to start the import.

Learn more information on the Data import page.

Best practices

• Always preview data before importing to ensure the format is correct.
• Map identifiers carefully to avoid duplicates or overwrites.
• Use scheduling for ongoing syncs and manual runs for one-time imports.
• Secure credentials and review permissions for all integrations.

Limitations

• Databases that require SSL and are only reachable through an SSH bastion aren't currently supported. You can't combine SSH tunnel and SSL encryption for the same integration.
• Schema changes in your database may disrupt active imports - manage changes cautiously.
• Follow data structure and mapping best practices to prevent import issues.
• Custom database integrations should align with Bloomreach's data requirements.