Upgrade to PostgreSQL 18 using brew on MacOS

 

1. Prepare for PostgreSQL18 installation

bash brew services install postgresql@18

brew services stop postgresql@17

If you have any extensions installed outside the default PostgreSQL installation, make them available for PostgreSQL 18 as well.

brew reinstall pgvector

2. Upgrade PostgreSQL 18

brew install postgresql@18

/opt/homebrew/opt/postgresql@18/bin/pg_upgrade \
  -d /opt/homebrew/var/postgresql@17 \
  -D /opt/homebrew/var/postgresql@18 \
  -b /opt/homebrew/opt/postgresql@17/bin \
  -B /opt/homebrew/opt/postgresql@18/bin

logs:

% /opt/homebrew/opt/postgresql@18/bin/pg_upgrade \
  -d /opt/homebrew/var/postgresql@17 \
  -D /opt/homebrew/var/postgresql@18 \
  -b /opt/homebrew/opt/postgresql@17/bin \
  -B /opt/homebrew/opt/postgresql@18/bin
Performing Consistency Checks
-----------------------------
Checking cluster versions                                     ok
Checking database connection settings                         ok
Checking database user is the install user                    ok
Checking for prepared transactions                            ok
Checking for contrib/isn with bigint-passing mismatch         ok
Checking for valid logical replication slots                  ok
Checking for subscription state                               ok
Checking data type usage                                      ok
Checking for objects affected by Unicode update               ok
Checking for not-null constraint inconsistencies              ok
Creating dump of global objects                               ok
Creating dump of database schemas
                                                              ok
Checking for presence of required libraries                   ok
Checking database user is the install user                    ok
Checking for prepared transactions                            ok
Checking for new cluster tablespace directories               ok

If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade
------------------
Setting locale and encoding for new cluster                   ok
Analyzing all rows in the new cluster                         ok
Freezing all rows in the new cluster                          ok
Deleting files from new pg_xact                               ok
Copying old pg_xact to new server                             ok
Setting oldest XID for new cluster                            ok
Setting next transaction ID and epoch for new cluster         ok
Deleting files from new pg_multixact/offsets                  ok
Copying old pg_multixact/offsets to new server                ok
Deleting files from new pg_multixact/members                  ok
Copying old pg_multixact/members to new server                ok
Setting next multixact ID and offset for new cluster          ok
Resetting WAL archives                                        ok
Setting frozenxid and minmxid counters in new cluster         ok
Restoring global objects in the new cluster                   ok
Restoring database schemas in the new cluster
                                                              ok
Copying user relation files
                                                              ok
Setting next OID for new cluster                              ok
Sync data directory to disk                                   ok
Creating script to delete old cluster                         ok
Checking for extension updates                                notice

Your installation contains extensions that should be updated
with the ALTER EXTENSION command.  The file
    update_extensions.sql
when executed by psql by the database superuser will update
these extensions.

Upgrade Complete
----------------
Some statistics are not transferred by pg_upgrade.
Once you start the new server, consider running these two commands:
    /opt/homebrew/opt/postgresql@18/bin/vacuumdb --all --analyze-in-stages --missing-stats-only
    /opt/homebrew/opt/postgresql@18/bin/vacuumdb --all --analyze-only
Running this script will delete the old cluster's data files:
    ./delete_old_cluster.sh

3. Post Upgrade Activities

\connect searchdb
ALTER EXTENSION "vector" UPDATE;
ALTER EXTENSION "btree_gist" UPDATE;

#Once you start the new server, consider running these two commands:
/opt/homebrew/opt/postgresql@18/bin/vacuumdb --all --analyze-in-stages --missing-stats-only
/opt/homebrew/opt/postgresql@18/bin/vacuumdb --all --analyze-only

./delete_old_cluster.sh

4. Troubleshooting

Symptom:

Performing Consistency Checks
-----------------------------
Checking cluster versions                                     ok

