Generic PostgreSQL (Edge)

Prerequisites

• The IP address or hostname and port number of your PostgreSQL server are available.

• The PostgreSQL server version is 10.0 or higher.

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

• Log-based incremental replication is enabled on the primary instance of your 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 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.

---

Create a Database User and Grant Privileges

1. Create a database user (Optional)

2. Grant privileges to the user

The following table lists the privileges that the database user for Hevo requires to Log in 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.

Grant privileges to the database user connecting to the PostgreSQL database as follows:

1. Connect to your PostgreSQL database instance.

2. (Optional) View existing users and their privileges in your PostgreSQL instance:

   psql -U postgres
   
   \du
   

   

   If the privileges are not assigned, perform the next step. Else, proceed to Step 2.

3. Grant the required privileges to the 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>;
   

4. (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>;
   

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.

---

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 the PostgreSQL server:

1. Update the database configuration file

1. Log in to your PostgreSQL primary database instance host as the root or admin user.

2. Locate your database configuration file, postgresql.conf, and modify it with your favorite text editor. For example, vim.

   Note: The postgresql.conf file is generally found in the /etc/postgresql/<version>/main/ directory on a UNIX-like system.

3. Update the values of the following parameters:

   Parameter	Value	Description
   max_replication_slots	Any whole number between 1 and 10	The number of clients that can connect to the server. Default value: 10.
   max_wal_senders	10	The number of processes that can simultaneously transmit the WAL. Default value: 10. You must set this value to at least twice the number of read replicas connecting to the primary database instance.
   wal_level	logical	The amount of information being written to the WAL log. For example, minimal, replica, or logical. Default value: replica. The value logical is required to enable log-based replication.
   wal_sender_timeout	0	The time, in seconds, after which PostgreSQL terminates the replication connections due to inactivity. Default value: 60 seconds. You must set the value to 0 so that the connections are never terminated and your Pipeline does not fail.

   Note: If any of the parameters do not exist in the configuration file, you should add them, and then, update their values.

2. Update the client authentication file

1. Log in to your PostgreSQL primary database instance host as the root or admin user.

2. Locate your client authentication file, pg_hba.conf, and open it in your favorite text editor.

   Note: The pg_hba.conf file is generally found in the database cluster’s data directory.

3. In the pg_hba.conf file, assign permission to the database user to connect from an external network to the PostgreSQL server:

   local replication <database_username> peer
   
   host replication <database_username> 127.0.0.1/0 md5
   
   host replication <database_username> ::1/0 md5
   

   Note: Replace the placeholder value in the statements above with your intended user. For example, <database_username> with hevouser.

3. Restart the PostgreSQL service

You must restart your PostgreSQL service after editing the configuration files for the changes to take effect. To do this:

1. Log in to your PostgreSQL primary database instance host as the root or admin user.

2. Run the following command in a Terminal window:

   sudo systemctl restart postgresql.service
   

4. 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 PostgreSQL primary database instance as a Superuser using an SQL client tool, such as psql.

2. Run one of the following commands to create a publication based on your requirements:

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

   • On one or more database tables:

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

   • On all database tables:

     Note: You can run this command only as a Superuser.

     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 if 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.

---

Allowlist Hevo IP addresses for your region

You must add Hevo’s IP address for your region to the database IP allowlist, enabling Hevo to connect to your PostgreSQL database. To do this:

1. Log in to your PostgreSQL primary database instance host as the root or admin user.

2. Locate and edit the database configuration file, postgresql.conf. This file is generally found in the path: /etc/postgresql/<version>/main/.

3. Update the listen_addresses parameter to add the Hevo IP address for your region or * to allow all IPs to connect.

4. Locate and edit the client authentication file, pg_hba.conf. This file is generally found in the path: /etc/postgresql/<version>/main/.

5. Scroll to the bottom, where the connection information is listed, and add the Hevo IP address for your region or 0.0.0.0 to allow all IPs to connect.

   # TYPE  DATABASE        USER                    ADDRESS             METHOD
     host  all             <database_username>     <Hevo IP address>   md5
   

   Note: Replace all placeholder values in the statement above with your own. For example, <Hevo IP address> with 13.235.131.126 if your Hevo account is in the India region.

---