PostgreSQL sources

Source preparation

• •On Windows, install the 64-bit PostgreSQL ODBC driver.
1. 1Download and install the PostgreSQL ODBC driver.
Note: If the source database contains objects with multibyte-character names, such as table names, column names, and publication names, you must use either a PostgreSQL Unicode ODBC driver or the DataDirect ODBC for PostgreSQL driver. This requirement applies to all PostgreSQL source types, including Amazon Aurora PostgreSQL, Azure Database for PostgreSQL - Flexible Server, Cloud SQL for PostgreSQL, and RDS for PostgreSQL. If you do not use a Unicode-compatible ODBC driver, your incremental load jobs will fail when encountering a multibyte-character name.
2. 2Set the PGSQL_ODBC_DRIVER environment variable to the driver name that is displayed by ODBC Data Source Administrator (64-bit).
Note: You can override this driver for a database ingestion and replication task by setting the pwx.custom.pgsql_odbc_driver custom property on the Source page of the task wizard.
• •On Linux or UNIX, the DataDirect ODBC driver for PostgreSQL is delivered as part of the Linux installation. Alternatively, you can install the unixODBC or iODBC driver manager or the PostgreSQL ODBC driver.
1. 1If you do not want to use the DataDirect ODBC for PostgreSQL driver that is provided in the Linux installation, install the unixODBC or iODBC driver manager or install the PostgreSQL ODBC driver.
Note: If the source database contains objects with multibyte-character names, such as table names, column names, and publication names, you must use either a PostgreSQL Unicode ODBC driver or the DataDirect ODBC for PostgreSQL driver. This requirement applies to all PostgreSQL source types, including Amazon Aurora PostgreSQL, Azure Database for PostgreSQL - Flexible Server, Cloud SQL for PostgreSQL, and RDS for PostgreSQL. If you do not use a Unicode-compatible ODBC driver, your incremental load jobs will fail when encountering a multibyte-character name.
2. 2Add a PostgreSQL entry to odbcinst.ini.
[PGSQL]
Description = ODBC for PostgreSQL
Driver =
Setup =
Driver64 = /usr/pgsql-9.6/lib/psqlodbca.so
Setup64 = /usr/lib64/libodbcpsqlS.so
FileUsage = 1
3. 3Optional. Set the following environment variables:
• ▪ Set the ODBCSYSINI variable to the directory where odbcinst.ini is located. If odbcinst.ini is located in the default /etc directory, you do not need to set the ODBCSYSINI variable.
• ▪ Add the directory where the PostgreSQL ODBC driver is installed to the LD_LIBRARY_PATH variable. If the driver is installed in the default directory of /usr/lib64, you do not need to add the path to the LD_LIBRARY_PATH variable.
• ▪ Set the PGSQL_ODBC_DRIVER parameter to the driver name that you specified in odbcinst.ini.
For example:
export ODBCSYSINI=/root/infaagent
export LD_LIBRARY_PATH=/usr/pgsql-9.6/lib
export PGSQL_ODBC_DRIVER=PGSQL

