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
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
If the target table (regardless of database type) contains a large object data type such as
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.
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.