Note:
- This tutorial requires access to Oracle Cloud. To sign up for a free account, see Get started with Oracle Cloud Infrastructure Free Tier.
- It uses example values for Oracle Cloud Infrastructure credentials, tenancy, and compartments. When completing your lab, substitute these values with ones specific to your cloud environment.
Connect to Oracle Cloud Infrastructure Database with PostgreSQL using Oracle Analytics Cloud
Introduction
In this tutorial, we will explore how to connect Oracle Cloud Infrastructure (OCI) Database with PostgreSQL to Oracle Analytics Cloud (OAC). A private access channel (PAC) is required because PostgreSQL is deployed in a private subnet.
You can set up a private access channel for Oracle Analytics Cloud instances deployed with enterprise edition. Regardless of whether your Oracle Analytics Cloud instance has a public or private endpoint, Oracle Analytics Cloud can access private data sources through a PAC in both network scenarios. It is essential to utilize a Domain Name System (DNS) resolvable name for your database instance, as the IP address will not work.
First, set up a PostgreSQL database and create a private zone with a record pointing to it. Second, provision an OAC instance and create a PAC to connect to the PostgreSQL database, which is hosted in a private subnet.
The following schema depicts the approach.
Objectives
-
A PostgreSQL database instance in private subnet.
-
An Oracle Analytics Cloud instance.
-
Create a private access channel in an Oracle Analytics Cloud instance.
Prerequisites
- Access to an OCI tenancy with privileges to create databases and OCI Compute instances.
Task 1: Create a PostgreSQL Database Instance in a Private Subnet
We can use multiple methods to create a PostgreSQL database instance like OCI Console, CLI and API. For more information, see Creating a Database System.
In this task, we will use the OCI Console to create a managed PostgreSQL database system.
-
Log in to the OCI Console, navigate to Databases, PostgreSQL and click DB Systems.
-
Click Create PostgreSQL Database system.
-
In the Create PostgreSQL Database system page, select Create new Database system and click Next.
-
In the Database system configuration section, enter the following information.
-
Database system name: Enter a name for the database system.
-
Description (Optional): Enter a description for the database system.
-
PostgreSQL major version: Select a major version of the PostgreSQL database. The only allowed value is 14.
-
-
In the Database system section, enter a value for Node count. One primary node is required. Extra nodes are reserved as read replica nodes.
-
Select Performance tier for the database. For more information, see Performance Tiers.
-
Select Data placement.
- Regional: Data is durably stored in multiple availability domains in the region.
- Availability Domain-specific: Data is durably stored in the selected availability domain.
-
In the Hardware configuration section, enter the following information.
-
OCPU count: Select the number of OCPUs per node.
-
Available Shapes: Select the shape of the database nodes.
-
-
In the Network configuration section, enter the following information.
-
Virtual Cloud Network: Select the VCN in which you want to create the database. If you need to create a VCN, click create a VCN. For more information, see VCN and Subnet Management.
-
Subnet: Select the private subnet of the selected VCN.
-
Private IP address: A private IP address in the subnet for the database instance. The value is optional. If an IP address is not provided, one is chosen from among the available IP addresses in the specified subnet.
-
-
In the Database system administrator credentials section, enter credentials for the database. Administrators do not get superuser access. An OCI Database with PostgreSQL admin user can create users and roles.
-
Username: Specify the username of the administrator. This value cannot be changed after the database system is created.
-
Password options: Select one of the following option.
-
Input password:
-
Password: Enter the administrator password.
-
Confirm password: Confirm the administrator password.
-
-
Use OCI Vault:
-
Vault: Select the vault that contains the administrator password.
-
Secret: Select the secret containing the administrator password.
-
Secret version: Select a secret version.
-
-
-
-
After configuring the database system, click Next.
-
Review the database system configuration and click Submit.
-
After successful creation, access the general information page for the database system.
Task 2: Create a New Private Zone in DNS Management
-
Go to the OCI Console, navigate to Networking, DNS Management and click Zones.
-
Select Private zones and click Create zone.
-
Enter Zone name, make sure you are in the right compartment and select the same network where your database is located.
-
Go to the OCI Console, navigate to Databases, PostgreSQL, DB Systems, click PostgreSQL database instance and copy the private Endpoint of the database.
-
Navigate back to the private zones and add a record in the new private zone, pointing to the private IP address or endpoint of the PostgreSQL database.
Task 3: Create an OAC Instance
-
Go to the OCI Console, navigate to Analytics & AI, Analytics and click Analytics Cloud.
-
Click Create Instance.
-
In the Create Analytics Instance page, select Enterprise Edition as it is required for provisioning a private access channel and click Create.
Task 4: Create a Private Access Channel
-
In the OCI Console, navigate back to the Oracle Analytics instance, Private Access Channel and click Configure Private Access Channel.
-
In the Configure Private Access Channel page, select the same Virtual Cloud Network as the PostgreSQL database, private Subnet and use the zone created in Task 2 (not the record).
The PAC is now configured.
Task 5: Connect OCI Database with PostgreSQL to Oracle Analytics Cloud
-
Connect to Oracle Analytics Cloud and click PostgreSQL to create a connection to PostgreSQL.
-
In the Create Connection page, enter the following information.
-
Host: Enter the DNS zone (a record domain) created in Task 2.
-
Database Name: Enter Postgres.
-
-
Access PostgreSQL data and create reports.
Related Links
Acknowledgments
- Authors - Ismael Hassane (Senior Solution Architect, Data Platform), Bob Peulen (Team leader, Open Source Data Services Specialist)
More Learning Resources
Explore other labs on docs.oracle.com/learn or access more free learning content on the Oracle Learning YouTube channel. Additionally, visit education.oracle.com/learning-explorer to become an Oracle Learning Explorer.
For product documentation, visit Oracle Help Center.
Connect to Oracle Cloud Infrastructure Database with PostgreSQL using Oracle Analytics Cloud
G15232-01
September 2024