O2 Extension Installation

O2 is a PostgreSQL extension that improves user convenience by providing an interface that is compatible with functions/packages/types supported by Oracle in the PostgreSQL environment.

System requirements

Follows the operating system/hardware specifications supported by OpenSQL.

---

Overview

O2 Extension offers two installation methods for installing the product, including

• Install with the make method

• Install with the shell method

O2 installation file directory structure

The O2 installation file collection directory, which will be enclosed inside the OpenSQL installation package, is in the following format. Inside, there are two installation files (Makefile and install.sh).

ls -rlta

total 60
drwxr-xr-x  2 root root 4096 Mar 13 05:09 utl_file
drwxr-xr-x  2 root root 4096 Mar 13 05:09 o2views
drwxr-xr-x  2 root root 4096 Mar 13 05:09 o2types
drwxr-xr-x  2 root root 4096 Mar 13 05:09 o2functions
-rw-r--r--  1 root root 3266 Mar 13 05:09 install.sh        # script for sh installation 
drwxr-xr-x  2 root root 4096 Mar 13 05:09 dbms_sql
drwxr-xr-x  2 root root 4096 Mar 13 05:09 dbms_random
drwxr-xr-x  2 root root 4096 Mar 13 05:09 dbms_pipe
drwxr-xr-x  2 root root 4096 Mar 13 05:09 dbms_output
drwxr-xr-x  2 root root 4096 Mar 13 05:09 dbms_alert
-rw-r--r--  1 root root 1465 Mar 13 05:09 VERSION.json      # o2 extensions ver. information
-rw-r--r--  1 root root  672 Mar 13 05:09 Makefile          # Installation file for the make method
drwxr-xr-x 12 root root 4096 Mar 13 05:09 .
drwxr-xr-x  6 root root 4096 Mar 13 05:09 ..

Installation with make method

On Linux platforms with PostgreSQL installed, the O2 Extension can be installed automatically via the make command.

There are two types of installations: a full installation, which installs all extensions that include the O2 Extension at the same time, and individual installations, which install individual extensions.

Pre-installation requirements

PostgreSQL must be installed and the make and pg_config commands must be available on the terminal command line.

Full installation

Navigate to the directory where the O2 Extension installation components are gathered (the directory where the Makefile is located) and execute the make command below.

cd o2
make all # o2 extensions  full installation

# Below is an example log that is output during the entire installation process
make[1]: Entering directory '/home/opensql/o2/build/o2-dist-1.0.1/o2functions'
/usr/bin/mkdir -p '/home/opensql/postgres/build/16/lib'
/usr/bin/mkdir -p '/home/opensql/postgres/build/16/share/extension'
/usr/bin/mkdir -p '/home/opensql/postgres/build/16/share/extension'
/usr/bin/install -c -m 755  o2functions.so '/home/opensql/postgres/build/16/lib/o2functions.so'
/usr/bin/install -c -m 644 .//o2functions.control '/home/opensql/postgres/build/16/share/extension/'
/usr/bin/install -c -m 644 .//VERSION.json .//o2functions--1.0.sql  '/home/opensql/postgres/build/16/share/extension/'
make[1]: Leaving directory '/home/opensql/o2/build/o2-dist-1.0.1/o2functions'
...

Individual installations

A individual installation is done by adding the name of the extension you want to install as an argument to the make command.

cd o2
# make [ extension1 extension2 ... ]
make o2functions o2types dbms_output

# Below is an example log from a make execution
make -C o2functions install
make[1]: Entering directory '/home/opensql/o2/build/o2-dist-1.0.1/o2functions'
/usr/bin/mkdir -p '/home/opensql/postgres/build/16/lib'
/usr/bin/mkdir -p '/home/opensql/postgres/build/16/share/extension'
/usr/bin/mkdir -p '/home/opensql/postgres/build/16/share/extension'
/usr/bin/install -c -m 755  o2functions.so '/home/opensql/postgres/build/16/lib/o2functions.so'
/usr/bin/install -c -m 644 .//o2functions.control '/home/opensql/postgres/build/16/share/extension/'
/usr/bin/install -c -m 644 .//VERSION.json .//o2functions--1.0.sql  '/home/opensql/postgres/build/16/share/extension/'
make[1]: Leaving directory '/home/opensql/o2/build/o2-dist-1.0.1/o2functions'
...

List of individual installation extension names

• DBMS_ALERT

• DBMS_OUTPUT

• DBMS_PIPE

• DBMS_RANDOM

• DBMS_SQL

• UTL_FILE

• O2Functions

• O2Types

• O2Views

---

Installation with shell method

In environments where the make command is not available, you can install manually using a shell script.

You can install the O2 Extension manually on Linux platforms with PostgreSQL installed. You can install the O2 Extension in two ways: a full installation, which installs all the extensions that the O2 Extension includes at the same time, or an individual installation, which installs the individual extensions.

Pre-installation requirements

PostgreSQL must be installed and the pg_config binary must be available on the terminal command line.

Full installation

