A slow endpoint can start with a small database decision: fetching unnecessary columns, binding the wrong data type, issuing hundreds of individual updates, or keeping a transaction open while waiting for another service.

Oracle Database gives backend engineers useful tools for addressing these problems. The challenge is knowing which tool fits — and checking whether it improves the actual workload.

Here are 25 techniques worth keeping close.

Compatibility: Examples target Oracle Database 19c. Tables and columns are illustrative, and :name denotes a bind variable supplied by your application or SQL client.

1. Fetch Only the Columns You Need

If an endpoint needs a customer’s name and email, make that explicit:

SELECT customer_name, email
FROM customers
WHERE customer_id = :customer_id;

A smaller projection reduces the result payload and can make an index sufficient to answer a query without accessing the table. Whether that happens depends on the selected columns, index definition, and execution plan.

Avoid assuming every extra column causes another physical read; Oracle retrieves table data in blocks. The reliable benefit is avoiding unnecessary data retrieval and transfer. Oracle access paths

2. Inspect the Executed Plan

An estimated plan is useful. Runtime statistics show what happened during execution.

For a targeted investigation:

SELECT /*+ gather_plan_statistics */
       order_id, total_amount
FROM orders
WHERE customer_id = :customer_id;

Fetch the complete result, then inspect the cursor:

SELECT *
FROM TABLE(
  DBMS_XPLAN.DISPLAY_CURSOR(
    NULL,
    NULL,
    'ALLSTATS LAST'
  )
);

Compare estimated rows with actual rows, and examine buffer gets and repeated operations. Large differences can expose poor cardinality estimates.

Use a specific SQL ID and child cursor when client activity makes “the last statement” ambiguous. DISPLAY_CURSOR also requires access to Oracle’s relevant dynamic performance views. DBMS_XPLAN documentation

3. Bind Values Instead of Building SQL Strings

Keep statement text stable:

SELECT order_id, status
FROM orders
WHERE customer_id = :customer_id;

Generating a different SQL string for every customer can increase hard parsing. Bind variables enable cursor sharing and keep values separate from SQL syntax.

Bind with the appropriate data type, too. A numeric customer ID should arrive as a number.

Binds do not guarantee one ideal plan for every value; skewed data can require different execution strategies. Oracle’s adaptive cursor sharing helps address that distinction. Oracle cursor sharing

4. Design Composite Indexes Around Queries

Consider this access pattern:

SELECT order_id, created_at
FROM orders
WHERE customer_id = :customer_id
  AND status = :status
ORDER BY created_at DESC;

A candidate index is:

CREATE INDEX orders_customer_status_dt_ix
ON orders(customer_id, status, created_at DESC);

Its leading columns match equality predicates, followed by the requested ordering.

Treat this as a candidate to measure. Index order should reflect the workload; “put the most selective column first” is not a universal rule. Oracle access paths

5. Express Date Filters as Ranges

For a DATE column, a day filter can be written as:

SELECT order_id, created_at
FROM orders
WHERE created_at >= DATE '2026-10-27'
  AND created_at <  DATE '2026-10-28';

This keeps the indexed column unwrapped and includes every time within the day.

A predicate such as TRUNC(created_at) = ... may need a matching function-based index for efficient indexed access. Oracle index access paths

6. Index Expressions You Regularly Search

If case-insensitive email lookup is part of the application’s rules:

CREATE INDEX customers_email_lower_ix
ON customers(LOWER(email));

Use the matching expression:

SELECT customer_id
FROM customers
WHERE LOWER(email) = LOWER(:email);

A function-based index gives Oracle an access path for the expression itself.

Choose normalization rules deliberately, and remember that every additional index adds storage and write-maintenance work. Function-based indexes

7. Treat NULL as a Separate State

This predicate does not find missing values:

WHERE cancelled_at = NULL

Use:

WHERE cancelled_at IS NULL

In Oracle 19c, a zero-length character string is also treated as null. That matters when application code distinguishes "" from a missing value.

For example, email = '' does not find empty emails. Define missing-value behavior explicitly at the application boundary. Oracle NULL semantics

8. Use EXISTS When the Question Is About Existence

To find customers who have orders:

SELECT c.customer_id, c.customer_name
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
);

This expresses the requirement directly and avoids generating duplicate customer rows that later require DISTINCT.

It does not guarantee a faster plan than every alternative. It gives the optimizer — and the next engineer — a clear statement of intent. EXISTS condition

9. Use Keyset Pagination for Sequential Browsing

For a feed ordered by a unique, increasing ID:

SELECT order_id, created_at, total_amount
FROM orders
WHERE order_id > :last_seen_order_id
ORDER BY order_id
FETCH FIRST 50 ROWS ONLY;

This lets the next page start after the previous page’s final key.

Use a unique, stable ordering. If the business order uses a timestamp, include a unique tie-breaker in both the ordering and continuation predicate.

Keyset pagination suits next-page navigation; arbitrary page-number jumps need a different design. Oracle row limiting

10. Avoid the ROWNUM Top-N Trap

This does not reliably return the ten highest-paid employees:

SELECT employee_id, salary
FROM employees
WHERE ROWNUM <= 10
ORDER BY salary DESC;

The row restriction can happen before the final sort.

Write the requirement directly:

SELECT employee_id, salary
FROM employees
ORDER BY salary DESC, employee_id
FETCH FIRST 10 ROWS ONLY;

The employee ID makes ties deterministic. ROWNUM behavior

11. Find the Latest Row per Group with ROW_NUMBER

To retrieve each customer’s latest order:

SELECT customer_id, order_id, created_at
FROM (
  SELECT customer_id,
         order_id,
         created_at,
         ROW_NUMBER() OVER (
           PARTITION BY customer_id
           ORDER BY created_at DESC, order_id DESC
         ) AS rn
  FROM orders
)
WHERE rn = 1;

The unique tie-breaker matters when two orders share a timestamp.

This pattern also works for the newest event per device, the latest subscription per account, or the top three products per category. ROW_NUMBER documentation

12. Use MERGE for Insert-or-Update Workflows

Oracle’s MERGE combines matched updates and unmatched inserts:

MERGE INTO customer_preferences p
USING (
  SELECT :customer_id AS customer_id,
         :theme AS theme
  FROM dual
) s
ON (p.customer_id = s.customer_id)
WHEN MATCHED THEN
  UPDATE SET p.theme = s.theme
WHEN NOT MATCHED THEN
  INSERT (customer_id, theme)
  VALUES (s.customer_id, s.theme);

Ensure the source has at most one applicable row per target key.

Also retain a primary or unique constraint. Concurrent sessions can race to insert the same absent key; MERGE does not remove the need to handle uniqueness conflicts. MERGE documentation

13. Return Inserted Values Without Another Query

Retrieve an inserted identifier through an output bind:

INSERT INTO orders (
  order_id,
  customer_id,
  total_amount
)
VALUES (
  orders_seq.NEXTVAL,
  :customer_id,
  :total_amount
)
RETURNING order_id INTO :new_order_id;

This avoids a follow-up query just to discover the inserted key.

Your driver must support the output binding used by the statement. Never use MAX(order_id) to identify your own insert; another session may have inserted a larger value. INSERT and RETURNING

14. Put Data Invariants in Constraints

If an inventory record must always have a nonnegative quantity:

CREATE TABLE inventory (
  product_id NUMBER PRIMARY KEY,
  quantity   NUMBER NOT NULL,
  CONSTRAINT inventory_quantity_ck
    CHECK (quantity >= 0)
);

Application validation provides helpful feedback. Database constraints enforce the rule across every writer.

Use both NOT NULL and CHECK here: a check constraint alone does not reject null merely because its condition evaluates to unknown. Oracle constraints

15. Review Foreign-Key Indexes

For a frequently accessed child relationship:

CREATE INDEX order_items_order_ix
ON order_items(order_id);

Foreign-key indexing affects more than joins. Unindexed child foreign keys can cause broader locking during parent-key changes or deletes.

Check existing indexes before creating another one. An appropriate composite index may already cover the foreign-key columns. Foreign keys and locking

16. Replace Procedural Updates with Set-Based SQL

If every qualifying row receives the same transformation, express it once:

UPDATE subscriptions
SET status = 'EXPIRED'
WHERE status = 'ACTIVE'
  AND expires_at < :cutoff;

Fetching IDs into application code and issuing one update per ID adds round trips and repeated statement execution.

A single statement is a useful starting point. Large maintenance jobs may still need bounded batches based on undo, lock duration, restartability, and business requirements. Oracle’s bulk-processing guidance explains the overhead of repeated SQL calls. Bulk SQL documentation

17. Use FORALL When Procedural Batching Is Necessary

When PL/SQL already has a collection of IDs:

DECLARE
  TYPE id_list IS TABLE OF NUMBER;
  l_ids id_list := id_list(101, 102, 103);
BEGIN
  FORALL i IN 1 .. l_ids.COUNT
    UPDATE orders
    SET status = 'ARCHIVED'
    WHERE order_id = l_ids(i);
END;
/

FORALL batches communication between PL/SQL and SQL.

Prefer a single set-based statement when possible. When using collections, bound their size and define whether failures should stop processing or be collected with SAVE EXCEPTIONS. FORALL and bulk binding

18. Commit at the Business Transaction Boundary

A transfer requires both balance changes to succeed together:

UPDATE accounts
SET balance = balance - :amount
WHERE account_id = :source_id;

UPDATE accounts
SET balance = balance + :amount
WHERE account_id = :destination_id;

COMMIT;

Application code must validate the amount, affected rows, and funds policy, then roll back on failure.

Committing after the first update breaks atomicity. Keeping the transaction open while calling a remote service prolongs lock ownership.

Also remember that Oracle DDL can implicitly commit surrounding work. COMMIT semantics

19. Use SKIP LOCKED for Competing Workers

Queue consumers can skip rows another transaction has locked:

SELECT job_id
FROM job_queue
WHERE status = 'READY'
FOR UPDATE SKIP LOCKED;

Fetch a bounded batch through a cursor, mark those jobs claimed in the same transaction, and commit promptly.

In 19c, do not combine FETCH FIRST with FOR UPDATE in the same query block.

A complete worker design also needs retries, recovery of abandoned claims, and idempotent processing. Oracle locking clauses

20. Detect Lost Updates with a Version Column

For records edited across separate requests:

UPDATE customer_profiles
SET display_name = :display_name,
    version_no = version_no + 1
WHERE customer_id = :customer_id
  AND version_no = :expected_version;

Check the affected-row count immediately.

One updated row means the expected version matched. Zero rows means the record was changed or removed, so the application must handle the conflict.

Initialize version_no as non-null, and require every writer to follow the same convention. This applies optimistic concurrency control without holding a lock across user interaction. Oracle concurrency concepts

21. Check Statistics Before Adding Hints

Inspect the statistics available for a table:

SELECT table_name,
       num_rows,
       last_analyzed,
       stale_stats
FROM user_tab_statistics
WHERE table_name = 'ORDERS'
  AND partition_name IS NULL;

A major data load or distribution change can make existing statistics less representative.

Where appropriate, gather statistics through the established operational process:

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname => USER,
    tabname => 'ORDERS'
  );
END;
/

Oracle already supports automatic collection. Investigate whether it is meeting the workload’s needs before adding routine manual gathers. Optimizer statistics collection

22. Keep Data Types Aligned

Suppose external_reference is a VARCHAR2 column containing values such as '00123'.

Bind it as text:

SELECT order_id
FROM orders
WHERE external_reference = :reference_text;

Binding a number can trigger implicit conversion, change matching semantics, cause conversion errors, or prevent an expected index access path.

Dates deserve the same care. Use typed date or timestamp binds, or explicit literals, instead of depending on a session’s default date format. Oracle conversion rules

23. Extract JSON Values Inside SQL

For JSON documents stored in payload:

SELECT event_id,
       JSON_VALUE(
         payload,
         '$.customerId'
         RETURNING NUMBER
         ERROR ON ERROR
       ) AS customer_id
FROM events;

This retrieves a scalar without transferring the entire document for application-side parsing.

Choose error handling deliberately. JSON_VALUE defaults to returning null on errors; ERROR ON ERROR makes malformed values visible, while missing-field behavior can be controlled separately.

Frequently searched JSON expressions can also be indexed. JSON_VALUE documentation

24. Use Flashback Query to Investigate Recent Changes

To inspect an earlier committed state:

SELECT order_id, status, total_amount
FROM orders AS OF TIMESTAMP (
  SYSTIMESTAMP - INTERVAL '10' MINUTE
)
WHERE order_id = :order_id;

This can help answer: “What did this order contain before the update?”

Historical availability depends on retained undo, privileges, and intervening operations. An AS OF query reads past data; it does not restore the current row. Oracle Flashback Query

25. Label Database Work with Application Context

Make sessions recognizable during production investigations:

BEGIN
  DBMS_APPLICATION_INFO.SET_MODULE(
    module_name => 'orders-api',
    action_name => 'create-order'
  );
END;
/

The module and action help connect database activity to application operations.

With connection pools, set the context when borrowing a connection and clear or replace it before reuse:

BEGIN
  DBMS_APPLICATION_INFO.SET_MODULE(NULL, NULL);
END;
/

Use stable operation names. Keep secrets and customer payloads out of diagnostic labels. DBMS_APPLICATION_INFO documentation

Backend performance improves when database work is deliberate: retrieve the required data, express operations clearly, keep transactions bounded, and inspect execution evidence before tuning.

Start with one expensive endpoint. Measure its SQL, change one thing, and measure again.

Postgres and Oracle are often considered polar opposites where it is either one of the other. Both databases have their strengths and drawbacks. Two is better than one, ask anyone, it is fun fun fun :)

Alrighty, now that we have got all the cute puns out of the way; lets dive deep into how would a solution look like if you want to query OCI Object Storage from OCI PostgreSQL. For this recipe we will use an intermittent layer of self-managed Postgres in OCI VM and Oracle Autonomous 26ai.

In this walkthrough, OCI Database with PostgreSQL queries orders stored in a Parquet file in OCI Object Storage. Oracle Autonomous AI Database 26ai reads the file as an external table, and a self-managed PostgreSQL server on an OCI Ubuntu VM bridges the two databases. While there is extra layers and hops. Currently this is the only way to achieve something like this.

But there is so much more you can do with the self-managed Postgres in the VM and the Oracle Autonomous 26ai Database.

Some other use-cases :

• Join Operation and Relational data. From your OCI PostgreSQL database and Historical Data in your Autonomous Lakehouse being queried together. No need for any reverse ETL
• Query Oracle tables and views from OCI PostgreSQL. The same bridge can expose selected ADB operational data without first copying whole tables into PostgreSQL.
• Join application and lake data. An OCI PostgreSQL application can join its local orders with selected rows from an ADB external table over Parquet files.
• Validate a migration in stages. Keep Oracle and PostgreSQL data accessible during a transition, then compare counts, keys, and business aggregates before changing an application’s data source.
• Explore other external sources. ADB can also expose supported Apache Iceberg tables through SQL. The bridge lets you test those results from OCI PostgreSQL, while ADB remains responsible for accessing the underlying lake data

Data Flow for the Solution

Set Up the Two-Hop FDW Bridge: OCI PostgreSQL → Ubuntu PostgreSQL → Autonomous Database 26ai

Install PostgreSQL 14 Server on OCI VM with Ubuntu 22, and then install Oracle FDW extension on it.

The bridge is necessary for this particular design because managed OCI Database with PostgreSQL supports postgres_fdw but does not list oracle_fdw among its supported extensions.

The Ubuntu server runs oracle_fdw; OCI PostgreSQL reaches that server through postgres_fdw. We will verify the data at each hop and then query it alongside a local PostgreSQL table. The Parquet file stays in Object Storage while each query retrieves the rows it needs through the chain.

This method can be used to query both external tables and relational tables in Oracle Autonomous Database 26ai from OCI PostgreSQL.

sudo apt-get update
sudo apt-get install postgresql-14-oracle-fdw
sudo apt-get install libaio1

postgres=# CREATE EXTENSION oracle_fdw;
CREATE EXTENSION

TNS entry for ADB 26ai from OCI Console :
(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1521)(host=hr4mqmvq.adb.ap-melbourne-1.oraclecloud.com))(connect_data=(service_name=g9b8049aad9c64c_demoadw_high.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))

 -- Connect to ADB to test via sqlcl --
sql admin@'(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1521)(host=hr4mqmvq.adb.ap-melbourne-1.oraclecloud.com))(connect_data=(service_name=g9b8049aad9c64c_demoadw_high.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))'

-- Create a sample table called PAYMENT --
  CREATE TABLE "ADMIN"."PAYMENT"
   ( "PAYMENT_ID" NUMBER(19,0),
 "INVOICE_ID" NUMBER(19,0) NOT NULL ENABLE,
 "PAYMENT_REF" VARCHAR2(40 BYTE) NOT NULL ENABLE,
 "PAYMENT_DATE" TIMESTAMP (3) NOT NULL ENABLE,
 "AMOUNT_NZD" NUMBER(14,2) NOT NULL ENABLE,
 "PAYMENT_METHOD" VARCHAR2(30 BYTE) NOT NULL ENABLE,
 "PAYMENT_STATUS" VARCHAR2(30 BYTE) NOT NULL ENABLE
   ) ;
ALTER TABLE "ADMIN"."PAYMENT" ADD PRIMARY KEY ("PAYMENT_ID")
  USING INDEX  ENABLE;
ALTER TABLE "ADMIN"."PAYMENT" ADD UNIQUE ("PAYMENT_REF")
  USING INDEX  ENABLE;

INSERT INTO "ADMIN"."PAYMENT"
("PAYMENT_ID", "INVOICE_ID", "PAYMENT_REF", "PAYMENT_DATE", "AMOUNT_NZD", "PAYMENT_METHOD", "PAYMENT_STATUS")
VALUES (900001, 501, 'PAY-NZ-900001', TIMESTAMP '2026-10-01 09:15:00.123', 125.50, 'CARD', 'COMPLETED');

INSERT INTO "ADMIN"."PAYMENT"
("PAYMENT_ID", "INVOICE_ID", "PAYMENT_REF", "PAYMENT_DATE", "AMOUNT_NZD", "PAYMENT_METHOD", "PAYMENT_STATUS")
VALUES (900002, 502, 'PAY-NZ-900002', TIMESTAMP '2026-10-01 11:30:00.000', 89.99, 'BANK_TRANSFER', 'COMPLETED');

INSERT INTO "ADMIN"."PAYMENT"
("PAYMENT_ID", "INVOICE_ID", "PAYMENT_REF", "PAYMENT_DATE", "AMOUNT_NZD", "PAYMENT_METHOD", "PAYMENT_STATUS")
VALUES (900003, 503, 'PAY-NZ-900003', TIMESTAMP '2026-10-02 14:05:30.250', 240.00, 'CARD', 'PENDING');

INSERT INTO "ADMIN"."PAYMENT"
("PAYMENT_ID", "INVOICE_ID", "PAYMENT_REF", "PAYMENT_DATE", "AMOUNT_NZD", "PAYMENT_METHOD", "PAYMENT_STATUS")
VALUES (900004, 504, 'PAY-NZ-900004', TIMESTAMP '2026-10-02 16:45:10.500', 55.75, 'DIGITAL_WALLET', 'COMPLETED');

INSERT INTO "ADMIN"."PAYMENT"
("PAYMENT_ID", "INVOICE_ID", "PAYMENT_REF", "PAYMENT_DATE", "AMOUNT_NZD", "PAYMENT_METHOD", "PAYMENT_STATUS")
VALUES (900005, 505, 'PAY-NZ-900005', TIMESTAMP '2026-10-03 08:20:00.000', 310.25, 'BANK_TRANSFER', 'FAILED');

A. Create Self-Managed PostgreSQL 14 on Ubuntu VM FDW to Connect to ADB 26ai

psql -h 127.0.0.1 -U postgres -d postgres

 -- Create Remote Server for Oracle ADB 26ai using the TNS entry (Can only be done for TLS with ADB in a subnet with VCN and DNS connectivity) - 

CREATE SERVER adb
 FOREIGN DATA WRAPPER oracle_fdw
 OPTIONS (dbserver '(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1521)(host=hrxxx.adb.ap-melbourne-1.oraclecloud.com))(connect_data=(service_name=gxxxx_demoadw_high.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))');

-- Map Current User to ADMIN user in Autonomous 26ai -- 

CREATE USER MAPPING FOR CURRENT_USER
 SERVER adb
 OPTIONS (user 'ADMIN', password 'AdminUserPWD1234##');
 
-- ADMIN is Oracle user and it's password -- 
-- Create a local Postgres schema to import Oracle schema into --

CREATE SCHEMA oracle_remote;
 - Import Oracle Schema into Postgres Schema - 
IMPORT FOREIGN SCHEMA "ADMIN"
 FROM SERVER adb
 INTO oracle_remote;

 -- Query Remote Oracle Table --
SELECT * FROM oracle_remote.some_table LIMIT 10;

 -- List all remote tables in Oracle -- 
\det oracle_remote.*

OR

SELECT foreign_table_schema, foreign_table_name
FROM information_schema.foreign_tables
WHERE foreign_table_schema = 'oracle_remote'
ORDER BY foreign_table_name;

B. Create a new bridge user in the same self-managed PostgreSQL 14 on Ubuntu to connect from OCI PostgreSQL

psql -h 127.0.0.1 -U postgres -d postgres 

CREATE ROLE bridge_reader LOGIN;

-- In psql, set its password interactively:
\password bridge_reader

GRANT CONNECT ON DATABASE postgres TO bridge_reader;
GRANT USAGE ON SCHEMA oracle_remote TO bridge_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA oracle_remote TO bridge_reader;
GRANT USAGE ON FOREIGN SERVER adb TO bridge_reader;

CREATE USER MAPPING FOR bridge_reader
  SERVER adb
  OPTIONS (user 'ADMIN', password 'YourPWD1234##');
  
-- ADMIN is Oracle user and it's password --

Note : A dedicated read-only Oracle user is preferable to ADMIN. Test the full second hop while logged in to Ubuntu PostgreSQL as bridge_reader:

SELECT * FROM oracle_remote.payment LIMIT 1;

C. Allow OCI PostgreSQL to reach the PostgreSQL 14 on Ubuntu VM

Use the VM’s FQDN. In OCI networking, allow outbound TCP 5432 from the managed PostgreSQL system and inbound TCP 5432 to the VM, limited to the relevant subnet or security group.

On Ubuntu, make PostgreSQL listen on its private interface and add a pg_hba.conf entry for bridge_reader from the managed PostgreSQL subnet. For a TLS-enabled VM PostgreSQL server, that entry would be:

hostssl  postgres  bridge_reader  10.180.2/24  scram-sha-256

Restart PostgreSQL after changing listen_addresses or pg_hba.conf. Test that the VM accepts a connection from within the OCI PostgreSQL network before creating the foreign server.

10.180.2.168 --> IP for OCI PostgreSQL DBSystem

psql -h 10.180.2.168 -U postgres -D postgres

CREATE EXTENSION postgres_fdw;

-- Create Postgres Remote FDW from OCI Postgres to Self-Managed Postgres --
CREATE SERVER ubuntu_bridge
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (
    host 'postgres-14.sub06091142401.demovcn.oraclevcn.com',
    port '5432',
    dbname 'postgres',
    sslmode 'prefer',
    updatable 'true'
  );
  
postgres-14.sub06091142401.demovcn.oraclevcn.com -> FQDN of Ubuntu VM where Postgres 14 is installed

-- Create User Mapping from OCI PostgreSQL to Self-managed Postgres User--
CREATE USER MAPPING FOR CURRENT_USER
  SERVER ubuntu_bridge
  OPTIONS (user 'bridge_reader', password 'BridgeReaderPWD1234##');

## bridge_reader is the user on Ubuntu VM PostgreSQL ##

-- Create OCI PostgreSQL Schema --
CREATE SCHEMA adb_via_vm;

-- Import the Remote Self-Managed Postgres Schema into OCI PostgreSQL schema created above --
IMPORT FOREIGN SCHEMA oracle_remote
  FROM SERVER ubuntu_bridge
  INTO adb_via_vm;
  
-- Incase you get this error , make sure you use FQDN and not IP address --
ERROR:  could not connect to server "ubuntu_bridge"
DETAIL:  connection to server at "10.180.2.27", port 5432 failed (for outgoing connections - you must specify FQDN, not ip number): Connection timed out
 Is the server running on that host and accepting TCP/IP connections?
 drop server ubuntu_bridge cascade;

SELECT * FROM adb_via_vm.payment LIMIT 10;

The query flow for the about output is like below :

Application queries OCI PostgreSQL
  → postgres_fdw connects to PostgreSQL 14 on the Ubuntu VM
  → the VM queries its oracle_fdw foreign table
  → oracle_fdw connects to Autonomous Database 26ai
  → results return through the VM to OCI PostgreSQL and the application

Note : The two FDWs can add latency, and a stopped VM will make the OCI ADB26ai foreign table unavailable.

Refresh Schemas in case schema new tables are added to ADB 26ai

When a new ADB table is added

eg :
CREATE TABLE orders (
  order_id NUMBER(38) GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id NUMBER(38),
  product_id NUMBER(38),
  product_description VARCHAR2(500),
  order_delivery_address VARCHAR2(500),
  order_date_taken DATE,
  order_misc_notes VARCHAR2(500)
);

Import it at both hops, in order:

-- On Ubuntu PostgreSQL --
IMPORT FOREIGN SCHEMA "ADMIN"
  LIMIT TO (orders)
  FROM SERVER adb INTO oracle_remote;

GRANT SELECT ON oracle_remote.orders TO bridge_reader;

-- On OCI PostgreSQL --
IMPORT FOREIGN SCHEMA oracle_remote
  LIMIT TO (orders)
  FROM SERVER ubuntu_bridge INTO adb_via_vm;
  
 \det adb_via_vm.*
 
SELECT * FROM adb_via_vm.orders LIMIT 10;

Schema change like addition, deletion of columns in ADB 26ai. Drop and re-import the table

Eg : table productsin ADB 26ai changed, so we will drop and re-create the tabels in Ubuntu VM PostgreSQL and OCI PostgreSQL

Important Note : These are separate database transactions, so do this during a quiet period if applications query the table.

-- On the Ubuntu PostgreSQL database:
BEGIN;

DROP FOREIGN TABLE oracle_remote.product;

IMPORT FOREIGN SCHEMA "ADMIN"
  LIMIT TO (product)
  FROM SERVER adb
  INTO oracle_remote;

GRANT SELECT ON oracle_remote.product TO bridge_reader;

COMMIT;

SELECT * FROM oracle_remote.product LIMIT 1;

-- On OCI PostgreSQL:
BEGIN;

DROP FOREIGN TABLE adb_via_vm.product;

IMPORT FOREIGN SCHEMA oracle_remote
  LIMIT TO (product)
  FROM SERVER ubuntu_bridge
  INTO adb_via_vm;

-- Reapply any grants your application needs:
GRANT SELECT ON adb_via_vm.product TO your_app_role;

COMMIT;

SELECT * FROM adb_via_vm.product LIMIT 1;

Note : Check the actual table spelling with \det oracle_remote.*; if it appears as “PRODUCT”, use that quoted name instead. Dropping may fail if views or other objects depend on the foreign table. Do not add CASCADE blindly — identify those dependencies and plan to recreate them.

Join local OCI PostgreSQL table order and Oracle tables order table

SELECT
    pg.order_id,
    pg.customer_id,
    pg.product_description AS pg_description,
    ora.product_description AS oracle_description,
    pg.order_date_taken AS pg_date,
    ora.order_date_taken AS oracle_date
