Snapshot replication v7
In snapshot replication, the target tables are completely reloaded from the source tables. The database system’s truncate operation is used to delete all rows from the target tables.
For Oracle and SQL Server only: Oracle and SQL Server target tables are loaded using JDBC batches of INSERT statements.
For Postgres only: In general, Postgres target tables are loaded using the JDBC COPY command, since using truncation and COPY is generally faster than executing an SQL DELETE statement against the entire table and then adding the rows using JDBC batches of INSERT statements. If the COPY command fails, the publication server retries the snapshot using JDBC batches of INSERT statements.
If the target table (regardless of database type) contains a large object data type such as BYTEA, BLOB, or CLOB, then rows are loaded one at a time per batch using an INSERT statement. This approach avoids a heap space error resulting from potentially large rows. Loading time can be decreased by allowing multiple inserts per batch, which you can do by adjusting the configuration option lobBatchSize described in Optimizing snapshot replication.
Note
EDB Postgres Advanced Server supports a number of aliases for data types. Such aliases that translate to BYTEA are treated as large object data types. See the Database Compatibility for Oracle Developers Reference Guide for a listing of EDB Postgres Advanced Server data types. (See the Database Compatibility for Oracle Developer’s Guide for EDB Postgres Advanced Server version 9.5 or earlier versions.)
Under certain circumstances, the corresponding Postgres target table created for certain types of Oracle partitioned tables is a set of inherited tables. In these cases, the SQL DELETE statement is used on the inherited child tables instead of truncation. See Replicating Oracle partitioned tables.
A server configuration option is available that forces the snapshot replication process to use the Oracle database link utility instead of JDBC COPY to populate the Postgres target tables from an Oracle publication. Oracle database link provides a performance improvement over JDBC COPY. See Optimizing snapshot replication for information on using the Oracle database link option as well as various configuration options to optimize snapshot replication.