OCI GoldenGate 23ai for PostgreSQL: Oracle GoldenGate 23ai is now available in OCI GoldenGate for Oracle and PostgreSQL databases.
Note:
Direct upgrades from 21c to 23ai is not available. To use Oracle GoldenGate 23ai for Oracle Database or PostgreSQL, you must create a new deployment.One of the new features within Oracle GoldenGate 23ai is capture and delivery of array, pgvector extension, tsquery and tsvector for PostgreSQL and most vendor derivatives.
One ideal use case for PopstgreSQL array data types is the storage of vectors. Perhaps you can store geographic data, or vector embeddings for similarity searches in your database. The native way to handle vectors in PostgreSQL are ARRAYs. These vectors helps in powering semantic search over the data.
Business Use-case:
Imagine you’re developing a LLM powered AI chatbot for a large shopping mall. The chatbot also acts a indoor navigation system for a large shopping mall. The system allows users to search for stores, facilities, and amenities within the mall and provides step-by-step directions to their destinations. Each location within the mall is represented as a vector (x, y coordinates). The AI chatbot is hosted on OCI and similarity search is used to enhance the search experience.
A scenario may involve where a user wants to find the nearest restaurant from their current location within the mall. The semantic search algorithm interprets the user query (“nearest restaurant”) and understands that the user is looking for restaurant facilities within the mall. Instead of just looking for the exact phrase “nearest restaurant,” the search engine considers the context and matches the user’s query with locations that serve as restaurant, even if the word ” restaurant ” is not explicitly mentioned in the location names or descriptions.The system calculates the distance between the user’s current location (represented as a vector) and each restaurant location in the database. It then ranks the restaurant locations based on their proximity to the user’s location.Then the system returns the nearest restaurant location to the user, along with step-by-step directions on how to get there within the mall, using the stored vectors and navigation algorithms.
Replication Use-case:
Oracle GoldenGate 23ai enables replication or migration of business data and vector data (i.e. stored in ARRAY or pgvector) from PostgreSQL to Oracle Database 23ai in near real-time. The Oracle Database 23ai allows business data to be stored next to vector data.
Overall Architecture:
Components:
OCI GoldenGate 23ai deployment (Oracle and PostgreSQL)
PostgreSQL Database 14.11 (source)
Oracle Database 23ai (target)
Note: At the time of posting, the Oracle 23ai database is only available in certain areas, please refer to Oracle’s Product News for Oracle 23ai availability.
OCI GoldenGate Deployment Creation
Create OCI GoldenGate 23ai deployments (for PostgreSQL and Oracle).
Navigate to GoldenGate within Oracle Database option from the OCI console.
On the OCI GoldenGate, click on “Create Deployment” option. OR click on deployments in the left hand menu.
In the Create deployment panel, enter a name and optionally, a description.Select the appropriate compartment. Select Devlopment or testing option to create a test deployment.
Within Subnet in <Compartment> dropdown, select the private subnet to which a private endpoint is created from the OCI GoldenGate service tenancy. Note: You can only select a private subnet when creating a deployment.
Then select a license type, and optionally Enable GoldenGate console public access to connect to your deployment over the public internet. See Create data replication resources for more information about the different parameters.
Select Data Replication a deployment type.Select PostgreSQL as the technology from the dropdown. For Version, ensure that you click Change version and select the 23.0.0.0 version (oggpostgresql:23.0.0.0.0_240425.1035_977 and higher for PostgreSQL) available. The default versions for GoldenGate Data replication deployments are set to GoldenGate 21c.
Provide a GoldenGate instance name and select between OCI IAM or GoldenGate within credential store.Finally, click Create to intiate the deployment creation work request.
Once the deployment is in Active state, click on Launch Console on the deployment details page to access the OCI GoldenGate deployment console.
Refer to this blog to create a similar deployment for Oracle Database.
For the scope of this blog, the source PostgreSQL database (v14.11) is running on OCI Compute. Refer to Preparing the database for Oracle GoldenGate for creating a database user with appropriate privileges and setting PostgreSQL database configuration.
Create and Assign OCI GoldenGate Connections
From the OCI GoldenGate Overview page, click Connections.
In the Create Connection panel, enter the General Information field such as Name and Description.Select the compartment in which the connection will be created.Select Connection Type as PostgreSQL server ( as source PostgreSQL database is running on OCI compute). Click Next.
On the Connection details page, enter the PostgreSQL database connection details. Username is the database user that was created in the last step.Select the Traffic routing method accordingly, If the database is on-premises or OCI Compute, select the Dedicated endpoint in order to make sure the traffic flows through a dedicated endpoint the the same subnet within the VCN.
Finally, click Create to intitiate the create connection work request.
Once the connection is in Active state, you can then assign the connection to a PostgreSQL deployment to use as a source or target.
Verify the connectivity to the PostgreSQL database by clicking the Test connection option under the Actions (three dots) menu.
Add Trandata for replication Tables
On the deployment details page, click Launch console. Alternatively, you can copy the Console URL and paste it into your browser.The Oracle GoldenGate 23ai deployment console introduces a newly revamped user interface for data replication.
After you log in successfully, you’re brought to the Administration Service Home page.
Go to DB connections, click on the PostgreSQL connection that was created earlier.It shows connected to <database_name>, once successfully connected to the PostgreSQL database.
Click on the Checkpoint option under the left-side menu to create a checkpoint table.
Click on the Trandata option under the left-side menu to add trandata for the tables/ schema,which are going to be part of the replication.
Click on ‘+’ icon to add trandata
Provide table names and provide column names or optionally select “All Columns”.Click Submit.
Click on the Home option to return to the Home page of the deployment.
Add Extract Process
Click ‘+’ under Extract section to start configuring the extract.
In the Add Extract panel, on the Extract Information page, complete the following fields as needed, and then click Next:
Select an Extract Type: Change Data Capture Extract or Initial Load Extract
On the extract options page, select Begin now to start capturing data from current timestamp. Provide a two character trail file name. You can leave the other fields with default values.
Select the Domain and Alias created earlier for the PostgreSQL database connection.
Select the Default for profile , if not create a new profile to set custom AutoStart or AutoRestart options.
On the Parameter file section, edit to add the schema name or table name .
Finally, Click Create and Run to start the extract process.
Once the Extract status is Running, you can go ahead and execute few DML operations on the source table.
Execute DMLs on the source PostgreSQL replication table
The source table has two columns i.e. name (varchar) and coordinates (array).
In the source PostgreSQL database table, you can store the location data using array data type to represent the vectors for each location.
Create a Distribution Path from PostgreSQL deployment to Oracle deployment
Create GoldenGate connection between the Deployments
Create a GoldenGate connection type that lets you create connections between two Oracle GoldenGate deployments.
Within OCI GoldenGate overview page, under Connections page click on Create Connections.Enter name, description and select the correct Comaprtment. From the Type dropdown, under Generic, select GoldenGate.
Click Next to move forward to Connection details page.
On the connections detail page, Select the target GoldenGate Deployment from the dropdown i.e. Oracle Deployment in this case. (Optional) Provide credentials i.e.username and password. Optionally, you can also provide the GoldenGate deployment details manually.
Once the connection is in Active state, Assign the connection to GoldenGate for PostgreSQL deployment.
Create a user and Path connection for Distribution Path
Create a user with operator role on GoldenGate for Oracle (target) deployment.
Under Administration service, click on User Administration on the left hand menu. Click ‘+’ in Users section to create a user. Select Password in the drop-down under Authenticated by and ‘Operator ‘ as role. Provide a username and password and click on Submit.
In OCI GoldenGate for PostgreSQL deployment, we create a credential alias and domain, which will refer to the network user created in the step above.
Create a Distribution Path
On the OCI GoldenGate for PostgreSQL deployment , traverse to Distribution Service. Click ‘+’ in the Distribution Path section.Add a Path Name and Description. Click Next.
On the Source Options page, select Extract from the Dropdown and the trail file name will be automatically populated. Leave rest of the fields with default values. The trail file name can also be manually entered in the Trail Name field.
On the Target option Page, Select wss as protocol and insert target OCI GoldenGate for Oracle deployment URL as hostname and 443 as port number.Provide a 2 character name for the target trail and select the credential alias ceated in the previous step,
You can keep rest of the values as default , and finally click Create and Run to start the distribution path.On the Distribution Service page, you should see the Distribution path created and in a Running state. Similary traverse to the Receiver service within GoldenGate for Oracle deployment, you should see a similar receiver path automatically created .
Create Replicat
Before proceeding to creating a Replicat, create the target table (if not already) with Vector datatype on the target Oracle Database 23ai.
Traverse to Adminstration service page on GoldenGate for Oracle deployment. Go to Replicat section under left hand side menu. Click ‘+’ icon to add Replicat.
Select Parallel Non-Integrated Replicat type. Provide a name and description for the replicat process.
In the Replicat options section, provide the trail name provided while creating the Distribution path.Select the Alias from the drop down that was created for connecting to target Oracle Database 23ai. Additionally, select the checkpoint table created earlier. Under Managed options, you can keep the default values or set Auto Start and Auto Restart option.Click Next to move to the Parmater file, edit to add the MAP statement for the replication table.
Click Create and Run .Make sure the Replicat is created and in Running status.
Click on the Replicat name, traverse to the Statistics section on the left hand side menu.
Verify the replicated record on the target Oracle Database 23ai table.
Replicating PostgreSQL’s ARRAY vector data type to Oracle’s vector data type via Oracle GoldenGate enables seamless integration of rich array data with core business datasets, enhancing AI use cases. This process allows organizations to store vector embeddings alongside transaction and customer data, fostering a unified view that supports advanced analytics and machine learning. By ensuring data consistency and real-time access, businesses can improve model training, derive deeper insights, and make more informed decisions, ultimately driving innovation and competitive advantage in AI-driven applications.
Authors
Shrinidhi Kulkarni
Principal Product Manager
Shrinidhi Kulkarni is a Principal Product Manager in the Oracle GoldenGate group focusing on OCI GoldenGate,GoldenGate for Non-Oracle and GoldenGate for Big Data.
INSERT INTO mall_locations (name, coordinates) VALUES (‘Store A’, ‘{100.0, 50.0}’);
