Google Cloud PostgreSQL (Edge)

Prerequisites

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

• The Google Cloud PostgreSQL server version is 10.0 or higher.

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

• Log-based incremental replication is enabled on the primary instance of your Google Cloud 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 database user for Hevo is created in your Google Cloud PostgreSQL database instance. You must have the cloudsqlsuperuser 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 Google Cloud PostgreSQL server:

1. Configure the replication parameters

1. Log in to Google Cloud SQL to access your database instance.

2. Click the More () icon next to the PostgreSQL instance and click Edit.

   

3. Scroll down to the Flags section.

   

4. Click the drop-down next to Flags and click ADD A DATABASE FLAG.

   

5. In the New database flag dialog window, click the arrow in the Choose a flag bar and type the flag name in the Filter bar.

   

6. Click the flag name to select it. Select an appropriate value for the flag from the drop-down or by typing it.

   

7. Click DONE and proceed to the next step to add all the required flags. Skip to step 9 if you have finished adding the flags.

   

8. Click ADD A DATABASE FLAG, and then, repeat steps 5-7 to add the following flags and the specified value:

   Flag Name	Value	Description
   cloudsql.logical_decoding	On	The setting to enable or turn off logical replication. Default value: Off.
   max_replication_slots	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.
   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.

9. Click SAVE.

   

10. In the confirmation dialog, click SAVE AND RESTART.

    

    Once the instance restarts, you can view the configured settings under the Flags section.

    

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 Google Cloud PostgreSQL database as a user having the cloudsqlsuperuser (Superuser) role with an SQL client such as DataGrip.

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.

   • 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;
     

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.

---

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 Google Cloud PostgreSQL database. To do this:

1. Log in to the Google Cloud SQL Console and click on your PostgreSQL instance ID.

   

2. In the left navigation pane of the <PostgreSQL Instance ID> page, click Connections.

3. In the Connections pane, click the NETWORKING tab, and scroll down to the Authorized networks section.

   

4. Click ADD A NETWORK, and in New Network, specify the following:

   

   • Name: A name to describe the region for Hevo’s IP address.

   • Network: The IP address(es) of your Hevo region in CIDR notation.

5. Click DONE, and then, repeat the step above for all the network addresses you want to add.

   Note: If you have created a read replica for your PostgreSQL database, you must add the outgoing IP address of the replica. Read Get the outgoing IP address of a replica instance for the steps to do this.

6. Click Save.

   

You can view the networks that you have authorized in the Summary tab of the Connections page under Security.

---

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:

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.
cloudsqlsuperuser	Create logical replication slots.

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

1. Connect to your Google Cloud PostgreSQL database as a user having the cloudsqlsuperuser (Superuser) role with an SQL client such as DataGrip.

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:

   ALTER USER <database_username> WITH REPLICATION;
   

5. Run the following command to allow your database user to create logical replication slots:

   GRANT cloudsqlsuperuser 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 (Optional)

---