Oracle DBA to PostgreSQL: Install and Architecture, Part I

As an Oracle DBA stepping into the PostgreSQL world, one of the first surprises you’ll encounter is during installation. If you’re used to Oracle’s DBCA and multi-step setup, PostgreSQL’s simplicity might feel almost too easy – there’s no complex response file, but don’t let that fool you. Under the hood, PostgreSQL has its own powerful architecture, and understanding those foundational differences is key to making the most of your new multi-platform world.

What’s in a Name?

I admit this is still causing me challenges: How do you pronounce PostgreSQL? 

It’s pronounced “Post-gres-Q-L”.  No, I’m not kidding and Ryan Booz could have knocked me over with a feather when he let me know, but he also told me if you just say Postgres and drop the Q & L, you’re fine in many instances.  The two terms are often interchangeable, but it’s not correct to say “Post-gre-SQL” or “Post-gres- SQL” (and I’ll most likely be caught making this mistake on auto-pilot for quite some time).

Instance?  No, It’s a Cluster

In Oracle, you’re familiar with the concept of a database instance, which includes the memory structures (SGA, PGA) and background processes that interact with your data files. In a multitenant environment, you also deal with container databases (CDBs) and pluggable databases (PDBs), adding layers to your administrative scope.

PostgreSQL does not use the term “instance” in the same way. Instead, when you install PostgreSQL, you’re setting up what it calls a cluster, but not in the RAC or Exadata sense of multiple servers or nodes. In PostgreSQL, a cluster refers to a single installation of the PostgreSQL server that manages one set of background processes and memory, and multiple databases.

Each cluster in PostgreSQL contains:

• One postgres server process (and supporting background processes).
• One data directory (i.e. /var/lib/pgsql/data).
• Multiple databases (each logically separate, but sharing system-wide configuration and physical storage control).

So, when you initialize PostgreSQL (using initdb), you’re creating a cluster, not a single database. Tools like pg_ctl manage the entire cluster, not an individual database.

No Listener, tnsnames.ora, Just Start It.

There’s no Oracle-style listener, support files or configuration. PostgreSQL uses a much simpler networking model. When the PostgreSQL service is running, it listens on a default port (5432) for all databases in the cluster, and client connections are managed using roles and access files like pg_hba.conf. You don’t need to configure each database to accept connections separately as they all just work through the same engine.

Write Ahead Log (WAL) vs. REDO Logs

Another major difference lies in the transaction logging system which is quite different. Oracle DBAs are accustomed to REDO logs and ARCHIVE logs, which is a mechanism designed to ensure recoverability and consistency.

PostgreSQL uses a Write Ahead Log (WAL), which has similar goals but has a different mechanism for processing changes:

• WAL writes changes to a log before they are applied to the data files, just like Oracle’s REDO.
• WAL files are stored in the pg_wal directory inside the data directory.
• There are no separate archive log files unless you configure archiving manually.
• WAL also supports features like Point-in-Time Recovery (PITR) and streaming replication, but there are differences in how these are implemented compared to Oracle’s Data Guard.

Unlike Oracle, PostgreSQL doesn’t use an undo tablespace. Rollback in PostgreSQL relies on MVCC (Multi-Version Concurrency Control), which keeps older versions of rows available until they’re no longer needed, rather than using undo segments and rollback information.

Main Architectural Differences

Feature	Oracle	PostgreSQL
Instance	Memory + processes tied to a DB	Not used – PostgreSQL uses “cluster”
Container/Pluggable Containers	Multitenant CDB/PDB setup	Not applicable – each database is separate
Initialization	Requires DBCA or response files	initdb creates the entire cluster
REDO Logs	Core to recovery and replication	Replaced by WAL in PostgreSQL
Data Archiving	Automatic archiving of REDO	Manual archiving of WAL (optional)
Listener	Required for client connections	Not needed, built-in port listener

Summary

While PostgreSQL’s setup process is more lightweight than Oracle’s, it’s important to understand that its design philosophy is different, vs. just considering it simpler. There’s no instance to configure, but that doesn’t mean less power.  Again, it’s different and that’s the glory of multi-platform – you’re learning something new and it’s important to look at the totality of the architecture to understand how it all works together. The PostgreSQL cluster architecture is modular, extensible, and quite efficient once you get used to it.

In the next post, I’ll dig more into PostgreSQL’s configuration files (postgresql.conf, pg_hba.conf), understanding the role-based authentication system, and experimenting with WAL-based backup and recovery tools.  It’s onward and upward as we continue to glue what we understand from our Oracle knowledge into what we will learn in multiplatform, open source, and increasingly part of the enterprise database toolbox.