1. 1For incremental load and initial and incremental load jobs, ensure that the PostgreSQL postgresql.conf configuration file specifies the wal_level=logical parameter.
This parameter determines how much information PostgreSQL writes to the Write-Ahead Log (WAL). The setting of logical adds information that is required to support logical decoding.
To set wal_level to logical on Amazon Aurora PostgreSQL or Amazon RDS for PostgreSQL sources, set the rds.logical_replication parameter to 1 in the cluster parameter group. For Azure Database for PostgreSQL - Flexible Server, set the wal_level parameter to logical on the Server Parameters page in the Azure portal.
For Cloud SQL for PostgreSQL sources, perform the following actions:
1. aConnect to the database by using the public IP.
Note: Ensure that you add the required IPs under Authorized networks in the Google Cloud console.
2. bCreate a Cloud SQL for PostgreSQL database instance replica.
3. cIn the Cloud Shell, as a local admin user, run the following commands:
alter database postgres set default_transaction_read_only = off;
gcloud sql connect database_replica --user=postgres --quiet;
ALTER USER postgres WITH REPLICATION;
CREATE USER replication_user WITH REPLICATION IN ROLE cloudsqlsuperuser LOGIN PASSWORD 'password';
ALTER USER postgres WITH REPLICATION;
4. dIn the Google Cloud console, add the following database flags:
• ▪ cloudsql.logical_decoding. Set the value to on.
• ▪ max_replication_slots. Set the value to 64.
• ▪ cloudsql.enable_pglogical. Set the value to on.
• ▪ max_wal_senders. Set the value to 64.
5. eRestart the database instance.
2. 2If you use the DataDirect ODBC for PostgreSQL driver, ensure that the database does not use the SCRAM-SHA-256 authentication method. Use another authentication method, such as MD5.
Note: The PostgreSQL ODBC driver supports the SCRAM-SHA-256 authentication method. In PostgreSQL 13, this authentication method became the default method.
3. 3To deploy and run a database ingestion and replication task that includes a PostgreSQL source, the source connection must specify a database user who has the required privileges. Create the user and grant privileges to that user in the following ways:
• - For initial load jobs, use the following SQL statements:
CREATE USER dbmi_user WITH PASSWORD 'password';
GRANT SELECT ON ALL TABLES IN SCHEMA schema TO dbmi_user;
• - For incremental load and initial and incremental load jobs with on-premises PostgreSQL sources, use the following SQL statement:
CREATE USER dbmi_user WITH PASSWORD 'password' REPLICATION;
For Amazon Aurora PostgreSQL and RDS for PostgreSQL sources, use the following statements:
CREATE USER dbmi_user WITH PASSWORD 'password';
GRANT rds_replication to dbmi_user;
Additionally, if you use the pgoutput plugin, use the following SQL statement to grant ownership of the tables in the database that you want to add to the pgoutput publication to the dbmi_user that you created:
GRANT CREATE ON DATABASE database TO dbmi_user;
4. 4If you plan to use the wal2json plugin for logical decoding output for incremental load or initial and incremental load jobs, install the plugin.
5. 5If you plan to use the pgoutput plugin for incremental load or initial and incremental load jobs, use the following SQL statement to create publications for database ingestion jobs:
CREATE PUBLICATION publication_name [FOR TABLE [ONLY] table_name [*] [,...] | FOR ALL TABLES ];
Ensure that the publication includes all tables that you want to replicate to the target.
6. 6For incremental load and initial and incremental load jobs, use the following function to create a PostgreSQL logical replication slot:
SELECT pg_create_logical_replication_slot('slot_name', 'plugin_type');
Where the plugin_type is either the pgoutput plugin or the wal2json plugin.
Note: If you capture change data from a PostgreSQL replica database, ensure that you create the replication slot on the replica.
7. 7For incremental load and initial and incremental load jobs, ensure that the max_replication_slots parameter in the postgresql.conf configuration file has a value greater than or equal to the number of concurrent database ingestion jobs that you plan to use.
Important: All replication slots must be unique across all concurrent jobs.
8. 8For incremental load and initial and incremental load jobs, ensure that the PostgreSQL sources use the UTF-8 encoding.
9. 9Ensure that the PostgreSQL sources use the UTF-8 encoding. If you use another encoding for the source database, your initial load, incremental load, and combined initial and incremental load jobs might fail.

Usage considerations