FROM public.orders AS pg
JOIN adb_via_vm.orders AS ora
  ON pg.order_id::numeric = ora.order_id
ORDER BY pg.order_id
LIMIT 20;

-- Complex Join 
-- Explain the SQL Query Path --
EXPLAIN (VERBOSE)
WITH pg_counts AS (
    SELECT customer_id, product_id, count(*) AS pg_orders
    FROM public.orders
    GROUP BY customer_id, product_id
),
oracle_counts AS (
    SELECT customer_id, product_id, count(*) AS oracle_orders
    FROM adb_via_vm.orders
    GROUP BY customer_id, product_id
)
SELECT
    coalesce(pg.customer_id::numeric, ora.customer_id) AS customer_id,
    coalesce(pg.product_id::numeric, ora.product_id) AS product_id,
    coalesce(pg.pg_orders, 0) AS pg_orders,
    coalesce(ora.oracle_orders, 0) AS oracle_orders
FROM pg_counts AS pg
FULL JOIN oracle_counts AS ora
  ON pg.customer_id::numeric = ora.customer_id
 AND pg.product_id::numeric = ora.product_id
ORDER BY customer_id, product_id;

-- Run the Complex SQL --
WITH pg_counts AS (
    SELECT customer_id, product_id, count(*) AS pg_orders
    FROM public.orders
    GROUP BY customer_id, product_id
),
oracle_counts AS (
    SELECT customer_id, product_id, count(*) AS oracle_orders
    FROM adb_via_vm.orders
    GROUP BY customer_id, product_id
)
SELECT
    coalesce(pg.customer_id::numeric, ora.customer_id) AS customer_id,
    coalesce(pg.product_id::numeric, ora.product_id) AS product_id,
    coalesce(pg.pg_orders, 0) AS pg_orders,
    coalesce(ora.oracle_orders, 0) AS oracle_orders
FROM pg_counts AS pg
FULL JOIN oracle_counts AS ora
  ON pg.customer_id::numeric = ora.customer_id
 AND pg.product_id::numeric = ora.product_id
ORDER BY customer_id, product_id;

Ref :
PostgreSQL Orders table : https://github.com/shadabshaukat/STAND/blob/main/postgres.sql
Oracle Orders table : https://github.com/shadabshaukat/STAND/blob/main/oracle.sql

Call External Table with Parquet in ADB from OCI PostgreSQL

1. Generate a parquet file which has structure similar to the Oracle orders table

"""Generate a 1,000-row Parquet orders fixture.
Requires pyarrow. Run: python3 generate_orders_parquet.py
"""

from datetime import date, timedelta
from pathlib import Path

import pyarrow as pa
import pyarrow.parquet as pq


OUTPUT = Path(__file__).with_name("orders_1000.parquet")
ROW_COUNT = 1_000
START_DATE = date(2026, 1, 1)


def main() -> None:
    order_ids = list(range(1, ROW_COUNT + 1))
    customer_ids = [((order_id - 1) % 120) + 1 for order_id in order_ids]
    product_ids = [((order_id * 7 - 1) % 80) + 1 for order_id in order_ids]

    schema = pa.schema(
        [
            pa.field("ORDER_ID", pa.int64(), nullable=False),
            pa.field("CUSTOMER_ID", pa.int32(), nullable=False),
            pa.field("PRODUCT_ID", pa.int32(), nullable=False),
            pa.field("PRODUCT_DESCRIPTION", pa.string(), nullable=False),
            pa.field("ORDER_DELIVERY_ADDRESS", pa.string(), nullable=False),
            pa.field("ORDER_DATE_TAKEN", pa.date32(), nullable=False),
            pa.field("ORDER_MISC_NOTES", pa.string(), nullable=True),
        ]
    )

    columns = [
        order_ids,
        customer_ids,
        product_ids,
        [f"Demo product {product_id:03d}" for product_id in product_ids],
        [
            f"Unit {((order_id - 1) % 25) + 1}, "
            f"{((order_id - 1) % 200) + 1} Example Street, Melbourne VIC 3000"
            for order_id in order_ids
        ],
        [START_DATE + timedelta(days=(order_id - 1) % 273) for order_id in order_ids],
        [
            None if order_id % 10 == 0 else f"Synthetic Parquet order {order_id:04d}"
            for order_id in order_ids
        ],
    ]

    table = pa.Table.from_arrays(
        [pa.array(values, type=field.type) for values, field in zip(columns, schema)],
        schema=schema,
    )
    pq.write_table(table, OUTPUT, compression="snappy", row_group_size=250)

    read_back = pq.read_table(OUTPUT)
    assert read_back.schema.equals(schema)
    assert read_back.num_rows == ROW_COUNT
    assert read_back.column("ORDER_ID").to_pylist() == order_ids
    print(f"Created {OUTPUT} with {read_back.num_rows} rows")


if __name__ == "__main__":
    main()

2. Upload the Parquet file to your OCI Object Storage bucket

Fill in your OCI username, auth token, namespace, and bucket in the ADB external table SQL below, and run in ADB 26ai

BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'ORDERS_PARQUET_CRED',
    username        => 'OracleIdentityCloudService/yourname@email.com',
    password        => '*******'
  );
END;
/

BEGIN
  DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
    table_name      => 'ORDERS_PARQUET_EXT',
    credential_name => 'ORDERS_PARQUET_CRED',
    file_uri_list   => 'https://objectstorage.ap-melbourne-1.oraclecloud.com/n/<namespace>/b/<bucket>/o/orders_1000.parquet',
    column_list     => 'ORDER_ID NUMBER(38), CUSTOMER_ID NUMBER(38), PRODUCT_ID NUMBER(38), PRODUCT_DESCRIPTION VARCHAR2(500), ORDER_DELIVERY_ADDRESS VARCHAR2(500), ORDER_DATE_TAKEN DATE, ORDER_MISC_NOTES VARCHAR2(500)',
    format          => '{"type":"parquet"}'
  );
END;
/


-- Expected: one row, 1000 / 1 / 1000.
SELECT COUNT(*) AS ROW_COUNT, MIN(ORDER_ID) AS FIRST_ID, MAX(ORDER_ID) AS LAST_ID
FROM ORDERS_PARQUET_EXT;

SELECT ORDER_ID, CUSTOMER_ID, PRODUCT_ID, ORDER_DATE_TAKEN
FROM ORDERS_PARQUET_EXT
ORDER BY ORDER_ID
FETCH FIRST 5 ROWS ONLY;

-- If the Ubuntu Oracle mapping uses another Oracle user, grant it SELECT here.
-- GRANT SELECT ON ORDERS_PARQUET_EXT TO <ORACLE_READ_USER>;

3. Run the Ubuntu SQL to import it as oracle_remote.orders_parquet_ext

-- IMPORT FOREIGN SCHEMA creates the PostgreSQL foreign table.
-- The default oracle_fdw case folding makes ORDERS_PARQUET_EXT lowercase.
IMPORT FOREIGN SCHEMA "ADMIN"
  LIMIT TO (orders_parquet_ext)
  FROM SERVER adb
  INTO oracle_remote
  OPTIONS (readonly 'true');

GRANT USAGE ON SCHEMA oracle_remote TO bridge_reader;
GRANT SELECT ON oracle_remote.orders_parquet_ext TO bridge_reader;

-- Expected: 1000 / 1 / 1000.
SELECT COUNT(*) AS row_count, MIN(order_id) AS first_id, MAX(order_id) AS last_id
FROM oracle_remote.orders_parquet_ext;

SELECT order_id, customer_id, product_id, order_date_taken
FROM oracle_remote.orders_parquet_ext
ORDER BY order_id
LIMIT 5;

4. Run the OCI PostgreSQL to import it as adb_via_vm.orders_parquet_ext

-- This imports the Ubuntu foreign table as another foreign table in OCI PG.
IMPORT FOREIGN SCHEMA oracle_remote
  LIMIT TO (orders_parquet_ext)
  FROM SERVER ubuntu_bridge
  INTO adb_via_vm;

-- Expected: 1000 / 1 / 1000.
SELECT COUNT(*) AS row_count, MIN(order_id) AS first_id, MAX(order_id) AS last_id
FROM adb_via_vm.orders_parquet_ext;

SELECT order_id, customer_id, product_id, product_description, order_date_taken
FROM adb_via_vm.orders_parquet_ext
ORDER BY order_id
LIMIT 10;

-- Example join to your local OCI PostgreSQL orders table. This is meaningful
-- only when both sources use the same customer_id and product_id values.
SELECT
  local_order.order_id AS local_order_id,
  parquet_order.order_id AS parquet_order_id,
  local_order.customer_id,
  local_order.product_id,
  parquet_order.product_description AS parquet_description
FROM public.orders AS local_order
JOIN adb_via_vm.orders_parquet_ext AS parquet_order
  ON local_order.customer_id::numeric = parquet_order.customer_id
 AND local_order.product_id::numeric = parquet_order.product_id
ORDER BY local_order.order_id, parquet_order.order_id
LIMIT 20;

-- Inspect the first hop's SQL without executing the query:
EXPLAIN (VERBOSE)
SELECT order_id, customer_id, product_id
FROM adb_via_vm.orders_parquet_ext
WHERE order_id BETWEEN 1 AND 10;

Write Data from OCI PostgreSQL back to OCI Object Storage using the same flow

In Oracle Autonomous Database :

CREATE TABLE PG_ORDERS_STAGE (
    ORDER_ID               NUMBER(10,0) PRIMARY KEY,
    CUSTOMER_ID            NUMBER(10,0),
    PRODUCT_ID             NUMBER(10,0),
    PRODUCT_DESCRIPTION    VARCHAR2(500 CHAR),
    ORDER_DELIVERY_ADDRESS VARCHAR2(500 CHAR),
    ORDER_DATE_TAKEN       DATE,
    ORDER_MISC_NOTES       VARCHAR2(500 CHAR)
);

Import it at both hops, in order:

-- On Ubuntu PostgreSQL --
IMPORT FOREIGN SCHEMA "ADMIN"
  LIMIT TO (PG_ORDERS_STAGE)
  FROM SERVER adb
  INTO oracle_remote
  OPTIONS (readonly 'false');

GRANT SELECT ON oracle_remote.PG_ORDERS_STAGE TO bridge_reader;
GRANT INSERT ON oracle_remote.PG_ORDERS_STAGE TO bridge_reader;
GRANT UPDATE ON oracle_remote.PG_ORDERS_STAGE TO bridge_reader;

--Check INSERT privilige --
SELECT has_table_privilege(
  'bridge_reader',
  'oracle_remote.pg_orders_stage',
  'INSERT'
);

ALTER SERVER adb
  OPTIONS (ADD isolation_level 'read_committed');

-- On OCI PostgreSQL --
IMPORT FOREIGN SCHEMA oracle_remote
  LIMIT TO (PG_ORDERS_STAGE)
  FROM SERVER ubuntu_bridge INTO adb_via_vm;
  
 \det adb_via_vm.*
 
SELECT * FROM adb_via_vm.PG_ORDERS_STAGE LIMIT 10;

Important Note : oracle_fdw documentation specifically describes read_committed as a workaround for ORA-08177 on inserts.

For a production setup, I’d use a separate Oracle FDW server for the staging load and keep the existing read path at its current isolation level. Because changing serveradb affects every foreign table using that server. READ COMMITTED can produce inconsistent results if a query scans the same Oracle table more than once while data changes.

In OCI PostgreSQL, copy the rows across the two hops :

Table public.orders in OCI PostgreSQL can be created from here : https://github.com/shadabshaukat/STAND/blob/main/postgres.sql

psql -h 10.180.2.168 -U postgres -d postgres

 INSERT INTO adb_via_vm.pg_orders_stage (
  order_id, customer_id, product_id,
  product_description, order_delivery_address,
  order_date_taken, order_misc_notes
)
SELECT
  order_id, customer_id, product_id,
  product_description, order_delivery_address,
  order_date_taken, order_misc_notes
FROM public.orders;

SELECT * FROM adb_via_vm.PG_ORDERS_STAGE LIMIT 10;

In ADB check the row count and export the table to OCI Object storage :

SQL> select count(*) from pg_orders_stage;

   COUNT(*)
___________
      10000


BEGIN
  DBMS_CLOUD.EXPORT_DATA(
    credential_name => 'ORDERS_PARQUET_CRED',
    file_uri_list   => 'https://objectstorage.ap-melbourne-1.oraclecloud.com/n/<namespace>/b/<bucket>/o/pg_orders_export',
    query           => 'SELECT * FROM ADMIN.PG_ORDERS_STAGE',
    format          => JSON_OBJECT('type' VALUE 'parquet',
                                   'compression' VALUE 'snappy')
  );
END;
/

You can see the exported Parquet files for the table in OCI Object storage bucket

Where could this pattern help?

1. Order reconciliation: Compare recent data in OCI PostgreSQL with historical or partner data exposed through ADB.
2. Archive exploration: Keep older Parquet data in Object Storage while allowing occasional SQL access from an existing PostgreSQL application or reporting tool.
3. Application enrichment: Combine a small set of external Oracle attributes with local PostgreSQL data for a specific workflow.
4. Phased modernization: Let teams test Oracle and lake data from a PostgreSQL-facing application before deciding what should be replicated, cached, or migrated permanently.

Conclusion

This example shows that OCI PostgreSQL can read Parquet data in Object Storage through two foreign data wrappers and an ADB external table. Each component has a clear role: ADB reads the file, the Ubuntu PostgreSQL server bridges Oracle and PostgreSQL, and OCI PostgreSQL remains the application’s query endpoint. The design is most useful for selective queries, exploration, and transition work.

Introduction

Recently we were planning to move a busy PostgreSQL 14 cluster from on-premises to OCI Database with PostgreSQL for a customer with a very large and busy PostgreSQL cluster.

The database was 10 TB and produces approximately 100+ GB of write-ahead log (WAL) every hour. A dump and restore alone would leave an unacceptable outage: even an ideal 1 Gbps link takes more than 22 hours to transmit 10 TB, before compression, indexes, restore work and network overhead. During a 48-hour migration, the source might generate another 4.8 TB of WAL

We need the application to continue writing while we copy the database. OCI GoldenGate gives us an enterprise replication service with managed deployments, connection management, Extract and Replicat processes, durable trails, checkpoints and operational monitoring. We combined these capabilities of Goldengate with a PostgreSQL snapshot-based initial load

pg_dump establishes the baseline, GoldenGate captures the subsequent transactions, and Replicat applies them after the restore. We reserve a short, controlled write pause for the final cutover. this pattern does not require HANDLECOLLISIONS which can lead to potential lost or duplicate transactions.

Setup

Important Note : Everything documented here can be run on not just a self-managed Postgres on-premise, but also PostgreSQL running on a managed service like Digital Ocean, RDS, Aurora, Azure or GCP. As long as the source database has native Postgres logical replication available and a replication role user which can create a replication slot, this should work on any other source platform.

Our source was PostgreSQL 14 on an Ubuntu VM at 10.180.2.27, our target was managed OCI PostgreSQL 10.180.2.199, and our OCI GoldenGate deployment was postgres-ogg at 10.180.2.176:443

On-prem PostgreSQL 14                      OCI GoldenGate                       OCI PostgreSQL
source 10.180.2.27                         postgres-ogg                         target 10.180.2.199
         │                                        │                                      ▲
         ├── pgoutput slot ───────────────────> PGEXT ── dv trail ── PGRP ─────────────┤
         │                                                                               │
         └── exported snapshot ── pg_dump ── migration host ── pg_restore ───────────────┘

The execution order is very critical : We create the publication, register the permanent `pgoutput` slot, export a temporary snapshot and its matching LSN, launch the dump against that snapshot, start Extract at that LSN, release the temporary slot after the dump imports the snapshot, restore, and finally start Replicat. We keep source writes flowing during the dump and restore.

High-Level Migration Sequence

We divide the migration into four phases: prepare, copy and capture, catch up, and cut over.

Phase 1 — Prepare the Environment

• 📐 Step 1 — Plan Migration Capacity and Network Connectivity
• ☁️ Step 2 — Create the OCI GoldenGate Deployment and Database Connections
• 🛠️ Step 3 — Build the Source Schema, Load Data, and Validate Replication Readiness
• 🔑 Step 4 — Configure GoldenGate TRANDATA and Verify Table Keys
• 📋 Step 5 — Create the Publication with Explicit Table Names

Phase 2 — Copy the Baseline and Capture Changes

• 📸 Step 6 — Register the Permanent Slot and Export a Snapshot with Its Matching LSN
• 💾 Step 7 — Run the Snapshot Backup While Source Writes Continue
• ▶️ Step 8 — Start GoldenGate Extract at the Matching LSN
• 📥 Step 9 — Restore the Initial Backup to OCI PostgreSQL

Phase 3 — Apply Changes and Validate

• 🔄 Step 10 — Start Replicat from the Beginning of the New Trail
• 📊 Step 11 — Monitor WAL, Replication Slots, Apply Progress, and Data Consistency
• 🧪 Step 12 — Validate CDC with Batched Updates, Deletes, and Upserts

Throughout the online migration, starting with slot creation, we monitor retained WAL, disk space, slot activity, long transactions, vacuum pressure, trail growth, replication lag, and target performance.

Phase 4 — Cut Over and Complete

• ⏸️ Step 13 — Pause Source Writes, Drain Replication, and Reconcile Data
• 🔢 Step 14 — Synchronize and Verify All 13 Sequences
• 🚀 Step 15 — Switch Application Connections and Enable Target Writes
• 🧹 Step 16 — Clean Up the GoldenGate and Database Lab Resources

Stop writers, resolve in-flight transactions, drain Replicat, reconcile data, synchronize sequences, and switch applications. Sequence synchronization is essential

Prerequisites and Steps for Setting Up the OCI Environment

1. Size the migration and its network paths

For an on-premises data centre, we would use private connectivity such as redundant FastConnect circuits, plan a VPN contingency, test return routes and DNS, and allow TCP/5432 only from the required private endpoints.

The 100 GB/hour WAL rate is about 27.8 MB/s, or 222 Mbps, of source WAL generation, which is approximately 2.4 TB per day. It is not a measured GoldenGate trail rate. We would collect peak WAL rate, Extract output rate, trail bytes per hour, Replicat apply rate and restore throughput in a rehearsal. The network link must carry the copy plus ongoing CDC with headroom; our target must apply faster than the new transactions arrive.

We would also reserve storage for the base copy, restored data and indexes, source WAL, GoldenGate trails and a recovery interval. If Extract stopped for 48 hours, source WAL exposure at the stated rate would approach 4.8 TB before any overhead. If Extract keeps pace but Replicat falls behind, the backlog moves into GoldenGate trails instead. OCI Golgengate currently allocates 1 Gbps of deployment bandwidth, and 500 GB of storage per consumed GoldenGate OCPU.

2. Create the GoldenGate deployment and both database connections

For a fresh environment, we use the OCI Console for OCI setup. In *GoldenGate → Deployments → Create deployment*, we select a “Data replication” deployment with PostgreSQL technology, the appropriate compartment, a private subnet and enough OCPU/storage for the measured CDC backlog.

For a fresh deployment, we create two connection resources in GoldenGate → Connections → Create connection.

Create 2 connections :

1. Source OnPremise Database to which you have connectivity from OCI to OnPremise via private network with high bandwidth and low latency
2. Target OCI PostgreSQL Database

Assign to the Postgres Goldengate Deployment

-- Source DB Connectivity Check --
psql -h 10.180.2.27 -U postgres -d postgres

-- TargetDB Connectivity Check --
psql -h 10.180.2.199 -U postgres -d postgres

SSH forward maps local `localhost:8443` to the private GoldenGate private endpoint `10.180.2.176:443`

ssh -fNT -v -L 8443:10.180.2.176:443 \
  opc@<bastion-host-ip> -i ~/.ssh/mydemo_vcn.priv &

We will create a Goldgentgate 26ai container to provide the local AdminClient, while the actual replication deployment remains in OCI.

docker run -d \
  --name ogg26ai \
  --hostname ogg26ai \
  --restart unless-stopped \
  -p 443:443 \
  --env-file "$HOME/goldengate/26ai/.env" \
  -v "$HOME/goldengate/26ai/u02:/u02" \
  -v "$HOME/goldengate/26ai/u03:/u03" \
  -v "$HOME/goldengate/26ai/cert:/etc/nginx/cert" \
  --log-opt max-size=50m \
  --log-opt max-file=5 \
  container-registry.oracle.com/goldengate/goldengate-oracle-free:latest

docker exec -it ogg26ai bash
adminclient

CONNECT https://host.docker.internal:8443 DEPLOYMENT postgres-ogg AS oggadmin PASSWORD YourPassword1234#_!

OGG> INFO CREDENTIALSTORE

Default domain: OracleGoldenGate

  Alias: postgres14ubuntuvm
  Userid: postgres@ip-10-180-2-27.ociggsvc.oraclevcn.com:5432/postgres;sslMode=disable

  Alias: pggoldengatedemo
  Userid: postgres@ip-10-180-2-199.ociggsvc.oraclevcn.com:5432/postgres;sslMode=prefer;validateServerCertificate=0

OGG> DBLOGIN USERIDALIAS postgres14ubuntuvm DOMAIN OracleGoldenGate

OGG> DBLOGIN USERIDALIAS pggoldengatedemo DOMAIN OracleGoldenGate

3. Build and validate the PostgreSQL Source DB

To simulate the customers schema we will use a slightly different version of DVDRental schema in our source database

SQL files which will be our Schema :

• 00_create_schema.sql creates schemas, types, 15 tables, constraints, indexes, 13 sequences, and the dvd_lab.generate_data(integer) procedure. It never generates rows.
• 01_load_source.sql validates -v rows, sets UTC for the transaction, and calls the generator. We run this script only on the source.

Refer to Appendix at the end of the article for both code files : 00_create_schema.sql 01_load_source.sql

The schema Gist defines dvdrental.year as a domain for film.release_year.GoldenGate’s PostgreSQL support list excludes domain types. We preserve the 1901–2155 rule with an integer column and a check constraint before loading data, publishing tables or starting CDC.

The schema also has a `text` column, an enum and a tsvector in `dvdrental.film`; Oracle Goldengate documents PostgreSQL array capture/delivery beginning with GoldenGate 26ai, and our OCI Goldengate identified build is 23.26.2.0.1.

Our 15-table comparison confirmed baseline and final data parity, while the live CDC test changed rental, payment and country rows. For production, we would also exercise representative CDC updates to the array, enum and `tsvector`

To Check what Postgres data types are supported by Oracle Goldengate refer this document : https://docs.oracle.com/en/database/goldengate/core/26/coredoc/prepare-postgresql-understanding-whats-supported.html

The Gist leaves four foreign-key columns without a leading index. Oracle requires indexes on foreign-key columns for Parallel Replicat. We include the four indexes before the dump in this repeatable runbook, so the restore carries them. In the actual lab we added them to both databases after the restore when the final audit found the omission.

Oracle Parallel Replicat guidance : https://docs.oracle.com/en/database/goldengate/core/26/coredoc/replicat-parallel-replicat.html

On a busy production source, we would schedule any missing index builds with the appropriate online PostgreSQL method and measure the extra I/O before migration day.

# On the bastion as opc, connected to the source over TCP

git clone https://github.com/shadabshaukat/ogg_dvd_lab/

cd ogg_dvd_lab/

psql -X -v ON_ERROR_STOP=1 -h 10.180.2.27 -U postgres -d postgres \
  -f /home/opc/ogg_dvd_lab/00_create_schema.sql

psql -X -v ON_ERROR_STOP=1 -h 10.180.2.27 -U postgres -d postgres <<'SQL'
BEGIN;
ALTER TABLE dvdrental.film
  ALTER COLUMN release_year TYPE integer USING release_year::integer;
ALTER TABLE dvdrental.film
  ADD CONSTRAINT film_release_year_range
  CHECK (release_year BETWEEN 1901 AND 2155);
DROP DOMAIN dvdrental.year;
COMMIT;
SQL

psql -X -v ON_ERROR_STOP=1 -h 10.180.2.27 -U postgres -d postgres <<'SQL'
CREATE INDEX IF NOT EXISTS film_original_language_idx
  ON dvdrental.film (original_language_id);
CREATE INDEX IF NOT EXISTS inventory_store_idx
  ON dvdrental.inventory (store_id);
CREATE INDEX IF NOT EXISTS staff_address_idx
  ON dvdrental.staff (address_id);
CREATE INDEX IF NOT EXISTS store_address_idx
  ON dvdrental.store (address_id);
SQL

-- Initial Load of Data --
psql -X -v ON_ERROR_STOP=1 -v rows=100000 \
  -h 10.180.2.27 -U postgres -d postgres \
  -f /home/opc/ogg_dvd_lab/01_load_source.sql

We verify that both ends use database/user ‘postgres’, that all 15 replicated tables have primary keys, and that source logical decoding is ready:

psql -h 10.180.2.27 -U postgres -d postgres

SELECT current_database(), current_user, version();
SHOW wal_level;
SHOW max_replication_slots;
SHOW max_wal_senders;
SHOW max_slot_wal_keep_size;
SHOW track_commit_timestamp;

-- Check Table with no PK --
SELECT
    n.nspname AS schema_name,
    c.relname AS table_name
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n
    ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'p')
  AND n.nspname NOT LIKE 'pg_%'
  AND n.nspname <> 'information_schema'
  AND NOT EXISTS (
      SELECT 1
      FROM pg_catalog.pg_constraint con
      WHERE con.conrelid = c.oid
        AND con.contype = 'p'
  )
ORDER BY schema_name, table_name;

-- Check Replica identity --
SELECT n.nspname, c.relname, c.relreplident,
       pg_get_userbyid(c.relowner) AS owner
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r' AND n.nspname NOT LIKE 'pg_%'
  AND n.nspname <> 'information_schema'
ORDER BY c.relname;

We require wal_level=logical , two available logical slots during the snapshot handoff plus recovery headroom, enough WAL senders and direct PostgreSQL connections rather than a transaction-pooling proxy.

In production, we would have to inventory the DDL, extensions, large objects, unsupported types, triggers and scheduled jobs before the migration freeze.

Goldengate PostgreSQL: Supported Data Types, Objects, and Operations

4. Goldengate Source Database Setup

Add Trandata on Source

DBLOGIN USERIDALIAS postgres14ubuntuvm DOMAIN OracleGoldenGate

ADD TRANDATA dvdrental.actor
ADD TRANDATA dvdrental.address
ADD TRANDATA dvdrental.category
ADD TRANDATA dvdrental.city
ADD TRANDATA dvdrental.country
ADD TRANDATA dvdrental.customer
ADD TRANDATA dvdrental.film
ADD TRANDATA dvdrental.film_actor
ADD TRANDATA dvdrental.film_category
ADD TRANDATA dvdrental.inventory
ADD TRANDATA dvdrental.language
ADD TRANDATA dvdrental.payment
ADD TRANDATA dvdrental.rental
ADD TRANDATA dvdrental.staff
ADD TRANDATA dvdrental.store

INFO TRANDATA dvdrental.actor
INFO TRANDATA dvdrental.address
INFO TRANDATA dvdrental.category
INFO TRANDATA dvdrental.city
INFO TRANDATA dvdrental.country
INFO TRANDATA dvdrental.customer
INFO TRANDATA dvdrental.film
INFO TRANDATA dvdrental.film_actor
INFO TRANDATA dvdrental.film_category
INFO TRANDATA dvdrental.inventory
INFO TRANDATA dvdrental.language
INFO TRANDATA dvdrental.payment
INFO TRANDATA dvdrental.rental
INFO TRANDATA dvdrental.staff
INFO TRANDATA dvdrental.store

