Databases Azure

IvorySQL 5.4 on Ubuntu 24.04 on Azure User Guide

| Product: IvorySQL 5.4 on Ubuntu 24.04 on Azure

Overview

This guide covers the deployment and use of IvorySQL 5.4 on Ubuntu 24.04 on Azure using cloudimg Azure Marketplace images.

IvorySQL is HighGo's Oracle compatible distribution of PostgreSQL. This release is built on PostgreSQL 18.4, so you get the complete, unmodified PostgreSQL engine — the same SQL, the same MVCC, the same drivers, the same extension ecosystem — and on top of it an Oracle compatibility layer providing PL/iSQL, Oracle data types such as number, varchar2 and date, packages, the sys schema, and Oracle built ins including dual, sysdate, nvl and to_char.

The single most important thing to understand about IvorySQL is that one cluster answers on two ports.

Port Mode SQL dialect
5432 native PostgreSQL (ivorysql.compatible_mode = pg) standard PostgreSQL
1521 Oracle compatibility (ivorysql.compatible_mode = oracle) Oracle SQL and PL/iSQL

Both ports serve the same databases and the same data. A table created through the Oracle port is immediately visible through the PostgreSQL port and vice versa. The port your client connects to decides which dialect the parser uses — it is not a configuration setting you turn on, and putting ivorysql.compatible_mode into postgresql.conf will stop the server from starting. This means a migrating Oracle application and a native PostgreSQL reporting tool can share one database without either of them being rewritten.

Security by design — the image ships with no database at all. There is no cluster in the captured image: no data directory, no pg_hba.conf, no roles, no TLS key and no credential of any kind. On first boot, and only on first boot, each VM runs initdb for itself and then:

  • writes pg_hba.conf with peer for the local socket and scram-sha-256 for TCP, so the passwordless trust authentication that stock PostgreSQL writes by default never exists on your VM

  • generates a unique 32 character superuser password for this VM and applies it to every role that is able to log in, enumerated from the catalogue at run time rather than from a fixed list

  • generates a unique self signed TLS certificate, so no two VMs share a private key

  • writes everything to the root only file /root/ivorysql-credentials.txt and only then creates the marker that allows ivorysql.service to start

Because ivorysql.service refuses to start until that marker exists, and because the bootstrap runs with no TCP listener at all on either port, there is no window in which the database is reachable before it has a password.

Both listeners are bound to loopback by default. In the shipped image 5432 and 1521 listen on 127.0.0.1 only, so a freshly deployed VM is not exposed on its Azure address even if the Network Security Group is open. Exposing the database is a deliberate step, documented below.

What is included:

  • IvorySQL 5.4 (PostgreSQL 18.4) compiled from the pinned upstream release tag IvorySQL_5.4, installed under /usr/local/ivorysql/ivorysql-5 and running under systemd as ivorysql.service

  • The Oracle compatibility extensions ivorysql_ora and plisql, plus uuid-ossp and gb18030_2022, enabled in the default ivorysql database

  • A per VM superuser password and a per VM TLS certificate generated on first boot into a root only credentials file

  • A shipped self check, ivorysql-selfcheck.sh, that verifies the whole security posture and the Oracle compatibility layer in one command

  • No C toolchain in the image — the compiler and every development header used to build IvorySQL are removed before capture

  • Unattended security upgrades left enabled so the appliance keeps receiving patches

Prerequisites

  • Active Azure subscription, an SSH public key, and a VNet plus subnet in the target region

  • Subscription to this listing on Azure Marketplace

  • A Network Security Group allowing TCP 22 for administration. You do not need to open 5432 or 1521 to use the database on the VM itself, because both listeners are bound to loopback in the shipped image.

Recommended virtual machine size: Standard_B2s (2 vCPU, 4 GB RAM) for development, evaluation and light workloads; the shipped configuration sets shared_buffers to 512 MB to suit it. IvorySQL's own documentation recommends 4 vCPU or more for production, so for real workloads choose a larger size such as Standard_D4s_v5 and raise shared_buffers and effective_cache_size to match.

Deploy the virtual machine

Create the VM from the image, opening only SSH to your own network:

