IvorySQL (Oracle)

Run “Oracle-Compatible” PostgreSQL cluster with the IvorySQL Kernel open sourced by HighGo

Pigsty allows you to create PostgreSQL clusters with the IvorySQL kernel, which is a PostgreSQL fork that is compatible with Oracle SQL dialects.

Beware that ivorySQL packages are conflicting with the vanilla PostgreSQL packages, they are mutually exclusive. Pigsty Professional Edition provides offline installation solutions that include the IvorySQL kernel in another local repo.

The latest version of IvroySQL is 3.4, which is compatible with PostgreSQL 16.4. IvorySQL now only supports EL8/EL9.

The last version of IvorySQL that supports EL7 is 3.3, which corresponds to PostgreSQL 16.3.


Get Started

The following parameters need to be configured for the IvorySQL database cluster:

#----------------------------------#
# Ivory SQL Configuration
#----------------------------------#
node_repo_modules: local,node,pgsql,ivory  # add ivorysql upstream repo
pg_mode: ivory                    # IvorySQL Oracle Compatible Mode
pg_packages: [ 'ivorysql patroni pgbouncer pgbackrest pg_exporter pgbadger vip-manager' ]
pg_libs: 'liboracle_parser, pg_stat_statements, auto_explain'
pg_extensions: [ ]                # do not install any vanilla postgresql extensions

You have to dynamically load the liboracle_parser library to enable Oracle SQL compatibility.


Client Access

IvorySQL 3 is equivalent to PostgreSQL 16, you can connect to the IvorySQL cluster using any PostgreSQL compatible client tools.


Installation

If you have the Internet access, you can install the IvorySQL software package online by setting the following parameters:

node_repo_modules: local,node,pgsql,ivory
node_packages: [ ivorysql ]

And install the IvorySQL kernel and related software packages by running the following command:

./node.yml -t node_repo,node_pkg

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 101 extension plugins:

name version comment
hstore_plperl 1.0 transform between hstore and plperl
plisql 1.0 PL/iSQL procedural language
hstore_plperlu 1.0 transform between hstore and plperlu
adminpack 2.1 administrative functions for PostgreSQL
insert_username 1.0 functions for tracking who changed a table
dblink 1.2 connect to other PostgreSQL databases from within a database
dict_int 1.0 text search dictionary template for integers
amcheck 1.3 functions for verifying relation integrity
intagg 1.1 integer aggregator and enumerator (obsolete)
autoinc 1.0 functions for autoincrementing fields
bloom 1.0 bloom access method - signature file based index
dict_xsyn 1.0 text search dictionary template for extended synonym processing
btree_gin 1.3 support for indexing common datatypes in GIN
earthdistance 1.1 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
btree_gist 1.7 support for indexing common datatypes in GiST
intarray 1.5 functions, operators, and index support for 1-D arrays of integers
citext 1.6 data type for case-insensitive character strings
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
cube 1.5 data type for multidimensional cubes
dummy_index_am 1.0 dummy_index_am - index access method template
dummy_seclabel 1.0 Test code for SECURITY LABEL feature
hstore 1.8 data type for storing sets of (key, value) pairs
jsonb_plperlu 1.0 transform between jsonb and plperlu
lo 1.1 Large Object maintenance
ltree 1.2 data type for hierarchical tree-like structures
moddatetime 1.0 functions for tracking last modification time
old_snapshot 1.0 utilities in support of old_snapshot_threshold
ora_btree_gin 1.0 support for indexing oracle datatypes in GIN
pg_trgm 1.6 text similarity measurement and index searching based on trigrams
ora_btree_gist 1.0 support for oracle indexing common datatypes in GiST
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
pgstattuple 1.5 show tuple-level statistics
pageinspect 1.12 inspect the contents of database pages at a low level
pgrowlocks 1.2 show row-level locking information
pg_buffercache 1.4 examine the shared buffer cache
pg_stat_statements 1.10 track planning and execution statistics of all SQL statements executed
pg_freespacemap 1.2 examine the free space map (FSM)
plsample 1.0 PL/Sample
pg_prewarm 1.2 prewarm relation data
pg_surgery 1.0 extension to perform surgery on a damaged relation
seg 1.4 data type for representing line segments or floating-point intervals
postgres_fdw 1.1 foreign-data wrapper for remote PostgreSQL servers
refint 1.0 functions for implementing referential integrity (obsolete)
test_ext_req_schema1 1.0 Required extension to be referenced
spgist_name_ops 1.0 Test opclass for SP-GiST
test_ext_req_schema2 1.0 Test schema referencing of required extensions
test_shm_mq 1.0 Test code for shared memory message queues
sslinfo 1.2 information about SSL certificates
test_slru 1.0 Test code for SLRU
tablefunc 1.0 functions that manipulate whole tables, including crosstab
bool_plperl 1.0 transform between bool and plperl
tcn 1.0 Triggered change notifications
test_ext_req_schema3 1.0 Test schema referencing of 2 required extensions
test_bloomfilter 1.0 Test code for Bloom filter library
test_copy_callbacks 1.0 Test code for COPY callbacks
test_ginpostinglist 1.0 Test code for ginpostinglist.c
test_custom_rmgrs 1.0 Test code for custom WAL resource managers
test_integerset 1.0 Test code for integerset
test_ddl_deparse 1.0 Test code for DDL deparse feature
tsm_system_rows 1.0 TABLESAMPLE method which accepts number of rows as a limit
test_ext1 1.0 Test extension 1
tsm_system_time 1.0 TABLESAMPLE method which accepts time in milliseconds as a limit
test_ext2 1.0 Test extension 2
unaccent 1.1 text search dictionary that removes accents
test_ext3 1.0 Test extension 3
test_ext4 1.0 Test extension 4
uuid-ossp 1.1 generate universally unique identifiers (UUIDs)
test_ext5 1.0 Test extension 5
worker_spi 1.0 Sample background worker
test_ext6 1.0 test_ext6
test_lfind 1.0 Test code for optimized linear search functions
xml2 1.1 XPath querying and XSLT
test_ext7 1.0 Test extension 7
plpgsql 1.0 PL/pgSQL procedural language
test_ext8 1.0 Test extension 8
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_ext_cine 1.0 Test extension using CREATE IF NOT EXISTS
test_predtest 1.0 Test code for optimizer/util/predtest.c
test_ext_cor 1.0 Test extension using CREATE OR REPLACE
test_rbtree 1.0 Test code for red-black tree library
test_ext_cyclic1 1.0 Test extension cyclic 1
test_ext_cyclic2 1.0 Test extension cyclic 2
test_ext_extschema 1.0 test @extschema@
test_regex 1.0 Test code for backend/regex/
test_ext_evttrig 1.0 Test extension - event trigger
bool_plperlu 1.0 transform between bool and plperlu
plperl 1.0 PL/Perl procedural language
plperlu 1.0 PL/PerlU untrusted procedural language
hstore_plpython3u 1.0 transform between hstore and plpython3u
jsonb_plpython3u 1.0 transform between jsonb and plpython3u
ltree_plpython3u 1.0 transform between ltree and plpython3u
plpython3u 1.0 PL/Python3U untrusted procedural language
pltcl 1.0 PL/Tcl procedural language
pltclu 1.0 PL/TclU untrusted procedural language

Last modified 2024-09-27: update ivorysql desc (5794cdb)