Replicate Data from PostgreSQL to Autonomous Transaction Processing
Learn to replicate data from a PostgreSQL server database to Autonomous Transaction Processing using OCI GoldenGate
Before you begin
To successfully complete this quicktart, you must have the following:
- A PostgreSQL installation to serve as the source database (Installation instructions follow in Task 0.)
- Open port 5432 in your VCN's security list
- An Autonomous Transaction Processing instance to serve as the target database
Environment set up: Autonomous Transaction Processing (ATP)
- Download and unzip the sample database schema.
- Enable the GGADMIN user.
- In the Oracle Cloud console, select your ATP instance from the Autonomous Databases page to view its details and access Database Actions.
- Click Database Actions, and then click Database Users.
- Locate GGADMIN, and then click its ellipsis menu (three dots) and select Edit.
- In the Edit User panel, enter the GGADMIN password, confirm the password, and then disable Account is Locked.
- Click Apply Changes.
- Load the target sample schema and data.
- From the Database Actions menu, select SQL.
- Copy and paste the script from OCIGGLL_OCIGGS_SETUP_USERS_ADW.sql into the SQL worksheet.
- Click Run Script. The Script Output tab displays confirmation messages.
- Clear the SQL worksheet and then copy and paste the SQL from OCIGGLL_OCIGGS_SRC_MIRROR_USER_SEED_DATA.sql.
- To verify that the tables were created successfully, close the SQL window and reopen it again. In the Navigator tab, look for the SRC_OCIGGLL schema and then select tables from their respective dropdowns.
Task 1: Create the OCI GoldenGate resources
This quickstart example requires deployments and connections for both
the source and target.
- Create a deployment for the source PostgreSQL database.
- Create a deployment for the target Autonomous Transaction Processing instance.
- Create a connection to the source PostgreSQL
database.
- For Type, ensure that you select PostgreSQL Server.
- For Database name, enter
ociggll
. - For Host, enter the public IP of the Compute instance that PostgreSQL runs on.
- For Port, enter
5432
. - For Username, enter
ggadmin
. - For Password, enter
W3lcome@1234
. - For Security Protocol, select Plain.
- Create connection for the target Autonomous Transaction Processing instance.
- Create a connection to GoldenGate, and then assign this connection to the source PostgreSQL deployment.
- Assign the source connection to the source PostgreSQL deployment.
- Assign the target connection to the target Oracle deployment.
Task 3: Create the Extracts
- Add the Change Data Capture Extract:
- Add the Initial Load Extract:
You return to the Administration Service Overview page, where you can
observe the Extract starting.
Task 6: Verify the initial load
The output should return the data that was loaded into the target database tables as
a result of the Initial Load.
Task 7: Create a Distribution Path for Change Data Capture
- In the source PostgreSQL deployment console, click Distribution Service.
- Click Add Path.
- Complete the following fields, and then click Create and
Run:
- For Path Name, enter a name.
- For Source Extract, select the Change Data
Capture Extract (
ECDCPSQL)
. - For Trail Name, select the Change Data Capture
Extract trail file (
P1
) - For Target Authentication Method, select UserID Alias.
- For Target, select wss.
- For Target Host, enter the target Autonomous Database deployment console URL, without the https:// or any trailing slashes.
- For Port Number, enter
443
. - For Trail Name, enter P1.
- For Domain, enter the domain name created in task 3.
- For Alias, enter the alias created in task 3.
- In the target Autonomous Database deployment console, click Receiver Service, and then review the Receiver path created.
Replicate Data from PostgreSQL to Autonomous Transaction Processing
Copyright ©2022,2024,
Oracle and/or its affiliates.
For information about Oracle's commitment to accessibility, visit the Oracle Accessibility Program website at http://www.oracle.com/pls/topic/lookup?ctx=acc&id=docacc.
Access to Oracle Support
Oracle customer access to and use of Oracle support services will be pursuant to the terms and conditions specified in their Oracle order for the applicable services.