az vm create \
  --resource-group my-resource-group \
  --name my-ivorysql-vm \
  --image <marketplace-image-urn> \
  --size Standard_B2s \
  --storage-sku StandardSSD_LRS \
  --admin-username azureuser \
  --ssh-key-values ~/.ssh/id_rsa.pub \
  --public-ip-sku Standard \
  --location eastus

# Then allow SSH from your own address only
az vm open-port \
  --resource-group my-resource-group \
  --name my-ivorysql-vm \
  --port 22 --priority 1001

Connect and confirm the service is healthy

SSH to the VM as azureuser, then confirm both units are up. ivorysql-firstboot.service is a one shot that stays in the active (exited) state once it has done its work, and ivorysql.service is the database server itself:

systemctl is-active ivorysql.service ivorysql-firstboot.service
systemctl status ivorysql.service --no-pager --lines=0 | head -6

Expected output:

active
active
● ivorysql.service - IvorySQL 5.4 database server (Oracle-compatible PostgreSQL 18.4)
     Loaded: loaded (/etc/systemd/system/ivorysql.service; enabled; preset: enabled)
     Active: active (running) since Sun 2026-07-26 05:43:22 UTC; 36s ago
       Docs: https://www.cloudimg.co.uk/guides/ivorysql-5-on-ubuntu-24-04-azure/
             https://docs.ivorysql.org/
    Process: 28161 ExecStartPre=/bin/mkdir -p /run/ivorysql (code=exited, status=0/SUCCESS)

Confirm the server version. This is the definitive check that you are running IvorySQL and not plain PostgreSQL — the banner reports both version numbers:

sudo -iu ivorysql psql -d ivorysql -w -c "SELECT version();"

                                                        version
------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 18.4 (IvorySQL 5.4) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.3.0-6ubuntu2~24.04.1) 13.3.0, 64-bit
(1 row)

Now look at the two listeners. Both are bound to 127.0.0.1, which is the shipped default:

ss -tlnp | grep -E "5432|1521"

LISTEN 0      200        127.0.0.1:1521      0.0.0.0:*    users:(("postgres",pid=28167,fd=7))
LISTEN 0      200        127.0.0.1:5432      0.0.0.0:*    users:(("postgres",pid=28167,fd=6))

Terminal showing ivorysql.service and ivorysql-firstboot.service both active, the systemd status block for the IvorySQL 5.4 database server, the SELECT version banner reporting PostgreSQL 18.4 with IvorySQL 5.4, and the socket table listing the native listener on 127.0.0.1 port 5432 and the Oracle compatibility listener on 127.0.0.1 port 1521

Retrieve the per VM credentials

The superuser password was generated on this VM's first boot and exists nowhere else. It is written to a root only file:

sudo stat -c '%a %U:%G' /root/ivorysql-credentials.txt
sudo grep -E '^ivorysql\.(role|database|host|port|oracle_port|sslmode)=' /root/ivorysql-credentials.txt

600 root:root
ivorysql.role=ivorysql
ivorysql.database=ivorysql
ivorysql.host=127.0.0.1
ivorysql.port=5432
ivorysql.oracle_port=1521
ivorysql.sslmode=require

To read the password itself:

sudo cat /root/ivorysql-credentials.txt

Store it somewhere safe. The file is the only place it is written in plain text.

Terminal showing the per VM credentials file at /root/ivorysql-credentials.txt with permissions 600 owned by root, listing the ivorysql role, the ivorysql database, host 127.0.0.1, the native port 5432, the Oracle compatibility port 1521 and sslmode require with the generated password masked, followed by the login capable roles enumerated from the catalogue showing a single superuser role named ivorysql, then a count of zero login roles with no password and zero login roles whose verifier is not SCRAM SHA 256

Verify the deployment in one command

The image ships a self check that exercises the whole security posture and the Oracle compatibility layer. Run it any time — it is read only apart from a temporary table it removes again:

sudo /usr/local/sbin/ivorysql-selfcheck.sh

It authenticates with the per VM credential over TLS, confirms the version pin, proves both ports are in the right mode, runs a PL/iSQL package, confirms pg_hba.conf contains no trust rule, enumerates every login capable role and proves each rejects its own name, the empty password and a list of published defaults, and confirms both ports refuse a connection on the VM's routable address. It finishes with a single RESULT healthy line.