Before publishing a table, ensure UPDATE and DELETE operations have an unambiguous key.

Preferred order:
1. Primary key.
2. Non-null unique index used as replica identity.
3. GoldenGate KEYCOLS backed by a genuinely unique target index.
4. REPLICA IDENTITY FULL only as a temporary last resort.

eg :
ALTER TABLE public.example
  REPLICA IDENTITY USING INDEX example_replica_key;
  
If not then :

ALTER TABLE public.example
  REPLICA IDENTITY FULL;

For your no-primary-key tables, do not use FULL where duplicate rows can exist and UPDATE/DELETE occurs. The same key or lookup index must exist on OCI before Replicat starts.

5. Publish the tables we will use for the dump and replicate

We will create the publication on the source before registering the pgoutput Extract. This order is mandatory for GoldenGate pgoutput.

The publication contains 15 tables and enables insert, update, delete and truncate operations.

psql -h 10.180.2.27 -U postgres -d postgres

CREATE PUBLICATION ogg_migration_pub FOR TABLE
  dvdrental.actor,
  dvdrental.address,
  dvdrental.category,
  dvdrental.city,
  dvdrental.country,
  dvdrental.customer,
  dvdrental.film,
  dvdrental.film_actor,
  dvdrental.film_category,
  dvdrental.inventory,
  dvdrental.language,
  dvdrental.payment,
  dvdrental.rental,
  dvdrental.staff,
  dvdrental.store
WITH (publish = 'insert, update, delete, truncate');

SELECT schemaname, tablename
FROM pg_publication_tables
WHERE pubname = 'ogg_migration_pub'
ORDER BY schemaname, tablename;

SELECT pubname, pubinsert, pubupdate, pubdelete, pubtruncate
FROM pg_publication
WHERE pubname = 'ogg_migration_pub';

6. Register the permanent slot, then export the snapshot and its LSN

We return to the AdminClient, log into the source alias and register PGEXT explicitly with pgoutput.

Registration creates the permanent logical replication slot; it does not add or start the Extract process. We will inspect the slot on the source before the temporary snapshot begins:

DBLOGIN USERIDALIAS postgres14ubuntuvm DOMAIN OracleGoldenGate

REGISTER EXTRACT PGEXT PGPLUGINTYPE pgoutput

SELECT slot_name, plugin, slot_type, temporary, active,
       restart_lsn, confirmed_flush_lsn
FROM pg_replication_slots
WHERE database = current_database()
ORDER BY slot_name;

       slot_name        |  plugin  | slot_type | temporary | active | restart_lsn | confirmed_flush_lsn
------------------------+----------+-----------+-----------+--------+-------------+---------------------
 pgext_e8f172784cad044e | pgoutput | logical   | f         | f      | 0/8C4BD8B0  | 0/8C4BD8E8

Expected: one new permanent logical slot using pgoutput, initially inactive. In our lab, GoldenGate reported the slot name pgext_e8f172784cad044e

We open a separate replication-protocol connection from the bastion to the source in a persistent terminal session. It stays open and idle after it returns a temporary slot, snapshot_name and consistent_point; closing that session invalidates the exported snapshot name:

tmux new -s pgdump

psql -X 'host=10.180.2.27 port=5432 dbname=postgres user=postgres replication=database'

CREATE_REPLICATION_SLOT gg_snap_tmp TEMPORARY LOGICAL pgoutput EXPORT_SNAPSHOT;

  slot_name  | consistent_point |    snapshot_name    | output_plugin
-------------+------------------+---------------------+---------------
 gg_snap_tmp | 0/8C4BDA08       | 00000003-0000B7E7-1 | pgoutput

Important Note : We record the two returned values as a pair. We give the snapshot name to pg_dump --snapshot; we position Extract at the matching consistent_point. We do not run another command in this exporter connection. PostgreSQL removes the temporary slot when we close the connection.

snapshot_name will be used in the pg_dump

consistent_point will be used to steer the Goldengate extract to start at that LSN

7. pgdump of Snapshot while writes continue on source

The below command is to simulate writes happening on the source database while we run the pg_dump and pg_restore

Paste this into the bastion prompt. It asks for the password once:

(
set -Eeuo pipefail
read -rsp "PostgreSQL source password: " PGPASSWORD
echo
export PGPASSWORD

control=/home/opc/ogg_dvd_lab/ogg_dvd_writer.run
touch "$control"
batch=0

while test -e "$control" && test "$batch" -lt 600; do
  psql -X -w -v ON_ERROR_STOP=1 -v rows=100 \
    -h 10.180.2.27 -U postgres -d postgres \
    -f /home/opc/ogg_dvd_lab/01_load_source.sql \
    >> /home/opc/ogg_dvd_lab/ogg_dvd_writer.log 2>&1
  batch=$((batch + 1))
  sleep 2
done
)

If you open another session and check the slots size, you will see it incrementing due to the writes happening on the database :

psql -h 10.180.2.27 -U postgres -d postgres

SELECT slot_name,
       plugin,
       temporary,
       active,
       restart_lsn,
       confirmed_flush_lsn,
       pg_size_pretty(
         pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)
       ) AS retained_wal,
       wal_status,
       pg_size_pretty(safe_wal_size) AS safe_wal_remaining
FROM pg_replication_slots
WHERE database = current_database();

 slot_name              |  plugin  | temporary | active | restart_lsn | confirmed_flush_lsn | retained_wal | wal_status | 
------------------------+----------+-----------+--------+-----------------+---------------------+--------------+---------
 pgext_e8f172784cad044e | pgoutput | f         | f      | 0/8C4BD8B0  | 0/8C4BD8E8          | 18 MB        | reserved   |
 gg_snap_tmp            | pgoutput | t         | t      | 0/8C4BD9D0  | 0/8C4BDA08          | 18 MB        | reserved   |

Take a snapshot with id00000003–0000B7E7–1 as in Step 6

PGAPPNAME=ogg_lab_dump pg_dump \
  --host=10.180.2.27 --port=5432 --username=postgres --dbname=postgres \
  --format=directory --jobs=4 --compress=6 --lock-wait-timeout=5s \
  --verbose \
  --snapshot='00000003-0000B7E7-1' \
  --schema=dvdrental --schema=dvd_lab \
  --quote-all-identifiers --no-owner --no-acl \
  --no-publications --no-subscriptions \
  --file=/home/opc/ogg_dvd_lab/dump > /home/opc/ogg_dvd_lab/dump.log 2>&1

Keep the snapshot-exporting session open until the dump finishes. If the dump directory already exists from an earlier attempt, choose a new output directory.

We confirm that pg_dump exits successfully, review its log, and inspect the archive before closing the snapshot-exporting session.

To monitor from a separate session run :

-- Check Active Sessions for the Dump --
SELECT pid,
       application_name,
       state,
       xact_start,
       backend_xmin,
       wait_event_type,
       wait_event,
       left(query, 120) AS query
FROM pg_stat_activity
WHERE application_name = 'ogg_lab_dump'
ORDER BY pid;

-- No rows for above query means pg_dump backup has completed --

-- Check for Retained WAL continously -- 
SELECT slot_name,
       plugin,
       temporary,
       active,
       restart_lsn,
       confirmed_flush_lsn,
       pg_size_pretty(
         pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)
       ) AS retained_wal,
       wal_status,
       pg_size_pretty(safe_wal_size) AS safe_wal_remaining
FROM pg_replication_slots
WHERE database = current_database();

We set a five-second dump lock-wait limit for the rehearsal; a production db lock timeout needs its own review. We run this job in a persistent bastion session, then use another AdminClient session for Extract.

You can add the Extract while the pg_dump is running.

8. Add and start Extract at the matching LSN

We will use the consistent_point LSN 0/8C4BDA08 from Step 6.

DBLOGIN USERIDALIAS postgres14ubuntuvm DOMAIN OracleGoldenGate

ADD EXTRACT PGEXT, TRANLOG, LSN 0/8C4BDA08

ADD EXTTRAIL dv, EXTRACT PGEXT

EDIT PARAMS PGEXT

EXTRACT PGEXT
USERIDALIAS postgres14ubuntuvm DOMAIN OracleGoldenGate
EXTTRAIL dv
TRANLOGOPTIONS STREAMINGOPTIONS (publication_names="ogg_migration_pub";)
GETTRUNCATES
TABLE dvdrental.actor;
TABLE dvdrental.address;
TABLE dvdrental.category;
TABLE dvdrental.city;
TABLE dvdrental.country;
TABLE dvdrental.customer;
TABLE dvdrental.film;
TABLE dvdrental.film_actor;
TABLE dvdrental.film_category;
TABLE dvdrental.inventory;
TABLE dvdrental.language;
TABLE dvdrental.payment;
TABLE dvdrental.rental;
TABLE dvdrental.staff;
TABLE dvdrental.store;

START EXTRACT PGEXT
INFO EXTRACT PGEXT, DETAIL
VIEW REPORT PGEXT
STATS EXTRACT PGEXT

Important Note : Once the extract is started the permanent slot created by Goldengate will lower the WAL retention.

For a long production dump, we would start Extract during the dump and release the exporter after snapshot import is completed. The gg_snap_tmp slot must disappear; the GoldenGate permanent slot must remain active and advancing.

9. Restore the dump to OCI PostgreSQL

PGAPPNAME=ogg_lab_restore pg_restore \
  --host=10.180.2.199 --port=5432 \
  --username=postgres --dbname=postgres \
  --jobs=4 --exit-on-error --verbose \
  --no-owner --no-acl --no-tablespaces \
  /home/opc/ogg_dvd_lab/dump > /home/opc/ogg_dvd_lab/restore.log 2>&1

To monitor from a separate session run :

psql -h 10.180.2.199 -U postgres -d postgres

SELECT pid,
       application_name,
       state,
       xact_start,
       backend_xmin,
       wait_event_type,
       wait_event,
       left(query, 120) AS query
FROM pg_stat_activity
WHERE application_name = 'ogg_lab_restore'
ORDER BY pid;

In case if any extensions are not available on OCI PostgreSQL as compared to Source then create a list

pg_restore -l /home/opc/ogg_dvd_lab/dump > restore-list.txt

Edit the restore-list.txt file using a text editor (like nano or vim). Find the lines that correspond to CREATE EXTENSION or the specific extensions you want to skip, and delete or comment out those lines (by placing a ; at the start of the line)

Run pg_restore with the modified list using the -L flag

PGAPPNAME=ogg_lab_restore pg_restore \
  --host=10.180.2.199 --port=5432 \
  --username=postgres --dbname=postgres \
  --jobs=4 --exit-on-error --verbose \
  -L restore-list.txt \
  --no-owner --no-acl --no-tablespaces \
  /home/opc/ogg_dvd_lab/dump > /home/opc/ogg_dvd_lab/restore.log 2>&1

10. Add Replicat from the beginning of the new trail

DBLOGIN USERIDALIAS pggoldengatedemo DOMAIN OracleGoldenGate

ADD REPLICAT PGRP, PARALLEL, EXTTRAIL dv, CHECKPOINTTABLE dvdrental.dvd_chkpt

EDIT PARAMS PGRP

REPLICAT PGRP
USERIDALIAS pggoldengatedemo DOMAIN OracleGoldenGate
MAP_PARALLELISM 4
APPLY_PARALLELISM 8
REPERROR (DEFAULT, ABEND)
GETTRUNCATES
MAP dvdrental.actor, TARGET dvdrental.actor;
MAP dvdrental.address, TARGET dvdrental.address;
MAP dvdrental.category, TARGET dvdrental.category;
MAP dvdrental.city, TARGET dvdrental.city;
MAP dvdrental.country, TARGET dvdrental.country;
MAP dvdrental.customer, TARGET dvdrental.customer;
MAP dvdrental.film, TARGET dvdrental.film;
MAP dvdrental.film_actor, TARGET dvdrental.film_actor;
MAP dvdrental.film_category, TARGET dvdrental.film_category;
MAP dvdrental.inventory, TARGET dvdrental.inventory;
MAP dvdrental.language, TARGET dvdrental.language;
MAP dvdrental.payment, TARGET dvdrental.payment;
MAP dvdrental.rental, TARGET dvdrental.rental;
MAP dvdrental.staff, TARGET dvdrental.staff;
MAP dvdrental.store, TARGET dvdrental.store;

VIEW PARAMS PGRP

START REPLICAT PGRP

INFO ALL

VIEW REPORT PGRP

STATS REPLICAT PGRP

LAG REPLICAT PGRP

We start at the beginning of the fresh trail. We omit BEGIN NOW, HANDLECOLLISIONS The snapshot/LSN pair gives us an exact instantiation boundary, and REPERROR (DEFAULT, ABEND) stops a conflict for diagnosis. We can tune Parallel Replicat from measured target apply rate and foreign-key behavior later.

11. Monitor the publication, slots, WAL and apply stream

Some important SQL queries to run at source

Source SQL Queries :

psql -h 10.180.2.27 -U postgres -d postgres

-- 1. Count the source tables included in our migration publication.
-- Expected: 15 tables for this lab.
SELECT count(*) AS published_tables
FROM pg_publication_tables
WHERE pubname = 'ogg_migration_pub';


-- 2. Check replication slots for the current source database.
-- restart_lsn determines the oldest WAL position each slot still needs.
-- confirmed_flush_lsn shows capture acknowledgement, not target apply progress.
-- retained_wal estimates the WAL span retained by each slot; these spans can overlap.
-- safe_wal_remaining is NULL when retention is unlimited or the slot is lost.
SELECT slot_name,
       plugin,
       temporary,
       active,
       restart_lsn,
       confirmed_flush_lsn,
       pg_size_pretty(
         pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)
       ) AS retained_wal,
       wal_status,
       pg_size_pretty(safe_wal_size) AS safe_wal_remaining
FROM pg_replication_slots
WHERE database = current_database();


-- 3. Sample the source cluster's current WAL position and cumulative WAL bytes.
-- Compare samples over time to measure WAL generation.
-- Check stats_reset before calculating differences; cumulative statistics can lag.
SELECT pg_current_wal_lsn() AS current_lsn,
       wal_bytes,
       stats_reset
FROM pg_stat_wal;


-- 4. Measure the physical size of files in the source pg_wal directory.
-- This is WAL-file usage, not available filesystem space or just slot-retained WAL.
SELECT pg_size_pretty(coalesce(sum(size), 0)) AS physical_pg_wal_files
FROM pg_ls_waldir();


-- 5. Check whether logical decoding spills transaction data from memory to disk.
-- Rising spill counters can help explain capture I/O pressure or slower decoding.
-- Use the permanent Extract slot name from the current migration run.
SELECT slot_name,
       spill_txns,
       spill_count,
       spill_bytes,
       stats_reset
FROM pg_stat_replication_slots
WHERE slot_name = 'pgext_e8f172784cad044e';


-- 6. Inspect open source transactions, including idle-in-transaction sessions.
-- Transaction age and backend_xmin help identify sessions delaying vacuum cleanup.
-- Wait details and blocking_pids help identify lock contention during the dump.
SELECT pid,
       application_name,
       state,
       extract(epoch FROM clock_timestamp() - xact_start)::bigint
         AS xact_age_seconds,
       backend_xmin,
       wait_event_type,
       wait_event,
       pg_blocking_pids(pid) AS blocking_pids
FROM pg_stat_activity
WHERE datname = current_database()
  AND pid <> pg_backend_pid()
  AND xact_start IS NOT NULL
ORDER BY xact_start;


-- 7. Monitor estimated dead tuples and autovacuum activity in our source schema.
-- Increasing dead tuples can indicate that cleanup is falling behind.
-- n_dead_tup is an estimate, not a direct measurement of table bloat.
SELECT relname,
       n_dead_tup,
       last_autovacuum,
       autovacuum_count
FROM pg_stat_user_tables
WHERE schemaname = 'dvdrental'
ORDER BY n_dead_tup DESC;


-- 8. Check the cumulative deadlock count for the current source database.
-- Compare with our baseline to detect new deadlocks during the migration.
SELECT deadlocks
FROM pg_stat_database
WHERE datname = current_database();

-- 9. Run this same query on both source and target to get exact row counts:
SELECT 'actor' AS table_name, count(*) AS row_count FROM dvdrental.actor
UNION ALL
SELECT 'address', count(*) FROM dvdrental.address
UNION ALL
SELECT 'category', count(*) FROM dvdrental.category
UNION ALL
SELECT 'city', count(*) FROM dvdrental.city
UNION ALL
SELECT 'country', count(*) FROM dvdrental.country
UNION ALL
SELECT 'customer', count(*) FROM dvdrental.customer
UNION ALL
SELECT 'film', count(*) FROM dvdrental.film
UNION ALL
SELECT 'film_actor', count(*) FROM dvdrental.film_actor
UNION ALL
SELECT 'film_category', count(*) FROM dvdrental.film_category
UNION ALL
SELECT 'inventory', count(*) FROM dvdrental.inventory
UNION ALL
SELECT 'language', count(*) FROM dvdrental.language
UNION ALL
SELECT 'payment', count(*) FROM dvdrental.payment
UNION ALL
SELECT 'rental', count(*) FROM dvdrental.rental
UNION ALL
SELECT 'staff', count(*) FROM dvdrental.staff
UNION ALL
SELECT 'store', count(*) FROM dvdrental.store
ORDER BY table_name;

Target Database SQL queries :

psql -h 10.180.2.199 -U postgres -d postgres

-- 1. Monitor active restore and replication sessions, open transactions and waits.
-- blocking_pids identifies sessions preventing another session from progressing.
-- GoldenGate sessions may use a generic application name, so we do not filter by it.
SELECT pid,
       application_name,
       usename,
       state,
       extract(epoch FROM clock_timestamp() - xact_start)::bigint
         AS xact_age_seconds,
       wait_event_type,
       wait_event,
       pg_blocking_pids(pid) AS blocking_pids,
       left(query, 200) AS query
FROM pg_stat_activity
WHERE datname = current_database()
  AND pid <> pg_backend_pid()
  AND state <> 'idle'
ORDER BY xact_start NULLS LAST;


-- 2. Monitor table data currently being loaded through COPY during pg_restore.
-- bytes_total can be zero when PostgreSQL does not know the incoming stream size.
-- An empty result means no COPY is currently visible, not that restore succeeded.
SELECT pid,
       relid::regclass AS table_name,
       command,
       type,
       tuples_processed,
       pg_size_pretty(bytes_processed) AS bytes_processed,
       pg_size_pretty(bytes_total) AS bytes_total
FROM pg_stat_progress_copy
WHERE datname = current_database()
ORDER BY pid;


-- 3. Monitor index creation during the restore's post-data phase.
-- These counters describe the current index-build phase, not the entire restore.
SELECT pid,
       relid::regclass AS table_name,
       index_relid::regclass AS index_name,
       command,
       phase,
       blocks_done,
       blocks_total,
       tuples_done,
       tuples_total
FROM pg_stat_progress_create_index
WHERE datname = current_database()
ORDER BY pid;


-- 4. Monitor table activity and storage growth during restore and CDC apply.
-- DML counters include activity from all sessions and are cumulative.
-- estimated_rows is approximate; use COUNT(*) for final row-count reconciliation.
-- total_size includes the table, its indexes and TOAST storage.
SELECT relname AS table_name,
       n_live_tup AS estimated_rows,
       n_tup_ins AS inserts,
       n_tup_upd AS updates,
       n_tup_del AS deletes,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_stat_user_tables
WHERE schemaname = 'dvdrental'
ORDER BY relname;


-- 5. Monitor database size, connections, rollbacks, deadlocks and temporary files.
-- Compare cumulative counters between samples and check stats_reset.
-- Temporary-file growth can indicate sorting or other work spilling to disk.
-- database_size does not report available or provisioned storage.
SELECT datname,
       pg_size_pretty(pg_database_size(datname)) AS database_size,
       numbackends AS connections,
       xact_commit,
       xact_rollback,
       deadlocks,
       temp_files,
       pg_size_pretty(temp_bytes) AS temp_data_written,
       stats_reset
FROM pg_stat_database
WHERE datname = current_database();


-- 6. Monitor dead tuples, vacuum cleanup and planner-statistics maintenance.
-- After the initial restore, ANALYZE should populate statistics for restored tables.
-- last_analyze records manual ANALYZE; last_autoanalyze records automatic ANALYZE.
SELECT relname AS table_name,
       n_dead_tup,
       last_vacuum,
       last_autovacuum,
       last_analyze,
       last_autoanalyze
FROM pg_stat_user_tables
WHERE schemaname = 'dvdrental'
ORDER BY relname;


-- 7. Check that foreign keys exist and have been validated after the full restore.
-- Expected for our complete lab schema: 22 foreign keys and zero unvalidated.
-- A custom restore list that omits constraints will require completing them later.
SELECT count(*) AS foreign_keys,
       count(*) FILTER (WHERE NOT c.convalidated) AS unvalidated_foreign_keys
FROM pg_constraint c
JOIN pg_namespace n ON n.oid = c.connamespace
WHERE n.nspname = 'dvdrental'
  AND c.contype = 'f';


-- 8. Find indexes that are not valid or not ready to receive changes.
-- Review any returned rows after index creation and restore have finished.
-- An empty result does not prove that every expected index was restored.
SELECT t.relname AS table_name,
       i.indexrelid::regclass AS index_name,
       i.indisprimary AS is_primary_key,
       i.indisunique AS is_unique,
       i.indisvalid AS is_valid,
       i.indisready AS is_ready
FROM pg_index i
JOIN pg_class t ON t.oid = i.indrelid
JOIN pg_namespace n ON n.oid = t.relnamespace
WHERE n.nspname = 'dvdrental'
  AND (NOT i.indisvalid OR NOT i.indisready)
ORDER BY t.relname, i.indexrelid;


-- 9. Inspect target sequence values without advancing them.
-- last_value can include values reserved by sequence caching.
-- This is an overview; final sequence validation also needs is_called,
-- source sequence state and target maximum IDs before enabling target writes.
SELECT sequencename,
       last_value,
       increment_by,
       cache_size
FROM pg_sequences
WHERE schemaname = 'dvdrental'
ORDER BY sequencename;

After Parallel Replicat creates its checkpoint tables, this additional query works with the checkpoint layout verified in our lab:

-- 10. Inspect the source positions recorded by each Parallel Replicat applier.
-- Compare successive samples to see whether the saved checkpoints advance.
-- For cutover, verify every applier against the final source barrier.
-- This is a GoldenGate implementation-specific table, not a PostgreSQL system view.
SELECT applier_id,
       lwm_csn AS applied_source_position,
       seqno AS trail_sequence,
       rba AS trail_byte_position,
       trail_name
FROM dvdrental.dvd_chkpt_pgrp
ORDER BY applier_id;

Some important Goldengate commands :

INFO EXTRACT PGEXT, DETAIL
STATS EXTRACT PGEXT, TOTAL
VIEW REPORT PGEXT

INFO REPLICAT PGRP, DETAIL
STATS REPLICAT PGRP, TOTAL
LAG REPLICAT PGRP
VIEW REPORT PGRP

Conclusion

What We Validated:

1. Initial and final row counts
2. CDC operations tested
3. Reconciliation results
4. Sequence verification
5. Any measured timings.

We make a large PostgreSQL migration manageable by coupling the initial copy and the CDC stream to one PostgreSQL snapshot boundary. The exported snapshot gives the dump a consistent baseline; its paired LSN tells GoldenGate where to start. Source applications continue to write while we copy and restore, and a measured final fence gives us a clean point for barrier verification and sequence synchronization.

Appendix A — Sequences Synchronization

Sequences are not handled by PostgreSQL logical replication and has to be handled outside. Please read : https://www.postgresql.org/docs/18/logical-replication-restrictions.html

For our dvdrental schema, we need to synchronize 13 sequences. film_actor and film_category use composite primary keys and have no sequences.

Where this step fits

Restore the initial backup
        ↓
Start Replicat and let it catch up
        ↓
Stop source application writes, jobs and the test writer
        ↓
Wait for in-flight transactions and final changes to reach the target
        ↓
SYNCHRONIZE THE 13 SEQUENCES
        ↓
Verify sequence values
        ↓
Switch applications and enable target writes

We perform this during the final cutover window. Replicat can remain running; target application writers must remain stopped.

The replicated rows contain their generated IDs, but those inserts do not advance the corresponding target sequences.

1. Export the final source sequence states

Run this from the bastion, after source writes have stopped and Replicat has applied the final changes:

# Export only the current states of our 13 source sequences.
pg_dump \
  --host=10.180.2.27 --port=5432 \
  --username=postgres --dbname=postgres \
  --data-only --no-owner --no-acl \
  --table=dvdrental.actor_actor_id_seq \
  --table=dvdrental.address_address_id_seq \
  --table=dvdrental.category_category_id_seq \
  --table=dvdrental.city_city_id_seq \
  --table=dvdrental.country_country_id_seq \
  --table=dvdrental.customer_customer_id_seq \
  --table=dvdrental.film_film_id_seq \
  --table=dvdrental.inventory_inventory_id_seq \
  --table=dvdrental.language_language_id_seq \
  --table=dvdrental.payment_payment_id_seq \
  --table=dvdrental.rental_rental_id_seq \
  --table=dvdrental.staff_staff_id_seq \
  --table=dvdrental.store_store_id_seq \
  --file=/home/opc/ogg_dvd_lab/final_sequences.sql

This produces setval() statements containing each sequence’s last_value and is_called. It does not export table rows.

We generate this file freshly at cutover because source sequence values continue advancing during the migration.

2. Compare sequence states before applying

Run the following in psql on both source and target. It discovers the sequences owned by columns in dvdrental and displays their states alongside each table’s maximum ID:

-- Inspect every owned sequence in our schema without advancing it.
-- Expected: 13 sequence results.
SELECT format(
  'SELECT %L AS sequence_name, '
  || '(SELECT max(%I) FROM %I.%I) AS max_id, '
  || 'last_value, is_called FROM %s;',
  pg_get_serial_sequence(
    format('%I.%I', n.nspname, c.relname), a.attname
  ),
  a.attname,
  n.nspname,
  c.relname,
  pg_get_serial_sequence(
    format('%I.%I', n.nspname, c.relname), a.attname
  )
)
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_attribute a ON a.attrelid = c.oid
WHERE n.nspname = 'dvdrental'
  AND c.relkind = 'r'
  AND a.attnum > 0
  AND NOT a.attisdropped
  AND pg_get_serial_sequence(
        format('%I.%I', n.nspname, c.relname), a.attname
      ) IS NOT NULL
ORDER BY c.relname
\gexec

For our sequences, which increment by one:

• is_called = true: the next generated value is last_value + 1.
• is_called = false: the next generated value is last_value.
• The planned next value must exceed the target table’s maximum ID.
• If a target sequence has already advanced beyond the source, we resolve that difference before applying the file; we do not move it backward.

We preserve source sequence state rather than simply setting every sequence to MAX(id), because unsuccessful inserts and rolled-back transactions can consume sequence values.

3. Apply the reviewed states to the target

After the export succeeds and the comparisons above are satisfactory, run this from the bastion:

# Apply the final source sequence states to OCI PostgreSQL.
psql -X -v ON_ERROR_STOP=1 \
  --host=10.180.2.199 --port=5432 \
  --username=postgres --dbname=postgres \
  --file=/home/opc/ogg_dvd_lab/final_sequences.sql

4. Verify, then enable target writes

Rerun the inspection query on both databases. For this fresh target, we expect all 13 last_value / is_called pairs to match, with safe next values.

