Amazon RDS PostgreSQL (Edge)

Prerequisites

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

• The Amazon RDS PostgreSQL server version is 11 or higher, up to 17.

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

• Log-based incremental replication is enabled for your Amazon RDS PostgreSQL database instance.

• A non-administrative database user for Hevo is created in your Amazon RDS 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.

---

Perform the following steps to configure your Amazon RDS PostgreSQL Source:

Create a Read Replica (Optional)

---

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 Amazon RDS PostgreSQL server:

1. Create a parameter group

1. Log in to the Amazon RDS console.

2. In the left navigation pane, click Parameter groups.

   

3. On the Parameter groups page, click Create parameter group.

   

4. On the Create parameter group page, perform the following steps:

   1. Select PostgreSQL from the Engine type drop-down.

      

   2. Select a postgresql version from the Parameter group family drop-down.

      

   3. Specify the Parameter group name and Description, and then click Create.

      

You have successfully created a parameter group.

2. Configure the replication parameters

1. On the Parameter groups page, select the check box corresponding to the parameter group you created in the Create a parameter group section.

   

2. Click the Actions drop-down, and then click Edit.

   

3. On the Modifiable parameters page, do the following:

   

   1. Search for and update the value of the following parameters:

      Parameter	Value	Description
      max_replication_slots	5	The number of clients that can connect to the server. Default value: 20. RDS recommends setting this parameter value to more than or equal to the number of planned publications and subscriptions so that internal replication by RDS is not affected.
      rds.logical_replication	1	The setting to enable or turn off logical replication. The default value for this parameter is 0, which means logical replication is turned off. To enable logical replication, a value of 1 is required.
      max_wal_senders	5	The maximum number of processes that can simultaneously transmit the WAL. Default value: 10. RDS recommends setting this value to at least 5 so that its internal replication is not affected.
      wal_sender_timeout	0	The time after which PostgreSQL terminates the replication connections due to inactivity. A time value specified without units is assumed to be in milliseconds. Default value: 60 seconds. You must set the value to 0 so that the connections are never terminated, and your Pipeline does not fail.
      hot_standby_feedback	1	The setting that allows the standby server to send updates to the primary server about the queries currently running on the standby. This prevents the primary server from removing WAL data required by these queries. Set this value to 1 to avoid query interruptions caused by replication conflicts. However, enabling it causes the primary server to retain older WAL data longer, which can increase storage usage.
      max_standby_streaming_delay	15000-25000	The maximum time (in milliseconds) the standby server can lag while applying changes from the primary database. If the delay exceeds this limit, the standby server cancels any conflicting queries to continue data replication. Default value: 14000. Set this parameter to allow a delay of 15–25 seconds so that the queries can complete before being canceled. However, this may increase replication lag and affect the availability of current data on the standby server, particularly when it acts as a backup for the primary database.

      Note: The max_standby_streaming_delay and hot_standby_feedback parameters are required only if you are connecting to Hevo using a read replica.

   2. Click Save Changes.

3. Apply the parameter group to your PostgreSQL database

1. In the left navigation pane of the Amazon RDS console, click Databases.

2. On the Databases page, select the DB identifier of your Amazon RDS PostgreSQL database instance, and then click Modify.

   

3. Scroll down to the Additional configuration section and do the following:

   

   1. In the Database options section, click the DB parameter group drop-down and select the parameter group you created in the Create a parameter group section.

   2. Ensure that the Backup retention period is set to at least 3 days. If not, select 3 from the drop-down. This setting defines the number of days for which automated backups are retained. Default value: 7.

4. Scroll down to the bottom and click Continue.

5. On the Modify DB instance: <your database instance> page, in the Schedule modifications section, select the time window for applying the changes, and then click Modify DB instance.

   

