How to Configure Oracle Connection Timeout in PostgreSQL

Why does an Oracle connection timeout matter?

Long connection attempts block queries, tie up connection pool slots, and create user frustration. Setting a timeout lets PostgreSQL abort the attempt quickly and return an error you can handle in code or retry logic.

Which PostgreSQL extension supports Oracle timeouts?

oracle_fdw is the de-facto extension for querying Oracle from PostgreSQL. It exposes a connect_timeout server option that limits connection attempts in seconds.

How do I set connect_timeout when creating a server?

Add the option directly in the CREATE SERVER statement so every session inherits it. You can also amend an existing server with ALTER SERVER.

CREATE SERVER with timeout

CREATE SERVER oracle_sales
FOREIGN DATA WRAPPER oracle_fdw
OPTIONS (
dbserver '//ora-host:1521/ORCLPDB',
connect_timeout '10' -- seconds
);

ALTER SERVER to add timeout

ALTER SERVER oracle_sales
OPTIONS (ADD connect_timeout '10');

Can I override the timeout per session?

Yes. Supply the parameter in the connection string of IMPORT FOREIGN SCHEMA or SET the oracle_fdw.connect_timeout GUC for the current backend.

What happens when the timeout is reached?

oracle_fdw raises error SQLSTATE 08001 (connection exception). Wrap the foreign query in application-side retry logic or fallback to cached data.

Best practices for production

Keep timeout under expected network latency (5–15s). Monitor failed connections in pg_stat_activity and Oracle alert logs. Combine with statement_timeout to protect long-running calls after the connection is established.

Example: querying Orders after setting timeout

IMPORT FOREIGN SCHEMA orders_schema
FROM SERVER oracle_sales INTO public;

SELECT id, customer_id, total_amount
FROM orders
WHERE order_date > CURRENT_DATE - INTERVAL '30 days';

If Oracle is unreachable for more than 10 seconds, the query aborts immediately.

How do I verify the timeout value?

Run SELECT srvoptions FROM pg_foreign_server WHERE srvname = 'oracle_sales'; and ensure connect_timeout=10 appears in the result.