Keep source writers stopped throughout. Reconnect any target application sessions that previously consumed sequence values before enabling writes, so they cannot reuse old cached allocations.

This sequence synchronization is the final database preparation step before applications start writing to OCI PostgreSQL.

Appendix B — Test Update, Delete, Upserts

The script operates on dvdrental.payment, which has no referencing child tables in your schema, so deleting payments preserves the foreign keys.

/home/opc/ogg_dvd_lab/02_dml_source.sql

Upserts use fresh sequence values for new rows and ON CONFLICT … DO UPDATE for existng rows.

Run this command from the bastion. Change the first three variables as required. The password is requested once.

(
set -Eeuo pipefail

operation=update
rows=100
batches=600

read -rsp "PostgreSQL source password: " PGPASSWORD
echo
export PGPASSWORD

control="/home/opc/ogg_dvd_lab/ogg_dvd_${operation}.run"
log="/home/opc/ogg_dvd_lab/ogg_dvd_${operation}.log"

touch "$control"
batch=0

while test -e "$control" && test "$batch" -lt "$batches"; do
  printf '\nBatch %s: %s\n' "$((batch + 1))" "$operation" >> "$log"

  PGAPPNAME="ogg_lab_${operation}" \
  psql -X -w -v ON_ERROR_STOP=1 \
    -v operation="$operation" -v rows="$rows" \
    -h 10.180.2.27 -U postgres -d postgres \
    -f /home/opc/ogg_dvd_lab/02_dml_source.sql \
    >> "$log" 2>&1

  batch=$((batch + 1))
  sleep 2
done
)

Each batch commits separately. If fewer than the requested number of unlocked payments remain, the script reports an error and the loop stops.

Use delete or upsert in these filenames when running those modes.

Performance note: Random selection uses ORDER BY random(), which scans the payment table. Logged timings include selection and source execution; monitor GoldenGate lag and operation counters separately to assess replication performance.

Upsert advances the source payment sequence, so retain the final sequence synchronization before target writes begin.

Appendix C — Workstation setup and lab cleanup

We use the following one-time workstation setup for the management forward and Docker AdminClient. During the rebuild, we reused the existing container and forward. The forward later closed and was not relaunched; our database checks continued from the bastion.

ssh -fNT -v -L 8443:10.180.2.176:443 opc@<bastion-host-ip> \
  -i ~/.ssh/mydemo_vcn.priv

docker run -d \
  --name ogg26ai \
  --hostname ogg26ai \
  --restart unless-stopped \
  -p 443:443 \
  --env-file "$HOME/goldengate/26ai/.env" \
  -v "$HOME/goldengate/26ai/u02:/u02" \
  -v "$HOME/goldengate/26ai/u03:/u03" \
  -v "$HOME/goldengate/26ai/cert:/etc/nginx/cert" \
  --log-opt max-size=50m \
  --log-opt max-file=5 \
  container-registry.oracle.com/goldengate/goldengate-oracle-free:latest

Cleanup for Goldengate :

STOP REPLICAT PGRP
STOP EXTRACT PGEXT
DBLOGIN USERIDALIAS pggoldengatedemo DOMAIN OracleGoldenGate
DELETE REPLICAT PGRP
DELETE CHECKPOINTTABLE dvdrental.dvd_chkpt !
DBLOGIN USERIDALIAS postgres14ubuntuvm DOMAIN OracleGoldenGate
DELETE EXTRACT PGEXT
UNREGISTER EXTRACT PGEXT
PURGE EXTTRAIL dv
PURGE EXTTRAIL pg
INFO ALL

We used ! only to suppress confirmation on DELETE CHECKPOINTTABLE. We did not use it on DELETE REPLICAT, where it can bypass database cleanup. We then removed the old publication, fixture schemas and the retired public.orders example from both lab databases:

psql -X -v ON_ERROR_STOP=1 -h 10.180.2.27 -U postgres -d postgres <<'SQL'
DROP PUBLICATION IF EXISTS ogg_migration_pub;
DROP SCHEMA IF EXISTS dvd_lab CASCADE;
DROP SCHEMA IF EXISTS dvdrental CASCADE;
SQL

psql -X -v ON_ERROR_STOP=1 -h 10.180.2.199 -U postgres -d postgres <<'SQL'
DROP PUBLICATION IF EXISTS ogg_migration_pub;
DROP SCHEMA IF EXISTS dvd_lab CASCADE;
DROP SCHEMA IF EXISTS dvdrental CASCADE;
SQL

Our post-cleanup checks showed no source logical slots, no old target checkpoint tables and no fixture tables or sequences. The new run could therefore register a new slot, create an empty trail and restore into empty fixture schemas.

References

• OCI GoldenGate service overview and deployment management
• Oracle Goldengate Support Matrix
• Create a GoldenGate data-replication deployment, PostgreSQL Server connection, assign and test connections
• Oracle precise instantiation with pg_dump, pgoutput Extract requirements, PostgreSQL type and operation support
• OCI PostgreSQL migration guidance and Warm Standby
• PostgreSQL replication protocol, pg_dump, pg_restore, replication-slot monitoring, sequence functions
• Our DVD rental schema and generator and source load script

With pglogical, we can explicitly select the tables and sequences to replicate, build a one-to-many topology from multiple regions, and retain options for cross-version migrations or replication beyond a single managed-service boundary. This makes it well suited to an architecture where the regional databases are deliberately prepared and operated as independent PostgreSQL systems.

While OCI PostgreSQL Warm Standby is best suited for service-managed disaster recovery: it asynchronously streams WAL for the complete database system. pglogical is selective logical distribution rather than a complete physical DR copy : pglogical does not replace backups or managed failover, and DDL changes, monitoring, sequence handling, and promotion must be planned separately.

Ref : https://blogs.oracle.com/cloud-infrastructure/reducing-postgresql-replication-latency

In this blog, we’ll build a one-way, one-to-many logical replication topology on Oracle Cloud Infrastructure (OCI) Database with PostgreSQL. Sydney will be the only writable source, while Melbourne and Singapore will receive changes as application-read-only subscribers.

                    publication-syd 
                         --------
                        | Sydney |
                         --------
                          Writer
                    |               |
                    |               |
                    v               v
        subscription_mel      subscription_sgp
            ---------             -----------
           | Melbourne|          | Singapore|
            ---------             ----------
              Reader                 Reader

The replication flow is one-way:

   - Applications write in Sydney.
   - Melbourne and Singapore are read-only for the application role.
   - Each target pulls from the Sydney provider over a private, TLS-verified connection.
   - We initialize Melbourne completely before starting Singapore.
   - The default replication set contains both the application tables and their sequences.

Prerequisites and Steps for Setting up OCI Environment

1. SQL files which will be our Schema :

• 00_create_schema.sql creates schemas, types, 15 tables, constraints, indexes, 13 sequences, and the dvd_lab.generate_data(integer) procedure. It never generates rows.

https://gist.github.com/shadabshaukat/9500bc7458a16e1733f8681aad10775a

• 01_load_source.sql validates -v rows, sets UTC for the transaction, and calls the generator. This will be run in Sydney only

https://gist.github.com/shadabshaukat/66e8c7ecc1222a6faff6d86d8510c982

Refer to Appendix at the end of the article to download code files for 00_create_schema.sql
01_load_source.sql

1.1 OCI PostgreSQL Setup

The below are the 3 OCI PostgreSQL DBSystems. Sydney is primary writer node and Melbourne and Singapore are the Reader target nodes

1.2 Other prerequisites:

• non-overlapping VCN CIDRs;
• a bastion or equivalent private administrative path in each region;
• OCI permissions for database configuration, networking, DNS, logging, and Network Path Analyzer;
• the OCI CA certificate for each client connection;
• a maintenance window for settings that require a restart.
• Copy 00_create_schema.sql to /home/opc on all three bastions.
• Copy 01_load_source.sql to Sydney only; do not distribute it to MEL or SGP. This makes accidental target-side data generation less likely.

2. OCI configuration, Routing, Security, and Private DNS Setup

2.1 PostgreSQL configuration on all three DB systems

Apply an OCI PostgreSQL configuration that permits pglogical, then wait for every DB system to return to ACTIVE.

oci.admin_enabled_extensions includes pglogical
shared_preload_libraries includes pglogical
wal_level = logical
track_commit_timestamp = on
max_replication_slots >= 2 on SYD, plus headroom
max_wal_senders >= 2 on SYD, plus headroom
max_worker_processes: target at least 16 if the OCI shape permits it

Run on every database:

SELECT name, setting, unit, pending_restart
FROM pg_settings
WHERE name IN (
  'wal_level',
  'track_commit_timestamp',
  'shared_preload_libraries',
  'max_worker_processes',
  'max_replication_slots',
  'max_wal_senders'
)
ORDER BY name;

Expected: pglogical is allowed and preloaded, wal_level is logical, track_commit_timestamp is on, and pending_restart is false after the maintenance action.

2.2 Cross-region network

Build and verify two independent peerings:

• SYD VCN ↔ MEL VCN
• SYD VCN ↔ SGP VCN

Each path needs:

1. DRGs and a distinct remote peering connection on each side.
2. Reciprocal subnet routes, including the return route from Sydney.
3. Stateful TCP/5432 ingress to the Sydney database subnet from the MEL and SGP private subnet CIDRs.
4. Target-side TCP/5432 egress to the Sydney subnet if the existing egress policy does not already permit it.
5. Non-overlapping VCN CIDRs.
6. Review of the route table and every security list/NSG attached to the actual database subnets.
7. DNS resolution does not create the network path. If DNS works but psql times out, use OCI Network Path Analyzer and VCN flow logs.

2.3 Private DNS on OCI

For one-way replication, only MEL and SGP need the Sydney private zone:

Zone: 7gtysdaouav7agdgmh3mdeekdz3fwq.postgresql.ap-sydney-1.oci.oraclecloud.com
Record: primary
Type: A
Address: 10.150.2.229

Attach the private view to each target VCN resolver and publish it. Sydney does not need custom MEL/SGP records for this one-way design, but it is good to get across and add DNS for all zones to all private resolvers, in-case you need to create multi-master replication in the future.

DNS Source setup in Sydney

10.150.2.229
primary.7gtysdaouav7agdgmh3mdeekdz3fwq.postgresql.ap-sydney-1.oci.oraclecloud.com

## Add Private Zone for Melbourne in Private DNS View of Sydney VCN as 'mniso4nt6sfnz3de4ysxauopiel5hq.postgresql.ap-melbourne-1.oci.oraclecloud.com' and add A-record 'primary' with IP of DBSystem of Melbourne '10.180.2.168'
## Add Private Zone for Singapore in Private DNS View of Sydney VCN as 'bts5ctx56gtoofohtkqcc33dinc2uq.postgresql.ap-singapore-1.oci.oraclecloud.com' and add A-record 'primary' with IP of DBSystem of Singapore '10.140.2.64'

DNS Target setup in Melbourne

10.180.2.168
primary.mniso4nt6sfnz3de4ysxauopiel5hq.postgresql.ap-melbourne-1.oci.oraclecloud.com

## Add Private Zone for Sydney in Private DNS View of Melbourne VCN as '7gtysdaouav7agdgmh3mdeekdz3fwq.postgresql.ap-sydney-1.oci.oraclecloud.com' and add A-record 'primary' with IP of DBSystem of Sydney '10.150.2.229'
## Add Private Zone for Singapore in Private DNS View of Melbourne VCN as 'bts5ctx56gtoofohtkqcc33dinc2uq.postgresql.ap-singapore-1.oci.oraclecloud.com' and add A-record 'primary' with IP of DBSystem of Singapore '10.140.2.64'

DNS Target setup in Singapore

10.140.2.64
primary.bts5ctx56gtoofohtkqcc33dinc2uq.postgresql.ap-singapore-1.oci.oraclecloud.com

## Add Private Zone for Sydney in Private DNS View of Singapore VCN as '7gtysdaouav7agdgmh3mdeekdz3fwq.postgresql.ap-sydney-1.oci.oraclecloud.com' and add A-record 'primary' with IP of DBSystem of Sydney '10.150.2.229'
## Add Private Zone for Melbourne in Private DNS View of Singapore VCN as 'mniso4nt6sfnz3de4ysxauopiel5hq.postgresql.ap-melbourne-1.oci.oraclecloud.com' and add A-record 'primary' with IP of DBSystem of Melbourne '10.180.2.168'

For this article : Run from both target bastions (MEL and SGP):

getent hosts primary.7gtysdaouav7agdgmh3mdeekdz3fwq.postgresql.ap-sydney-1.oci.oraclecloud.com

psql -X \
  "host=primary.7gtysdaouav7agdgmh3mdeekdz3fwq.postgresql.ap-sydney-1.oci.oraclecloud.com port=5432 dbname=postgres user=postgres sslmode=require connect_timeout=10" \
  -v ON_ERROR_STOP=1 \
  -c "SELECT current_database(), current_user, inet_server_addr();"

Expected: the hostname resolves to 10.150.2.229 (Sydney DBSystem IP) and the TLS connection succeeds.

3. Common database setup on SYD, MEL, and SGP

Run this section separately on all three DB systems.

Use the provisioned OCI administrator, not dvdrental_user.

-- Sydney --
psql -X \
  "host=primary.7gtysdaouav7agdgmh3mdeekdz3fwq.postgresql.ap-sydney-1.oci.oraclecloud.com port=5432 dbname=postgres user=postgres sslmode=require" \
  -v ON_ERROR_STOP=1

-- Melbourne --
psql -X \
  "host=primary.mniso4nt6sfnz3de4ysxauopiel5hq.postgresql.ap-melbourne-1.oci.oraclecloud.com port=5432 dbname=postgres user=postgres sslmode=require" \
  -v ON_ERROR_STOP=1

-- Singapore --
psql -X \
  "host=primary.bts5ctx56gtoofohtkqcc33dinc2uq.postgresql.ap-singapore-1.oci.oraclecloud.com port=5432 dbname=postgres user=postgres sslmode=require" \
  -v ON_ERROR_STOP=1

Run once on all 3 DB system in SYD, MEL, SGP :

SELECT rolname, rolreplication
FROM pg_roles
WHERE rolname = 'postgres';

-- Run only if the preceding result rolreplication is false.
ALTER ROLE postgres WITH REPLICATION;

CREATE ROLE dvdrental_user LOGIN NOREPLICATION;
\password dvdrental_user

CREATE DATABASE dvdrental_lab;
\connect dvdrental_lab

SELECT current_database(), current_user;
-- Must be: dvdrental_lab | postgres

CREATE EXTENSION pglogical;

GRANT EXECUTE
ON FUNCTION pg_catalog.pg_replication_origin_session_setup(text)
TO postgres;

GRANT EXECUTE
ON FUNCTION pg_catalog.pg_replication_origin_session_reset()
TO postgres;

-- Verify while still connected to dvdrental_lab:

SELECT
  current_database(),
  current_user,
  has_function_privilege(
    'postgres',
    'pg_catalog.pg_replication_origin_session_setup(text)',
    'EXECUTE'
  ) AS can_origin_setup,
  has_function_privilege(
    'postgres',
    'pg_catalog.pg_replication_origin_session_reset()',
    'EXECUTE'
  ) AS can_origin_reset;

SELECT extname, extversion
FROM pg_extension
WHERE extname = 'pglogical';

Expected: database dvdrental_lab, user postgres, both booleans true, and one pglogical extension row.

Do not install pglogical in database postgres. Extensions and the function grants are database-local.

4. Initial schema and data load on the Sydney source

Run on the Sydney DB:

psql -X \
  "host=primary.7gtysdaouav7agdgmh3mdeekdz3fwq.postgresql.ap-sydney-1.oci.oraclecloud.com port=5432 dbname=dvdrental_lab user=postgres sslmode=require" \
  -v ON_ERROR_STOP=1 \
  -f /home/opc/00_create_schema.sql

psql -X \
  "host=primary.7gtysdaouav7agdgmh3mdeekdz3fwq.postgresql.ap-sydney-1.oci.oraclecloud.com port=5432 dbname=dvdrental_lab user=postgres sslmode=require" \
  -v ON_ERROR_STOP=1 \
  -v rows=100000 \
  -f /home/opc/01_load_source.sql

The first command creates objects only.
The second validates rows before any cast, opens one transaction, sets UTC with SET LOCAL TIME ZONE ‘UTC’, generates the source rows, and commits atomically.

Grant the source application role access to existing objects and set prospective defaults:

GRANT CONNECT ON DATABASE dvdrental_lab TO dvdrental_user;
GRANT USAGE ON SCHEMA dvdrental TO dvdrental_user;

GRANT SELECT, INSERT, UPDATE, DELETE
ON ALL TABLES IN SCHEMA dvdrental
TO dvdrental_user;

GRANT USAGE, SELECT
ON ALL SEQUENCES IN SCHEMA dvdrental
TO dvdrental_user;

ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA dvdrental
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO dvdrental_user;

ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA dvdrental
  GRANT USAGE, SELECT ON SEQUENCES TO dvdrental_user;

Keep the generator owner-only. Do not grant dvdrental_user access to dvd_lab or the pglogical schema.

Verify source object counts:

SELECT count(*) AS tables
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'dvdrental'
  AND c.relkind IN ('r', 'p');

SELECT count(*) AS owned_sequences
FROM pg_class s
JOIN pg_namespace n ON n.oid = s.relnamespace
WHERE n.nspname = 'dvdrental'
  AND s.relkind = 'S';

Expected: 15 tables and 13 sequences.

5. Schema-only setup on the Melbourne target

Run on the Melbourne bastion. Do not pass rows and do not run 01_load_source.sql.

psql -X \
  "host=primary.mniso4nt6sfnz3de4ysxauopiel5hq.postgresql.ap-melbourne-1.oci.oraclecloud.com port=5432 dbname=dvdrental_lab user=postgres sslmode=require" \
  -v ON_ERROR_STOP=1 \
  -f /home/opc/00_create_schema.sql

Grant read-only application access:

psql -X \
  "host=primary.mniso4nt6sfnz3de4ysxauopiel5hq.postgresql.ap-melbourne-1.oci.oraclecloud.com port=5432 dbname=dvdrental_lab user=postgres sslmode=require" \
  -v ON_ERROR_STOP=1

GRANT CONNECT ON DATABASE dvdrental_lab TO dvdrental_user;
GRANT USAGE ON SCHEMA dvdrental TO dvdrental_user;
GRANT SELECT ON ALL TABLES IN SCHEMA dvdrental TO dvdrental_user;

ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA dvdrental
  GRANT SELECT ON TABLES TO dvdrental_user;

Do not grant table DML, sequence use, generator execution, REPLICATION, replication-origin functions, or pglogical catalog access to the target application user.

Run the empty-target assertion in dvdrental_lab:

DO $empty_check$
DECLARE
  r record;
  v_table_count integer;
  v_has_rows boolean;
BEGIN
  SELECT count(*)
  INTO v_table_count
  FROM pg_class c
  JOIN pg_namespace n ON n.oid = c.relnamespace
  WHERE n.nspname = 'dvdrental'
    AND c.relkind IN ('r', 'p');

  IF v_table_count <> 15 THEN
    RAISE EXCEPTION 'Expected 15 dvdrental tables, found %', v_table_count;
  END IF;

  FOR r IN
    SELECT n.nspname, c.relname
    FROM pg_class c
    JOIN pg_namespace n ON n.oid = c.relnamespace
    WHERE n.nspname = 'dvdrental'
      AND c.relkind IN ('r', 'p')
    ORDER BY c.relname
  LOOP
    EXECUTE format(
      'SELECT EXISTS (SELECT 1 FROM %I.%I LIMIT 1)',
      r.nspname,
      r.relname
    ) INTO v_has_rows;

    IF v_has_rows THEN
      RAISE EXCEPTION 'Target table %.% is not empty', r.nspname, r.relname;
    END IF;
  END LOOP;
END
$empty_check$;

Expected: DO completes without an exception.

6. Schema-only setup on the Singapore target

psql -X \
  "host=primary.bts5ctx56gtoofohtkqcc33dinc2uq.postgresql.ap-singapore-1.oci.oraclecloud.com port=5432 dbname=dvdrental_lab user=postgres sslmode=require" \
  -v ON_ERROR_STOP=1 \
  -f /home/opc/00_create_schema.sql

psql -X \
  "host=primary.bts5ctx56gtoofohtkqcc33dinc2uq.postgresql.ap-singapore-1.oci.oraclecloud.com port=5432 dbname=dvdrental_lab user=postgres sslmode=require" \
  -v ON_ERROR_STOP=1

GRANT CONNECT ON DATABASE dvdrental_lab TO dvdrental_user;
GRANT USAGE ON SCHEMA dvdrental TO dvdrental_user;
GRANT SELECT ON ALL TABLES IN SCHEMA dvdrental TO dvdrental_user;

ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA dvdrental
 GRANT SELECT ON TABLES TO dvdrental_user;

Apply the same read-only grants and the same 15-table empty-target assertion from section 5.

Expected: all 15 tables exist and all are empty.

7. Some Important Assumptions

• pglogical runs in the application database. We install the extension in dvdrental_lab not in the postgres maintenance database.
• The OCI administrator owns the replication workflow. In this managed-service design, pglogical copy and apply need privileges such as changing session_replication_role. PostgreSQL restricts that setting to superusers or roles with the appropriate SET privilege.
• The application login is not the replication identity. dvdrental_user remains NOREPLICATION and receives no access to pglogical catalogs.
• Only Sydney receives generated data. Melbourne and Singapore get the same schema but remain empty until pglogical performs the initial copy.
• Schema and data loading are separate artifacts. This prevents a target operator from accidentally generating an independent dataset.
• Existing-object and future-object privileges are handled separately. ALTER DEFAULT PRIVILEGES affects future objects; it does not update tables or sequences that already exist.
• Subscribers are initialized sequentially. Melbourne must be healthy before Singapore begins. This reduces worker pressure and makes failures easier to isolate.
• Monitoring queries never print stored DSNs. A pglogical provider DSN contains credentials, so I project only safe status columns.

OCI pglogical guide and upstream pglogical documentation are useful companion references.

pglogical Setup

1. Create the Sydney provider

Connect to Sydney dvdrental_lab as postgres.

pglogical persists working DSNs in protected catalogs. Use a controlled interactive psql session, disable its history before entering a real DSN, and do not save a substituted copy of this template:

\set HISTFILE /dev/null

SELECT pglogical.create_node(
  node_name := 'publication-syd',
  dsn := 'host=primary.7gtysdaouav7agdgmh3mdeekdz3fwq.postgresql.ap-sydney-1.oci.oraclecloud.com port=5432 dbname=dvdrental_lab user=postgres password=YourPassword1234## sslmode=verify-full sslrootcert=/etc/opt/postgresql/ca-bundle.pem connect_timeout=60'
);

SELECT pglogical.replication_set_add_all_tables(
  'default',
  ARRAY['dvdrental'],
  true
);

SELECT pglogical.replication_set_add_all_sequences(
  'default',
  ARRAY['dvdrental'],
  true
);

A real password must be escaped for both the surrounding SQL literal and libpq keyword/value syntax. Do not blindly paste a password containing quotes, backslashes, or spaces into the template.

Verify membership without printing DSNs:

SELECT count(*) AS table_members
FROM pglogical.replication_set_table rst
JOIN pglogical.replication_set rs USING (set_id)
WHERE rs.set_name = 'default';

SELECT count(*) AS sequence_members
FROM pglogical.replication_set_seq rss
JOIN pglogical.replication_set rs USING (set_id)
WHERE rs.set_name = 'default';

Expected: 15 tables and 13 sequences.

2. Create and finish Melbourne Node and Subscriber Setup before starting Singapore

Connect to Melbourne dvdrental_lab as postgres:

\set HISTFILE /dev/null

SELECT pglogical.create_node(
  node_name := 'subscriber-mel',
  dsn := 'host=primary.mniso4nt6sfnz3de4ysxauopiel5hq.postgresql.ap-melbourne-1.oci.oraclecloud.com port=5432 dbname=dvdrental_lab user=postgres password=YourPassword1234## sslmode=verify-full sslrootcert=/etc/opt/postgresql/ca-bundle.pem connect_timeout=60'
);

SELECT pglogical.create_subscription(
  subscription_name := 'subscription_mel',
  provider_dsn := 'host=primary.7gtysdaouav7agdgmh3mdeekdz3fwq.postgresql.ap-sydney-1.oci.oraclecloud.com port=5432 dbname=dvdrental_lab user=postgres password=YourPassword1234## sslmode=verify-full sslrootcert=/etc/opt/postgresql/ca-bundle.pem connect_timeout=60',
  replication_sets := ARRAY['default'],
  synchronize_structure := false,
  synchronize_data := true,
  forward_origins := ARRAY[]::text[]
);

Poll the following safe status query:

SELECT
  subscription_name,
  status,
  provider_node,
  slot_name,
  replication_sets
FROM pglogical.show_subscription_status();

Never use SELECT * here; provider_dsn contains the stored password.

Check sync state:

SELECT
  sync_kind,
  sync_nspname,
  sync_relname,
  sync_status,
  sync_statuslsn
FROM pglogical.local_sync_status
ORDER BY sync_kind, sync_nspname, sync_relname;

SELECT sync_status, count(*)
FROM pglogical.local_sync_status
GROUP BY sync_status
ORDER BY sync_status;

Melbourne is accepted only when:

• subscription_mel is replicating;
• local_sync_status has 16 rows total — one database-level row and 15 table rows — and every row is r;
• the exact row counts match Sydney;
• Sydney shows one active pglogical slot and one streaming sender for MEL.

pglogical.wait_for_subscription_sync_complete(…) is only an observer. A statement_timeout on that function does not control or repair the background sync.

Do not continue to section 9 until all Melbourne acceptance checks pass.

3. Create and finish Singapore Node and Subscriber Setup

After Melbourne passes, connect to Singapore dvdrental_lab as postgres:

\set HISTFILE /dev/null

SELECT pglogical.create_node(
  node_name := 'subscriber-sgp',
  dsn := 'host=primary.bts5ctx56gtoofohtkqcc33dinc2uq.postgresql.ap-singapore-1.oci.oraclecloud.com port=5432 dbname=dvdrental_lab user=postgres password=YourPassword1234## sslmode=verify-full sslrootcert=/etc/opt/postgresql/ca-bundle.pem connect_timeout=60'
);

SELECT pglogical.create_subscription(
  subscription_name := 'subscription_sgp',
  provider_dsn := 'host=primary.7gtysdaouav7agdgmh3mdeekdz3fwq.postgresql.ap-sydney-1.oci.oraclecloud.com port=5432 dbname=dvdrental_lab user=postgres password=YourPassword1234## sslmode=verify-full sslrootcert=/etc/opt/postgresql/ca-bundle.pem connect_timeout=60',
  replication_sets := ARRAY['default'],
  synchronize_structure := false,
  synchronize_data := true,
  forward_origins := ARRAY[]::text[]
);

Use the same safe status and sync-state queries from section 8.

Singapore is accepted only when subscription_sgp is replicating, all 16 sync rows are r, counts match Sydney, and Sydney now shows two active pglogical slots and two streaming senders.

4. Full Replication topology validation

4.1 Provider slots and senders : Run on Sydney

SELECT
  slot_name,
  plugin,
  database,
  active,
  active_pid,
  restart_lsn,
  confirmed_flush_lsn,
  pg_size_pretty(
    pg_wal_lsn_diff(pg_current_wal_lsn(), confirmed_flush_lsn)
  ) AS confirmed_gap,
  pg_size_pretty(
    pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)
  ) AS retained_wal
