18 Configuring Extract

This chapter contains instructions for configuring the Oracle GoldenGate Extract to capture initial load and transactional data from a PostgreSQL database.

Topics:

• About Extract
  
• Prerequisites for Creating a Change Data Capture Extract
  
• Creating a Change Data Capture Extract
  

About Extract

For Oracle GoldenGate for PostgreSQL, there are two types of Extracts that can be created.

• Initial Load Extract
  
• Change Data Capture Extract
  
• Extract Deployment Options
  

Initial Load Extract

An Initial Load Extract is used to read all records from a table and write them to an EXTFILE or RMTFILE. Initial load Extracts are created with the SOURCEISTABLE option of the ADD EXTRACT command and do not maintain checkpointing for recovery.

For more information on the Initial Load process, see Configuring an Initial Synchronization for a PostgreSQL Source Database using Precise Instantiation.

Change Data Capture Extract

A Change Data Capture Extract is used to capture transactional data changes from that point in time at which it is created or positioned into the write-ahead log.

The Oracle GoldenGate Extract process for PostgreSQL receives logical records from the PostgreSQL test_decoding database plugin and writes them in commit order into trail files for downstream consumption by a Replicat.

Extract Deployment Options

• Local deployment: For a local deployment, the source database and Oracle GoldenGate are installed on the same server. No extra consideration is needed for local deployments.

• Remote deployment: For a remote deployment, the source database and Oracle GoldenGate are installed on separate servers. Remote deployments are the only option available for supporting cloud databases, such as Azure for PostgreSQL or Amazon Aurora PostgreSQL.

  For remote deployments, operating system endianness between the database server and Oracle GoldenGate server need to be the same, such as Windows and Windows, Linux and Linux, or Windows and Linux.

  Server time and time zones of the Oracle GoldenGate server should be synchronized with that of the database server. If this is not possible, then positioning of an Extract when creating or altering one will need to be done by LSN.

  In remote capture use cases, using SQLEXEC may introduce additional latency, as the SQLEXEC operation must be done serially for each record that the Extract processes. If special filtering that would require a SQLEXEC is done by a remote hub Extract and the performance impact is too severe, it may become necessary to move the Extract process closer to the source database.

  With remote deployments, low network latency is important, and it is recommended that the network latency between the Oracle GoldenGate server and the source database server be less than 1 millisecond.

Prerequisites for Creating a Change Data Capture Extract

Review the Prerequisites for Installing Oracle GoldenGate for PostgreSQL.

Ensure that the database connection is configured correctly See Configuring a Database Connection for details.

• Registering the Extract for PostgreSQL
  

Registering the Extract for PostgreSQL

An Extract for PostgreSQL must be registered with the database and be granted a reserved replication slot. Replication slots are allocated through the database configuration setting max_replication_slots and can be configured as discussed in Database Configuration.

Follow these instructions to register an Extract. Extract registration must be done prior to creating an Extract. See REGISTER EXTRACT in the Command Line Interface Reference for Oracle GoldenGate for more information.

Topics:

• Registering Extract in Microservices Architecture for PostgreSQL
  
• Registering an Extract in Classic Architecture for PostgreSQL
  

Registering Extract in Microservices Architecture for PostgreSQL