6. Reboot the database instance that you are using to connect to Hevo, to apply the above changes.
   To do this:

   1. In the left navigation pane, click Databases.

   2. In the Databases section on the right, click the DB identifier of your Amazon RDS PostgreSQL database instance.

      

   3. Click the Actions drop-down, and then click Reboot.

      

   4. In the Reboot DB Instance section, click Confirm to reboot your database instance.

      

4. Create a publication for your database tables

In PostgreSQL 10 onwards, the data to be replicated is identified via publications. A publication defines a group that can include all tables in a database, all tables within a specific schema, or individual tables. It tracks and determines the set of changes generated by those tables from the Write-Ahead Logs (WALs).

Perform the following steps to define a publication:

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

1. Connect to your primary Amazon RDS PostgreSQL database instance as a Superuser 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 whose names do not start with a number in a single database.

   • In PostgreSQL 11 and above, up to 17 without the publish_via_partition_root parameter:

     Note: By default, the versions that support this parameter create a publication with publish_via_partition_root set to FALSE.

     • For one or more database tables:

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

     • For all database tables:

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

       CREATE PUBLICATION <publication_name> FOR ALL TABLES;
       

   • In PostgreSQL 13 and above, up to 17 with the publish_via_partition_root parameter:

     • For one or more database tables:

       CREATE PUBLICATION <publication_name> FOR TABLE <table_1>, <table_4>, <table_5> WITH (publish_via_partition_root);
       

     • For all database tables:

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

       CREATE PUBLICATION <publication_name> FOR ALL TABLES WITH (publish_via_partition_root);
       

     Read Handling Source Partitioned Tables for information on how this parameter affects data loading from partitioned 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.

---

5. Create a replication slot

Hevo uses replication slots to track changes from the Write-Ahead Logs (WALs) for incremental ingestion.

Perform the following steps to create a replication slot:

1. Connect to your Amazon RDS PostgreSQL database instance as a user with the REPLICATION privilege using any SQL client tool, such as psql.

   Note: If your database version is 16 and you are connecting through a read replica, connect to the replica instance to create the replication slot.

2. Run the following command to create a replication slot using the pgoutput plugin:

   SELECT * FROM pg_create_logical_replication_slot('hevo_slot', 'pgoutput');
   

   Note: You can replace the sample value, hevo_slot, in the command above with your own replication slot name.

3. Run the following command to view the replication slots created in your database:

   SELECT slot_name, database, plugin FROM pg_replication_slots;
   

   This command lists all the replication slots along with the associated database and plugin.
   Verify that the output displays your replication slot name, the corresponding database, and the plugin as pgoutput.

   Sample Output:

   slot_name	database	plugin
   hevo_slot	mydb	pgoutput

---

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 Amazon RDS PostgreSQL database. To do this:

1. Add inbound rules

1. Log in to the Amazon RDS console.

2. In the left navigation pane, click Databases.

3. In the Databases section on the right, click the DB identifier of your Amazon RDS database instance.

   

4. In the Connectivity & security tab, click the link text under Security, VPC security groups.

   

5. On the Security groups page, select the check box for your Security group ID, and from the Actions drop-down, click Edit inbound rules.

   

6. On the Edit inbound rules page:

   

   1. Click Add rule.

   2. Add Hevo’s IP address for your region to allow connections to your Amazon RDS PostgreSQL database instance.

   3. Click Save rules.

2. Configure Virtual Private Cloud (VPC)

1. Follow steps 1-3 from the section above.

2. In the Connectivity & security tab, click the link text under Networking, VPC.

   

3. On the Your VPCs page, click the VPC ID, and in the Details section, click the link text under Main network ACL.

   

4. On the Network ACLs page, click the Inbound Rules tab and ensure that the IP address you added is set to Allow.

   

---

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 connect to and ingest data from your PostgreSQL database:

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

1. Connect to your Amazon RDS PostgreSQL database instance as a Superuser with 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. Run the following command to grant your database user permission to read from the WALs:

   GRANT rds_replication TO <database_username>;
   

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

---

Retrieve the Database Hostname and Port Number (Optional)

---