Terminal showing the ivorysql-selfcheck.sh report with every line prefixed OK: credentials file mode 600 root root, per VM password present, credential authenticated over TLS on both ports, server version pin PostgreSQL 18.4 with IvorySQL 5.4, port 5432 in native mode and port 1521 in Oracle mode, PL/iSQL package returning the exact expected total, zero active trust rules, every login role rejecting all sixteen guessable password probes, both ports bound to loopback only and the routable address refusing connections, ending with RESULT healthy

Open a native PostgreSQL session on port 5432

Port 5432 behaves exactly like stock PostgreSQL. The ivorysql OS account authenticates over the local unix socket with peer, so no password is needed on the VM itself:

sudo -iu ivorysql psql -d ivorysql -w -c "SHOW ivorysql.compatible_mode;"
sudo -iu ivorysql psql -d ivorysql -w -c "SELECT rolname, rolsuper, rolcanlogin FROM pg_roles WHERE rolcanlogin ORDER BY 1;"

 ivorysql.compatible_mode
--------------------------
 pg
(1 row)

 rolname  | rolsuper | rolcanlogin
----------+----------+-------------
 ivorysql | t        | t
(1 row)

There is exactly one login capable role. Everything else in pg_roles is one of PostgreSQL's built in pg_* predefined roles, which cannot log in.

Four databases exist — the three PostgreSQL always creates plus IvorySQL's own default ivorysql database. Nothing has been seeded:

sudo -iu ivorysql psql -d ivorysql -w -c "SELECT datname FROM pg_database ORDER BY 1;"
sudo -iu ivorysql psql -d ivorysql -w -c "SELECT extname, extversion FROM pg_extension ORDER BY 1;"

  datname
-----------
 ivorysql
 postgres
 template0
 template1
(4 rows)

   extname    | extversion
--------------+------------
 gb18030_2022 | 1.0
 ivorysql_ora | 1.0
 plisql       | 1.0
 plpgsql      | 1.0
 uuid-ossp    | 1.1
(5 rows)

To connect over TCP with the per VM password instead of the socket, read the password out of the credentials file:

PW=$(sudo sed -n 's/^ivorysql\.password=//p' /root/ivorysql-credentials.txt)
sudo -u ivorysql /usr/local/ivorysql/ivorysql-5/bin/psql -w \
  "host=127.0.0.1 port=5432 dbname=ivorysql user=ivorysql password=$PW sslmode=require" \
  -c "SELECT ssl, version AS tls FROM pg_stat_ssl WHERE pid = pg_backend_pid();"

ssl comes back as t, confirming the session really is encrypted rather than merely having asked for encryption.

Open an Oracle compatibility session on port 1521

Connect to port 1521 and the very same server parses Oracle SQL:

sudo -iu ivorysql psql -d ivorysql -p 1521 -w -c "SHOW ivorysql.compatible_mode;"
sudo -iu ivorysql psql -d ivorysql -p 1521 -w -c "SELECT to_char(sysdate, 'YYYY-MM-DD') AS today FROM dual;"

 ivorysql.compatible_mode
--------------------------
 oracle
(1 row)

   today
------------
 2026-07-26
(1 row)

dual and sysdate are Oracle constructs that do not exist in PostgreSQL. On port 5432 the same query is correctly rejected, which is the clearest demonstration that the two dialects really are separate:

sudo -iu ivorysql psql -d ivorysql -w -c "SELECT 1 FROM dual;" 2>/dev/null || echo "rejected on the native port, as expected"

rejected on the native port, as expected

Work with Oracle data types and PL/iSQL packages

Create a table using Oracle types through the Oracle port:

sudo -iu ivorysql psql -d ivorysql -p 1521 -w <<'SQL'
CREATE TABLE demo_emp (
    id      number(6)     PRIMARY KEY,
    name    varchar2(40)  NOT NULL,
    salary  number(10,2),
    hired   date
);
INSERT INTO demo_emp VALUES (1, 'Ada Lovelace', 72000.00, sysdate);
INSERT INTO demo_emp VALUES (2, 'Grace Hopper', 81000.50, sysdate);
INSERT INTO demo_emp VALUES (3, 'Alan Turing',  NULL,     sysdate);
SELECT id, name, nvl(to_char(salary, 'FM999999.00'), 'unset') AS salary FROM demo_emp ORDER BY id;
SQL

CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
 id |     name     |  salary
----+--------------+----------
 1  | Ada Lovelace | 72000.00
 2  | Grace Hopper | 81000.50
 3  | Alan Turing  | unset
(3 rows)

number(6), varchar2(40) and nvl() are Oracle spellings; IvorySQL maps them onto real PostgreSQL storage in the sys schema.

Now create a package — a named container for functions and procedures, and one of the Oracle features PostgreSQL has no equivalent for. Note the / terminator, exactly as in Oracle's SQL*Plus:

sudo -iu ivorysql psql -d ivorysql -p 1521 -w <<'SQL'
CREATE OR REPLACE PACKAGE hr_pkg IS
    FUNCTION total_payroll RETURN number;
END hr_pkg;
/
CREATE OR REPLACE PACKAGE BODY hr_pkg IS
    FUNCTION total_payroll RETURN number IS
        t number;
    BEGIN
        SELECT sum(nvl(salary, 0)) INTO t FROM demo_emp;
        RETURN t;
    END;
END hr_pkg;
/
SELECT to_char(hr_pkg.total_payroll(), 'FM999999.00') AS total_payroll FROM dual;
SQL

CREATE PACKAGE
CREATE PACKAGE BODY
 total_payroll
---------------
 153000.50
(1 row)

Terminal showing an Oracle compatibility session on port 1521: SELECT sysdate FROM dual returning 2026-07-26 alongside nvl returning nvl-works, a table created with number and varchar2 and date columns, three employee rows inserted where nvl renders Alan Turing's null salary as unset, then CREATE PACKAGE and CREATE PACKAGE BODY followed by the package function returning a total payroll of 153000.50, and finally the same three rows read back through the native PostgreSQL port 5432 where the null salary displays as empty

One cluster, two dialects

The rows you just inserted through the Oracle port are ordinary PostgreSQL rows. Query them through the native port:

sudo -iu ivorysql psql -d ivorysql -w -c "SELECT id, name, salary FROM demo_emp ORDER BY id;"

 id |     name     |  salary
----+--------------+----------
 1  | Ada Lovelace | 72000.00
 2  | Grace Hopper | 81000.50
 3  | Alan Turing  |
(3 rows)

The only difference is presentation: the Oracle session rendered the NULL salary through nvl(), while the native session shows it as an empty PostgreSQL NULL. This is what makes IvorySQL useful for a migration — Oracle application code keeps talking Oracle on 1521 while your PostgreSQL reporting, backup and monitoring tooling keeps talking PostgreSQL on 5432, against one set of tables.

Remove the demonstration objects when you are done:

sudo -iu ivorysql psql -d ivorysql -p 1521 -w -c "DROP PACKAGE IF EXISTS hr_pkg;" -c "DROP TABLE IF EXISTS demo_emp;"

DROP PACKAGE
DROP TABLE

Security posture

The authentication rules generated on first boot are deliberately minimal. Note that no line uses trust, PostgreSQL's passwordless method, which is what a default initdb would otherwise have written for both local and TCP connections:

sudo grep -vE '^[[:space:]]*(#|$)' /var/lib/ivorysql/5/data/pg_hba.conf

local   all             all                                     peer
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             ::1/128                 scram-sha-256
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256

You can confirm for yourself that a wrong password is refused:

sudo -u ivorysql /usr/local/ivorysql/ivorysql-5/bin/psql -w \
  "host=127.0.0.1 port=5432 dbname=ivorysql user=ivorysql password=not-the-password sslmode=require connect_timeout=8" \
  -c "SELECT 1;" </dev/null 2>/dev/null || echo "wrong password refused, as expected"

wrong password refused, as expected

And that the database is not listening on the VM's own routable address:

ADDR=$(hostname -I | awk '{print $1}')
sudo -u ivorysql /usr/local/ivorysql/ivorysql-5/bin/psql -w \
  "host=$ADDR port=5432 dbname=ivorysql user=ivorysql password=x sslmode=require connect_timeout=6" \
  -c "SELECT 1;" </dev/null 2>/dev/null || echo "not reachable on $ADDR, as expected"