1. Using the Admin Client, connect to the deployment, then connect to the credential alias for the source database.

   OGG> CONNECT https://remotehost:srvmgrport DEPLOYMENT
             deployment_name AS deployment_user PASSWORD deployment_password
   

   OGG (https://remotehost:16000postgresql_source)> DBLOGIN USERIDALIAS alias

2. Register the Extract, which internally creates a replication slot for the Extract. Extract names cannot be more than 8 alpha-numeric characters.

   OGG (https://remotehost:16000postgresql_source)> REGISTER EXTRACT extname

You can also register an Extract from the Oracle GoldenGate MA web interface. See How to Add Extracts in the Step by Step Data Replication Using Oracle GoldenGate Microservices guide.

Registering an Extract in Classic Architecture for PostgreSQL

Follow these instructions to register an Extract. Extract registration must be done prior to creating an Extract. See REGISTER EXTRACT in the Command Line Interface Reference for Oracle GoldenGate guide for more information.

1. Using GGSCI, connect to the DSN for the source database.

   GGSCI> DBLOGIN SOURCEDB dsn USERIDALIAS alias

2. Register the Extract using the GGSCI command. This command internally creates the replication slot. Extract names cannot be more than 8 alpha-numeric characters.

   GGSCI> REGISTER EXTRACT extname

Creating a Change Data Capture Extract

These steps configure a CDC Extract to capture transactional data from a source PostgreSQL database.

Note:

One Extract per database is generally sufficient, but multiple Extracts are allowed if the replication slots are available.

1. In GGSCI, Admin Client, or REST API client on the source system, create the Extract parameter file.

   EDIT PARAMS extname

   In this sample, extname is the name of the primary Extract and matches the name of the Extract that was registered with the database in the previous steps.

   To learn about using Oracle GoldenGate Microservices to perform this task, see How to Add Extracts in the Step by Step Data Replication Using Oracle GoldenGate Microservices guide.

2. Enter the Extract parameters in the order shown, starting a new line for each parameter statement.

   Sample basic parameters for Extract for Microservices installations:

   EXTRACT extname
   SOURCEDB dsn_name USERIDALIAS alias
   EXTTRAIL ep
   GETTRUNCATES
   TABLE schema.*;

   Sample basic parameters for Extract for Classic architecture installations:

   EXTRACT extname
   SOURCEDB dsn_name USERIDALIAS alias
   EXTTRAIL ./dirdat/ep
   GETTRUNCATES
   TABLE schema.object;

   Parameter	Description
   EXTRACT extname	extname is the name of the Extract and cannot be more than 8 alpha-numeric characters in length. For more information, see extract in Reference for Oracle GoldenGate.
   SOURCEDB dsn_name	Specifies the name of the database connection DSN.
   USERIDALIAS alias	Specifies the alias of the database login credential of the user that is assigned to Extract. This credential must exist in the Oracle GoldenGate credential store. For more information, see Establishing Oracle GoldenGate Credentials.
   EXTTRAIL trailname	Specifies a two character, local trail to which the primary Extract writes captured data.
   GETTRUNCATES	Optional parameter but needed in order to capture truncation operations.
   TABLE schema.object; or TABLE schema.*;	Specifies the database object for which to capture data. TABLE specifies a table or a wildcarded set of tables. schema is the schema name or a wildcarded set of schemas. object is the table or sequence name, or a wildcarded set of those objects. * is a wildcard for all tables in the schema. Terminate the parameter statement with a semi-colon. To exclude a name from a wildcard specification, use the SCHEMAEXCLUDE, TABLEEXCLUDE, and EXCLUDEWILDCARDOBJECTSONLY parameters as appropriate. Note: If the schema of tables to be captured from is the same as the schema in GGSCHEMA of the GLOBALS file, which is not recommended, then you cannot use schema.* in the TABLE statement.

3. Enter any optional Extract parameters that are recommended for your configuration. You can edit this file at any point before starting processing by using the EDIT PARAMS command.

4. Save and close the file.

5. Add the Extract and its associated trail file.

   For Microservices architecture using the Admin Client:

   OGG (https://remotehost:16000 postgresql_source)> ADD EXTRACT extname, TRANLOG, BEGIN NOW
   OGG (https://remotehost:16000 postgresql_source)> ADD EXTTRAIL ep, EXTRACT extname

   For Classic Architecture using GGSCI:

   GGSCI> ADD EXTRACT extname, TRANLOG, BEGIN NOW
   GGSCI> ADD EXTTRAIL ./dirdat/ep, EXTRACT extname

6. Start the Extract.