Replicate data from PostgreSQL to Snowflake
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.
- A Snowflake database to serve as the target database.
Environment set up: Snowflake
- Create a GoldenGate user in Snowflake with appropriate privileges.
- Create target tables using sample schema.
Ensure the tables and user have been succesfully created.
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 Big Data deployment for the target Snowflake database.
- Create a PostgreSQL
connection with the following values:
- For Type, select PostgreSQL Server from the dropdown.
- 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 your password.
- For Security Protocol, select Plain from the dropdown.
- Create a Snowflake connection
with the following values:
- (Optional) If your Big Data deployment doesn't have a public endpoint, then create a connection to GoldenGate, and then assign this connection to the source PostgreSQL deployment.
- Assign the source PostgreSQL connection to the PostgreSQL deployment.
- Assign the Snowflake connection to the target Big Data 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 4: Create the Distribution Path for Initial Load Extract
To create a Distribution Path for Initial Load Extract, complete the
following:
- In the Oracle Cloud console, on the Deployments page, select the target Big Data deployment.
- On the deployment details page, click Launch Console. Log in with the admin user details created in task 1, step 2.
- Create a user for the Distribution Path.
- In the source PostgreSQL deployment console, create a credential for the user
created in the previous step.
- In the source PostgreSQL deployment console, create a Distribution Path.
- In the target Big Data deployment console, review the Receiver path created as
a result of the Distribution path:
- Click Receiver Service.
- Review the Receiver path details.
Task 6: Create the Distribution Path for Change Data Capture
To create a Distribution Path for Change Data Capture, complete the
following:
- In the source PostgreSQL deployment console, click Distribution Service.
- On the Paths page, click Add Path.
- On the Add Path page, 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 OCI GoldenGate 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 Big Data deployment console, click Receiver Service, and then review the Receiver path created.
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.