Production Go-Live Checklist and Best Practices
This page collects the checks that must be confirmed one by one before an IvorySQL deployment goes live, together with the three compatibility cases that most often surface only after go-live. Every command and output below comes from a measured record on a single host: IvorySQL 5.6 from /opt/ivory56/bin (PostgreSQL 18.6 (IvorySQL 5.6)), with the instance listening on both 127.0.0.1:1521 (Oracle compatible) and 127.0.0.1:5432 (PG compatible). Container images usually publish the container 5432 on the host as -p 1521:1521 -p 5433:5432, which makes the PG port 5433 on the host; replace 5432 with 5433 and the commands and conclusions on this page still hold.
1. Go-Live Checklist
Commands in the "Command" column run on the database host. The "Expected result" column gives the measured sample value, which must be resized for the machine in production, while the "Impact if it fails" column describes what actually happens when nothing is adjusted.
Check |
Command |
Expected result |
Impact if it fails |
Both listeners are serving and the ports do not collide |
|
Both return |
With equal values the server fails to start and the log reads |
Which compatibility mode the connection landed in |
|
|
Connecting to the wrong port raises no error at all; only casing, empty strings and built-in functions drift silently |
Initialization mode of the cluster |
|
|
The mode is a cluster-wide property; a wrong choice can only be fixed by running initdb again |
uuid functions are usable on the Oracle port |
|
Returns a UUID such as |
Table definitions that use a UUID default or function fail, and migration scripts stop at that statement |
Visibility of uuid functions on the PG port |
|
The |
The same SQL moved from the Oracle port to the PG port fails everywhere with "function does not exist" |
How to spell the identifier when installing an extension |
|
With the default value it fails as |
The |
Whether empty strings are converted to NULL |
|
On 1521 the value is |
Unique constraints are bypassed by NULL on the Oracle side, and the same table written through two ports gets two different semantics |
Identifier case switch |
|
On 1521 the default is |
Result column names drift with the mode, so ORMs and result mappers pick the wrong column |
WAL level and archiving (PITR prerequisite) |
|
Production needs |
Without archiving only the latest base backup can be restored, and point-in-time recovery is impossible |
Memory baseline |
Read |
Defaults measured were |
Large scans and sorts spill to disk, producing hard-to-reproduce latency spikes |
Connection ceiling |
|
Must match the sum of the connection pool limits; the sample value is |
Connections above the ceiling are rejected, the pool times out as a whole, and the visible symptom is a total outage |
No trust rule in pg_hba.conf |
|
|
Any process that reaches the port can impersonate any role, and replication connections are equally open |
Password encryption algorithm |
|
|
An older algorithm combined with trust rules leaves the deployment effectively unauthenticated |
autovacuum is enabled |
|
|
Table bloat and transaction ID ageing are never reclaimed until a manual shutdown for cleanup |
Slow statement logging threshold |
|
A positive number of milliseconds (the sample read |
Slow queries on production leave no trace, so performance work is guesswork |
Synchronous replication settings after promotion or switchover |
|
It must be empty when no standby exists; when it is not empty and no standby exists the wait is |
Every commit stalls, |
Logical backup drill |
|
|
The backup file was never read back, so its unusability is discovered during the incident |
Physical backup drill |
|
Returns 0; |
Without a usable base backup the first step of PITR is already missing |
A new database inherits the extensions of template1: both template1 and a freshly created database measured five rows (gb18030_2022, ivorysql_ora, plisql, plpgsql, uuid-ossp), so an already exists error from create extension only means the template already has it, not that this deployment installed it. uuid-ossp lives in the sys schema, which holds all five functions uuid_generate_v1, uuid_generate_v1mc, uuid_generate_v3, uuid_generate_v4 and uuid_generate_v5; the Oracle port puts sys on the effective search path and the PG port does not, so this check has to be repeated per port.
2. Compatibility Best-Practice Cases
2.1. Case 1: one database, two ports, two compatibility modes
2.1.1. Scenario
Only the port changed in the application configuration, yet behaviour differs from the test environment. IvorySQL decides the compatibility mode of a session from the listener that accepted the connection, and neither port nor ivorysql.port reveals which one that was.
2.1.2. Reproduction SQL
-- run once per port; the database and the role are identical
select current_setting('ivorysql.compatible_mode') as mode,
current_setting('ivorysql.database_mode') as db_mode,
current_setting('ivorysql.port') as ora_port,
current_setting('port') as pg_port;
show port;
show ivorysql.port;
2.1.3. Measured output
-- psql -h 127.0.0.1 -p 1521 -d d3bp
compatible_mode | database_mode | ora_port | pg_port
-----------------+---------------+----------+---------
oracle | oracle | 1521 | 5432
(1 row)
-- psql -h 127.0.0.1 -p 5432 -d d3bp
compatible_mode | database_mode | ora_port | pg_port
-----------------+---------------+----------+---------
pg | oracle | 1521 | 5432
(1 row)
On both ports show port returns 5432 and show ivorysql.port returns 1521, byte for byte identical.
2.1.4. Conclusions and recommendations
-
Only
current_setting('ivorysql.compatible_mode')identifies the mode of the current session;port,ivorysql.portandivorysql.database_modecannot distinguish anything, anddatabase_modeis a cluster-wide property that staysoracleon both ports. -
Map roles to ports explicitly: Oracle-compatible workloads connect to
1521, PG workloads to5432, and never mix both ports inside one connection pool. -
Before go-live run the SQL above on both ports and attach the output to the change record; on a container deployment where the host PG port is
5433, run it against5433instead.
2.2. Case 2: the identifier case switch changes result column names
2.2.1. Scenario
The same query returns differently cased column names on the Oracle port depending on ivorysql.identifier_case_switch, because quoted aliases are folded; the switch has no effect on the PG port. Applications that read columns by name then report a missing column or read the wrong one.
2.2.2. Reproduction SQL
show ivorysql.identifier_case_switch;
set ivorysql.identifier_case_switch = normal;
select 1 as "ABC", 2 as "abc", 3 as MiXeD;
set ivorysql.identifier_case_switch = interchange;
select 1 as "ABC", 2 as "abc", 3 as MiXeD;
set ivorysql.identifier_case_switch = lowercase;
select 1 as "ABC", 2 as "abc", 3 as MiXeD;
2.2.3. Measured output
Port 1521 (Oracle compatible):
ivorysql.identifier_case_switch
---------------------------------
interchange
(1 row)
-- normal
ABC | abc | mixed
-----+-----+-------
1 | 2 | 3
(1 row)
-- interchange
abc | ABC | mixed
-----+-----+-------
1 | 2 | 3
(1 row)
-- lowercase
abc | abc | mixed
-----+-----+-------
1 | 2 | 3
(1 row)
On port 5432 (PG compatible) all three values produce exactly the same output, ABC | abc | mixed, so the switch does not apply.
Checking the table definition and the access path next: with create table d3_case ("UserId" int, payload text); and create index d3_case_userid_idx on d3_case ("UserId"); created under normal, the same select * from d3_case where "UserId" = 42; still reports this plan after switching to interchange:
Index Scan using d3_case_userid_idx on d3_case
Index Cond: ("UserId" = 42)
(2 rows)
2.2.4. Conclusions and recommendations
-
Under all three values the stored column name stays
UserIdand the indexd3_case_userid_idxis still used through anIndex Scan, so the drift never happens on the access path, only on the output column names of quoted aliases. -
When diagnosing a "column does not exist" failure, first check whether a quoted alias was folded instead of suspecting the table definition or the index.
-
Where stable column names matter, pin
ivorysql.identifier_case_switchtonormalor use unquoted lower-case aliases only; the PG port needs no setting at all.
2.3. Case 3: empty strings become NULL and defeat unique constraints
2.3.1. Scenario
In Oracle semantics an empty string equals NULL, and IvorySQL controls that conversion with ivorysql.enable_emptystring_to_NULL. When it is on, '' is stored as NULL, unique constraints no longer apply to NULLs, and any number of "empty" values can be inserted into the same column; a not null column rejects '' with a not-null violation whose message shows null, which is easily mistaken for an application sending an empty object.
2.3.2. Reproduction SQL
show ivorysql.enable_emptystring_to_NULL;
create table d3_empty (id serial primary key, code text);
insert into d3_empty (code) values ('');
select id, code, code is null as is_null, length(code) as len from d3_empty;
create unique index d3_empty_code_uq on d3_empty (code);
insert into d3_empty (code) values ('');
insert into d3_empty (code) values ('');
select count(*) as total_rows, count(*) filter (where code is null) as null_rows from d3_empty;
create table d3_notnull (id int primary key, code text not null);
insert into d3_notnull (id, code) values (1, '');
analyze d3_empty;
select attname, null_frac, n_distinct from pg_stats where tablename = 'd3_empty' order by attname;
2.3.3. Measured output
With the default value on (port 1521):
id | code | is_null | len
----+------+---------+-----
1 | | t |
(1 row)
rows_matching_empty
---------------------
0
(1 row)
total_rows | null_rows
------------+-----------
3 | 3
(1 row)
ERROR: null value in column "code" of relation "d3_notnull" violates not-null constraint
DETAIL: Failing row contains (1, null).
attname | null_frac | n_distinct
---------+-----------+------------
code | 1 | 0
id | 0 | -1
(2 rows)
All three inserts (one plain, two behind the unique index) succeeded, code = '' matches no row at all, and the statistics report null_frac of 1 with n_distinct of 0.
The same statements with the switch off:
id | code | is_null | len
----+------+---------+-----
1 | | f | 0
(1 row)
ERROR: duplicate key value violates unique constraint "d3_empty_off_code_uq"
DETAIL: Key (code)=() already exists.
attname | null_frac | n_distinct
---------+-----------+------------
code | 0 | -1
id | 0 | -1
(2 rows)
Here the empty string stays an empty string, the second copy is rejected by the unique constraint, the same not null column accepts '', and length(code) reads 0.
Also note the port difference: on 5432 (PG compatible) show ivorysql.enable_emptystring_to_NULL still returns on, yet after inserting '' the value of code is null is f and the empty string remains an empty string.
2.3.4. Conclusions and recommendations
-
Applications that rely on "empty string equals NULL" must rewrite uniqueness checks on the Oracle port so they cover NULLs; otherwise
code = ''matches nothing and the unique index still lets duplicates through. -
The not-null violation for
''printsnullin the message, so do not read it as the application sending a NULL. -
With
null_frac = 1andn_distinct = 0the planner estimates the whole column as empty and picks plans that differ from expectations, so runanalyzeon the critical tables before go-live and verify the statistics. -
Do not write to the same table through both
1521and5432, because empty-string semantics differ between the two ports.