Add an Extract for PostgreSQL

Extract is a process that runs against the source data source connection and extracts, or captures, data. Learn how to add an Extract for PostgreSQL Database, OCI Database with PostgreSQL, Amazon Aurora PostgreSQL, Amazon RDS for PostgreSQL, Azure Database for PostgreSQL, Azure Cosmos DB for PostgreSQL, EDB Postgres Advanced Server, Google Cloud SQL for PostgreSQL technologies, and YugabyteDB in OCI GoldenGate.

Ensure that you follow the appropriate instructions for your deployment version.

Add an Extract in Oracle GoldenGate 26ai

Before you begin

Before adding and running an Extract to capture data from the source, ensure that you:

• Prepare the database for Oracle GoldenGate.

• Created a PostgreSQL connection and associated the connection to the PostgreSQL deployment

• Enable supplemental logging:

1. Launch the PostgreSQL GoldenGate deployment console:

   1. From the Deployments page, select the PostgreSQL deployment to view its details.

   2. On the PostgreSQL deployment details page, select Launch console.

   3. On the deployment console sign in page, enter the GoldenGate admin credentials provided when you created the PostgreSQL deployment.

2. After signing in, in the navigation menu, select DB Connections.

3. For the PostgreSQL database connection, select Connect. Checkpoint table and TRANDATA fields appear if the connection is successful.

4. Next to TRANDATA Information, select Add TRANDATA (plus icon).

5. Enter a table name, schema name, or wildcard. For example, src_ociggll.

6. Select Submit.

Note: You only need to select Submit once. Use the search field to search for your table name and verify the tables were added.

Add the Extract

1. In the deployment console navigation menu, select Extracts.

2. On the Extracts page, select Add Extract (plus icon).

3. The Add Extract form consists of the following pages. Complete the Add Extract form as needed:

   1. On the Extract Information page:

      1. Select an Extract type:

         • Change Data Capture Extract

         • Initial Load Extract

      2. Enter a Process Name.

      3. (Optional) Enter a Description.

      4. Select Next.

   2. On the Register Extract page:

      1. Select the Source Credentials: Domain and alias

      2. Select the Plugin Type (logical decoding plugin): TEST_DECODING or PGOUTPUT.

         Note: For more information, see:

         • Guidelines and Limitations for PGOUTPUT
         • Add the Extract - Register Extract for PostgreSQL

   3. On the Extract Options page:

      1. Select when to Begin:

         • Now

         • Custom time

         • Position in Log

         • End of Log

      2. Enter the Extract Trail details:

         • Name (can only be two characters.)

         • Subdirectory

         • Trail Size

         • Trail Sequence

         • Trail offset

      3. Select the Source Credentials Domain and Alias from the dropdowns.

      4. Select Next.

   4. On the Managed Options page, complete the optional fields as needed, then select Next:

   • Profile Name

   • Critical to deployment health

   • Auto Start

   • Auto Restart

     Note: Adding a profile and configuring Auto Start and Auto Restart options enables your deployment to restart automatically after a network disruption. See Configure managed processes to learn more.

   1. On the Parameter file page, can edit the parameter file in the textarea to list the table details to capture. For example:

      table source.table1;

      Note:

      GoldenGate uses Greenwich Mean Time (GMT) by default. Use SETENV to override the default setting for the Extract. This change applies only to the Extract process and not the deployment.

      setenv (TZ="US/Eastern")
      setenv (TZ="GMT+5")

4. Select Create and Run to start the Extract. If you select Create, you can manually start the Extract later from the Extracts page.

You return to the Extracts page, where you can select the Extract process to view its details.

Add an Extract for PostgreSQL in GoldenGate 21c

Before you begin

Before adding and running an Extract to capture data from the source, ensure that you:

• Prepare your database for Oracle GoldenGate

• Created a PostgreSQL connection and associated the connection to the PostgreSQL deployment

• Enable supplemental logging:

  1. Launch the PostgreSQL GoldenGate deployment console:

     1. From the Deployments page, select the PostgreSQL deployment to view its details.

     2. On the PostgreSQL deployment details page, select Launch console.

     3. On the deployment console sign in page, enter the GoldenGate admin credentials provided when you created the PostgreSQL deployment.

  2. After signing in, open the navigation menu, and then select Configuration.

  3. For the PostgreSQL database connection, select Connect. Checkpoint table and TRANDATA fields appear if the connection is successful.

  4. Next to TRANDATA Information, select Add TRANDATA (plus icon).

  5. Enter a table name, schema name, or wildcard. For example, src_ociggll.

  6. Select Submit.

  Note: You only need to select Submit once. Use the search field to search for your table name and verify the tables were added.

Add the Extract

1. In the OCI GoldenGate deployment console, ensure that you're on the Administration Service Overview page, and then select Add Extract (plus icon).

2. For Extract Type, select one of the following:

   • Initial Load Extract

   • Change Data Capture Extract

3. Enter the Process Name, select the Credential Domain and Alias, and then enter a two-character name for the Trail name.

4. If you selected Initial Load Extract, select Next and skip to Step 7 - Extract Parameters. If you selected Change Data Capture Extract, you can select Register only to return to register the Extract and then return to the Overview page, or select Next to configure additional Extract Options.

   Note: Register only registers the Extract without adding it. The registration creates the replication slot when you register the Extract or use the Register Only option.

5. On the Extract Options page, under Basic Information, complete the fields as needed:

   1. For Process Name, enter a name for the Extract process, up to 8 characters.

   2. For Intent, select the option that best describes the purpose of this Extract:

      • Now

      • Custom time

      • Position in log

      • End of log

   3. For Trail Name, enter a two character name for the Trail file.

   4. (Optional) For Trail Subdirectory, set a custom location for the generated Trail file.

   5. (Optional) For Trail Size, set the max size for the generated trail file.

   6. (Optional) Under Managed Options, you can configure the following:

      • Profile Name

      • Auto Start

      • Auto Restart

      Note: Adding a profile and configuring Auto Start and Auto Restart options enables your deployment to restart automatically after a network disruption. See Configure managed processes for more information.

6. On the Extract Parameters page, you can edit the parameter file in the textarea to list the table details to capture. For example:

   table source.table1;

   Note:

   GoldenGate uses Greenwich Mean Time (GMT) by default. Use SETENV to override the default setting for the Extract. This change applies only to the Extract process and not the deployment.

   setenv (TZ="US/Eastern")
   setenv (TZ="GMT+5")

   Learn more about SETENV.

7. Select Create and Run to create and start the Extract. If you select Create, then you can manually start Extract later from the Administration Service Overview page.

You’re returned to the Administration Service Overview page, where you can view the status of the Extract process. Select Details from the Extract Action menu to view process information, checkpoint, statistics, parameters, and reports.