IvorySQL (Oracle)
Module:
IvorySQL is an open-source “Oracle-compatible” PostgreSQL kernel, developed by HighGo, licensed under Apache 2.0.
The Oracle compatibility here refers to compatibility at the PL/SQL, syntax, built-in functions, data types, system views, MERGE, and GUC parameter levels. It’s not a wire protocol compatibility like Babelfish, openHalo, or FerretDB that allows using the original client drivers. Users still need to use PostgreSQL client tools to access IvorySQL, but can use Oracle-compatible syntax.
Currently, IvorySQL’s latest version 4.4 maintains compatibility with PostgreSQL’s latest minor version 17.4, and provides binary RPM/DEB packages for mainstream Linux distributions. Pigsty offers the option to replace the native PostgreSQL with the IvorySQL kernel in PG RDS.
Quick Start
Use the standard procedure to install Pigsty with the ivory configuration template:
curl -fsSL https://repo.pigsty.cc/get | bash; cd ~/pigsty
./bootstrap # Install Pigsty dependencies
./configure -c ivory # Use IvorySQL configuration template
./install.yml # Execute deployment using playbook
For production deployments, you should edit the auto-generated pigsty.yml configuration file to modify parameters like passwords before executing ./install.yml for deployment.
The latest IvorySQL 4.4 is equivalent to PostgreSQL 17. Any client tool compatible with PostgreSQL’s wire protocol can access IvorySQL clusters.
By default, you can use a PostgreSQL client to access through the alternative 1521 port, which enables Oracle compatibility mode by default.
Configuration Instructions
To use the IvorySQL kernel in Pigsty, modify the following four configuration parameters:
pg_mode: Useivorycompatibility moderepo_extra_packages: Downloadivorysqlpackagespg_packages: Installivorysqlpackagespg_libs: Load Oracle syntax compatibility extensions
It’s that simple - just add these four lines to the global variables in the configuration file, and Pigsty will replace the native PostgreSQL kernel with IvorySQL:
pg_mode: ivory # IvorySQL compatibility mode, uses IvorySQL binaries
pg_packages: [ ivorysql, pgsql-common ] # Install ivorysql, replacing pgsql-main kernel
pg_libs: 'liboracle_parser, pg_stat_statements, auto_explain' # Load Oracle compatibility extensions
repo_extra_packages: [ ivorysql ] # Download ivorysql packages
IvorySQL also provides a series of new GUC parameters that can be specified in pg_parameters.
Extensions
Most of the PGSQL modules’ extension (non-SQL classes) cannot be used directly on the IvorySQL kernel. If you need to use them, you need to recompile and install from source code for the new kernel.
Currently, the IvorySQL kernel comes with the following 109 extension plugins:
IvorySQL Available Extensions
| Name | Version | Description |
|---|---|---|
| amcheck | 1.4 | functions for verifying relation integrity |
| autoinc | 1.0 | functions for autoincrementing fields |
| bloom | 1.0 | bloom access method - signature file based index |
| bool_plperl | 1.0 | transform between bool and plperl |
| bool_plperlu | 1.0 | transform between bool and plperlu |
| btree_gin | 1.3 | support for indexing common datatypes in GIN |
| btree_gist | 1.7 | support for indexing common datatypes in GiST |
| citext | 1.6 | data type for case-insensitive character strings |
| cube | 1.5 | data type for multidimensional cubes |
| dblink | 1.2 | connect to other PostgreSQL databases from within a database |
| dict_int | 1.0 | text search dictionary template for integers |
| dict_xsyn | 1.0 | text search dictionary template for extended synonym processing |
| dummy_index_am | 1.0 | dummy_index_am - index access method template |
| dummy_seclabel | 1.0 | Test code for SECURITY LABEL feature |
| earthdistance | 1.2 | calculate great-circle distances on the surface of the Earth |
| file_fdw | 1.0 | foreign-data wrapper for flat file access |
| fuzzystrmatch | 1.2 | determine similarities and distance between strings |
| hstore | 1.8 | data type for storing sets of (key, value) pairs |
| hstore_plperl | 1.0 | transform between hstore and plperl |
| hstore_plperlu | 1.0 | transform between hstore and plperlu |
| hstore_plpython3u | 1.0 | transform between hstore and plpython3u |
| injection_points | 1.0 | Test code for injection points |
| insert_username | 1.0 | functions for tracking who changed a table |
| intagg | 1.1 | integer aggregator and enumerator (obsolete) |
| intarray | 1.5 | functions, operators, and index support for 1-D arrays of integers |
| isn | 1.2 | data types for international product numbering standards |
| ivorysql_ora | 1.0 | Oracle Compatible extenison on Postgres Database |
| jsonb_plperl | 1.0 | transform between jsonb and plperl |
| jsonb_plperlu | 1.0 | transform between jsonb and plperlu |
| jsonb_plpython3u | 1.0 | transform between jsonb and plpython3u |
| lo | 1.1 | Large Object maintenance |
| ltree | 1.3 | data type for hierarchical tree-like structures |
| ltree_plpython3u | 1.0 | transform between ltree and plpython3u |
| moddatetime | 1.0 | functions for tracking last modification time |
| ora_btree_gin | 1.0 | support for indexing oracle datatypes in GIN |
| ora_btree_gist | 1.0 | support for oracle indexing common datatypes in GiST |
| pageinspect | 1.12 | inspect the contents of database pages at a low level |
| pg_buffercache | 1.5 | examine the shared buffer cache |
| pg_freespacemap | 1.2 | examine the free space map (FSM) |
| pg_get_functiondef | 1.0 | Get function’s definition |
| pg_prewarm | 1.2 | prewarm relation data |
| pg_stat_statements | 1.11 | track planning and execution statistics of all SQL statements executed |
| pg_surgery | 1.0 | extension to perform surgery on a damaged relation |
| pg_trgm | 1.6 | text similarity measurement and index searching based on trigrams |
| pg_visibility | 1.2 | examine the visibility map (VM) and page-level visibility info |
| pg_walinspect | 1.1 | functions to inspect contents of PostgreSQL Write-Ahead Log |
| pgcrypto | 1.3 | cryptographic functions |
| pgrowlocks | 1.2 | show row-level locking information |
| pgstattuple | 1.5 | show tuple-level statistics |
| plisql | 1.0 | PL/iSQL procedural language |
| plperl | 1.0 | PL/Perl procedural language |
| plperlu | 1.0 | PL/PerlU untrusted procedural language |
| plpgsql | 1.0 | PL/pgSQL procedural language |
| plpython3u | 1.0 | PL/Python3U untrusted procedural language |
| plsample | 1.0 | PL/Sample |
| pltcl | 1.0 | PL/Tcl procedural language |
| pltclu | 1.0 | PL/TclU untrusted procedural language |
| postgres_fdw | 1.1 | foreign-data wrapper for remote PostgreSQL servers |
| refint | 1.0 | functions for implementing referential integrity (obsolete) |
| seg | 1.4 | data type for representing line segments or floating-point intervals |
| spgist_name_ops | 1.0 | Test opclass for SP-GiST |
| sslinfo | 1.2 | information about SSL certificates |
| tablefunc | 1.0 | functions that manipulate whole tables, including crosstab |
| tcn | 1.0 | Triggered change notifications |
| test_bloomfilter | 1.0 | Test code for Bloom filter library |
| test_copy_callbacks | 1.0 | Test code for COPY callbacks |
| test_custom_rmgrs | 1.0 | Test code for custom WAL resource managers |
| test_ddl_deparse | 1.0 | Test code for DDL deparse feature |
| test_dsa | 1.0 | Test code for dynamic shared memory areas |
| test_dsm_registry | 1.0 | Test code for the DSM registry |
| test_ext1 | 1.0 | Test extension 1 |
| test_ext2 | 1.0 | Test extension 2 |
| test_ext3 | 1.0 | Test extension 3 |
| test_ext4 | 1.0 | Test extension 4 |
| test_ext5 | 1.0 | Test extension 5 |
| test_ext6 | 1.0 | test_ext6 |
| test_ext7 | 1.0 | Test extension 7 |
| test_ext8 | 1.0 | Test extension 8 |
| test_ext9 | 1.0 | test_ext9 |
| test_ext_cine | 1.0 | Test extension using CREATE IF NOT EXISTS |
| test_ext_cor | 1.0 | Test extension using CREATE OR REPLACE |
| test_ext_cyclic1 | 1.0 | Test extension cyclic 1 |
| test_ext_cyclic2 | 1.0 | Test extension cyclic 2 |
| test_ext_evttrig | 1.0 | Test extension - event trigger |
| test_ext_extschema | 1.0 | test @extschema@ |
| test_ext_req_schema1 | 1.0 | Required extension to be referenced |
| test_ext_req_schema2 | 1.0 | Test schema referencing of required extensions |
| test_ext_req_schema3 | 1.0 | Test schema referencing of 2 required extensions |
| test_ext_set_schema | 1.0 | Test ALTER EXTENSION SET SCHEMA |
| test_ginpostinglist | 1.0 | Test code for ginpostinglist.c |
| test_integerset | 1.0 | Test code for integerset |
| test_lfind | 1.0 | Test code for optimized linear search functions |
| test_parser | 1.0 | example of a custom parser for full-text search |
| test_pg_dump | 1.0 | Test pg_dump with an extension |
| test_predtest | 1.0 | Test code for optimizer/util/predtest.c |
| test_radixtree | 1.0 | Test code for radix tree |
| test_rbtree | 1.0 | Test code for red-black tree library |
| test_regex | 1.0 | Test code for backend/regex/ |
| test_resowner | 1.0 | Test code for ResourceOwners |
| test_shm_mq | 1.0 | Test code for shared memory message queues |
| test_slru | 1.0 | Test code for SLRU |
| test_tidstore | 1.0 | Test code for tidstore |
| tsm_system_rows | 1.0 | TABLESAMPLE method which accepts number of rows as a limit |
| tsm_system_time | 1.0 | TABLESAMPLE method which accepts time in milliseconds as a limit |
| unaccent | 1.1 | text search dictionary that removes accents |
| uuid-ossp | 1.1 | generate universally unique identifiers (UUIDs) |
| worker_spi | 1.0 | Sample background worker |
| xid_wraparound | 1.0 | Tests for XID wraparound |
| xml2 | 1.1 | XPath querying and XSLT |
Caveats
-
The IvorySQL software package is located in the
pigsty-infrarepository, not inpigsty-pgsqlorpigsty-ivoryrepositories. -
The default FHS of IvorySQL 4.4 has changed, please pay attention to users upgrading from older versions.
-
IvorySQL 4.4 requires gibc version >= 2.17, which is currently supported by Pigsty.
-
The last IvorySQL version supporting EL7 is 3.3, corresponding to PostgreSQL 16.3, and IvorySQL 4.x no longer supports EL7.
-
Pigsty does not assume any warranty for using the IvorySQL kernel, and any issues or requests should be addressed to the manufacturer.
Feedback
Was this page helpful?
Glad to hear it! Please tell us how we can improve.
Sorry to hear that. Please tell us how we can improve.