Upgrading Pipelines with PostgreSQL Sources to Use the pgoutput Plugin

PostgreSQL provides pgoutput as its native plugin for publication-based replication. Hevo is upgrading all Pipelines created with PostgreSQL Sources that use the test_decoding and wal2json plugins for replication.

The pgoutput plugin provides:

• more efficient WAL and resource usage during replication

• improved stability using PostgreSQL’s native replication format

• support for newer PostgreSQL features

To complete this upgrade, Hevo requires a publication for the tables that you want to replicate from your database.

Note: This upgrade applies only to Pipelines using logical replication (WAL-based replication). Pipelines created using Table or Custom SQL mode are not affected.

Benefits of Using the pgoutput Plugin

Publication Requirement for pgoutput

A publication identifies the set of tables whose changes are tracked and made available for replication via the Write-Ahead Logs (WALs). PostgreSQL uses publications to determine which tables and operations are replicated. The publication can include:

• All tables in a database

• All tables within a specific schema

• Selected individual tables

Before you begin

• Ensure you have superuser or admin access to your PostgreSQL Source database.

• If you have multiple Pipelines using the same Source database, create a separate publication for each Pipeline using the naming convention, hevo_publication_<pipeline_number>. You can find the Pipeline number in the Pipeline Detailed View.

  

• A publication applies only to the database in which it is created. Each publication must have a unique name and cannot start with a number.

Step 1: Check if a publication already exists

If you already have a publication configured for your Source database, contact Hevo Support or your account executive and share the publication details. Hevo will use it to complete the upgrade. No further action is required from your end.

If you do not have a publication, proceed to Step 2.

Step 2: Create a publication

Connect to your PostgreSQL Source database as a superuser or admin using an SQL client such as psql, then run the appropriate command below.

Note: The commands in this section create a publication that includes all three change operations: insert, update, and delete. If you want to replicate only specific operations, you can define a publication for a subset of operations. For example, to capture only insert operations for the employees table:

CREATE PUBLICATION insert_only FOR TABLE employees WITH (publish = 'insert');

Refer to the CREATE PUBLICATION documentation for details on configuring the operations based on your requirements.

Run one of the following commands to create a publication named hevo_publication so that Hevo can identify and use it to upgrade the Pipeline:

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

  • For one or more database tables:

    CREATE PUBLICATION hevo_publication FOR TABLE <table_1>, <table_2>, <table_3>;
    

  • For all database tables:

    CREATE PUBLICATION hevo_publication FOR ALL TABLES;
    

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

  • For one or more database tables:

    CREATE PUBLICATION hevo_publication FOR TABLE <table_1>, <table_2>, <table_3> WITH (publish_via_partition_root);
    

  • For all database tables:

    CREATE PUBLICATION hevo_publication FOR ALL TABLES WITH (publish_via_partition_root);
    

FAQs Related to the Upgrade

Will my Pipeline stop during the upgrade?

A brief pause in data replication may occur while Hevo upgrades the Pipeline. Once the upgrade is complete, replication resumes from the offset where it stopped. No data is lost during this process.

Does creating a publication affect the performance of my Source database?

No. Creating a publication only defines which tables and operations PostgreSQL should track for replication. It does not interrupt inserts, updates, or deletes on your Source tables.

Do I need to manually switch the plugin from test_decoding or wal2json to pgoutput?

No. Once the publication is created, Hevo detects it and automatically upgrades the Pipeline.

What if I have multiple Pipelines using the same Source database?

Create a separate publication for each Pipeline using the naming convention hevo_publication_<pipeline_number>.

What if I only want to replicate specific tables?

You can create a publication for specific tables instead of all tables. Use the FOR TABLE syntax and list the tables that you want to include in the publication. If your replication requirements change later, you can modify the publication to add or remove tables.

Revision History