• •Database Ingestion and Replication supports the following types of PostgreSQL sources in database ingestion and replication jobs that use any load type: on-premises PostgreSQL, Amazon Aurora PostgreSQL, Azure Database for PostgreSQL - Flexible Server, Cloud SQL for PostgreSQL, and RDS PostgreSQL.
• •Database ingestion and replication jobs support Cloud SQL for PostgreSQL sources with Google BigQuery or Snowflake targets only. For on-prem PostgreSQL sources, you can use any supported target type.
• •Database ingestion and replication jobs of any load type can use a PostgreSQL replica database as the source. In this case, you must define the replication slot on the replica. Perform other configuration steps as normal on the primary database.
• •Because Database Ingestion and Replication expects each source table row to be unique, Informatica recommends that each source table have a primary key. Database Ingestion and Replication does not honor unique indexes in place of a primary key. If no primary key is specified, Database Ingestion and Replication treats all columns as if they are part of the primary key.
• •Database Ingestion and Replication supports schema drift options for PostgreSQL sources in database ingestion and replication incremental load and initial and incremental load jobs with the following limitations:
• - PostgreSQL does not support changes to primary keys for tables from which change data capture is enabled.
• - Database ingestion and replication jobs cannot capture DML changes from source tables for which table partition IDs are changed.
If you enable schema drift for a PostgreSQL primary database with a replica database, the replica is kept in sync with the selected schema drift DDL changes made to the primary database.
• •Database Ingestion and Replication does not support generated columns in incremental load jobs that have a PostgreSQL source. If the source table contains generated columns, change data capture will ignore them and will continue with the rest of the columns.
• •If you use a Secure Agent group with multiple agents and the active agent goes down unexpectedly, database ingestion and replication jobs can automatically switch over to another available agent in the Secure Agent group. The automatic switchover occurs after the 15 minute heartbeat interval elapses. For database ingestion and replication jobs that have a PostgreSQL source, automatic switchovers are subject to following limitations:
• - Jobs cannot have persistent storage enabled.
• - Jobs that have Kafka targets must store checkpoint information in the Kafka header. For any jobs that existed before the July 2025 release, automatic switchovers can't occur because checkpoint information is stored in the checkpoint file in the Secure Agent.
• - If you use a read-only replica for a PostgreSQL connection, you must set the readerPostgreSQLShouldDisableLocalPersistent custom property to false on the Task Details source page of the task configuration wizard .
• •For incremental load and combined initial and incremental load jobs, Database Ingestion and Replication stores the replication slot data immediately in a PostgreSQL database table in the public schema and deletes the persisted rows only after receiving confirmation that the corresponding DML changes have been successfully written to the target. The persistence table is created if it does not already exist. It must be present during a restart operation. The structure of the table varies depending on the replication plugin type, with pgoutput slots containing a binary data type column and wal2json slots containing a text data type column.
• •Database ingestion and replication jobs can replicate data from PostgreSQL BYTEA, JSON, JSONB, TEXT, and XML columns if you select Include LOBs under Advanced on the Source page of the task wizard.
Database ingestion and replication incremental load and initial and incremental load jobs can replicate data from TEXT, XML, and BIT VARYING and CHARACTER VARYING columns without length limits if you set the pwx.custom.pgsql_enable_lobs custom property to true on the Source page of the task wizard. Incremental load and initial and incremental load jobs also always replicate data from BYTEA, JSON, and JSONB columns.
LOB column data is truncated before being written to the target if it is greater in size than a byte limit that depends on the LOB type and target type. For more information, see Configure a PostgreSQL source.
• •For initial load jobs, Database Ingestion and Replication does not support the following PostgreSQL data types:
• - ABSTIME
• - Array types
• - NAME
• - Object identifier types
• - PG_LSN
• - RELTIME
• - Text search types:
• ▪ TSQUERY
• ▪ TSVECTOR
• - User-defined types
For incremental load and initial and incremental load jobs, Database Ingestion and Replication does not support the following PostgreSQL data types, in addition to those not supported for initial load jobs:
• - Spatial types
• ▪ Box
• ▪ Circle
• ▪ Line
• ▪ LSeg
• ▪ Path
• ▪ Point
• ▪ Polygon
• - Unbounded varying types
Database ingestion and replication jobs either fail to deploy or propagate nulls for columns that have these data types.
For information about the default mappings of supported PostgreSQL data types to target types, see Default data-type mappings.
• •If you update the primary key value in the PostgreSQL source table for a record that does not exist in the target table, the record is not replicated to the target. However, the monitoring interface increments the Update count to include the primary key update. The data will be replicated to the target only if the record exists in the target table before the primary key update occurs.
• •For database ingestion and replication initial load and combined load jobs that have a PostgreSQL source, initial unload processing fails if partitioning is enabled for the unload phase and the source's primary key contains columns with any of the following data types: boolean, cidr, inet, int4range, int8range, interval, macaddr, macaddr8, and uuid.
• •The following limitations apply to PostgreSQL partitioned tables:
• - When a partition is dropped, the implicitly DELETED rows are not replicated.
• - When a partition is detached, the implicitly removed rows are not replicated.
• - When a partition is attached, the implicitly INSERTED rows are not replicated.
• - Partitions created or attached during runtime are not monitored for ongoing DML changes. Restart the job to apply the changes.
• - All partitions need to have their REPLICA IDENTITY set to FULL. Use the following command:
ALTER TABLE <table_name> REPLICA IDENTITY FULL;
If you are using the pgoutput replication slot with a publication configured using the publish_via_partition_root=TRUE parameter, the following exception applies:
• - Partitions created or attached during runtime are monitored for ongoing DML changes.