not reachable on 10.0.0.20, as expected

Exposing IvorySQL to remote clients

The shipped image is loopback only. Exposing the database is a deliberate, three part change: widen the listener, add a pg_hba.conf rule for your client network, and open the Network Security Group. Replace <your-mgmt-cidr> with your own CIDR — never use 0.0.0.0/0.

Edit the managed drop in rather than postgresql.conf, so upstream's file stays pristine:

sudo tee -a /var/lib/ivorysql/5/data/conf.d/cloudimg.conf >/dev/null <<'CONF'
listen_addresses = '*'
ivorysql.listen_addresses = '*'
CONF
sudo tee -a /var/lib/ivorysql/5/data/pg_hba.conf >/dev/null <<'HBA'
hostssl all all <your-mgmt-cidr> scram-sha-256
HBA
sudo systemctl restart ivorysql.service

Then open the ports you actually need to that same CIDR, and only that CIDR:

az network nsg rule create \
  --resource-group my-resource-group \
  --nsg-name my-ivorysql-vm-nsg \
  --name allow-ivorysql \
  --priority 1100 --protocol Tcp \
  --destination-port-ranges 5432 1521 \
  --source-address-prefixes <your-mgmt-cidr> \
  --access Allow

Because the rule you added is hostssl, remote clients are accepted only over TLS and only with the per VM password. The certificate generated on first boot is self signed, so clients should use sslmode=require (encrypt without verifying the issuer) or be given the certificate to pin. For a publicly trusted certificate, replace server.crt and server.key in the data directory with your own pair and reload.

Rotating the superuser password

Choose your own password and update the credentials file to match:

sudo -iu ivorysql psql -d ivorysql -w -c "ALTER ROLE ivorysql PASSWORD '<new-password>';"
sudo sed -i "s|^ivorysql.password=.*|ivorysql.password=<new-password>|" /root/ivorysql-credentials.txt

Managing the service

sudo systemctl status ivorysql.service --no-pager --lines=0
sudo journalctl -u ivorysql.service -n 30 --no-pager

The server's configuration lives in /var/lib/ivorysql/5/data. Put your own settings in conf.d/cloudimg.conf, which is included last so its values win, and restart the service to apply them. Two settings are worth knowing about:

  • ivorysql.port sets the Oracle compatibility port, 1521 by default

  • ivorysql.compatible_mode is reported per connection and is chosen by the port you connect to. Do not set it in a configuration file — the native postmaster rejects it at startup with cannot be changed in native PG mode and the server will not boot.

Backups

IvorySQL ships the standard PostgreSQL utilities under /usr/local/ivorysql/ivorysql-5/bin, so the familiar tools work unchanged:

sudo -iu ivorysql pg_dump -d ivorysql -Fc -f /var/lib/ivorysql/ivorysql-$(date +%F).dump
sudo -iu ivorysql pg_dumpall --globals-only -f /var/lib/ivorysql/globals-$(date +%F).sql

Restore with pg_restore. For point in time recovery, enable WAL archiving in conf.d/cloudimg.conf and ship the archive to Azure Blob Storage.

Troubleshooting

ivorysql.service will not start and reports a failed condition. The service is gated on /var/lib/cloudimg/ivorysql-bootstrap-ready, which first boot creates only after the cluster exists and the password has been set. Check that first boot finished:

systemctl status ivorysql-firstboot.service --no-pager --lines=0
sudo tail -20 /var/log/cloudimg-firstboot.log

SELECT ... FROM dual fails with relation "dual" does not exist. You are connected to port 5432, which is the native PostgreSQL port. Reconnect with -p 1521.

A client cannot connect from another machine. By design — both listeners are bound to loopback in the shipped image. Follow "Exposing IvorySQL to remote clients" above.

The server refuses to start after a configuration change. Check the journal; the most common cause is ivorysql.compatible_mode having been added to a configuration file, which the native postmaster rejects at startup.

Support

cloudimg provides 24/7 support for this image. Contact support@cloudimg.co.uk with the VM name, region and the output of sudo /usr/local/sbin/ivorysql-selfcheck.sh.

Upstream project documentation: docs.ivorysql.org. IvorySQL is licensed under Apache 2.0, with the inherited PostgreSQL code under the PostgreSQL licence.