PostgreSQL as Source

Capture

Fivetran HVR supports capturing changes from PostgreSQL location. HVR uses PostgreSQL native client library "libpq" to connect to the PostgreSQL server. For the list of supported PostgreSQL versions from which HVR can capture changes, see Capture changes from location in Capabilities.

Table Types

• HVR supports capture from regular tables.
• HVR does not support capture from inheritance tables.

Capture Methods

HVR supports the following methods for capturing changes from PostgreSQL:

• Logical Replication (SQL Fetch from Replication Slot): Capture changes using PostgreSQL's logical decoding infrastructure and streaming replication protocol. This is the default and recommended capture method for any PostgreSQL installation.

• Direct DBMS Log Reading: Capture changes directly from PostgreSQL's binary logs and archive.

  Since version 6.1.5/5, capture from PostgreSQL using Direct DBMS Log Reading method is deprecated. The support for this capture method will be removed in the future HVR 6 releases. We encourage you to consider migrating to Logical Replication capture method.

Grants for Log-Based Capture

This section lists the grants required for capturing changes from PostgreSQL database.

• For Logical Replication capture method, the HVR database user must have the REPLICATION privilege.

• The HVR database user must be a SUPERUSER to perform Activate Replication with Supplemental Logging:

  ALTER ROLE username SUPERUSER;
  

  You can revert this after the activation is complete:

  ALTER ROLE username NOSUPERUSER;
  

  If you prefer not to grant SUPERUSER privileges to the HVR database user, the error F_JD023F that will appear during replication activation will provide you with the specific SQL statements that the SUPERUSER privilege would normally execute automatically. You can then manually execute these statements to enable supplemental logging.

Configuring REPLICA IDENTITY

In PostgreSQL, configuring the table-level setting REPLICA IDENTITY is crucial for replication and data capture, especially in cases involving row-level changes. It specifies how PostgreSQL identifies rows in replicated tables, using either a PRIMARY KEY or a UNIQUE INDEX. This is essential for understanding how data changes, such as updates or deletes, are tracked and replicated to another system.

The following options are available for REPLICA IDENTITY, along with their effects on HVR replication:

• DEFAULT: This is the default option. It uses the table's PRIMARY KEY as the replication key.

  Without a PRIMARY KEY, HVR cannot replicate tables if REPLICA IDENTITY is set to DEFAULT. Consider adding a PRIMARY KEY in such cases.

• USING INDEX: This option allows the use of a UNIQUE INDEX, other than the PRIMARY KEY, as the replication key.

  If the chosen UNIQUE INDEX is dropped, HVR will not replicate the tables. Consider changing REPLICA IDENTITY to DEFAULT or using a different UNIQUE INDEX.

• FULL: This option uses all table columns as the replication key. If available, HVR uses the PRIMARY KEY or a UNIQUE INDEX as the replication key.

• NOTHING: This option explicitly disables the REPLICA IDENTITY mechanism, making it impossible to capture UPDATE and DELETE operations.

  This option is generally not recommended outside of testing environments.

REPLICA IDENTITY can be configured manually using the ALTER TABLE statement.

For example:

ALTER TABLE table_name REPLICA IDENTITY FULL;

Both capture methods depend on the REPLICA IDENTITY setting of the replicated tables:

• Direct DBMS Log Reading is supported only when REPLICA IDENTITY is set to FULL.
• Logical Replication supports all options since version 6.1.5/4.

Activating Replication with Supplemental Logging option selected (equivalent to hvractivate with option -ol) will set the REPLICA IDENTITY to FULL when using the Direct DBMS Log Reading capture method or when the replication actions require more logging for a particular table.

Capture Limitations

PostgreSQL-Specific Limitations

• HVR does not support capture from tables with multi-level partitions.

• Until version 6.1.5/3, only INSERT, UPDATE, and DELETE operations are captured, TRUNCATE is not captured. Since version 6.1.5/3, TRUNCATE is also captured by Logical Replication when PostgreSQL version is 11 or higher.

• TRUNCATE of table partitions is not captured, which may result in data duplication on the target.

• Until version 6.1.5/6, AdaptDDL was not supported for PostgreSQL. Since version 6.1.5/6, AdaptDDL is supported when using the Logical Replication capture method. However, this support is limited to DDL commands that modify the columns of a table in the channel, such as ALTER TABLE ... ADD COLUMN, ALTER TABLE ... DROP COLUMN, etc. Commands like CREATE TABLE or DROP TABLE are not supported. Since version 6.2.5/5, support was added for the ALTER TABLE ... ALTER COLUMN statement to modify a column's data type, scale, or precision (for numeric or timestamp types).

• DDL commands that modify table columns are not captured immediately. In versions prior to 6.2.5/5, HVR detects such changes during the nextINSERT or UPDATE operation. Since version 6.2.5/5, the changes are detected during any subsequent DML operation (INSERT, UPDATE, or DELETE).

• Only a subset of AdaptDDL action parameters is supported for PostgreSQL: OnEnrollBreak, RefreshOptions, and KeepExistingStructure. Note that the -m option of RefreshOptions is not supported.

• For bi-directional replication, the state tables for collision detection must be created before replication begins. You can create state tables by activating the channel with the State Tables option selected or using the CLI command hvractivate with the option -os. If the state table is created while capture is running, capture will re-capture changes coming from integrate and/or Refresh.

YugabyteDB-Specific Limitations

• When capturing changes from YugabyteDB, the limitations of YugabyteDB’s CDC using logical replication apply. For detailed information, refer to the YugabyteDB documentation.

• If a capture job is suspended beyond the cdc_intent_retention_ms threshold (4 hours by default) configured in YugabyteDB, a Snapshot too old error will occur. In this case, you must reactivate the channel(s). In the Activate Replication dialog, in addition to the default selected components, you must select the State Tables and Capture Time and Transaction Files options under the Replication Components section.

• Until YugabyteDB version 2024.1.3.0, adding or removing tables from an activated channel is not supported because dynamic table addition and removal are not supported in YugabyteDB CDC streams.

Compare and Refresh from Source

HVR allows you to perform only Compare and Refresh from PostgreSQL database (without using Capture). This section describes the configuration requirements for performing Compare and Refresh from PostgreSQL location.

Grants for Compare and Refresh from PostgreSQL

This section lists the grants required for performing only Compare and Refresh from PostgreSQL database.

The HVR database user must be granted the SELECT privilege:

GRANT SELECT ON table_name TO username;

Related Articles

• Capture from PostgreSQL using Logical Replication
• Capture from PostgreSQL using Direct DBMS Log Reading