old cluster does not use data checksums but the new one does
Failure, exiting

Fix:

# Remove the new PostgreSQL 18 data directory
rm -rf /opt/homebrew/var/postgresql@18

# Initialize without checksums to match your old cluster
/opt/homebrew/opt/postgresql@18/bin/initdb -D /opt/homebrew/var/postgresql@18 --no-data-checksums

Symptom:

Checking for presence of required libraries                   fatal

Your installation references loadable libraries that are missing from the
new installation.  You can add these libraries to the new installation,
or remove the functions using them from the old installation.  A list of
problem libraries is in the file:
    /opt/homebrew/var/postgresql@18/pg_upgrade_output.d/20251021T115934.477/loadable_libraries.txt
Failure, exiting

Fix:#

# Reinstall extension to make it available for new PG version
# pgvector used as example
brew reinstall pgvector

Oracle Database Auditing: Standard and Unified Auditing Working Together in Mixed Mode

 

Introduction

Oracle Database 19c supports both traditional standard auditing and the newer unified auditing framework. When running in mixed mode (the default configuration), both auditing mechanisms can operate simultaneously, providing comprehensive audit coverage during migration periods. This blog demonstrates how both auditing types work together and showcases the performance benefits of unified auditing's partitioned architecture.

Understanding Mixed Mode Auditing

In mixed mode, Oracle Database allows:

• Standard Auditing: Traditional audit trail stored in SYS.AUD$ and accessed via DBA_AUDIT_TRAIL
• Unified Auditing: Modern audit trail stored in AUDSYS.AUD$UNIFIED and accessed via UNIFIED_AUDIT_TRAIL
• Simultaneous Operation: Both mechanisms can capture the same events

Environment Setup

First, let's verify the audit configuration and create a test user:

-- Check audit trail parameter
SHOW PARAMETER audit_trail;

NAME        TYPE   VALUE        
----------- ------ ------------ 
audit_trail string DB, EXTENDED

The DB, EXTENDED value indicates standard auditing is enabled with extended information.

-- Create test user
CREATE USER donghua IDENTIFIED BY ComplexPassword;
GRANT DBA TO donghua;

Configuring Standard Auditing

Standard auditing uses the traditional AUDIT command to track database operations:

-- Enable standard auditing for table operations
AUDIT SELECT TABLE, INSERT TABLE, UPDATE TABLE, DELETE TABLE BY donghua;

This captures all SELECT, INSERT, UPDATE, and DELETE operations on tables by user DONGHUA.

Configuring Unified Auditing

Unified auditing uses policy-based configuration for more flexible and centralized audit management:

-- Create unified audit policy
CREATE AUDIT POLICY donghua_table_audit
ACTIONS SELECT, INSERT, UPDATE, DELETE;

-- Apply policy to user
AUDIT POLICY donghua_table_audit BY donghua;

Testing: Both Auditing Mechanisms in Action

Test 1: Operations on Table T1 (Before Unified Policy)

-- Connect as donghua user
CREATE TABLE t1(id INT);
INSERT INTO t1 VALUES(1);
SELECT * FROM t1;
DROP TABLE t1;

Standard Audit Results:

SELECT username, action_name, obj_name, timestamp
FROM dba_audit_trail
WHERE username = 'DONGHUA' AND obj_name = 'T1'
ORDER BY timestamp DESC;

USERNAME    ACTION_NAME    OBJ_NAME    TIMESTAMP 
----------- -------------- ----------- ------------ 
DONGHUA     SELECT         T1          09-OCT-25    
DONGHUA     INSERT         T1          09-OCT-25

At this point, only standard auditing captured the operations since the unified audit policy was not yet created.

Test 2: Operations on Table T2 (After Unified Policy)

-- Connect as donghua user
CREATE TABLE t2 (id INT);
INSERT INTO t2 VALUES (1);
COMMIT;
SELECT * FROM t2;
DROP TABLE t2;

Unified Audit Results:

