Prerequisites

• The IP address or hostname and port number of your Azure PostgreSQL database instance are available.

• The Azure PostgreSQL server version is 10.0 or higher.

• Hevo’s IP address(es) for your region is added to the Azure PostgreSQL database IP Allowlist.

• Log-based incremental replication is enabled on the primary instance of your Azure PostgreSQL database server.

  Note: PostgreSQL versions 15.8 and lower do not support logical replication on read replicas. This feature is available in versions 16 and higher.

• A non-administrative database user for Hevo is created in your Azure PostgreSQL database instance. You must have the Superuser or CREATE ROLE privileges to add a new user.

• The SELECT, USAGE, and CONNECT privileges are granted to the database user.

---

Set up Logical Replication for Incremental Data

Hevo supports data replication from PostgreSQL servers using the pgoutput plugin (available on PostgreSQL version 10.0 and above). For this, Hevo identifies the incremental data from publications, which are defined to track changes generated by all or some database tables. A publication identifies the changes generated by the tables from the Write Ahead Logs (WALs) set at the logical level.

Perform the following steps to enable logical replication on your Azure PostgreSQL instance:

1. Configure the replication parameters

1. Log in to your Azure Portal.

2. Under Resources, from the Recent tab, select the database for which you want to change the replication mode. For example, hevo in the image below.

   

3. In the left navigation pane of your <Database Name> page, under Settings, click Server parameters.

   

4. In the Search bar of the Server parameters pane, type the name of the required parameter. For example, wal_level in the image below.

   

5. Search and update the values of the following parameters:

   Parameter	Value	Description
   wal_level	LOGICAL	The level at which information is written to the WAL. Default value: REPLICA. The value LOGICAL is required to enable log-based replication.
   max_worker_processes	16	The maximum number of background processes that the PostgreSQL server can use exclusively. Default value: 8. The logical replication workers spawned to receive changes from the WAL are taken from the pool of background workers. Hence, if this number is too small, you may encounter issues during logical replication.

6. Click Save.

   

7. In the confirmation dialog, click Save and Restart.

   

8. In the Notifications pane, confirm that the server has restarted successfully.

   

2. Create a publication for your database tables

In PostgreSQL 10 onwards, the data to be replicated is identified via publications. A publication must be defined on the primary database instance and can include some or all the database tables. The publication is a group of tables that tracks and determines the set of changes generated by those tables from the Write-Ahead Logs (WALs).

To define a publication:

Note: You must define a publication with the insert, update, and delete privileges.

1. Connect to your Azure PostgreSQL primary database instance as an admin user with an SQL client tool, such as psql.

2. Run one of the following commands to create a publication:

   Note: You can create multiple distinct publications in a single database whose names do not start with a number.

   • For one or more database tables:

     CREATE PUBLICATION <publication_name> FOR TABLE <table_1>, <table_4>, <table_5>;
     

   • For all database tables:

     CREATE PUBLICATION <publication_name> FOR ALL TABLES;
     

3. (Optional) Run the following command to add table(s) to or remove them from a publication:

   Note: You can modify a publication only if it is not defined on all tables and you have ownership rights on the table(s) being added or removed.

   ALTER PUBLICATION <publication_name> ADD/DROP TABLE <table_name>;
   

   When you alter a publication, you must refresh the schema for the changes to be visible in your Pipeline.

4. (Optional) Run the following command to create a publication on a column list:

   Note: This feature is available in PostgreSQL versions 15 and higher.

   CREATE PUBLICATION <columns_publication> FOR TABLE <table_name> (<column_name1>, <column_name2>, <column_name3>, <column_name4>,...);
   
   -- Example to create a publication with three columns
   
   CREATE PUBLICATION film_data_filtered FOR TABLE film (film_id, title, description);
   

   Run the following command to alter a publication created on a column list:

   ALTER PUBLICATION <columns_publication> SET TABLE <table_name> (<column_name1>, <column_name2>, ...);
   
   -- Example to drop a column from the publication created above
   
   ALTER PUBLICATION film_data_filtered SET TABLE film (film_id, title);
   

Note: Replace the placeholder values in the commands above with your own. For example, <publication_name> with hevo_publication.

---

Create a Database User and Grant Privileges

1. Create a database user (Optional)

2. Grant privileges to the database user

The following table lists the privileges that the database user for Hevo requires to connect to and ingest data from your PostgreSQL database:

Privilege Name	Allows Hevo to
CONNECT	Connect to the specified database.
USAGE	Access the objects in the specified schema.
SELECT	Select rows from the database tables.
ALTER DEFAULT PRIVILEGES	Access new tables created in the specified schema after Hevo has connected to the PostgreSQL database.
REPLICATION	Access the WALs.

Perform the following steps to grant privileges to the database user connecting to the PostgreSQL database as follows:

1. Connect to your Azure PostgreSQL database as a user with admin privilege using an SQL client tool, such as psql.

2. Run the following commands to grant privileges to your database user:

   GRANT CONNECT ON DATABASE <database_name> TO <database_username>;
   GRANT USAGE ON SCHEMA <schema_name> TO <database_username>;
   GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> to <database_username>;
   

3. (Optional) Alter the schema to grant SELECT privileges on tables created in the future to your database user:

   Note: Grant this privilege only if you want Hevo to replicate data from tables created in the schema after the Pipeline is created.

   ALTER DEFAULT PRIVILEGES IN SCHEMA <schema_name> GRANT SELECT ON TABLES TO <database_username>;
   

4. (Optional) Run the following command only if the user with the admin privilege does not have permission to create logical replication slots.

   ALTER ROLE <admin_role> WITH REPLICATION;
   

5. Run the following command to grant your database user permission to read from the WALs:

   ALTER ROLE <database_username> WITH REPLICATION;
   

Note: Replace the placeholder values in the commands above with your own. For example, <database_username> with hevouser.

---

Allowlist Hevo IP addresses for your region

You must add Hevo’s IP address(es) for your region to the database IP allowlist, enabling Hevo to connect to your Azure PostgreSQL database. You can do this by creating firewall rules in your Microsoft Azure database settings as follows:

1. Log in to your Azure Portal.

2. Under Resources, from the Recent tab, select your database server. For example, hevo in the image below.

   

3. In the left navigation pane of your <Database Name> page, under Settings, click Networking.

   

4. In the Public access section of the Networking pane, ensure that the Allow public access to this… check box is selected.

   

   Note: You must select the check box to allow connections from the IP address(es) added to the firewall rules.

5. Scroll to the Firewall rules section and do the following:

   

   • Select the Allow public access from any Azure service… check box if you want to allow connections from your Azure services and resources to your Azure PostgreSQL database.

     Note: This setting is internal to Azure and does not affect the data replication process in Hevo.

   • Click + Add current client IP address to add your machine’s IP address, which will allow clients, such as psql, running on your machine to connect to the Azure PostgreSQL database.

   • Specify the following to add your firewall rules:

     • Firewall rule name: A name to identify the rule. For example, HevoIndia.

     • Start IP: The starting address of the IP range.

     • End IP: The ending address of the IP range.

     Note: As Hevo has specific IP addresses and not a range, the value in the Start IP and End IP fields is the same. For example, 13.235.131.126 for the India region.

   • Repeat the step above to add all the IP addresses for your Hevo region.

6. Click Save.

---

Turn off Encrypted Connectivity for your Database (Optional)

---

Retrieve the Hostname and Port Number (Optional)