FROM pg_replication_slots
WHERE slot_name LIKE 'pgl\_%' ESCAPE '\'
  AND plugin = 'pglogical_output'
  AND database = 'dvdrental_lab'
ORDER BY slot_name;

SELECT
  application_name,
  usename,
  client_addr,
  state,
  sent_lsn,
  write_lsn,
  flush_lsn,
  replay_lsn,
  write_lag,
  flush_lag,
  replay_lag
FROM pg_stat_replication
WHERE application_name IN ('subscription_mel', 'subscription_sgp')
ORDER BY application_name;

Expected steady state: two active pglogical_output slots and two streaming sender rows.

4.2 Exact row counts : Run on all three databases

SELECT 'actor' AS table_name, count(*) AS rows FROM dvdrental.actor
UNION ALL SELECT 'address', count(*) FROM dvdrental.address
UNION ALL SELECT 'category', count(*) FROM dvdrental.category
UNION ALL SELECT 'city', count(*) FROM dvdrental.city
UNION ALL SELECT 'country', count(*) FROM dvdrental.country
UNION ALL SELECT 'customer', count(*) FROM dvdrental.customer
UNION ALL SELECT 'film', count(*) FROM dvdrental.film
UNION ALL SELECT 'film_actor', count(*) FROM dvdrental.film_actor
UNION ALL SELECT 'film_category', count(*) FROM dvdrental.film_category
UNION ALL SELECT 'inventory', count(*) FROM dvdrental.inventory
UNION ALL SELECT 'language', count(*) FROM dvdrental.language
UNION ALL SELECT 'payment', count(*) FROM dvdrental.payment
UNION ALL SELECT 'rental', count(*) FROM dvdrental.rental
UNION ALL SELECT 'staff', count(*) FROM dvdrental.staff
UNION ALL SELECT 'store', count(*) FROM dvdrental.store
ORDER BY table_name;

Expected after the first rows=100,000 load

4.3 Self-cleaning streaming test

Choose an unused negative bigint and a short unique change ID. First confirm neither value exists:

SELECT country_id, country
FROM dvdrental.country
WHERE country_id = <UNUSED_NEGATIVE_BIGINT>
   OR country = '__pglogical_probe_<SHORT_CHANGE_ID>__';

Expected: zero rows.

Insert on Sydney:

INSERT INTO dvdrental.country(country_id, country)
VALUES (<UNUSED_NEGATIVE_BIGINT>, '__pglogical_probe_<SHORT_CHANGE_ID>__')
RETURNING country_id;

Confirm it appears on MEL and SGP, then delete it on Sydney:

DELETE FROM dvdrental.country
WHERE country_id = <UNUSED_NEGATIVE_BIGINT>
  AND country = '__pglogical_probe_<SHORT_CHANGE_ID>__';

Confirm the delete reaches both targets. A negative key prevents the probe from advancing the normal positive sequence.

5. Loading another generated batch on Sydney

Run only on Sydney and as the generator owner:

psql -X \
  "host=primary.7gtysdaouav7agdgmh3mdeekdz3fwq.postgresql.ap-sydney-1.oci.oraclecloud.com port=5432 dbname=dvdrental_lab user=postgres sslmode=require" \
  -v ON_ERROR_STOP=1 \
  -v rows=10000 \
  -f /home/opc/01_load_source.sql

The value must be an unsigned canonical integer from 1 through 1,000,000. Repeat the command for larger controlled loads rather than increasing the limit. Monitor both provider slots during the load and verify the new counts on both targets.

6. Stop and restart one subscription

Run only on the relevant target.

-- On MEL:
SELECT pglogical.alter_subscription_disable('subscription_mel', true);
SELECT pglogical.alter_subscription_enable('subscription_mel', true);

-- On SGP:
SELECT pglogical.alter_subscription_disable('subscription_sgp', true);
SELECT pglogical.alter_subscription_enable('subscription_sgp', true);

After enabling, verify replicating and check slot lag. Disabling a subscription does not make its target writable and does not remove the provider slot; prolonged disablement can retain WAL on Sydney.

7. Recover a failed initial sync stuck at down /d

Use this only when all are true:

• subscription status is down;
• local_sync_status is stuck at d;
• there is no matching copy/apply initialization worker;
• pg_stat_progress_copy shows no copy for the subscription;
• the target data is disposable, or an approved backup exists.

If target data is valuable, stop and reconcile it or build a new subscriber database. Do not truncate it.

7.1 Prove that initialization is abandoned

Run on the affected target:

SELECT subscription_name, status, slot_name
FROM pglogical.show_subscription_status()
WHERE subscription_name = '<SUBSCRIPTION_NAME>';

SELECT
  pid,
  usename,
  application_name,
  backend_type,
  state,
  wait_event_type,
  wait_event
FROM pg_stat_activity
WHERE datname = 'dvdrental_lab'
  AND (
    application_name LIKE '<SUBSCRIPTION_NAME>%'
    OR application_name ILIKE 'pglogical apply%'
    OR backend_type ILIKE 'pglogical apply%'
    OR backend_type ILIKE 'pglogical sync%'
  )
ORDER BY pid;

SELECT * FROM pg_stat_progress_copy;

Record the exact slot name before dropping metadata.

7.2 Drop failed target metadata

Run on the affected target:

SELECT pglogical.drop_subscription('<SUBSCRIPTION_NAME>', true);

SELECT subscription_name, status
FROM pglogical.show_subscription_status()
WHERE subscription_name = '<SUBSCRIPTION_NAME>';

SELECT pid, application_name, backend_type
FROM pg_stat_activity
WHERE datname = 'dvdrental_lab'
  AND (
    application_name LIKE '<SUBSCRIPTION_NAME>%'
    OR backend_type ILIKE 'pglogical apply%'
    OR backend_type ILIKE 'pglogical sync%'
  );

SELECT pglogical.drop_node('<SUBSCRIBER_NODE_NAME>', true);

The two post-condition queries must return zero rows before continuing. The `true` argument means “if exists”; it is not a force flag.

7.3 Check the exact provider slot

Run on Sydney:

SELECT slot_name, active, active_pid
FROM pg_replication_slots
WHERE slot_name = '<EXACT_SLOT_NAME>';

Normally drop_subscription removes the remote slot. If the exact slot remains, drop it only after the subscription is absent and active = false:

SELECT pg_drop_replication_slot('<EXACT_SLOT_NAME>');

Never create a pglogical slot manually.

7.4 Empty only the approved disposable target

Run on the affected target only:

TRUNCATE TABLE
  dvdrental.payment,
  dvdrental.rental,
  dvdrental.inventory,
  dvdrental.film_actor,
  dvdrental.film_category,
  dvdrental.customer,
  dvdrental.staff,
  dvdrental.store,
  dvdrental.film,
  dvdrental.actor,
  dvdrental.category,
  dvdrental.language,
  dvdrental.address,
  dvdrental.city,
  dvdrental.country
RESTART IDENTITY;

Re-run the empty-target assertion, then recreate that target node and subscription. Complete and validate it before making any change to the other subscriber.

8. Planned promotion and sequence safety

Sequence replication is not a guarantee that every target last_value always equals Sydney. PostgreSQL sequences are non-transactional and may cache or advance values.

Before a planned promotion:

1. Fence all writes to Sydney.

2. Wait on Sydney for every pglogical slot:

BEGIN; 
SET LOCAL statement_timeout = '30min'; 
SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 
COMMIT;

3. If the wait times out, roll back, diagnose the lagging subscriber, and abort the cutover.

4. Confirm both slots are active and caught up.

5. Stop subscriber apply according to the cutover plan.

6. With both application writes and apply fenced, advance any sequence whose next value could collide with stored keys:

DO $block$
DECLARE
r record;
v_max bigint;
v_last bigint;
v_is_called boolean;
BEGIN
FOR r IN
SELECT
n.nspname AS schema_name,
c.relname AS table_name,
a.attname AS column_name,
pg_get_serial_sequence(
format('%I.%I', n.nspname, c.relname),
a.attname
) AS sequence_name
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_attribute a ON a.attrelid = c.oid
WHERE n.nspname = 'dvdrental'
AND c.relkind IN ('r', 'p')
AND a.attnum > 0
AND NOT a.attisdropped
AND pg_get_serial_sequence(
format('%I.%I', n.nspname, c.relname),
a.attname
) IS NOT NULL
LOOP
EXECUTE format(
'SELECT max(%I)::bigint FROM %I.%I',
r.column_name,
r.schema_name,
r.table_name
) INTO v_max;
EXECUTE format( 'SELECT last_value::bigint, is_called FROM %s', r.sequence_name ) INTO v_last, v_is_called; IF v_max IS NOT NULL AND ( v_last < v_max OR (v_last = v_max AND NOT v_is_called) ) THEN PERFORM setval(r.sequence_name::regclass, v_max, true); END IF; END LOOP;
END
$block$;

7. Enable required application DML only on the promoted target.

8. Redirect the application and keep the old source write-fenced until a reverse topology is deliberately designed.

9. setval is not transactional. Test the promotion procedure in a clone before production use.

9. DDL and new tables

pglogical does not automatically replicate arbitrary DDL or auto-enroll future objects.

For a new table:

1. Apply compatible DDL on MEL and SGP first.
2. Apply it on Sydney.
3. Apply explicit current-object grants and correct owner-scoped default privileges.

4. Add the table on Sydney:

SELECT pglogical.replication_set_add_table( 'default', 'dvdrental.<NEW_TABLE>'::regclass, true );

5. Add its sequence separately, if present:

SELECT pglogical.replication_set_add_sequence( 'default', 'dvdrental.<NEW_SEQUENCE>'::regclass, true );

6. Wait for both provider slots on Sydney:

BEGIN; 
SET LOCAL statement_timeout = '30min'; 
SELECT pglogical.wait_slot_confirm_lsn(NULL, NULL); 
COMMIT;

7. Wait for the new table separately on MEL and SGP, using bounded timeouts. Run on MEL only:

BEGIN; 
SET LOCAL statement_timeout = '30min';
SELECT pglogical.wait_for_table_sync_complete( 'subscription_mel', 'dvdrental.<NEW_TABLE>'::regclass );
COMMIT;

8. Run in a separate session on SGP only:

BEGIN; 
SET LOCAL statement_timeout = '30min'; 
SELECT pglogical.wait_for_table_sync_complete( 'subscription_sgp', 'dvdrental.<NEW_TABLE>'::regclass ); 
COMMIT;

If either wait times out, roll back that session, inspect local_sync_status, and treat the DDL deployment gate as failed.

All replicated tables need a suitable primary key or replica identity for UPDATE and DELETE. The current 15 tables have primary keys.

10. Credential and privilege rules

1. Use separate strong secrets for the administrator and application login.
2. Keep dvdrental_user NOREPLICATION.
3. Do not grant the application role access to pglogical catalogs or the replication-origin functions.
4. Do not log or share full pglogical DSNs.
5. Use the projected status query; never SELECT * from show_subscription_status().

11. Troubleshooting Guide

When diagnosing a target, use:

SELECT
  subscription_name,
  status,
  provider_node,
  slot_name,
  replication_sets
FROM pglogical.show_subscription_status();

SELECT
  sync_kind,
  sync_nspname,
  sync_relname,
  sync_status,
  sync_statuslsn
FROM pglogical.local_sync_status
ORDER BY sync_kind, sync_nspname, sync_relname;

SELECT pid, application_name, backend_type, state, wait_event_type, wait_event
FROM pg_stat_activity
WHERE datname = 'dvdrental_lab'
ORDER BY pid;

SELECT * FROM pg_stat_progress_copy;

In OCI Logging, search the current DB-system log resource for `pglogical`, subscription name, `permission denied`, `session_replication_role`, `table copy failed`, `could not connect`, certificate failures, and background-worker errors.

12. Controlled teardown to remove the pglogical setup

Remove subscribers before the provider. Run each block in a separately verified regional session.

-- On MEL:
SELECT pglogical.alter_subscription_disable('subscription_mel', true);
SELECT pglogical.drop_subscription('subscription_mel', true);
SELECT pglogical.drop_node('subscriber-mel', true);

-- On SGP:
SELECT pglogical.alter_subscription_disable('subscription_sgp', true);
SELECT pglogical.drop_subscription('subscription_sgp', true);
SELECT pglogical.drop_node('subscriber-sgp', true);

-- On Sydney, first verify no pglogical slots remain:
SELECT slot_name, active
FROM pg_replication_slots
WHERE slot_name LIKE 'pgl\_%' ESCAPE '\'
  AND plugin = 'pglogical_output'
  AND database = 'dvdrental_lab';

-- Only when the topology is intentionally being dismantled:
SELECT pglogical.drop_node('publication-syd', true);

-- Do not drop nodes in an unlabeled all-regions script; connection context and order matter.

Appendix

A. References

• Oracle: Cross-Region Synchronization of OCI Database with PostgreSQL using pglogical
• Oracle: Manage Sequences in pglogical Replication for DR
• PostgreSQL 16: psql
• PostgreSQL 16: session_replication_role
• PostgreSQL 16: ALTER DEFAULT PRIVILEGES
• pglogical upstream documentation

B. Code

00_create_schema.sql

01_load_source.sql

C. DVDRental Entity Relationship Diagram

In this post, I will walk you through a Docker-based (Podman can also be used) for Oracle GoldenGate 26ai deployment which :

• Loads the Oracle GoldenGate container image
• Runs a quick validation container
• Builds a persistent Docker filesystem layout
• Keeps credentials out of the docker run command
• Starts GoldenGate with operationally useful Docker options
• Locates the trail files
• Accesses the container
• Checks GoldenGate Microservices inside the container
• Connects to the Admin Client
• Inspects trails with Logdump
• Monitors trail growth from the Docker host
• Useful troubleshooting and operational commands

The examples use the Oracle GoldenGate 26ai container image. This image is for migrations and can be downloaded using OCI Marketplace image. Please see section Oracle GoldenGate on Podman in the below document :

Ref : https://docs.oracle.com/en/database/oracle/zero-downtime-migration/21.5/zdmea/#ZDM%E2%80%93LogicalOnlineMigrationtoADBDonOracleDatabase@AWS-OracleGoldenGateonPodman

Security note: All passwords, IP addresses, and similar values shown in this article should be treated as examples. Use your organization’s secret-management and certificate-management standards for real environments.

A Typical Goldengate flow for Source to Target Database Replication:

Source Database
      |
      v
   Extract (Transaction Capture from Source)
      |
      v
 Local Trail (Transactions stored)
      |
      v
Distribution Path (if Heterogenous or Multiple goldengate deployments)
      |
      v
 Remote Trail (Transactions stored)
      |
      v
   Replicat (Transaction Apply to Target)
      |
      v
Target Database

Architecture at a Glance

The deployment we are building looks conceptually like this:

Docker Host
│
├── $HOME/goldengate/26ai/
│   │
│   ├── u02/  ────────────────> /u02
│   │                            Persistent GoldenGate data
│   │                            Deployments, trails, configuration
│   │
│   ├── u03/  ────────────────> /u03
│   │                            Temporary GoldenGate data
│   │
│   └── cert/ ────────────────> /etc/nginx/cert
│                                HTTPS certificate material
│
└── Docker Container: ogg26ai
    │
    ├── /u01/ogg               GoldenGate binaries
    ├── /u02                   Persistent deployment data
    ├── /u03                   Temporary data
    │
    └── GoldenGate Microservices
        ├── Administration Server
        ├── Distribution Server
        ├── Receiver Server
        └── Performance Metrics Server

That separation becomes particularly important during database migrations, where trail continuity and recoverability matter considerably more than the lifecycle of an individual Docker container.

                       [~~ Docker Host ~~]
                              |
                +-------------+-------------+
                |             |             |
                v             v             v
              /u02          /u03          cert
           Persistent      Temporary       TLS
                |
                v
   +---------------------------+
   | Oracle GoldenGate 26ai    |
   | Docker Container          |
   |                           |
   | /u01/ogg -> Software      |
   | /u02     -> Deployment    |
   | /u03     -> Temporary     |
   +---------------------------+
                |
                v
   +---------------------------+
   | GoldenGate Microservices  |
   |                           |
   | Administration Server     |
   | Distribution Server       |
   | Receiver Server           |
   | Performance Metrics       |
   +---------------------------+
                |
                v
   Extract -> Trail -> Distribution -> Trail -> Replicat

Step 1 — Load the Oracle GoldenGate Container Image

After downloading the Oracle GoldenGate container image from OCI Marketplace Image for Database Migrations, load the image into the local Docker repository.

For example:

sudo docker load -i ora23ai-2326300.tar

Verify that Docker can see the image:

sudo docker images

REPOSITORY                       TAG                 IMAGE ID       CREATED        SIZE
localhost/oracle/goldengate      23.26.3.0.0         5bb91f52a79a   4 weeks ago    3.1GB

At this point, Docker has the image locally and we can perform an initial validation.

Step 2 — Perform a Quick Container Validation

Before building the persistent configuration, it can be useful to verify that the image starts successfully.

A minimal invocation looks like:

docker run -p 443:443 \
  -e OGG_ADMIN=oggadmin \
  -e OGG_ADMIN_PWD='<temporary-password>' \
  localhost/oracle/goldengate:23.26.3.0.0

This is useful for confirming that:

• The image starts
• GoldenGate initializes
• HTTPS is exposed
• The administration account works
• The host can reach the GoldenGate UI

In case you want to use the Oracle Container for registry for the free Goldengate image, you can run the below command directly without downloading the image from the Marketplace image.

docker run -p 443:443 \
  -e OGG_ADMIN=oggadmin \
  -e OGG_ADMIN_PWD='<temporary-password>' \
container-registry.oracle.com/goldengate/goldengate-oracle-free:latest

However, we will not use this as the long-term deployment model.

The container lifecycle should not determine the lifecycle of your GoldenGate deployment data. We need persistent storage for that.

Step 3 — Create a Persistent GoldenGate Filesystem Layout

Create a dedicated directory on the Docker host:

~/goldengate/26ai/
├── u02/        # Persistent GoldenGate deployments, trails, config and logs
├── u03/        # Temporary GoldenGate data
└── cert/       # HTTPS certificate material

Create the directories:

mkdir -p "$HOME/goldengate/26ai/u02"
mkdir -p "$HOME/goldengate/26ai/u03"
mkdir -p "$HOME/goldengate/26ai/cert"

chmod -R 777 $HOME/goldengate/26ai/u02

chmod -R 777 $HOME/goldengate/26ai/u03

chmod -R 777 $HOME/goldengate/26ai/cert

Oracle GoldenGate’s container layout is important to understand:

/u01                GoldenGate installation
/u02                Persistent GoldenGate data
/u03                Temporary GoldenGate data
/etc/nginx/cert     HTTPS certificate material

The important design decision here is that don’t bind-mount /u01.

/u01 contains the GoldenGate software delivered by the container image. Keeping it within the image maintains a clean separation between:

Immutable software  -> Container image
Persistent state    -> Host-mounted storage

This also makes container upgrades and replacements considerably easier.

Step 4 — Define a Named GoldenGate Deployment

Rather than relying entirely on defaults, define the deployment explicitly:

export OGG_DEPLOYMENT=Local

In this example, the deployment will be called:

Local

Using an explicit deployment name makes scripting, troubleshooting, and Admin Client connections easier to understand.

Step 5 — Keep the Password Out of docker run

Putting credentials directly on a docker run command is easy for quick testing but should not be used for a persistent environment.

Instead, create an environment file:

cat > "$HOME/goldengate/26ai/.env" <<'EOF'
OGG_ADMIN=oggadmin
OGG_ADMIN_PWD=<strong-password>
OGG_DEPLOYMENT=Local
OGG_DOMAIN=localhost
EOF

Restrict access:

chmod 600 "$HOME/goldengate/26ai/.env"

GoldenGate passwords must comply with Oracle’s documented password requirements, including the required combination of uppercase, lowercase, numeric, and permitted special characters.

For a production environment, I would take this one step further and integrate the deployment with the organization’s established secrets-management mechanism rather than maintaining long-lived credentials in plaintext files.

Step 6 — Start the GoldenGate Container

Now start the container with persistent storage and some useful operational settings:

docker run -d \
  --name ogg26ai \
  --hostname ogg26ai \
  --restart unless-stopped \
  -p 443:443 \
  --env-file "$HOME/goldengate/26ai/.env" \
  -v "$HOME/goldengate/26ai/u02:/u02" \
  -v "$HOME/goldengate/26ai/u03:/u03" \
  -v "$HOME/goldengate/26ai/cert:/etc/nginx/cert" \
  --log-opt max-size=50m \
  --log-opt max-file=5 \
  localhost/oracle/goldengate:23.26.3.0.0

In Case of using the Oracle container registry with free Goldengate image, just run the below :

docker run -d \
  --name ogg26ai \
  --hostname ogg26ai \
  --restart unless-stopped \
  -p 443:443 \
  --env-file "$HOME/goldengate/26ai/.env" \
  -v "$HOME/goldengate/26ai/u02:/u02" \
  -v "$HOME/goldengate/26ai/u03:/u03" \
  -v "$HOME/goldengate/26ai/cert:/etc/nginx/cert" \
  --log-opt max-size=50m \
  --log-opt max-file=5 \
container-registry.oracle.com/goldengate/goldengate-oracle-free:latest

If you’re using Podman on RHEL or Oracle Linux, run the command as below :

docker run -d \
  --name ogg26ai \
  --hostname ogg26ai \
  --restart unless-stopped \
  -p 8443:443 \
  --env-file "$HOME/goldengate/26ai/.env" \
  -v "$HOME/goldengate/26ai/u02:/u02:z" \
  -v "$HOME/goldengate/26ai/u03:/u03:z" \
  -v "$HOME/goldengate/26ai/cert:/etc/nginx/cert:z" \
  --log-opt max-size=50m \
  --log-opt max-file=5 \
container-registry.oracle.com/goldengate/goldengate-oracle-free:latest

Step 7 — Verify the Container

First verify that the container is running:

docker ps --filter name=ogg26ai

Check the most recent logs:

docker logs --tail 100 ogg26ai

To follow startup or runtime messages continuously:

docker logs -f ogg26ai

Next, verify the actual Docker mounts:

docker inspect ogg26ai \
  --format '{{range .Mounts}}{{println .Source "->" .Destination}}{{end}}'

On my test host, the result looked similar to:

/home/ec2-user/goldengate/26ai/u02 -> /u02
/home/ec2-user/goldengate/26ai/u03 -> /u03
/home/ec2-user/goldengate/26ai/cert -> /etc/nginx/cert
/var/lib/docker/volumes/<volume-id>/_data -> /u01/ogg/scripts

This is an important validation.

Do not assume that Docker mounted what you intended. Verify it.

For migration infrastructure, knowing exactly which host filesystem contains your GoldenGate state is critical.

Step 8 — Enter the GoldenGate Container

Open an interactive shell:

docker exec -it ogg26ai bash

Check the runtime identity and working directory:

whoami
pwd

Now inspect the GoldenGate-related environment:

env | sort | grep -E '^(OGG|DEPLOYMENT|TNS)'

In my environment, this returned:

OGG_ADMIN=oggadmin
OGG_ADMIN_PWD=<redacted>
OGG_DEPLOYMENT=Local
OGG_DEPLOYMENT_HOME=/u02
OGG_DEPLOYMENT_SCRIPTS=/u01/ogg/scripts
OGG_DOMAIN=localhost
OGG_HOME=/u01/ogg
OGG_TEMPORARY_FILES=/u03

This tells us several useful things immediately:

GoldenGate software     -> /u01/ogg
Deployment root         -> /u02
Deployment scripts      -> /u01/ogg/scripts
Temporary files         -> /u03
Deployment              -> Local

You can also inspect the standard GoldenGate variables individually:

echo "$OGG_HOME"
echo "$OGG_ETC_HOME"
echo "$OGG_VAR_HOME"
echo "$OGG_DATA_HOME"
echo "$OGG_SSL_HOME"

For example:

echo "$OGG_HOME"

returns:

/u01/ogg

Oracle documents the GoldenGate deployment directories and the role of the Data Home here:

https://docs.oracle.com/en/database/goldengate/core/26/coredoc/deployment-directories.html

Step 9 — Locate the GoldenGate Trail Files

Trail location is one of the first things I want to establish when troubleshooting any GoldenGate environment.

Inside the container, search for deployment data directories:

find /u02 -maxdepth 6 -type d -name data -print

Then list files underneath GoldenGate data directories:

find /u02 -maxdepth 8 -path '*/var/lib/data/*' -type f -print

Another useful exploratory command is:

find /u02 -type f \( -name '*000000*' -o -name '??*' \) 2>/dev/null | head -100

A deployment will conceptually resemble:

/u02/Deployment/
├── etc/
│   ├── conf/
│   └── ssl/
└── var/
    └── lib/
        ├── data/
        │   ├── ea000000001
        │   ├── ea000000002
        │   └── ...
        └── archive/

The important part is:

var/lib/data

This is where GoldenGate trail data is stored for the deployment.

From an operational perspective, I want to be able to answer three questions immediately:

1. Which Extract is writing the trail?
2. Where is that trail physically stored?
3. Which downstream process has consumed it?

Those three questions resolve a surprising percentage of GoldenGate migration troubleshooting scenarios.

Step 10 — Check the GoldenGate Processes

Inside the container:

ps -ef | grep -i '[o]gg'

For the Microservices components:

ps -ef | grep -Ei '[s]ervice|[a]dminsrvr|[d]istsrvr|[r]eceiversrvr|[p]msrvr'

Also inspect listening ports:

ss -lntp

With GoldenGate Microservices Architecture, the major services to understand are:

Administration Server
Distribution Server
Receiver Server
Performance Metrics Server
Service Manager

At this stage, we are validating two different layers:

Docker says container is running
             AND
GoldenGate services are actually running

A healthy Docker container does not automatically mean every GoldenGate service inside it is healthy. Check & Verify

Step 11 — Access GoldenGate from a Browser

If running locally:

https://localhost

If the Docker host is remotely accessible:

https://<host-ip-or-dns-name>

The reason no additional port is required is our Docker mapping:

-p 443:443

This maps:

Host TCP/443 -> Container TCP/443

If the environment uses the default or a self-signed HTTPS certificate, expect the browser to present a certificate warning.

For anything beyond a lab environment, replace this with certificate material that complies with your organization’s PKI and TLS standards.

Step 12 — Connect Using GoldenGate Admin Client

For database migration work, Admin Client is one of the most useful tools to become comfortable with.

Inside the container, locate it:

find /u01/ogg -type f -name adminclient -print 2>/dev/null

Or:

which adminclient

Start it:

adminclient

Connect to the deployment:

CONNECT https://localhost:443 DEPLOYMENT Local AS oggadmin !

Enter the password when prompted.

Once connected:

INFO ALL

In a newly initialized environment, output may resemble:

Program     Status      Group       Type                   Lag at Chkpt       Time Since Chkpt
ADMINSRVR   RUNNING
DISTSRVR    RUNNING
PMSRVR      RUNNING
RECVSRVR    RUNNING

Then inspect Extracts:

INFO EXTRACT ALL

Replicats:

INFO REPLICAT ALL

Distribution Paths:

INFO DISTPATH ALL

For detailed information about an individual Extract:

INFO EXTRACT <extract_name>, DETAIL

For an individual Replicat:

INFO REPLICAT <replicat_name>, DETAIL

And for a specific Distribution Path:

INFO DISTPATH <path_name>, DETAIL

During a migration incident, I generally work through this pipeline in order rather than jumping directly to whichever component happens to report an error.