SELECT unified_audit_policies, dbusername, action_name, object_name, event_timestamp
FROM unified_audit_trail
WHERE dbusername = 'DONGHUA'
AND event_timestamp_utc > TO_TIMESTAMP('2025-10-01','YYYY-MM-DD')
AND object_name = 'T2'
ORDER BY event_timestamp DESC;

UNIFIED_AUDIT_POLICIES    DBUSERNAME    ACTION_NAME    OBJECT_NAME    EVENT_TIMESTAMP 
------------------------- ------------- -------------- --------------- ---------------------------------- 
DONGHUA_TABLE_AUDIT       DONGHUA       SELECT         T2              09-OCT-25 01.37.44.549052000 PM    
DONGHUA_TABLE_AUDIT       DONGHUA       INSERT         T2              09-OCT-25 01.37.39.747061000 PM

Standard Audit Results (Same Operations):

SELECT username, action_name, obj_name, timestamp
FROM dba_audit_trail
WHERE username = 'DONGHUA' AND obj_name IN ('T1','T2')
ORDER BY timestamp DESC;

USERNAME    ACTION_NAME    OBJ_NAME    TIMESTAMP 
----------- -------------- ----------- ------------ 
DONGHUA     SELECT         T2          09-OCT-25    
DONGHUA     INSERT         T2          09-OCT-25    
DONGHUA     SELECT         T1          09-OCT-25    
DONGHUA     INSERT         T1          09-OCT-25

Key Observation: Dual Capture in Mixed Mode

Notice that operations on T2 appear in both audit trails:

• Unified Audit Trail: Captured by the DONGHUA_TABLE_AUDIT policy
• Standard Audit Trail: Captured by the traditional AUDIT statement

This demonstrates mixed mode operation where both mechanisms work simultaneously, ensuring no audit gaps during migration.

Unified Auditing Performance: Partitioning and Partition Pruning

One of the key advantages of unified auditing is its use of range partitioning on the EVENT_TIMESTAMP column, enabling efficient data management and query performance.

Partition Structure

The AUDSYS.AUD$UNIFIED table is partitioned by range on EVENT_TIMESTAMP with monthly intervals:

PARTITION BY RANGE ("EVENT_TIMESTAMP") INTERVAL (INTERVAL '1' MONTH)

This means:

• Each month's audit data is stored in a separate partition
• Old partitions can be easily archived or dropped
• Queries with time-based predicates benefit from partition pruning

The column EVENT_TIMESTAMP_UTC in view AUDSYS.UNIFIED_AUDIT_TRAIL , it maps to column EVENT_TIMESTAMP in table AUDSYS.AUD$UNIFIED.

Demonstrating Partition Pruning

Let's examine the execution plan for a time-filtered query:

EXPLAIN PLAN FOR
SELECT unified_audit_policies, dbusername, action_name, object_name, event_timestamp
FROM unified_audit_trail
WHERE dbusername = 'DONGHUA'
AND event_timestamp_utc > TO_TIMESTAMP('2025-10-01','YYYY-MM-DD')
AND object_name IN ('T1','T2')
ORDER BY event_timestamp DESC;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Key Execution Plan Details:

-----------------------------------------------------------------------------------------------------------------------------    
| Id  | Operation                       | Name                      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    
-----------------------------------------------------------------------------------------------------------------------------    
...
|  17 |         PARTITION RANGE ITERATOR|                           |     1 |    82 | 45551   (1)| 00:00:02 |   137 |1048575|    
|* 18 |          TABLE ACCESS FULL      | AUD$UNIFIED               |     1 |    82 | 45551   (1)| 00:00:02 |   137 |1048575|    
...

