Install orafce for AlloyDB Omni

Debian UBI

This page describes how to manually add the orafce extension to an existing AlloyDB Omni installation. The orafce extension provides functions and operators that emulate a subset of functions and packages from the Oracle database. This extension simplifies migration of applications from Oracle to PostgreSQL-compatible databases such as AlloyDB Omni.

Before you begin

Install AlloyDB Omni on your system.

Add orafce to your AlloyDB Omni installation

To add the orafce extension to your AlloyDB Omni installation, follow these steps:

1. Find your installed AlloyDB Omni version labels:

   Docker

   docker run --rm -it  google/alloydbomni cat VERSION.txt
   

   Podman

   podman run --rm -it  google/alloydbomni cat VERSION.txt
   

   The output is similar to the following:

   AlloyDB Omni version: 16.8.0
   

   Take note of the AlloyDB Omni version number because you need it in the next step.

2. Build a custom Docker image that includes the orafce extension:

   $ ~/alloydb-omni-orafce
   
   $ cd ~/alloydb-omni-orafce
   
   $ cat <<EOF > Dockerfile
   FROM google/alloydbomni:16.8.0-ubi
   RUN arch=$(uname -m)
   RUN dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-$(arch)/pgdg-redhat-repo-latest.noarch.rpm
   RUN dnf install -y orafce_16
   
   # On RHEL-based systems, PostgreSQL extensions like Orafce and PostGIS are typically installed in /usr/pgsql-16/share/extension and /usr/pgsql-16/lib.
   # This step creates symbolic links to those files in the paths expected by AlloyDB Omni,
   # which lets AlloyDB Omni locate extension control files and shared libraries without duplicating data.
   RUN \
      for file in /usr/pgsql-16/share/extension/*; do \
      target="/usr/lib/postgresql/16/share/extension/$(realpath -m --relative-to=/usr/pgsql-16/share/extension/ "$file")"; \
      if [ ! -e "$target" ]; then \
         ln -s "$file" "$target" || (echo "Failed to link \"$file\" to \"$target\", exiting." && exit 1); \
      else \
         echo "$target already exists"; \
      fi; \
   done && \
   for file in /usr/pgsql-16/lib/*; do \
      target="/usr/lib/postgresql/16/lib/$(realpath -m --relative-to=/usr/pgsql-16/lib/ "$file")"; \
      if [ ! -e "$target" ]; then \
         ln -s "$file" "$target" || (echo "Failed to link \"$file\" to \"$target\", exiting." && exit 1); \
      else \
         echo "$target already exists"; \
      fi; \
   done
   EOF
   

3. Create a new container with AlloyDB Omni named my-omni-orafce:

   docker build -t google/alloydbomni-with-orafce:latest
   docker run --name my-omni-orafce  -e POSTGRES_PASSWORD=NEW_PASSWORD  -d google/alloydbomni-with-orafce:OMNI_VERSION
   

4. Connect to your database with the orafce extension:

   docker exec -it my-omni-orafce psql -h localhost -U postgres
   

5. Enable orafce:

   CREATE EXTENSION IF NOT EXISTS ORAFCE;
   

6. Confirm that orafce is installed and enabled:

   SELECT oracle.sysdate();
   

   The output is similar to the following:

   postgres=# SELECT oracle.sysdate();
   sysdate
   ---------------------
   2024-06-10 16:36:30
   (1 row)