Step 13 — Inspect Trail Records with Logdump

Sometimes process status isn’t enough. You need to know what is actually inside the trail. GoldenGate’s Logdump utility allows us to inspect trail records directly.

REF : https://docs.oracle.com/en/database/goldengate/core/26/logdump-ref/using-logdump-utility.html

Inside the container:

which logdump

Alternately :

find /u01/ogg -type f -name logdump -print 2>/dev/null

Start it:

logdump

Open a trail:

open /u02/Deployment/var/lib/data/<trail-file>

For example:

open /u02/Deployment/var/lib/data/ea000000001

Enable useful record information:

ghdr on
detail on
nldetail on

Then move through records:

next

Logdump is particularly useful when investigating questions such as:

• Is Extract actually writing operations into the trail?
• Does the trail contain the expected table?
• What operation type was captured?
• What timestamp does the record carry?
• Is the issue upstream or downstream of trail creation?

That makes it one of the most valuable low-level troubleshooting utilities in the GoldenGate tool kit.

Step 14 — Inspect Deployment Metadata

Another useful troubleshooting technique is simply understanding what GoldenGate created beneath /u02.

Inside the container:

find /u02 -maxdepth 4 -type f | sort | head -200

Inspect configuration and variable areas:

find /u02 -type f | grep -E '/etc/(conf|ssl)|/var/'

GoldenGate deployments logically expose locations such as:

GoldenGate Home
Configuration Home
SSL Home
Variable Home
Data Home
Archive Home

Knowing these locations is extremely useful when troubleshooting configuration, logging, certificate, and trail-related issues.

Step 15 — Inspect Trails Directly from the Docker Host

This is where the bind-mounted architecture pays off.

We don’t need to enter the container simply to inspect the filesystem.

From the Docker host:

find "$HOME/goldengate/26ai/u02" \
  -path '*/var/lib/data/*' \
  -type f \
  -ls

Check total persistent storage usage:

du -sh "$HOME/goldengate/26ai/u02"

On Linux, watch it continuously:

watch -n 2 'du -sh "$HOME/goldengate/26ai/u02"'

If watch is unavailable, for example on a default macOS installation:

while true; do
  du -sh "$HOME/goldengate/26ai/u02"
  sleep 2
done

During high-volume migrations, trail growth is something I would actively monitor.

An abended (stopped) Replicat or Distribution Path can turn what initially looks like a replication problem into a filesystem-capacity problem surprisingly quickly.

Step 16 — Inspect the Container Itself

This command provides a useful high-level Docker summary:

docker inspect ogg26ai \
  --format '
Name: {{.Name}}
Image: {{.Config.Image}}
Status: {{.State.Status}}
Started: {{.State.StartedAt}}
RestartCount: {{.RestartCount}}
'

Also monitor runtime resource consumption:

docker stats ogg26ai

This helps separate infrastructure symptoms from GoldenGate symptoms.

For example:

High CPU                    -> Extract/Replicat workload or host contention
High memory                 -> Process/container pressure
Rapid /u02 growth           -> Trail accumulation
Container restart count     -> Infrastructure/container instability
GoldenGate lag increasing   -> Replication pipeline issue

Looking at these signals together is much more useful than troubleshooting GoldenGate entirely in isolation.

Step 17 — Consider Using a Non-Default Host Port

Port 443 is convenient:

-p 443:443

but it may conflict with another HTTPS service on the host.

For development and test environments, I often prefer:

-p 8443:443

The mapping becomes:

Host TCP/8443 -> Container TCP/443

Access GoldenGate using:

https://localhost:8443

The container still listens on its expected HTTPS port while avoiding a collision with the host’s standard HTTPS endpoint.

Step 18 — My GoldenGate Container Health-Check Cheat Sheet

These are the commands I find myself using repeatedly.

Enter the container

docker exec -it ogg26ai bash

Check Docker status

docker ps --filter name=ogg26ai

Check logs

docker logs --tail 100 ogg26ai

Follow logs

docker logs -f ogg26ai

Verify persistent mounts

docker inspect ogg26ai \
  --format '{{range .Mounts}}{{println .Source "->" .Destination}}{{end}}'

Check GoldenGate environment

env | sort | grep -E '^(OGG|DEPLOYMENT|TNS)'

Check the installation home

echo "$OGG_HOME"

Locate deployment data

find /u02 -maxdepth 6 -type d -name data -print

Find trail files

find /u02 -maxdepth 8 \
  -path '*/var/lib/data/*' \
  -type f \
  -print

Check GoldenGate processes

ps -ef | grep -Ei '[o]gg|[s]ervice|[a]dminsrvr|[d]istsrvr|[r]eceiversrvr'

Check filesystem capacity

df -h

Check GoldenGate persistent storage consumption