Predicate Information (identified by operation id):
---------------------------------------------------
  18 - filter("USERID"='DONGHUA' AND ("OBJ_NAME"='T1' OR "OBJ_NAME"='T2') AND "EVENT_TIMESTAMP">TIMESTAMP'
              2025-10-01 00:00:00.000000000')

Partition Pruning in Action:

• Operation: PARTITION RANGE ITERATOR (Id 17)
• Pstart: 137 - Starting partition number
• Pstop: 1048575 - Maximum partition number (but only relevant partitions are scanned)

The optimizer uses the event_timestamp_utc > TO_TIMESTAMP('2025-10-01','YYYY-MM-DD') predicate to:

1. Identify which partitions contain data from October 2025 onwards
2. Skip all partitions before October 2025
3. Only scan relevant monthly partitions (starting from partition 137)

This dramatically reduces I/O and improves query performance, especially in databases with years of audit history.

Performance Impact: Constant vs. Dynamic Predicates

The effectiveness of partition pruning depends heavily on whether the optimizer can determine partition boundaries at parse time. Let's compare two queries:

Query 1: Using Constant Timestamp (Optimal)

SET TIMING ON;

SELECT unified_audit_policies, dbusername, action_name, object_name, event_timestamp
FROM unified_audit_trail
WHERE dbusername = 'DONGHUA'
AND event_timestamp_utc > TO_TIMESTAMP('2025-10-01','YYYY-MM-DD')
AND object_name IN ('T1','T2')
ORDER BY event_timestamp DESC;

Elapsed: 00:00:00.293

Query 2: Using Dynamic Expression (Suboptimal)

SELECT unified_audit_policies, dbusername, action_name, object_name, event_timestamp
FROM unified_audit_trail
WHERE dbusername = 'DONGHUA'
AND event_timestamp_utc > SYSTIMESTAMP - 1
AND object_name IN ('T1','T2')
ORDER BY event_timestamp DESC;

Elapsed: 00:00:09.181

Performance Difference: 31x slower (9.181s vs 0.293s)

Execution Plan Comparison

Constant Predicate Plan:

| Id  | Operation                       | Pstart| Pstop |  Cost  |
------------------------------------------------------------------
|  17 | PARTITION RANGE ITERATOR        |   137 |1048575| 45551  |
|  18 | TABLE ACCESS FULL (AUD$UNIFIED) |   137 |1048575| 45551  |

• Pstart: 137 - Specific starting partition identified at parse time
• Cost: 45,551 - Lower cost due to fewer partitions scanned

Dynamic Predicate Plan:

| Id  | Operation                       | Pstart| Pstop |  Cost  |
------------------------------------------------------------------
|  17 | PARTITION RANGE ITERATOR        |   KEY |1048575| 492K   |
|  18 | TABLE ACCESS FULL (AUD$UNIFIED) |   KEY |1048575| 492K   |

• Pstart: KEY - Partition determined at runtime (cannot prune at parse time)
• Cost: 492,000 - 10.8x higher cost estimate
• Must evaluate SYSTIMESTAMP - 1 for each execution

Key Takeaway

For optimal partition pruning performance:

• ✅ Use constant values: TO_TIMESTAMP('2025-10-01','YYYY-MM-DD')
• ❌ Avoid dynamic expressions: SYSTIMESTAMP - 1, SYSDATE - 7

When the optimizer sees a constant, it can:

1. Determine exact partition boundaries at parse time
2. Generate a more efficient execution plan
3. Skip irrelevant partitions entirely

When using dynamic expressions, the optimizer must:

1. Defer partition determination to runtime
2. Potentially scan more partitions
3. Re-evaluate the expression for each query execution

Conclusion

Oracle's mixed mode auditing provides a seamless transition path from standard to unified auditing. Both mechanisms can coexist, ensuring comprehensive audit coverage. The unified auditing framework's partitioned architecture delivers significant performance advantages through partition pruning, making it the recommended approach for modern Oracle databases.

When querying audit data with time-based filters using event_timestamp_utc, the optimizer automatically leverages partition pruning to scan only relevant monthly partitions, resulting in faster queries and reduced resource consumption—a critical feature for databases with extensive audit histories.