Upgrading from PostgreSQL to IvorySQL

# Source PostgreSQL
psql -U postgres -c "SELECT version();"

# Target IvorySQL
psql -U postgres -p 5432 -c "SELECT version();"

/usr/local/ivorysql/ivorysql-5/bin/initdb -D /data/ivorysql -U postgres

psql -U postgres -p 5432 -c "SHOW ivorysql.compatible_mode;"
psql -U postgres -p 1521 -c "SHOW ivorysql.compatible_mode;"

# Export globals from the source
pg_dumpall -U postgres --globals-only -f globals.sql

# Before importing on the target, edit globals.sql:
# 1) Remove superuser-related statements
# 2) Verify the directory paths in CREATE TABLESPACE statements
#    and create these directories on the IvorySQL host in advance
# 3) To skip conflicts such as pre-existing roles, remove the corresponding
#    CREATE ROLE statements (-v ON_ERROR_STOP=1 stops at the first error,
#    ensuring critical objects like tablespaces are created successfully)
psql -U postgres -p 5432 -v ON_ERROR_STOP=1 -f globals.sql

pg_dump -U postgres -Fc -d appdb -f appdb.dump

# Create the target database first
psql -U postgres -p 5432 -c "CREATE DATABASE appdb;"

# Restore the data (all objects go into the default tablespace)
pg_restore -U postgres -p 5432 -d appdb --no-owner --no-privileges --no-tablespaces appdb.dump

# 1. List all role creation statements in the dump
#    (they are in the global objects section at the top of the file)
grep -n '^CREATE ROLE' full_cluster.sql

# 2. List roles that already exist on the target
psql -U postgres -p 5432 -c '\du'

# 3. Compare the two lists and delete or comment out the CREATE ROLE lines
#    for roles that already exist; the postgres role always exists:
sed -i 's/^CREATE ROLE postgres;/-- CREATE ROLE postgres;/' full_cluster.sql

# Import on the target (-v ON_ERROR_STOP=1 stops at the first error so
# failures are not silently skipped)
psql -U postgres -p 5432 -v ON_ERROR_STOP=1 -f full_cluster.sql

wal_level = logical
max_replication_slots = 10
max_wal_senders = 10

-- Tables with primary keys need no extra step (the primary key is the replica identity)
-- Tables without primary keys: set REPLICA IDENTITY FULL on the source
ALTER TABLE app_table REPLICA IDENTITY FULL;

-- Check user tables on the source that lack a replica identity
-- (relreplident = 'd' and no primary key)
SELECT n.nspname AS schema_name, c.relname AS table_name
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
  AND c.relreplident = 'd'
  AND NOT EXISTS (
    SELECT 1 FROM pg_index i
    WHERE i.indrelid = c.oid AND i.indisprimary
  )
  AND n.nspname NOT IN ('pg_catalog', 'information_schema');

-- Create a publication on the source
CREATE PUBLICATION app_pub FOR ALL TABLES;

-- Create a subscription on IvorySQL
CREATE SUBSCRIPTION app_sub
  CONNECTION 'host=src_host port=5432 dbname=appdb user=replica password=***'
  PUBLICATION app_pub;

-- Run on IvorySQL
SELECT s.subname AS subscription,
       r.srrelid::regclass AS table_name,
       r.srsubstate
FROM pg_subscription_rel r
JOIN pg_subscription s ON s.oid = r.srsubid
ORDER BY 1, 2;

-- srsubstate: 'd' data copy in progress; 's' data synchronized;
-- 'r' copy finished and in normal replication (all tables must be 'r' before cutover)

-- Source: run CHECKPOINT after stopping writes to flush all committed transactions to WAL
CHECKPOINT;

-- Source: record the current WAL position L1
SELECT pg_current_wal_lsn() AS source_lsn;

-- IvorySQL: wait until latest_end_lsn >= L1 (caught up or beyond)
-- and observe that latest_end_lsn no longer advances for a while,
-- which confirms all incremental changes have been applied
SELECT subname, received_lsn, latest_end_lsn, latest_end_time
FROM pg_stat_subscription;

-- Compare row counts of key tables (run on both source and target)
SELECT count(*) FROM app_table;

-- Check sequence values to avoid primary-key conflicts after cutover
SELECT last_value FROM app_id_seq;

# Export sequence state from the source
# (covers all non-system schemas with schema-qualified sequence names)
psql -U postgres -d appdb -Atc \
  "SELECT 'SELECT setval(' || quote_literal(format('%I.%I', schemaname, sequencename)) || ',' || last_value || ');' \
   FROM pg_sequences \
   WHERE last_value IS NOT NULL \
     AND schemaname NOT IN ('pg_catalog', 'information_schema')" > sync_sequences.sql

# Execute on IvorySQL
psql -U postgres -p 5432 -d appdb -f sync_sequences.sql

-- Confirm the compatibility mode
SHOW ivorysql.compatible_mode;

-- Verify Oracle-style objects
SELECT SYSDATE FROM DUAL;
SELECT NVL(NULL, 'N/A') FROM DUAL;