du -sh /u02/*

Start Admin Client

adminclient

Connect

CONNECT https://localhost:443 DEPLOYMENT Local AS oggadmin !

Operational Considerations for Real Migration Environments

The Docker command is only one part of running GoldenGate reliably.

For an enterprise database migration, I would additionally consider the following.

1. Trail Storage Capacity

Trail storage should be sized based on:

Source transaction volume
×
Maximum expected outage/recovery window
+
Operational safety margin

Do not size /u02 simply based on the current database size.

The relevant question is how much change data GoldenGate may need to retain while downstream processing is unavailable.

2. Trail Purging

Avoid treating old GoldenGate trails like ordinary temporary files.

Do not build a cron job such as:

find ... -mtime +N -delete

without understanding GoldenGate checkpoints and downstream consumers. Trail retention is part of the recoverability of the replication pipeline.

3. Monitor Lag and Checkpoints

Filesystem monitoring tells us whether trails are accumulating.

During a migration, monitor:

Extract status
Extract lag
Replicat status
Replicat lag
Checkpoint progression
Distribution Path status
Trail filesystem growth
Host filesystem utilization

Together, these provide a much more complete view of replication health.

4. Protect /u02

Once /u02 contains active deployment state and trails, treat it as persistent application data.

The useful containerization model is:

Container      = Replaceable
Image          = Replaceable
/u01 software  = Replaceable

/u02 data      = Persistent
Trails         = Operationally critical
Configuration  = Operationally critical
Credentials    = Sensitive
Certificates   = Sensitive

That distinction makes container lifecycle management significantly safer.

5. Replace Self-Signed Certificates

Self-signed certificates are convenient for initial validation.

For an enterprise deployment, use certificates issued according to the organization’s PKI standards and validate:

• Certificate chain
• Hostname/SAN configuration
• Expiration monitoring
• Private-key permissions
• Certificate rotation procedures

6. Don’t Expose GoldenGate Administration to the Internet

A public IP may technically make:

https://<public-ip>

reachable, but that doesn’t mean GoldenGate’s administration interface should be broadly internet-accessible.

For a real migration environment, restrict connectivity using the relevant network controls: security groups, firewall rules, private subnets/networks, VPN, bastion access, or equivalent enterprise controls.

The Final Docker Configuration

Putting everything together, this is the baseline configuration used in this walkthrough:

docker run -d \
  --name ogg26ai \
  --hostname ogg26ai \
  --restart unless-stopped \
  -p 443:443 \
  --env-file "$HOME/goldengate/26ai/.env" \
  -v "$HOME/goldengate/26ai/u02:/u02" \
  -v "$HOME/goldengate/26ai/u03:/u03" \
  -v "$HOME/goldengate/26ai/cert:/etc/nginx/cert" \
  --log-opt max-size=50m \
  --log-opt max-file=5 \
  localhost/oracle/goldengate:23.26.3.0.0

Conclusion

Containerizing Oracle GoldenGate 26ai is relatively straightforward. The more important decision is determining what belongs to the container and what must survive the container restarts.

Some of the Key principles are:

1. Keep GoldenGate software in the image.
Don’t unnecessarily externalize /u01.

2. Persist /u02.
Your deployment configuration and trail data should not disappear because a container was replaced.

3. Know exactly where the trails are.
During migration troubleshooting, trail location, growth, and consumer checkpoints are fundamental signals.

4. Validate both Docker and GoldenGate.
docker ps tells you the container is running. INFO ALL tells you considerably more about GoldenGate.

5. Learn Admin Client and Logdump.
The UI is useful, but these tools become invaluable when diagnosing real replication problems.

6. Monitor disk consumption.
An interrupted downstream consumer can translate directly into accumulating trails and eventually filesystem exhaustion.

7. Treat the container as disposable but not the system of record.

If I can remove and recreate the GoldenGate container while retaining the deployment state I intentionally chose to persist, I have a much cleaner operational boundary.

This separation makes the environment easier to operate, troubleshoot, upgrade, and the most importantly easier to troubleshoot during a high-pressure database migration.

References

[1] Oracle GoldenGate 26ai — Deployment Directories

https://docs.oracle.com/en/database/goldengate/core/26/coredoc/deployment-directories.html

[2] Oracle GoldenGate 26ai — Logdump Utility

https://docs.oracle.com/en/database/goldengate/core/26/logdump-ref/using-logdump-utility.html

The commands and configuration in this post are based on a working Oracle GoldenGate 26ai Docker deployment. Adapt filesystem paths, network exposure, authentication, TLS configuration, resource limits, storage design, and security controls to the requirements of your environment before using the approach for production workloads.

The useful conclusion is not that one platform is universally faster. For this particular end-to-end, read-heavy geospatial API path, OCI supplied the strongest throughput-per-dollar result under the stated assumptions. Teams should validate that result with their own data distribution, query mix, index design, network topology, repeat runs, and complete pricing model.

## FAQs

### Was this a direct database benchmark?

No. JMeter called an HTTPS endpoint on Apache; Apache/PHP then connected to the selected PostgreSQL backend. The result is closer to a small web-service path than a direct SQL microbenchmark.

### Why compare OCI 4 OCPUs with Aurora 4, 8, and 16 vCPUs?

The 4-vCPU Aurora run provides a baseline. The larger Aurora shapes show how the same workload responds to more compute. Because one OCPU equals two vCPUs, OCI’s 4-OCPU result is also shown as an 8-vCPU equivalent.

### Does this prove OCI PostgreSQL is always faster than Aurora PostgreSQL?

No. It shows OCI PostgreSQL performed better and more cost efficiently for this specific end-to-end PostGIS read workload under the stated topology and pricing assumptions. Other schemas, indexes, query plans, regions, data sizes, I/O profiles, and write patterns should be tested separately.

### Why are PostGIS and geospatial workloads so sensitive to indexes, query shape, and data distribution?

Spatial predicates first depend on how effectively the index narrows the search area. The radius, geometry/geography type, ordering, returned-row limit, and density of points near the searched location determine how many candidates must be evaluated and returned. Small changes can therefore affect both database time and JSON response size substantially.

### What should someone replicate before making a platform decision?

Use the same schema, seed process, indexes, query parameters, application path, network placement, concurrency profile, and pricing model. Repeat the runs and inspect both client-side metrics and database-side behaviour.

## Methodology and source notes

- Benchmark source repository: https://github.com/shadabshaukat/jmeter-postgis-e2e-benchmark
- JMeter artifacts used for metrics: `web_read_public`, `web_read_public_aurora`, `web_read_public_aurora_8vcpu`, and `web_read_public_aurora_16vcpu`.
- Effective run evidence: each JTL contains 48,000 samples and a maximum observed thread count of 120. The repository sources `.env`, so the artifacts are the source of truth for the executed profile.
- AWS pricing source: Amazon RDS public price list for `ap-southeast-2`, publication date 2026–06–19T20:39:21Z. Rates used: Aurora PostgreSQL `db.r6gd.xlarge` US$0.751/hour, `db.r5.2xlarge` US$1.40/hour, `db.r5.4xlarge` US$2.80/hour, and Aurora storage US$0.11/GB-month.
- Oracle pricing source: OCI public price list, last updated 2026–06–11T14:42:17.843Z. Rates used: Database with PostgreSQL — X86 US$0.098/OCPU-hour and Database Optimized Storage US$0.072/GB-month.

OCI PostgreSQL vs. Amazon Aurora PostgreSQL for Geospatial Workloads — A Benchmarking Comparison was originally published in Towards Dev on Medium, where people are continuing the conversation by highlighting and responding to this story.

I recently explored an approach using an intermediary PostgreSQL instance on OCI Compute.

Architecture in brief

** On-prem PostgreSQL (A) **
    |
    |
    |
(Physical replication)
    |
    |
    V
** OCI Instance PostgreSQL (B)  **
    |
    |
    |
(Logical replication)
    |
    | 
    V                                                              
 ** OCI PostgreSQL (C) **

This architecture is used in complex migrations, especially when you want to decouple source load, add transformation control, or work around network/security constraints.

We’re essentially proposing a hybrid replication chain:

• A (on-prem PostgreSQL) → B (OCI Instance PostgreSQL) via physical replication
• B (OCI Instance PostgreSQL) → C (OCI PostgreSQL) via logical replication / OCI Goldengate

Important Note : You can use native postgres logical replication, pglogical or any other logical tool for the replication from B → C.
Becuase the target is on OCI PostgreSQL on Oracle Cloud Infrastructure, we’re using OCI Goldengate; which is an industry leading real-time replication solution.

Why this is interesting

• Physical replication ensures a low-impact, byte-level copy from the source
• Logical replication from the intermediary allows selective migration and transformation
• The OCI compute layer acts as a buffer, isolating production from migration tooling

Key benefits

• Zero logical decoding overhead on production
• Better control over migration scope
• Improved fault isolation
• Flexible transformation using OCI Goldengate

Trade-offs

• Increased replication lag due to chaining
• More operational complexity
• WAL retention needs careful monitoring
• Logical decoding on standby requires newer PostgreSQL versions

Pre-Requisities on both A and B

1. Ensure Port 5432 is added to Firewall whitelist on both hosts for ingress and also on the Network Security Group of the instance

sudo firewall-cmd --zone=public --permanent --add-port=5432/tcp
sudo firewall-cmd --reload

2. listen_address in postgresql.conf is set to ‘*’

3. pg_hba.confis set to allow all hosts

host    all             all             0.0.0.0/0               scram-sha-256

Step-by-Step Implementation

Phase 1: Setup Physical Replication (A → B)

1. Prepare On-Premise Source (A)

Update postgresql.conf:

wal_level = replica
max_wal_senders = 10
wal_keep_size = 2048 #2GB
hot_standby = on

Update pg_hba.conf:

host    replication     replicator      <Server-B-IP>/32         scram-sha-256

Create replication user:

CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'password';

2. Take Base Backup to OCI Instance with Postgres -Instance B

On OCI instance (B) install a fresh PostgreSQL engine or disable the previous Postgresql database for a fresh restore :

mv /var/lib/pgsql/17/data/ /var/lib/pgsql/17/data_old/

sudo systemctl stop postgresql-17

Take backup on OCI instance postgres (B) :

pg_basebackup \
  -h <Server-A-IP> \
  -D /var/lib/pgsql/17/data/ \
  -U replicator \
  -P \
  -R \
  -X stream

-h <Server-A-IP>Host (source database server)

• Specifies the primary server (A) to connect to
• Can be:
• IP address
• DNS name

-D /var/lib/pgsql/17/data/Destination directory (data directory on B)

• Where the base backup will be written
• This becomes the entire PostgreSQL data directory on B

⚠️ Important:

• Must be empty or non-existent
• Will contain:
• base/, pg_wal/, global/, etc.

👉 This is effectively:

“Make this directory an exact clone of A”

-U replicatorReplication user

• PostgreSQL user used to connect
• Must have:
• REPLICATION LOGIN privilige

-PProgress output

• Shows real-time progress of backup:
• Total size
• Data transferred
• Percentage

-RWrite replication configuration automatically

This is a very important flag 👇

It does two things:

1. Creates standby.signal

• Tells PostgreSQL:
• “Start in standby (replica) mode”

2. Writes connection info into:

postgresql.auto.conf

primary_conninfo = 'host=Server-A-IP user=replicator password=xxx'

👉 Without -R, you would have to:

• Manually create standby.signal
• Manually configure primary_conninfo

-X streamWAL (Write-Ahead Log) handling method

This ensures backup consistency.

What it does:

• Streams WAL files in parallel with the base backup
• Ensures backup is transactionally consistent

Alternatives:

-X streamStream WAL during backup (most common)
-X fetchFetch WAL after backup
-X noneNo WAL (⚠️ unsafe for replication)

👉 Always use stream for replication setups

⚡What this Does :

• Copies data
• Creates standby.signal
• Configures replication automatically

🔄 What Happens End-to-End

When you run this command:

1. Connects to A using replication protocol
2. Starts a consistent snapshot
3. Copies all data files
4. Streams WAL simultaneously (-X stream)
5. Writes standby config (-R)
6. Prepares B to start as a replica

📦 Result on B

After execution, your directory contains:

/var/lib/pgsql/17/data/
├── base/
├── global/
├── pg_wal/
├── postgresql.auto.conf   <-- contains primary_conninfo
├── standby.signal         <-- enables replica mode

✅ Pro Tips (Real-World)

Add compression (faster over network)

--compress=server-gzip

Limit bandwidth (avoid saturating link)

--max-rate=100MB

Use replication slot (prevent WAL loss)

--slot=my_slot --create-slot

⚠️ Common Mistakes

• Forgetting -R → replica won’t start automatically
• Using non-empty -D directory
• Not allowing replication in pg_hba.conf
• Not enough WAL retention on A

Start B as Standby

sudo systemctl start postgresql-17

Verify:

SELECT * FROM pg_stat_wal_receiver;

Phase 2: Prepare B for Logical Replication / OCI Goldengate

This is the critical step

By default, a physical replica cannot act as a logical replication source unless configured properly.

1. Enable Logical Decoding on B

Update on B:

wal_level = logical
max_replication_slots = 10
max_wal_senders = 10

Important:
–> Requires restart
–> In PostgreSQL 14+, logical decoding on standby is supported

Create Logical Replication Slot (if using native or pglogical. Goldengate creates it’s own replication slot)

SELECT * FROM pg_create_logical_replication_slot('goldengate_slot', 'test_decoding');

Phase 3: Setup OCI Goldengate (B → C)

For detailed steps refer these articles :

[1] https://medium.com/oracledevs/oci-goldengate-to-perform-cross-region-replication-for-oci-postgresql-dbsystems-b14d4b7623d5

[2] https://medium.com/@shadabshaukat/zero-downtime-migration-to-oci-postgresql-from-amazon-rds-postgresql-using-oci-goldengate-76c80c631c45

1. Using OCI Goldengate:

a. Create GoldenGate deployment in OCI

b. Configure Extract on B :

• Capture changes using logical decoding

c. Configure Replicat to Target:

• OCI PostgreSQL

2. Perform Initial Load + CDC:

a. Perform initial load using:

• Native dump/restore OR
• GoldenGate initial load

b. Start CDC pipeline:

GoldenGate streams ongoing changes from B → C

Advantages

Zero Impact on Source (A)

No logical decoding or CDC load on production

High Control Over Migration

GoldenGate provides:

• Filtering
• Transformation
• Conflict handling

Cloud-Native Integration

Seamless integration within OCI ecosystem

Better Failure Isolation

Failures in replication do not impact production

Trade-offs

Increased Complexity

Two replication layers instead of one

Replication Lag

Lag compounds:

• A → B
• B → C

WAL Retention Management

Logical replication slots on B must be monitored

Operational Overhead

Requires monitoring:

• Physical replication health
• GoldenGate processes

When Should You Use This Pattern?

This approach is ideal when:

• Production systems cannot handle additional load especially with replication slots which is an overhead of logical replication
• Strict network/security boundaries exist
• Advanced transformation or filtering is required
• Migration must be highly controlled and observable

Final Thoughts

This hybrid architecture is not the simplest path, but it is a powerful one. By combining PostgreSQL physical replication with OCI GoldenGate’s logical capabilities, you gain a robust and flexible migration pipeline that minimizes production risk while maximizing control.

Key Takeaways

• Blue-green for databases, simplified on OCI: Run parallel Blue (prod) and Green (staging) MySQL HeatWave environments, keep them in sync, and switch traffic with near-zero downtime.
• Two replication paths: Use MySQL Channel Replication for simpler, cost-effective rollouts; use Oracle GoldenGate for enterprise-grade CDC and major-version upgrades.
• Smooth cutovers with OCI networking: Private DNS plus OCI Network Load Balancer keep app endpoints stable, enabling quick, reversible switchovers.
• Practical, repeatable flow: Provision two DB Systems, seed Green from Blue, start replication, validate, then flip NLB backends — keeping risk low during upgrades, tests, and schema changes.

Introduction

If you have been following my work on Medium, you may have read my previous article describing how to orchestrate a blue-green deployment for OCI PostgreSQL.

In this article, we will explore how to implement the same deployment strategy for MySQL HeatWave on Oracle Cloud Infrastructure (OCI).

Blue-green deployment is a well-established strategy used by DBAs and DevOps teams to safely introduce changes to production systems. The idea is simple but powerful: maintain two environments — Blue (Production) and Green (Staging) — that remain synchronized through replication. This allows new versions, schema changes, or application updates to be tested in a production-like environment before switching live traffic.

In the context of databases, blue-green deployment involves:

• A Blue environment serving live production workloads
• A Green environment acting as a synchronized staging copy
• Continuous replication from Blue → Green

Once testing is complete, traffic can be switched to the Green environment with minimal disruption.

With OCI MySQL HeatWave, implementing this pattern is easier than many expect. It requires only a few OCI native services combined with MySQL replication technologies such as:

• MySQL Channel Replication (binlog replication)
• Oracle GoldenGate

In this article, we will walk through how to build a blue-green deployment architecture for OCI MySQL Database Service to support application testing, regression validation, and database upgrades.

High-level steps to orchestrate a Blue-Green deployment strategy with OCI MySQL HeatWave

A typical OCI MySQL blue-green deployment consists of:

2 MySQL DB Systems

• Blue (Production)
• Green (Staging)

Replication

• MySQL Channel Replication or
• Oracle GoldenGate

Network orchestration

• Oracle Cloud Infrastructure Network Load Balancer

Private DNS

• Ensures application connectivity remains constant during switchovers

Method 1: Using OCI MySQL Channel Replication — Primary (Preferred)

Use cases: Minor version upgrades, app performance & regression testing

Pro: Easy to setup using OCI Native Tooling.
Con: Needs downtime of the source to keep backup and restore consistently, before creating channel replication. A major version upgrade is possible, but will incur a longer downtime of the source.

Prerequisites

1. OCI Tenancy with a user with appropriate IAM permissions : https://docs.oracle.com/en-us/iaas/Content/Identity/Concepts/overview.htm

2. OCI Tenancy API key: https://docs.oracle.com/en-us/iaas/Content/API/Concepts/apisigningkey.htm

3. OCI command line installed and configured on your machine from where you’re running this : https://docs.oracle.com/en-us/iaas/Content/API/SDKDocs/cliinstall.htm

Step 1

Use the below Github repository to provision OCI MySQL Infrastructure, or use your own instances:

Code Repo: https://github.com/shadabshaukat/oci-mysql-blue-green-deployment.git

Edit the terraform.tfvars as per your environment. Once done, run terraform plan and terraform apply .

Step 2

If you have used the above to create the stack, then it provisions the below compoments:

2 x OCI MySQL HA DB Systems

1 x MySQL Bastion VM

1 x NLB

1 X VCN with 2 subnets, along with network security list and routing tables

3 x DNS Records in Private Zone for the VCN created above

1 x OCI GoldenGate for MySQL Deployment

Step 3

Create a backup of the Blue Database (8.0.45), and restore to a higher version (8.4.8).

You can also use MySQL Channel replication to clone the Blue (8.0.45) to Green (8.4.8) , or even the 9.x DBSystem, using the MySQL Shell util.copyInstance utility.

mysqlsh -h mysql-blue.private.mysqlvcn.oraclevcn.com -u admin -D mysql

\js

 --Dry Run--
util.copyInstance('mysql://admin@mysql-green.private.mysqlvcn.oraclevcn.com', {"compatibility":["force_innodb", "skip_invalid_accounts", "strip_definers","strip_restricted_grants","strip_tablespaces","ignore_wildcard_grants","strip_invalid_grants","create_invisible_pks"], updateGtidSet: "append", users: "true", threads: 8, consistent: "true" , dryRun:"true"})

 --Actual Run--
util.copyInstance('mysql://admin@mysql-green.private.mysqlvcn.oraclevcn.com', {"compatibility":["force_innodb", "skip_invalid_accounts", "strip_definers","strip_restricted_grants","strip_tablespaces","ignore_wildcard_grants","strip_invalid_grants","create_invisible_pks"], updateGtidSet: "append", users: "true", threads: 8, consistent: "true" , dryRun:"false"})

---
Dump_metadata:
  Binlog_file: binary-log.002478
  Binlog_position: 197
  Executed_GTID_set: 8880f0e7-a14a-400c-8eda-1c9fd5c969be:1-15

To keep a consistent backup, stop the Blue DB System during non-business hours for some time, to ensure no writes are happening on the Source DBSystem.

oci mysql backup create \
--db-system-id ocid1.mysqldbsystem.oc1.ap-tokyo-1.aaaaaaaa5phic3km677ppklnlzmp6et7i44tbsacae5xdusxgo4hwzzpb77a \
--backup-type FULL \
--display-name Full_Backup_MySQL_Blue_Source \
--retention-in-days 7 \
--region ap-tokyo-1 \
--profile DEFAULT

Once the backup is created successfully, check in the OCI Console.

Copy the Backup OCI: ocid1.mysqlbackup.oc1.ap-tokyo-1.aaaaaaaakjzryp4l77vv3bhgiosjrb7hgqi23q4nkkmf77lovpo4oj7aewaq

Then, create a clone of this DBSystem with a higher version.

oci mysql db-system clone \
--admin-username admin \
--admin-password RAbbithole1234## \
--availability-domain Whux:AP-TOKYO-1-AD-1 \
--shape-name MySQL.2 \
--is-highly-available 1 \
--mysql-version 8.4.8 \
--source-backup-id ocid1.mysqlbackup.oc1.ap-tokyo-1.aaaaaaaakjzryp4l77vv3bhgiosjrb7hgqi23q4nkkmf77lovpo4oj7aewaq \
--subnet-id ocid1.subnet.oc1.ap-tokyo-1.aaaaaaaaazmu66kfwu5fwhtn45thir3wox2kqkqsvk6fc32lkhrdzdfpkvca \
--data-storage-size-in-gbs 50 \
--display-name MySQL_Green_Staging \
--region ap-tokyo-1 \
--profile DEFAULT

admin-username : Username of the new DB System
admin-password : Password of the new DB System
availability-domain : Get the AD details from the Blue DB System
source-backup-id : Source manual Backup OCID
subnet-id : OCID of the subnet where you want to provision the new staging database

Wait for the DBSystem to complete, then copy the OCID and IP.

ocid1.mysqldbsystem.oc1.ap-tokyo-1.aaaaaaaajlwl23yuczci637xey5xv3rfmqlkwg2x66rzbbcciw6nbko33ama

IP — 10.30.20.59

Start up the Blue Source DB System.

Create channel replication from the Target (Green Staging) to the Source (Blue).

oci mysql channel create-from-mysql \
--source-hostname 10.30.20.78 \
--source-port 3306 \
--source-username admin \
--source-password RAbbithole1234## \
--source-ssl-mode REQUIRED \
--target-db-system-id ocid1.mysqldbsystem.oc1.ap-tokyo-1.aaaaaaaa72t2etr2tdpyw47g2b4ukr22xkynlsw7g6nhjdtyub7amtpsae3q \
--display-name channel-rep-blue-to-green \
--is-enabled true \
--target-applier-username admin \
--target-channel-name replication_channel \
--target-delay-in-seconds 0 \
--target-tables-without-primary-key-handling ALLOW \
--region ap-tokyo-1 \
--profile DEFAULT

Important: You cannot upgrade from 8.0.X to 9.X You would need to first upgrade 8.0.X to 8.4 , and then to 9.X— hence you can only restore your 8.0.X backup to 8.4. Once you restored to 8.4, you can then upgrade to 9.X.

Refer to the official documentation for more information:

https://docs.oracle.com/en-us/iaas/mysql-database/doc/major-version-upgrade.html

https://docs.oracle.com/en-us/iaas/mysql-database/doc/restoring-backup.html

Upgrading 8.0 → 8.4 → 9.x

To upgrade the DB System from 8.4.x to 9.x, run the below OCI CLI:

oci mysql db-system update \
--db-system-id ocid1.mysqldbsystem.oc1.ap-tokyo-1.aaaaaaaa72t2etr2tdpyw47g2b4ukr22xkynlsw7g6nhjdtyub7amtpsae3q \
--mysql-version 9.6.1 \
--region ap-tokyo-1 \
--profile DEFAULT

The DB System is upgraded:

After this, you can create the channel replication, or start it after upgrade is completed if you wish.

Step 4

Now, let’s add the new DBSystem MySQL_Green_Staging IP to the NLB backend set, and keep it offline.

First, make sure that the header preservation of source IP is set to off on the backend set of the Network Load Balancer.

Add the IP of the new Staging DB to the NLB:

Ensure all your production apps always use the NLB’s DNS Endpoint. Your Test App environment can use the Green Staging DB endpoint.

So, every time the Production app connects to the NLB DB endpoint mysql-nlb.private.mysqlvcn.oraclevcn.com , it is directed to the Blue Environment.

MySQL  mysql-nlb.private.mysqlvcn.oraclevcn.com:3306 ssl  mysql

SQL > SELECT VERSION();

+--------------+
| VERSION()    |
+--------------+
| 8.0.45-cloud |
+--------------+

1 row in set (0.0006 sec)

Once you’ve completed testing, it is a simple matter of draining the blue-db backend, take it offline, and bring the green-db backend online.

Take the blue backend offline:

Bring the Green backend online:

App now connects back, and this time it goes to the new Green DB.

MySQL  mysql-nlb.private.mysqlvcn.oraclevcn.com:3306 ssl  mysql  

SQL > select version();
+----------------+
| version()      |
+----------------+
| 8.4.8-u1-cloud |
+----------------+
1 row in set (0.0007 sec)

Important Note: You can remove the channel replication after this activity.

Method 2: Using OCI GoldenGate Replication

Use cases: Minor and major version upgrades, app performance and regression testing

Pros: Enterprise-grade, cloud-native replication technology which can be used for testing major and minor versions both.
Cons: Additional cost; requires basic knowledge of GoldenGate.

If you’ve used the previous code repository to provision the stack, this creates an OCI GoldenGate for MySQL deployment in the same VCN. Also, it creates the OCI Network Load Balancer, along with the private DNS zones.

To use OCI Goldengate for Blue-Green deployment, we will use MySQL Shell for the initial load, and then GoldenGate to enable change data capture from the Blue to Green environment.

2 x MySQL DB Systems — one is Blue (Prod), and other is Green (Staging).

3 Records in DNS Private Zone for Blue, Green and NLB.

Prerequisites

In the Blue Database:

mysqlsh -h mysql-blue.private.mysqlvcn.oraclevcn.com -u admin -D mysql

select version();

+--------------+
| version()    |
+--------------+
| 8.0.45-cloud |
+--------------+

CREATE DATABASE app;

CREATE TABLE app.orders (
  order_id bigint PRIMARY KEY,
  customer_id bigint,
  product_id bigint,
  product_description VARCHAR(500),
  order_delivery_address VARCHAR(500),
  order_date_taken date,
  order_misc_notes VARCHAR(500)
);


INSERT into app.orders  values (1,1000,2048,"Chair","30/2 Acme Street, Pin : 9000, AZ-US","2022-12-25","Deliver to Doorstep");

INSERT into app.orders  values (2,1000,2049,"Table","30/2 Acme Street, Pin : 9000, AZ-US","2022-12-25","Deliver to Doorstep");

INSERT into app.orders  values (3,1000,2050,"Lounge","30/2 Acme Street, Pin : 9000, AZ-US","2022-12-25","Deliver to Doorstep");

INSERT into app.orders  values (4,1000,2051,"TV","30/2 Acme Street, Pin : 9000, AZ-US","2022-12-25","Deliver to Doorstep");

INSERT into app.orders  values (5,1000,2052,"Fridge","30/2 Acme Street, Pin : 9000, AZ-US","2022-12-25","Deliver to Doorstep");

INSERT into app.orders  values (6,1000,2053,"Mattress","30/2 Acme Street, Pin : 9000, AZ-US","2022-12-31","Deliver to Doorstep");

In Green Database

mysqlsh -h mysql-green.private.mysqlvcn.oraclevcn.com -u admin -D mysql

CREATE DATABASE app;

select version();

+----------------+
| version()      |
+----------------+
| 8.4.8-u1-cloud |
+----------------+

Step 1

Now, we will add Connections between Blue/Green MySQL DB and the OCI GoldenGate Deployment.

Go to OCI Console > AI Database > GoldenGate:

Create a connection:

Select Use Oracle-managed encryption key:

Assign to the MySQL Deployment:

Do the same for the Green environment, and you will have 2 connections assigned to the OCI Goldengate instance:

Step 2

Login to OCI Goldengate MySQL Console URL:

Open SSH tunnel, via bastion host for the OCI Goldengate console URL:

 ssh -fNT -L 8443:10.30.20.42:443 opc@141.147.162.126 -i ~/Download

Open in browser: https://localhost:8443/

Then, check the connection to both Databases.

Add the checkpoint table to the Green database:

Step 3

Add a Change Data Capture Extract (Capture) onto the Blue Database:

Step 4

Perform an initial Load with MySQL Shell from Blue to Green:

mysqlsh -h mysql-blue.private.mysqlvcn.oraclevcn.com -u admin -D mysql

\js

 --Dry Run--
util.copyInstance('mysql://admin@mysql-green.private.mysqlvcn.oraclevcn.com', {"compatibility":["force_innodb", "skip_invalid_accounts", "strip_definers","strip_restricted_grants","strip_tablespaces","ignore_wildcard_grants","strip_invalid_grants","create_invisible_pks"], updateGtidSet: "append", users: "true", threads: 8, consistent: "true" , dryRun:"true"})

 --Actual Run--
util.copyInstance('mysql://admin@mysql-green.private.mysqlvcn.oraclevcn.com', {"compatibility":["force_innodb", "skip_invalid_accounts", "strip_definers","strip_restricted_grants","strip_tablespaces","ignore_wildcard_grants","strip_invalid_grants","create_invisible_pks"], updateGtidSet: "append", users: "true", threads: 8, consistent: "true" , dryRun:"false"})

---
Dump_metadata:
  Binlog_file: binary-log.002478
  Binlog_position: 197
  Executed_GTID_set: 8880f0e7-a14a-400c-8eda-1c9fd5c969be:1-15

binary-log.002478 is the last position of the bin log, and we will start the CDC Replicat in GoldenGate after this position, to ensure the initial load and CDC replication is consistent and avoid duplicates.

Step 5

Create a Replicat for ongoing CDC:

Alter Replicat and position to GTID:

We will position the replicat to start after CSN, which is the bin log position of the completion of initial load — binary-log.002478

Now we have initial load and CDC via Goldengate, we can discuss how the OCI Network Load Balancer can be used to connect productions apps and then failover to Blue environment seamlessly.

Step 6

Now, let’s look at orchestrating application connectivity via the OCI Load Balancer.

If you connect through the NLB endpoint mysql-nlb.private.mysqlvcn.oraclevcn.com, you connect to the Blue database. Your test application continues to connect, and you can test the green database while the replication is happening.

mysqlsh -h mysql-nlb.private.mysqlvcn.oraclevcn.com -u admin -D mysql


select version();
+--------------+
| version()    |
+--------------+
| 8.0.45-cloud |
+--------------+

When you’re ready to switch the green environment to Production, just flip the offline status of the Green environment to false and set the offline status of Blue to true .

mysqlsh -h mysql-nlb.private.mysqlvcn.oraclevcn.com -u admin -D mysql

select version();
+ — — — — — — — — +
| version() |
+ — — — — — — — — +
| 8.4.8-u1-cloud |
+ — — — — — — — — +

Once the switch-over is completed, you can stop the Replicat, extract from OCI GoldenGate and delete them.

Final Thoughts

Blue-green deployment is an effective strategy for minimizing risk during:

• Database upgrades
• Schema changes
• Application testing

Using OCI MySQL HeatWave, you can implement this pattern using either:

• MySQL Channel Replication for simpler environments
• Oracle GoldenGate for enterprise-grade replication and major upgrades

Combined with OCI Network Load Balancer, these approaches allow seamless cutover between environments with minimal disruption to production workloads.

Frequently Asked Questions (FAQs)

What problem does blue-green solve for MySQL HeatWave on OCI?

It reduces upgrade and change risk by testing on a production-like Green copy and switching traffic only when ready.

When should I choose MySQL Channel Replication vs. Oracle GoldenGate?

Pick Channel Replication for minor upgrades and straightforward testing with minimal setup. Choose GoldenGate for major-version moves, enterprise resilience, and fine-grained CDC.

How is downtime minimized during setup?

You may briefly pause writes or stop the Blue system to take a consistent backup or use MySQL Shell copyInstance for initial load. After that, replication keeps Green current until cutover.

How do applications avoid changing connection strings?

Route all production traffic through an OCI Network Load Balancer and Private DNS. During cutover, toggle backend health/online status to point to Blue or Green without app changes.

Can I upgrade from MySQL 8.0 directly to 9.x?

No. Follow the supported path: 8.0 → 8.4 → 9.x. You can restore an 8.0 backup to 8.4, then upgrade to 9.x.

What happens after the cutover?

Disable or remove replication (Channel or GoldenGate), monitor the new production Green environment, and optionally repurpose the old Blue as the next staging target.

Zero-Downtime Blue-Green Deployments for OCI MySQL HeatWave was originally published in Oracle Developers on Medium, where people are continuing the conversation by highlighting and responding to this story.

Blue/Green deployments work by creating a fully synchronized copy of your production database and running that copy as a separate staging environment.

In this article, I will show how you can build a Blue-Green deployment for your OCI PostgreSQL, for performing apps-related testing.

High-level steps to Orchestrate a Blue-Green deployment strategy with OCI PostgreSQL

Via Logical Replication for Major Version Upgrades or App Performance/Regression Testing

Most DBAs & DevOps people are already familiar with the Blue-Green deployment methodology when deploying a new version of a service. Setting up Blue-Green deployment in PostgreSQL is easier than one would expect, requiring a few OCI native components and logical replication with pglogical (or OCI Goldengate).

1. Initial and one-time setup:

• Create an additional DB2 OCI PostgreSQL cluster.
• Create OCI DNS Private Zone for the application, for your VCN’s DNS resolver. This zone will be used by the local applications to connect to OCI PostgreSQL via the OCI Load Balancer. If you have an on-premise DNS and need to extend your on-premise DNS to resolve this private zone, please refer to this documentation: https://docs.oracle.com/en/solutions/oci-best-practices-networking/private-dns-oci-and-premises-or-third-party-cloud.html
• Create an OCI Network Load Balancer for their applications to connect to. This LB will act as a proxy to the actual database system.
• Have the Load Balancer backend point to the primary endpoint IP address of the database system (say DB1).

2. When we have new changes, do initial load for OCI Postgres between DB1 to DB2 using any of the logical data migration utilities like pg_dump, pg_restore.

3. Establish publication and subscription from DB1 to DB2 using pglogical (or OCI Goldengate).

4. Have the app tests hit DB2 endpoint to perform read queries, validate and certify the changes.

5. When DB2 appears ready for production consumption, orchestrate:

• Pause any app activity and pause pglogical replication (optional). Since pglogical is a logical replication tool, the DB2 is always available in Read-Write mode. Just for app testing, we are using read-only mode, to avoid conflicts and complex scenario of reverse replication from DB2 to DB1.
• Update load balancer backend to the primary endpoint IP address of DB2.
• Stop the replication setup between DB1 and DB2, by stopping the publisher and subscribers.

6. Production client apps are now connecting to the new Production environment (Green).

Step-By-Step Orchestration of Blue Green Deployment:

Setup

1. OCI PostgreSQL Database v15, which is the Blue environment aka Current Production.

IP address: 10.150.2.105

2. OCI PostgreSQL Database v16 which is the Green environment aka Staging.

IP address: 10.150.2.62

3. OCI Network Load Balancer, with the 2 database endpoints added as a backend set, fronted with a listener on tcp/5432.

Listener

Backends

Make sure the Blue (Production) environment is active, and the Green environment backend set is offline and drained.

4. Create an OCI DNS Private Zone in your VCN’s DNS resolver.

In this case, I call my private zone postgres.local.

nlb-app.postgres.local is the main FQDN all apps will use to connect the backend database.

nlb-app-blue.postgres.local is the FQDN for Blue Database, and this is not used by App connections.

nlb-app-green.postgres.local is the FQDN for the Green Database and this is used by the App stack, which will perform the Read-only queries for validating the application performance.

We will use the OCI Network Load Balancers backend, set to make the blue backend IP offline, and activate the green IP. This incurs a small outage (in seconds depending on TTL of the DNS resolver) where the App moves from connecting to Blue Database to the Final Green Database, which is promoted as production.

Create pglogical replication from DB1 to DB2

Reference: https://docs.oracle.com/en/learn/oci-pglogical-extension/index.html#task-5-configure-the-source-database

In this example, we are demonstrating using a simple table: https://github.com/shadabshaukat/STAND/blob/main/postgres.sql

#################
 — Source Blue v15 — 
#################

Hostname: primary.umiokgnhd4hlpov7xncfwymgxv4pgq.postgresql.us-sanjose-1.oci.oraclecloud.com
Version: 15.12

• Run the following query to grant permissions on the source database, to enable logical replication.

CREATE EXTENSION pglogical;
show oci.admin_enabled_extensions ;

alter role postgres with replication;
grant EXECUTE on FUNCTION pg_catalog.pg_replication_origin_session_reset() to postgres ;
grant EXECUTE on FUNCTION pg_catalog.pg_replication_origin_session_setup to postgres ;
grant all on FUNCTION pg_catalog.pg_replication_origin_session_setup to postgres;

Note: postgres is the admin user created during the database setup process.

• Create the publisher node on the source database.

SELECT pglogical.create_node(node_name := 'provider1',dsn :='host=primary.umiokgnhd4hlpov7xncfwymgxv4pgq.postgresql.us-sanjose-1.oci.oraclecloud.com port=5432 user=postgres password=RAbbithole1234## dbname=postgres');

node_name: Specify the name of the publisher to be created on the source database.
host: Enter the fully qualified domain name (FQDN) of the source database.
port_number: Provide the port on which the source database is running.
database_name: Specify the database where the publication will be created.

• Include all tables in the public schema to the default replication set.

SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public']);

replication_set_add_all_tables
 - - - - - - - - - - - - - - - - 
 t
(1 row)

#################
 — Target Green v16 — 
#################

Hostname: primary.46mtfkxsj6337nqvx2de6gq3a57m4a.postgresql.us-sanjose-1.oci.oraclecloud.com
Version: 16.8

• Run the following query to grant permissions on the target database to enable logical replication.

CREATE EXTENSION pglogical;
show oci.admin_enabled_extensions ;

alter role postgres with replication;
grant EXECUTE on FUNCTION pg_catalog.pg_replication_origin_session_reset() to postgres ;
grant EXECUTE on FUNCTION pg_catalog.pg_replication_origin_session_setup to postgres ;
grant all on FUNCTION pg_catalog.pg_replication_origin_session_setup to postgres;

Note: postgres is the admin user created during the database setup process.

• Create the subscriber node on the target database.

SELECT pglogical.create_node(node_name := 'subscriber1',dsn :='host=primary.46mtfkxsj6337nqvx2de6gq3a57m4a.postgresql.us-sanjose-1.oci.oraclecloud.com port=5432 user=postgres password=RAbbithole1234## dbname=postgres');

node_name: Define the name of the subscriber on the target database.
host: Enter the fully qualified domain name (FQDN) of the target database.
port_number: Enter the port on which the target database is running.
database_name: Provide the name of the database where the subscription will be created.

• Set Schema-only on the target database. This can also be done with pg_dump and pg_restore or psql.

CREATE TABLE orders (
 order_id SERIAL PRIMARY KEY,
 customer_id INTEGER,
 product_id INTEGER,
 product_description VARCHAR(500),
 order_delivery_address VARCHAR(500),
 order_date_taken DATE,
 order_misc_notes VARCHAR(500)
);

CREATE OR REPLACE FUNCTION add_random_orders(n INTEGER) RETURNS TEXT AS $$
DECLARE
 i INTEGER := 1;
 v_customer_id INTEGER;
 v_product_id INTEGER;
 v_product_description VARCHAR(500);
 v_order_delivery_address VARCHAR(500);
 v_order_date_taken DATE;
 v_order_misc_notes VARCHAR(500);
BEGIN
 WHILE i <= n LOOP
 v_customer_id := floor(random() * 100) + 1;
 v_product_id := floor(random() * 50) + 1;
 v_product_description := CONCAT('Product ', floor(random() * 10) + 1);
 v_order_delivery_address := CONCAT('Address ', floor(random() * 10) + 1);
 v_order_date_taken := CURRENT_DATE - (floor(random() * 30) || ' days')::INTERVAL;
 v_order_misc_notes := CONCAT('Note ', floor(random() * 10) + 1);
INSERT INTO orders (customer_id, product_id, product_description, order_delivery_address, order_date_taken, order_misc_notes)
 VALUES (v_customer_id, v_product_id, v_product_description, v_order_delivery_address, v_order_date_taken, v_order_misc_notes);
i := i + 1;
 END LOOP;
RETURN n || ' random orders added.';
EXCEPTION
 WHEN OTHERS THEN
 RAISE EXCEPTION 'Error: %', SQLERRM;
END;
$$ LANGUAGE plpgsql;

• Create the subscription on the subscriber node, which will initiate the background synchronization and replication processes.

SELECT pglogical.create_subscription(subscription_name := 'subscription1',provider_dsn := 'host=primary.umiokgnhd4hlpov7xncfwymgxv4pgq.postgresql.us-sanjose-1.oci.oraclecloud.com port=5432 user=postgres password=RAbbithole1234## dbname=postgres sslmode=verify-full sslrootcert=/etc/opt/postgresql/ca-bundle.pem');

subscription_name: Provide the name of the subscription.
host: Provide the FQDN of the source database.
port_number: Provide the port on which the target database is running.
database_name: Provide the name of the source database.

Note: Be sure to use sslmode=verify-full and sslrootcert = /etc/opt/postgresql/ca-bundle.pem in subscription creation string to prevent any connection failures.

SELECT pglogical.wait_for_subscription_sync_complete('subscription1');

#################
 — Target Green v16 — 
#################

• Run the following statement to check the status of your subscription on your target database.

select * from pglogical.show_subscription_status();

subscription_name | status | provider_node |
 provider_dsn |
 slot_name | replication_sets | forward_origins
 - - - - - - - - - -+ - - - - - - -+ - - - - - - - -+ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -+
 - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - -+ - - - - - - - - -
 subscription1 | replicating | provider1 | host=primary.umiokgnhd4hlpov7xncfwymgxv4pgq.postgresql.us-sanjose-1.oci.oraclecloud.c
om port=5432 user=postgres password=RAbbithole1234## dbname=postgres sslmode=verify-full sslrootcert=/etc/opt/postgresql/ca-bundle.pem |
 pgl_postgres_provider1_subscription1 | {default,default_insert_only,ddl_sql} | {all}
(1 row)

#################
 — Source Blue v15 — 
#################

• Run the following statement to check the status of your replication on your source database.

SELECT * FROM pg_stat_replication;

pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend
_xmin | state | sent_lsn | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state |
 reply_time
 - - - -+ - - - - - + - - - - - + - - - - - - - - - + - - - - - - - + - - - - - - - - -+ - - - - - - -+ - - - - - - - - - - - - - - - -+ - - - - 
 - - - + - - - - - -+ - - - - - -+ - - - - - -+ - - - - - -+ - - - - - - + - - - - - -+ - - - - - -+ - - - - - - + - - - - - - - -+ - - - - - - + - 
 - - - - - - - - - - - - - - -
 18569 | 16387 | postgres | subscription1 | 10.150.2.196 | | 1247 | 2025–12–02 04:50:07.242335+00 |
 | streaming | 0/16BAB50 | 0/16BAB50 | 0/16BAB50 | 0/16BAB50 | | | | 0 | async | 2
025–12–02 05:09:09.626248+00
(1 row)

#################
 — Target Green v16 — 
#################
A. Stop or Start the Replication

• You can disable the subscription using the following command on your target database.

select pglogical.alter_subscription_disable('subscription_name');

-- Target --
select pglogical.alter_subscription_disable('subscription1');

• You can enable the subscription using the following command on your target database.

select pglogical.alter_subscription_enable('subscription_name');

-- Target --
select pglogical.alter_subscription_enable('subscription1');

Note: In subscription_name, enter the name of the subscription created at the target.

B. Drop the Subscription

select pglogical.drop_subscription('subscription_name');

-- Target --
select pglogical.drop_subscription('subscription1');

Note: In subscription_name, enter the name of the subscription created at the target.

C. Drop the Nodes

• To drop the node from your Source or Target database, execute the following command :

select pglogical.drop_node('node_name');

Note: In node_name, enter the node name created in source/target database.

-- Source --
select pglogical.drop_node('provider1');

-- Target --
select pglogical.drop_node('subscriber1');

Orchestration of Blue-Green Deployment by Updating OCI Network Load Balancer Backend

Scenario 1 — Everything is business as usual (Refer Diagram 1 — Blue-Green Workflow above)

All the production clients are connected to Blue (Production) DB environment.

NLB has the Blue environment active in the backend set.

We will use psql for the testing. Let’s add aliases to the app host to make the testing a bit simpler.

alias pgblue='PGPASSWORD=YourPasswor@123# psql -h nlb-app-blue.postgres.local -U postgres -d postgres'
alias pggreen='PGPASSWORD=YourPasswor@123# psql -h nlb-app-green.postgres.local -U postgres -d postgres'
alias pgnlb='PGPASSWORD=YourPasswor@123# psql -h nlb-app.postgres.local -U postgres -d postgres'

Your apps are connecting to the v15 Blue database via this endpoint.

$ alias | grep pgnlb
alias pgnlb='PGPASSWORD=YourPasswor@123# psql -h nlb-app.postgres.local -U postgres -d postgres'

$ pgnlb

psql (17.7, server 15.12)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, compression: off, ALPN: none)
Type "help" for help.
## The Server we're connecting to is the v15.12 which is Blue

Scenario 2 — The replication is ongoing to DB2 and Your Testing Clients connect to DB2

Your testing apps are now connecting to the v16 Green database via this endpoint.

$ alias | grep pggreen
alias pggreen='PGPASSWORD=YourPasswor@123# psql -h nlb-app-green.postgres.local -U postgres -d postgres'

$ pggreen
psql (17.7, server 16.8)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, compression: off, ALPN: none)
Type "help" for help.

Scenario 3 — The replication is stopped to Green (DB2), Green is promoted as Production and your Production clients connect to the Green environment

Flip the OCI Network Load Balancer backend set.

Make the current Blue IP backend offline, and drain it.

Save the changes.

For a brief moment, there is no connectivity from the app to the database, and your business users should be notified that there will be a brief outage.

If you run pgnlb, it will hang, as there is no backend IP to connect to.

[opc@linux-bastion ~]$ pgnlb

Now, let’s make the Green environment as online from the backend set, and re-connect the apps.

Select Save Changes.

Now, connect with pgnlb.

[opc@linux-bastion ~]$ pgnlb
psql (17.7, server 16.8)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, compression: off, ALPN: none)
Type "help" for help.

You can see that pgnlb is now connecting to the new upgraded v16 version, which is the Green environment.

Advantages of Blue-Green testing

· Perform testing of major version upgrades (e.g. pgsql 13 -> 15)

· Major patching/testing with a production-like copy

· Validate application behavior against new database versions/configs

· Support phased testing (read-only validation, performance testing)

· Decouple app endpoint from database endpoint using a Load Balancer

Conclusion

We’ve successfully created an end-to-end Blue-Green Testing Methodolgy for OCI PostgreSQL.

Blue-Green Deployment for OCI PostgreSQL was originally published in Oracle Developers on Medium, where people are continuing the conversation by highlighting and responding to this story.

Why PostgreSQL Is All You Need for Search in 2025 (No Elasticsearch, Solr OpenSearch Required)

PostgreSQL has quietly become the backbone of some of the world’s most effective search experiences. Starting with version 16, its Full-Text Search (FTS) engine is smarter, faster, and more flexible than ever.

In this guide, we’ll build a search-centric online shop and demonstrate how PostgreSQL can deliver advanced search, typo tolerance, ranking, and highlighting — all natively in SQL, without relying on external services.

🔎 Introduction

Search defines the user experience in any online store. Customers expect results that are fast, accurate, typo-tolerant, and ranked by relevance. With PostgreSQL 16+, you get Google-like search capabilities out-of-the-box:

• Natural language understanding
• Tolerance for misspellings
• Flexible ranking and weighting
• Sub-second response times

And the best part? It’s all inside PostgreSQL — no extra SaaS costs, no additional latency.

This tutorial walks through production-ready SQL patterns you can use today in your e-commerce or SaaS product.

🚀 Why PostgreSQL Full-Text Search?

• No external search engine: Everything runs in SQL — no Elasticsearch, no SaaS bills.
• Powerful features: Boolean queries, ranked results, snippets, and phrase search.
• Fuzzy matching: Catch typos like iphne → iPhone.
• Flexible schema support: Works across text, arrays, and multilingual content.

🛒 Designing the Online Shop Schema

We’ll keep it simple with one table, but the same design applies to large catalogs.

-- 1) Create the products table
CREATE TABLE products (
id SERIAL PRIMARY KEY,
title TEXT NOT NULL,
description TEXT,
brand TEXT,
tags TEXT[],
price NUMERIC(10,2)
);

-- 2) Add the column (no generated expression)
ALTER TABLE products
  ADD COLUMN search_vector tsvector;

-- 3) Create a trigger function that builds the weighted tsvector
CREATE OR REPLACE FUNCTION products_tsvector_update()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  NEW.search_vector :=
      setweight(to_tsvector('pg_catalog.english', coalesce(NEW.title,'')), 'A')
   || setweight(to_tsvector('pg_catalog.english', coalesce(NEW.brand,'')), 'B')
   || setweight(to_tsvector('pg_catalog.english', coalesce(NEW.description,'')), 'C')
   || setweight(to_tsvector('pg_catalog.english', array_to_string(NEW.tags,' ')), 'D');
  RETURN NEW;
END;
$$;

-- 4) Hook it up for INSERT/UPDATE
CREATE TRIGGER t_products_tsvector_update
BEFORE INSERT OR UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION products_tsvector_update();

🌱 Seeding Example Data

A realistic dataset makes the queries more accurate.

The below INSERT sql query inserts 108 unique electronics product into the productstable.

-- If Data already exists in the table, truncate the table and reset the identity column sequence
--TRUNCATE TABLE products RESTART IDENTITY;

INSERT INTO products (title, description, brand, tags, price) VALUES
('Apple iPhone 15', 'Latest Apple iPhone with improved camera and battery.', 'Apple', ARRAY['smartphone','electronics','flagship'], 1200.00),
('Samsung Galaxy S24', 'Premium Android phone with powerful features.', 'Samsung', ARRAY['smartphone','android','flagship'], 1100.00),
('Sony WH-1000XM5 Headphones', 'Top-rated noise-cancelling headphones for music lovers.', 'Sony', ARRAY['headphones','audio','wireless'], 400.00),
('Dell XPS 13 Laptop', 'Sleek ultrabook, ideal for productivity and travel.', 'Dell', ARRAY['laptop','ultrabook','windows'], 1800.00),
('Apple MacBook Air M3', 'Apple silicon, fast and lightweight. Perfect for students.', 'Apple', ARRAY['laptop','macos','apple silicon'], 1800.00),
('Google Pixel Buds Pro', 'Google''s premium wireless earbuds with ANC.', 'Google', ARRAY['earbuds','audio','wireless'], 250.00),
('Apple iPad Air M2', 'Powerful and portable tablet with Apple silicon and vibrant display.', 'Apple', ARRAY['tablet','electronics'], 699.00),
('Apple MacBook Pro 14', 'Professional-grade laptop with Apple silicon and Liquid Retina display.', 'Apple', ARRAY['laptop','macos','pro'], 2299.00),
('Apple Watch Series 9', 'Advanced smartwatch with health and fitness tracking.', 'Apple', ARRAY['smartwatch','wearable','fitness'], 399.00),
('Apple AirPods Pro 2', 'Premium wireless earbuds with active noise cancellation.', 'Apple', ARRAY['earbuds','audio','wireless'], 249.00),
('Apple HomePod mini', 'Smart speaker with Siri and powerful sound in a compact size.', 'Apple', ARRAY['speaker','smart home','audio'], 129.00),
('Apple TV 4K', 'Streaming device with 4K HDR and Dolby Atmos support.', 'Apple', ARRAY['streaming','tv','entertainment'], 179.00),
('Samsung Galaxy S24 Ultra', 'Flagship Android phone with advanced camera and display.', 'Samsung', ARRAY['smartphone','android','flagship'], 1299.00),
('Samsung Galaxy Tab S9', 'High-performance Android tablet with AMOLED display.', 'Samsung', ARRAY['tablet','android'], 899.00),
('Samsung Galaxy Book3', 'Powerful Windows ultrabook with sleek design.', 'Samsung', ARRAY['laptop','windows','ultrabook'], 1499.00),
('Samsung Galaxy Watch6', 'Smartwatch with advanced health tracking and AMOLED display.', 'Samsung', ARRAY['smartwatch','wearable'], 349.00),
('Samsung Galaxy Buds2 Pro', 'Wireless earbuds with Hi-Fi sound and ANC.', 'Samsung', ARRAY['earbuds','audio','wireless'], 229.00),
('Samsung Neo QLED TV', 'High-end QLED 4K TV with HDR and vivid colors.', 'Samsung', ARRAY['tv','display','home theater'], 1999.00),
('Google Pixel 8', 'Next-gen Google smartphone with AI-powered features.', 'Google', ARRAY['smartphone','android','flagship'], 899.00),
('Google Pixel 8 Pro', 'Premium Pixel with advanced camera and AI tools.', 'Google', ARRAY['smartphone','android','flagship'], 1099.00),
('Google Pixel Tablet', 'Versatile Android tablet designed for home and productivity.', 'Google', ARRAY['tablet','android'], 599.00),
('Google Nest Hub 2', 'Smart display with Google Assistant integration.', 'Google', ARRAY['smart home','display','assistant'], 129.00),
('Google Nest WiFi Pro', 'Mesh Wi-Fi 6E system for whole-home coverage.', 'Google', ARRAY['wifi','mesh','networking'], 399.00),
('Google Chromecast 4K', 'Streaming device with Google TV support.', 'Google', ARRAY['streaming','entertainment','tv'], 49.00),
('Sony WF-1000XM5', 'Premium earbuds with market-leading noise cancellation.', 'Sony', ARRAY['earbuds','audio','wireless'], 279.00),
('Sony PlayStation 5', 'Next-gen gaming console with immersive graphics.', 'Sony', ARRAY['gaming','console'], 499.00),
('Sony A7 IV', 'Mirrorless full-frame camera with 4K recording.', 'Sony', ARRAY['camera','mirrorless','photography'], 2499.00),
('Sony ZV-E10', 'Vlogger-friendly mirrorless camera with interchangeable lenses.', 'Sony', ARRAY['camera','mirrorless','video'], 799.00),
('Sony Bravia XR OLED', 'High-end OLED TV with XR processor for cinematic quality.', 'Sony', ARRAY['tv','display','home theater'], 2299.00),
('Sony HT-A7000', 'Dolby Atmos soundbar with immersive surround sound.', 'Sony', ARRAY['audio','soundbar','home theater'], 1199.00),
('Dell XPS 15', 'High-performance laptop for creators and professionals.', 'Dell', ARRAY['laptop','windows','ultrabook'], 1999.00),
('Dell Alienware m16', 'Gaming laptop with powerful GPU and cooling system.', 'Dell', ARRAY['laptop','gaming','windows'], 2499.00),
('Dell UltraSharp 27 4K', 'Professional-grade 4K monitor with excellent color accuracy.', 'Dell', ARRAY['monitor','display','4k'], 699.00),
('Dell Inspiron 14', 'Affordable and reliable laptop for everyday tasks.', 'Dell', ARRAY['laptop','windows','budget'], 799.00),
('HP Spectre x360 14', 'Premium convertible laptop with OLED display.', 'HP', ARRAY['laptop','windows','2-in-1'], 1499.00),
('HP Omen 16', 'Gaming laptop with dedicated graphics and RGB keyboard.', 'HP', ARRAY['laptop','gaming','windows'], 1699.00),
('HP Envy 15', 'Stylish and powerful laptop for creators.', 'HP', ARRAY['laptop','windows','creative'], 1399.00),
('HP Dragonfly G4', 'Ultra-light business laptop with long battery life.', 'HP', ARRAY['laptop','business','windows'], 1899.00),
('Lenovo ThinkPad X1 Carbon', 'Business-class ultrabook with durability and speed.', 'Lenovo', ARRAY['laptop','business','windows'], 1799.00),
('Lenovo Legion 7', 'High-performance gaming laptop with RGB keyboard.', 'Lenovo', ARRAY['laptop','gaming','windows'], 2199.00),
('Lenovo Yoga 9i', 'Convertible 2-in-1 laptop with touch display.', 'Lenovo', ARRAY['laptop','2-in-1','windows'], 1399.00),
('Lenovo IdeaPad 5', 'Affordable laptop for students and casual use.', 'Lenovo', ARRAY['laptop','budget','windows'], 699.00),
('ASUS ROG Zephyrus G14', 'Gaming laptop with powerful GPU and lightweight design.', 'ASUS', ARRAY['laptop','gaming','windows'], 1899.00),
('ASUS ZenBook 14 OLED', 'Slim ultrabook with OLED display and long battery.', 'ASUS', ARRAY['laptop','ultrabook','windows'], 1399.00),
('ASUS ProArt PA279CV Monitor', 'Professional 4K monitor for creators.', 'ASUS', ARRAY['monitor','display','4k'], 499.00),
('ASUS RT-AX88U Router', 'Wi-Fi 6 router with high throughput.', 'ASUS', ARRAY['router','wifi','networking'], 299.00),
('Acer Swift 5', 'Lightweight ultrabook for mobility.', 'Acer', ARRAY['laptop','ultrabook','windows'], 1199.00),
('Acer Predator Helios 16', 'Gaming laptop with high refresh display.', 'Acer', ARRAY['laptop','gaming','windows'], 1799.00),
('Acer Nitro 5', 'Budget-friendly gaming laptop.', 'Acer', ARRAY['laptop','gaming','windows'], 999.00),
('Bose QuietComfort Ultra', 'Noise-cancelling headphones with immersive audio.', 'Bose', ARRAY['headphones','audio','wireless'], 399.00),
('Bose SoundLink Flex', 'Portable Bluetooth speaker with waterproof design.', 'Bose', ARRAY['speaker','audio','wireless'], 149.00),
('Sennheiser Momentum 4', 'High-end wireless headphones with long battery life.', 'Sennheiser', ARRAY['headphones','audio','wireless'], 379.00),
('Sennheiser IE 600', 'Premium in-ear monitors for audiophiles.', 'Sennheiser', ARRAY['earbuds','audio','wired'], 699.00),
('JBL Charge 5', 'Portable Bluetooth speaker with long playtime.', 'JBL', ARRAY['speaker','audio','wireless'], 179.00),
('JBL Flip 6', 'Compact waterproof Bluetooth speaker.', 'JBL', ARRAY['speaker','audio','wireless'], 129.00),
('JBL Bar 5.1', 'Soundbar with surround sound and wireless subwoofer.', 'JBL', ARRAY['soundbar','audio','home theater'], 499.00),
('DJI Mini 4 Pro', 'Compact drone with 4K camera and obstacle avoidance.', 'DJI', ARRAY['drone','camera','aerial'], 999.00),
('DJI Air 3', 'Drone with dual camera system for professional use.', 'DJI', ARRAY['drone','camera','aerial'], 1299.00),
('DJI Osmo Pocket 3', 'Handheld camera with 4K stabilization.', 'DJI', ARRAY['camera','handheld','video'], 499.00),
('Canon EOS R6 Mark II', 'Mirrorless full-frame camera with excellent autofocus.', 'Canon', ARRAY['camera','mirrorless','photography'], 2499.00),
('Canon PIXMA G6020', 'All-in-one inkjet printer with refillable tanks.', 'Canon', ARRAY['printer','inkjet','office'], 299.00),
('Nikon Z6 II', 'Mirrorless camera with 24MP sensor and 4K video.', 'Nikon', ARRAY['camera','mirrorless','photography'], 1999.00),
('Nikon Z8', 'Professional mirrorless camera with 8K recording.', 'Nikon', ARRAY['camera','mirrorless','photography'], 3999.00),
('Logitech MX Master 3S', 'Ergonomic wireless mouse with advanced features.', 'Logitech', ARRAY['mouse','accessory','wireless'], 129.00),
('Logitech MX Keys', 'Wireless keyboard with smart illumination.', 'Logitech', ARRAY['keyboard','accessory','wireless'], 119.00),
('Logitech G Pro X Superlight', 'Ultra-lightweight wireless gaming mouse.', 'Logitech', ARRAY['mouse','gaming','wireless'], 149.00),
('Razer DeathAdder V3', 'Gaming mouse with ergonomic design.', 'Razer', ARRAY['mouse','gaming','wired'], 79.00),
('Razer BlackWidow V4 Pro', 'Mechanical gaming keyboard with RGB.', 'Razer', ARRAY['keyboard','gaming','mechanical'], 229.00),
('Razer Kiyo Pro Ultra', 'High-quality webcam for streaming.', 'Razer', ARRAY['camera','webcam','streaming'], 299.00),
('Microsoft Surface Laptop 7', 'Powerful Windows laptop with sleek design.', 'Microsoft', ARRAY['laptop','windows','ultrabook'], 1599.00),
('Microsoft Surface Pro 11', '2-in-1 tablet PC with detachable keyboard.', 'Microsoft', ARRAY['tablet','windows','2-in-1'], 1299.00),
('Microsoft Xbox Series X', 'Next-gen gaming console with 4K HDR gaming.', 'Microsoft', ARRAY['gaming','console'], 499.00),
('Nintendo Switch OLED', 'Popular hybrid gaming console with OLED display.', 'Nintendo', ARRAY['gaming','console','handheld'], 349.00),
('AMD Radeon RX 7900 XTX', 'High-end GPU for gaming and creators.', 'AMD', ARRAY['gpu','graphics','pc'], 999.00),
('NVIDIA GeForce RTX 4090', 'Flagship GPU with massive performance for gaming and AI.', 'NVIDIA', ARRAY['gpu','graphics','pc'], 1599.00),
('Seagate FireCuda 530 2TB', 'High-speed NVMe SSD for gaming PCs.', 'Seagate', ARRAY['storage','ssd','nvme'], 299.00),
('WD Black SN850X 2TB', 'High-performance NVMe SSD for gamers.', 'WD', ARRAY['storage','ssd','nvme'], 289.00),
('WD My Cloud EX2 Ultra', '2-bay NAS for home storage and backups.', 'WD', ARRAY['storage','nas','backup'], 349.00),
('Crucial T700 2TB', 'PCIe Gen5 NVMe SSD with blazing speed.', 'Crucial', ARRAY['storage','ssd','nvme'], 399.00),
('TP-Link Archer AXE75', 'Wi-Fi 6E router with fast speeds.', 'TP-Link', ARRAY['router','wifi','networking'], 199.00),
('TP-Link Deco XE75', 'Mesh Wi-Fi system with Wi-Fi 6E support.', 'TP-Link', ARRAY['wifi','mesh','networking'], 399.00),
('Netgear Nighthawk RAXE500', 'Wi-Fi 6E router with tri-band performance.', 'Netgear', ARRAY['router','wifi','networking'], 499.00),
('Netgear Orbi RBKE963', 'Premium mesh Wi-Fi system for large homes.', 'Netgear', ARRAY['wifi','mesh','networking'], 799.00),
('Philips Hue Starter Kit', 'Smart lighting kit with hub and bulbs.', 'Philips', ARRAY['lighting','smart home'], 199.00),
('Ring Video Doorbell Pro 2', 'Smart doorbell with motion detection and HDR video.', 'Ring', ARRAY['smart home','security','camera'], 249.00),
('Epson EcoTank ET-3850', 'All-in-one printer with refillable ink tanks.', 'Epson', ARRAY['printer','inkjet','office'], 329.00),
('Brother HL-L3270CDW', 'Compact color laser printer with wireless support.', 'Brother', ARRAY['printer','laser','office'], 249.00),
('Kindle Paperwhite 11th Gen', 'E-reader with warm light and waterproof design.', 'Kindle', ARRAY['ereader','reading'], 179.00),
('Meta Quest 3', 'VR headset with advanced graphics and comfort.', 'Meta', ARRAY['vr','headset','gaming'], 499.00),
('Oculus Quest 2', 'Affordable standalone VR headset.', 'Oculus', ARRAY['vr','headset','gaming'], 299.00),
('Anker Soundcore Motion X600', 'Portable Bluetooth speaker with spatial audio.', 'Anker', ARRAY['speaker','audio','wireless'], 199.00),
('Anker PowerCore 20K', 'High-capacity portable charger with USB-C.', 'Anker', ARRAY['charger','powerbank','accessory'], 79.00),
('Sonos Beam Gen 2', 'Compact soundbar with Dolby Atmos.', 'Sonos', ARRAY['soundbar','audio','home theater'], 499.00),
('Sonos Sub Mini', 'Wireless subwoofer for Sonos systems.', 'Sonos', ARRAY['speaker','audio','wireless'], 429.00),
('Yamaha YAS-209', 'Soundbar with built-in subwoofer and Alexa.', 'Yamaha', ARRAY['soundbar','audio','home theater'], 349.00),
('LG C3 OLED 55', 'OLED TV with stunning picture and webOS.', 'LG', ARRAY['tv','display','home theater'], 1799.00),
('LG UltraFine 5K', 'High-resolution 5K monitor for Mac users.', 'LG', ARRAY['monitor','display','5k'], 1299.00),
('Hisense U8K Mini-LED 65', 'Affordable Mini-LED TV with HDR support.', 'Hisense', ARRAY['tv','display','home theater'], 1399.00),
('GoPro Hero12 Black', 'Action camera with 5.3K video and stabilization.', 'GoPro', ARRAY['camera','action','4k'], 399.00),
('Garmin Fenix 7 Pro', 'Premium multisport smartwatch with GPS.', 'Garmin', ARRAY['smartwatch','fitness','gps'], 799.00),
('Fitbit Versa 4', 'Fitness smartwatch with health tracking.', 'Fitbit', ARRAY['smartwatch','fitness','wearable'], 229.00),
('Tile Pro 2024', 'Bluetooth tracker with long range and replaceable battery.', 'Tile', ARRAY['tracker','accessory','smart home'], 49.00),
('Synology DS923+', 'Powerful 4-bay NAS for small businesses.', 'Synology', ARRAY['storage','nas','backup'], 649.00),
('QNAP TS-464', 'Compact 4-bay NAS with expandable storage.', 'QNAP', ARRAY['storage','nas','backup'], 699.00),
('Corsair K70 RGB Pro', 'Mechanical gaming keyboard with RGB lighting.', 'Corsair', ARRAY['keyboard','gaming','mechanical'], 169.00),
('SteelSeries Apex Pro TKL', 'Gaming keyboard with adjustable switches.', 'SteelSeries', ARRAY['keyboard','gaming','mechanical'], 199.00),
('SanDisk Extreme Pro 256GB', 'High-performance microSD card for phones and cameras.', 'SanDisk', ARRAY['storage','microsd','memory'], 59.00),
('SanDisk Extreme Pro 512GB', 'High-capacity microSD card for professional use.', 'SanDisk', ARRAY['storage','microsd','memory'], 99.00);

select count(*) from products;

⚙️ Setting Up Full-Text Search

Create Indexes

To prevent slow searches as your catalog grows, create these indexes. Indexes keep searches sub-second even at scale.

CREATE INDEX idx_products_search_vector ON products USING GIN (search_vector);

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_products_title_trgm ON products USING GIN (title gin_trgm_ops);

CREATE EXTENSION IF NOT EXISTS unaccent;
CREATE INDEX IF NOT EXISTS idx_products_title_trgm_ci
ON products USING GIN ((lower(title)) gin_trgm_ops);

🔥 Search in Action

[1] Basic Full-Text Search with Ranking

SELECT
  id, title, brand, price,
  ts_rank(search_vector, plainto_tsquery('english', 'apple laptop')) AS rank
FROM products
WHERE search_vector @@ plainto_tsquery('english', 'apple laptop')
ORDER BY rank DESC;

----+----------------------+-------+---------+------------
 id |        title         | brand |  price  |    rank
----+----------------------+-------+---------+------------
  8 | Apple MacBook Pro 14 | Apple | 2299.00 |  0.6552824
  5 | Apple MacBook Air M3 | Apple | 1800.00 | 0.30206525
(2 rows)

plainto_tsquery: Converts user-friendly queries (“apple laptop”)

@@: Matches documents against the query

ts_rank: Computes relevance score

[2] Boolean, Phrase, and Prefix Search

-- Boolean: apple OR samsung
SELECT * FROM products
WHERE search_vector @@ to_tsquery('english', 'apple | samsung');

----+----------------------+------------------------------------------------------------+---------+-----------------------------------+---------+------------------------------------------------------------------------------------------------------------------------------------------------
 id |        title         |                        description                         |  brand  |               tags                |  price  |                                                                 search_vector
----+----------------------+------------------------------------------------------------+---------+-----------------------------------+---------+------------------------------------------------------------------------------------------------------------------------------------------------
  7 | Apple iPhone 15      | Latest Apple iPhone with improved camera and battery.      | Apple   | {smartphone,electronics,flagship} | 1200.00 | '15':3A 'appl':1A,4B,6C 'batteri':12C 'camera':10C 'electron':14 'flagship':15 'improv':9C 'iphon':2A,7C 'latest':5C 'smartphon':13
  8 | Samsung Galaxy S24   | Premium Android phone with powerful features.              | Samsung | {smartphone,android,flagship}     | 1100.00 | 'android':6C,12 'featur':10C 'flagship':13 'galaxi':2A 'phone':7C 'power':9C 'premium':5C 's24':3A 'samsung':1A,4B 'smartphon':11
 11 | Apple MacBook Air M3 | Apple silicon, fast and lightweight. Perfect for students. | Apple   | {laptop,macos,"apple silicon"}    | 1800.00 | 'air':3A 'appl':1A,5B,6C,16 'fast':8C 'laptop':14 'lightweight':10C 'm3':4A 'macbook':2A 'maco':15 'perfect':11C 'silicon':7C,17 'student':13C
(3 rows)

-- Phrase match: "noise cancelling"
SELECT * FROM products
WHERE search_vector @@ phraseto_tsquery('english', 'noise cancelling');

----+----------------------------+---------------------------------------------------------+-------+-----------------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 id |           title            |                       description                       | brand |            tags             | price  |                                                                                           search_vector
----+----------------------------+---------------------------------------------------------+-------+-----------------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  9 | Sony WH-1000XM5 Headphones | Top-rated noise-cancelling headphones for music lovers. | Sony  | {headphones,audio,wireless} | 400.00 | '1000xm5':4A 'audio':18 'cancel':12C 'headphon':5A,13C,17 'lover':16C 'music':15C 'nois':11C 'noise-cancel':10C 'rate':9C 'soni':1A,6B 'top':8C 'top-rat':7C 'wh':3A 'wh-1000xm5':2A 'wireless':19
(1 row)


-- Prefix match: words starting with "head"
SELECT * FROM products
WHERE search_vector @@ to_tsquery('english', 'head:*');

----+----------------------------+---------------------------------------------------------+-------+-----------------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 id |           title            |                       description                       | brand |            tags             | price  |                                                                                           search_vector
----+----------------------------+---------------------------------------------------------+-------+-----------------------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  9 | Sony WH-1000XM5 Headphones | Top-rated noise-cancelling headphones for music lovers. | Sony  | {headphones,audio,wireless} | 400.00 | '1000xm5':4A 'audio':18 'cancel':12C 'headphon':5A,13C,17 'lover':16C 'music':15C 'nois':11C 'noise-cancel':10C 'rate':9C 'soni':1A,6B 'top':8C 'top-rat':7C 'wh':3A 'wh-1000xm5':2A 'wireless':19
(1 row)

[3] Highlighting Matches

SELECT
title,
ts_headline('english', description, plainto_tsquery('english', 'wireless'), 'StartSel=<b>, StopSel=</b>')
FROM
products
WHERE
search_vector @@ plainto_tsquery('english', 'wireless');

----------------------------+---------------------------------------------------------
           title            |                       ts_headline
----------------------------+---------------------------------------------------------
 Sony WH-1000XM5 Headphones | Top-rated noise-cancelling headphones for music lovers.
 Pixel Buds Pro             | Google's premium <b>wireless</b> earbuds with ANC.
(2 rows)

[4] Fuzzy / Typo-Tolerant Search with Trigrams

Powerful for catching “iphne”, “galaxi”, “headphnes” etc

Confirm data + extension and lower similarity threshold for smaller words

SELECT COUNT(*) FROM products;
SELECT title FROM products;
SELECT extname FROM pg_extension WHERE extname='pg_trgm';

SHOW pg_trgm.similarity_threshold;      -- likely 0.3
SET pg_trgm.similarity_threshold = 0.15;  -- try 0.15–0.2 for short typos

SELECT title, similarity(lower(title), 'iphne') AS sim
FROM products
WHERE lower(title) % 'iphne'
ORDER BY sim DESC
LIMIT 5;

If the result returns 0 rows, avoid changing it at all and filter explicitly:

SELECT title, similarity(lower(title), 'iphne') AS sim
FROM products
WHERE similarity(lower(title), 'iphne') > 0.12
ORDER BY sim DESC
LIMIT 5;

-----------------+------------
      title      |    sim
-----------------+------------
 Apple iPhone 15 | 0.22222222
(1 row)

SELECT title, similarity(lower(title), 'iphne') AS sim
FROM products
WHERE similarity(lower(title), 'iphne') > 0.18
ORDER BY sim DESC
LIMIT 5;

-----------------+------------
      title      |    sim
-----------------+------------
 Apple iPhone 15 | 0.22222222
(1 row)

SELECT title, similarity(lower(title), q) AS sim
FROM products, (VALUES ('iphne'), ('galaxi'), ('macbok')) AS v(q)
WHERE similarity(lower(title), q) > 0.18
ORDER BY q, sim DESC;

--------------------------+------------
          title           |    sim
--------------------------+------------
 Samsung Galaxy S24       |       0.25
 Samsung Galaxy Tab S9    |  0.2173913
 Samsung Galaxy Book3     |  0.2173913
 Samsung Galaxy Watch6    | 0.20833333
 Samsung Galaxy S24 Ultra |  0.1923077
 Samsung Galaxy Buds2 Pro | 0.18518518
 Apple iPhone 15          | 0.22222222
 Apple MacBook Air M3     |        0.3
 Apple MacBook Pro 14     | 0.27272728
(9 rows)

[5] Hybrid search: FTS + Fuzzy

SELECT *,
  ts_rank(search_vector, plainto_tsquery('english', 'iphone')) AS rank,
  similarity(title, 'iphne') AS sim
FROM products
WHERE search_vector @@ plainto_tsquery('english', 'iphone')
   OR title % 'iphne'
ORDER BY rank DESC, sim DESC;

----+-----------------+-------------------------------------------------------+-------+-----------------------------------+---------+-------------------------------------------------------------------------------------------------------------------------------------+-----------+------------
 id |      title      |                      description                      | brand |               tags                |  price  |                                                            search_vector                                                            |   rank    |    sim
----+-----------------+-------------------------------------------------------+-------+-----------------------------------+---------+-------------------------------------------------------------------------------------------------------------------------------------+-----------+------------
  7 | Apple iPhone 15 | Latest Apple iPhone with improved camera and battery. | Apple | {smartphone,electronics,flagship} | 1200.00 | '15':3A 'appl':1A,4B,6C 'batteri':12C 'camera':10C 'electron':14 'flagship':15 'improv':9C 'iphon':2A,7C 'latest':5C 'smartphon':13 | 0.6383234 | 0.22222222
(1 row)

--Alternate

WITH q AS (
  SELECT 'iphone'::text AS fts_query, 'iphne'::text AS typo
)
SELECT p.title,
       ts_rank(
         to_tsvector('pg_catalog.english', coalesce(p.title,'') || ' ' || coalesce(p.description,'')),
         plainto_tsquery('pg_catalog.english', (SELECT fts_query FROM q))
       ) AS rank,
       similarity(lower(p.title), (SELECT typo FROM q)) AS sim
FROM products p
WHERE to_tsvector('pg_catalog.english', coalesce(p.title,'') || ' ' || coalesce(p.description,'')) @@
      plainto_tsquery('pg_catalog.english', (SELECT fts_query FROM q))
   OR lower(p.title) % (SELECT typo FROM q)
ORDER BY rank DESC NULLS LAST, sim DESC NULLS LAST
LIMIT 10;

-----------------+-------------+------------
      title      |    rank     |    sim
-----------------+-------------+------------
 Apple iPhone 15 | 0.075990885 | 0.22222222
(1 row)

⚡ Performance Best Practices

• Always add a GIN index on search_vector
• Use trigram indexes for typo-prone columns (title, brand)
• Tune ranking weights with setweight
• Run VACUUM ANALYZE regularly for accurate query planning

✅ Conclusion & Next Steps

With PostgreSQL 16+ FTS:

• You get fast, typo-resistant, ranked search with zero added servers.
• Advanced features rival dedicated search engines — without the SaaS bill.
• Your search logic lives inside the database, not in scattered microservices.

If you like this blog post please press the clap button 👏

What’s next?

• Add multilingual support: swap english for your audience’s language.
• Expand search to customer reviews, blog posts, and FAQs.
• Incorporate behavioral ranking (clicks, purchases, personalization).