Execute the install.sh script included in the directory (o2) where the O2 Extension installation components are located.

cd o2
sh install.sh

# Below is an example log that is output during the entire installation process
Installing extensions to:
  Library directory: /home/opensql/postgres/build/16/lib
  Shared extension directory: /home/opensql/postgres/build/16/share/extension
No extension names provided. Scanning ./extensions/ for valid extensions...
Extensions to install: o2checks dbms_alert dbms_output dbms_pipe dbms_random dbms_sql o2functions o2packages o2types utl_file o2views
Installing extension: o2checks
  Skipped installing o2checks; This is the internal test framework.
Installing extension: dbms_alert
  Copied control file: ./dbms_alert.control
  Copied SQL file: ./dbms_alert--1.0.sql
  Copied shared library: ./extensions/dbms_alert/dbms_alert.so
...
Copied METADATA: VERSION.json
Installation completed.

Individual installation

A individual installation is done by adding the name of the extension you want to install as an argument to the make command.

# sh install.sh [ extension1 extension2 ... ]
sh install.sh o2functions o2types dbms_output

# Below is an example log that is output during an individual inSstallation
Installing extensions to:
  Library directory: /home/opensql/postgres/build/16/lib
  Shared extension directory: /home/opensql/postgres/build/16/share/extension
Extensions to install: o2functions o2types dbms_output
Installing extension: o2functions
  Copied control file: ./o2functions.control
  Copied SQL file: ./o2functions--1.0.sql
  Copied shared library: ./extensions/functions/o2functions.so
Installing extension: o2types
  Copied control file: ./o2types.control
  Copied SQL file: ./o2types--1.0.sql
  Copied shared library: ./extensions/types/o2types.so
Installing extension: dbms_output
  Copied control file: ./dbms_output.control
  Copied SQL file: ./dbms_output--1.0.sql
  Copied shared library: ./extensions/dbms_output/dbms_output.so
Copied METADATA: VERSION.json
Installation completed.

List of individual installation extension names

• DBMS_ALERT

• DBMS_OUTPUT

• DBMS_PIPE

• DBMS_RANDOM

• DBMS_SQL

• UTL_FILE

• O2Functions

• O2Types

• O2Views

---

Precaution with installation

Configuration of pg_config and $PATH

Both make/shell installation methods require that you perform the installation with an account where pg_config is available. Verify that the value of the $PATH variable contains /usr/pgsql-{PGversion}/bin .

[root@20fec5585ddd /]# echo $PATH
/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/pgsql-16/bin/

# If the path doeS not exISt in $PATH for the account you are currently logged Into, add the path approprIate for the PG verSIon to the $PATH path
[root@20fec5585ddd /]# echo "export PATH=$PATH:/usr/pgsql-16/bin/" >> ~/.bashrc
[root@20fec5585ddd /]# source ~/.bashrc

Method to install from a user account without PGpath set in the $PATH value

You will need to bypass the $PATH and proceed with the installation as the user with the set permissions. Execute the following commands with sudo privileges.

sudo -u {another USER with PGpath Set to $PATH} bash -l -c "{command to install make or shell}"

# 예시)
# If the path, /usr/pgsql-16/bin/, is registered in the $PATH of postgres user
# and to install o2 accessing user, opensql, by the make method
[opensql@20fec5585ddd /]# sudo -u postgres bash -l -c "make all"

---

Installation components

Extension type

O2 extensions are categorized into four main groups as follows: The sublists within each group list the names of the individual extensions that are actually available.

• Types Extension

  • O2Types

• Views Extension

  • O2Views

• Functions Extension

  • O2Functions

• Package Extensions

  • DBMS_ALERT

  • DBMS_OUTPUT

  • DBMS_PIPE

  • DBMS_RANDOM

  • DBMS_SQL

  • UTL_FILE

Extension components

Each extension consists of the following files, which can be created separately for each extension.

Ex) The configuration file for the O2Functions Extension is shown below.

• Shared object file (eg. o2functions.so)

• Control file (eg. o2functions.control)

• Script file (eg. o2functions—1.0.sql, o2functions—1.0—1.1.sql, …)

And there's a VERSION.json file that organizes the version information for extensions into one place.

Activate O2 Extension

To activate the O2 Extension after installing it, you need to execute the SQL command like below.

CREATE EXTENSION {extension name} [WITH SCHEMA {default schema}];

The WITH SCHEMA clause allows you to specify the schema in which the objects defined by the extension will be created; otherwise, the default schema specified in the extension's control file will be used.

However, it is not available to specify the default schema for Package Extension (DBMS_ALERT, DBMS_PIPE, etc.).

---

Update O2 Extension

To update the O2 Extension to a higher version, you need to execute the SQL command like below.

ALTER EXTENSION {extension name} UPDATE TO {new version};

---

Precautions for HA configuration

In HA configurations, you must manually attach to each node to install and update the O2 Extension. Ensure that the O2 Extension version installed on each node matches.

When a version difference occurs between nodes, the difference in behavior between versions causes a difference in which node the query was sent to when it was performed.