Tag Archives: Database Internals

How PostgreSQL Replay Uncommitted Data During Crash Recovery

PostgreSQLclog, Crash Recovery, Database Architecture, Database Internals, How PostgreSQL Handles Uncommitted Data During Crash Recovery, How PostgreSQL Replay Uncommitted Data During Crash Recovery, mvcc, PostgreSQL, PostgreSQL recovery, redo log, uncommitted data, WAL

How PostgreSQL Replay Uncommitted Data During Crash Recovery

When PostgreSQL crashes, everything in memory is lost but the important files on disk survive — WAL files (transaction history), data files (actual tables), CLOG (who committed or not) and pg_control (database status file).
When PostgreSQL restarts, the Postmaster (the main process) first reads pg_control file — if it sees the database was not shut down cleanly, it understands a crash happened and starts the Startup Process to fix the database.
The Startup Process reads pg_control to find the last checkpoint position (think of it as the last known good save point) and goes to that exact position in the WAL files to start reading from there.
The Startup Process reads WAL files one record at a time — for each record, it finds the related data page, loads it into memory (Shared Buffers), applies the change to that page in memory and marks it as dirty (modified but not yet written to disk).
The Startup Process applies every single WAL record to memory pages — it does not care whether the transaction was committed or not — both committed and uncommitted changes are loaded into Shared Buffers because the WAL replay never checks CLOG at this stage.
Before applying any change to a page, the Startup Process checks if that page was already updated before the crash (by checking pd_lsn on the page) — if the page is already up to date it simply skips that WAL record and moves to the next one — this makes replay safe to run multiple times.
When the Startup Process sees a COMMIT or ABORT record in the WAL, it immediately updates the CLOG file — marking that transaction as COMMITTED or ABORTED so PostgreSQL knows the final outcome of that transaction.
When the Startup Process reaches the end of WAL files (the crash point), any transaction that had no COMMIT or ABORT record is considered as never completed — the Startup Process explicitly marks all those transactions as ABORTED in CLOG right away before allowing any user to connect — this is why you always see ABORTED and never IN_PROGRESS after a crash recovery.
Once WAL replay is done, a final checkpoint runs — BGWriter and Checkpointer take all the dirty pages sitting in Shared Buffers (including pages with uncommitted data) and write them permanently to the actual database files on disk — then pg_control is updated to say the database is healthy and users are allowed to connect.
Even though uncommitted data physically exists in the database files on disk, no user will ever see it — because CLOG says those transactions are ABORTED, MVCC automatically hides those rows from every query — and later VACUUM comes along and physically cleans up those dead rows from the pages and frees up the space.

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you

Rajasekhar Amudala

Email: br8dba@gmail.com
LinkedIn: linkedin.com/in/rajasekhar-amudala

How ABORT/ROLLBACK Works in PostgreSQL

PostgreSQLabort, autovacuum, clog, Database Internals, database optimization, dead tuples, How abort works in in postgresql, how rollback works in postgresql, mvcc, pg_xact, PostgreSQL, PostgreSQL internals, rollback, transaction management, WAL, write ahead log

How ABORT Works in PostgreSQL

Signal: The database detects an error or receives a ROLLBACK command and marks the transaction as aborted.
WAL File Write: An ABORT record is written to the WAL buffer in RAM and flushed into the actual 16MB WAL files on disk to keep a complete history.
CLOG Status: The status of the Transaction ID (XID) is instantly switched to “Aborted” in the Commit Log bitmap.
MVCC Isolation: The modified data rows are left on the disk but immediately become invisible to all other users.
Cleanup: The background Autovacuum process later scans the database, clears out these dead rows, and reclaims the disk space.

Thank you

Rajasekhar Amudala

Email: br8dba@gmail.com
LinkedIn: linkedin.com/in/rajasekhar-amudala

WAL Commit Process

PostgreSQLACID Transactions, archive_command, archive_mode, br8dba, Checkpoint Process, Crash Recovery, Database Durability, Database Internals, Database Recovery, Dirty Pages, Enterprise PostgreSQL, fsync, Log Sequence Number, LSN, Open Source Database, pg_switch_wal, pg_wal, PostgreSQL, PostgreSQL 16, PostgreSQL 17, PostgreSQL administration, PostgreSQL architecture, PostgreSQL checkpoint, PostgreSQL DBA, PostgreSQL Guide, PostgreSQL internals, PostgreSQL Monitoring, PostgreSQL Performance, PostgreSQL performance tuning, PostgreSQL recovery, PostgreSQL training, PostgreSQL Tutorial, PostgreSQL WAL, Shared Buffers, synchronous_commit, Transaction Commit, WAL archiving, WAL Buffer, WAL Commit Process, WAL files, WAL Flush, WAL Lifecycle, WAL Record, WAL Segment, WAL Writer, write-ahead logging

PostgreSQL WAL Commit Process

1. The Modification (In-Memory)

When a user executes a data-modifying query (like INSERT, UPDATE, or DELETE):

The change is made to the table or index data inside the Shared Buffers (RAM). The page in memory is now marked as “dirty.”
Simultaneously, a record of this exact change is constructed and written sequentially into the WAL Buffers (also in RAM).

2. The `COMMIT` Command Issued

When the client sends the COMMIT command, PostgreSQL must guarantee that this change will survive a sudden power outage or crash before it can tell the user “Success.”

3. Flushing to Disk (`XLogFlush`)

To ensure durability without the massive overhead of writing entire data pages to disk immediately, Postgres uses the Write-Ahead Logging protocol:

The internal function XLogFlush() is called.
It identifies the exact position (Log Sequence Number, or LSN) of the commit record in the WAL Buffer.
It issues a synchronous write to flush all WAL buffers up to that LSN out of RAM and into the current 16MB WAL segment file on permanent storage.
An fsync() system call is issued to ensure the OS cache actually commits the data to physical disk platters or flash memory.

4. Acknowledgment to the Client

Once the operating system confirms that the WAL record is safely written to the physical storage, the transaction status is updated to “committed” in the commit log (CLOG), and PostgreSQL sends a success acknowledgment back to the client application.

Crucial Architectural Concepts

Write-Ahead Rule: The core rule of WAL is that changes to data pages must not be written to permanent database files on disk until the log records describing those changes have been flushed to stable storage. If the server crashes, Postgres reads the WAL from the last checkpoint forward and reapplies the changes (“redoes” them).
Asynchronous Commit Alternative: If you set the configuration parameter synchronous_commit = off, Postgres will acknowledge the client’s COMMIT before the WAL buffer is flushed to disk (relying on the WAL Writer background process to flush it within roughly 3 times wal_writer_delay). This massively increases write throughput but introduces a risk of losing up to a split-second of recent transactions if the server suddenly loses power.

Thank you

Rajasekhar Amudala

Email: br8dba@gmail.com
LinkedIn: linkedin.com/in/rajasekhar-amudala

Database Directory Layout

PostgreSQL$PGDATA/base, $PGDATA/global, Database Architecture, Database Design, Database DevOps, Database File Organization, Database Internals, database management system, database performance tuning, Database Storage Layout, Database Storage Structure, DevOps, Learn PostgreSQL, Linux Database Administration, Linux Server Administration, Open Source Database, Open Source RDBMS, pg_default, pg_default Tablespace, pg_global, pg_global Tablespace, pgdata, PGDATA Directory, Postgres, PostgreSQL, PostgreSQL administration, PostgreSQL Architecture Explained, PostgreSQL backup and recovery, PostgreSQL Base Directory, PostgreSQL Basics, postgresql best practices, PostgreSQL Cluster, PostgreSQL Configuration, PostgreSQL data directory, PostgreSQL Data Files, PostgreSQL Database Cluster, PostgreSQL DBA Guide, PostgreSQL directory structure, PostgreSQL Disk Layout, PostgreSQL Documentation, PostgreSQL File Structure, PostgreSQL File System Layout, PostgreSQL for beginners, PostgreSQL for DBAs, PostgreSQL Guide, PostgreSQL Installation Guide, PostgreSQL Installation Steps, PostgreSQL internals, PostgreSQL Maintenance, PostgreSQL Metadata, PostgreSQL Performance, PostgreSQL Physical Storage, PostgreSQL Setup, PostgreSQL Storage Architecture, PostgreSQL Storage Engine, PostgreSQL System Catalogs, postgresql tablespace management, PostgreSQL Tablespaces, PostgreSQL Tips, PostgreSQL Tutorial, RDBMS, relational database, Server Administration, SQL database, SQL Server Alternative, Sysadmin Tips

PostgreSQL Physical Directory Layout

Installation Directory Layout

[postgres@pgdb02 ~]$ ls -ltr /usr/pgsql-17/
total 16
drwxr-xr-x. 3 postgres postgres 23 Jun 4 20:38 doc
drwxr-xr-x. 8 postgres postgres 4096 Jun 22 21:08 share
drwxr-xr-x. 2 postgres postgres 4096 Jun 22 21:08 bin
drwxr-xr-x. 3 postgres postgres 4096 Jun 22 21:08 lib
[postgres@pgdb02 ~]$

[postgres@pgdb02 ~]$ ls -lt /usr/pgsql-17/bin
total 14492
-rwxr-xr-x. 1 postgres postgres 71072 May 21 13:49 clusterdb
-rwxr-xr-x. 1 postgres postgres 75240 May 21 13:49 createdb
-rwxr-xr-x. 1 postgres postgres 75624 May 21 13:49 createuser
-rwxr-xr-x. 1 postgres postgres 66632 May 21 13:49 dropdb
-rwxr-xr-x. 1 postgres postgres 66576 May 21 13:49 dropuser
-rwxr-xr-x. 1 postgres postgres 113288 May 21 13:49 initdb
-rwxr-xr-x. 1 postgres postgres 41400 May 21 13:49 oid2name
-rwxr-xr-x. 1 postgres postgres 96824 May 21 13:49 pg_amcheck
-rwxr-xr-x. 1 postgres postgres 41056 May 21 13:49 pg_archivecleanup
-rwxr-xr-x. 1 postgres postgres 142928 May 21 13:49 pg_basebackup
-rwxr-xr-x. 1 postgres postgres 162984 May 21 13:49 pgbench
-rwxr-xr-x. 1 postgres postgres 49632 May 21 13:49 pg_checksums
-rwxr-xr-x. 1 postgres postgres 116872 May 21 13:49 pg_combinebackup
-rwxr-xr-x. 1 postgres postgres 36656 May 21 13:49 pg_config
-rwxr-xr-x. 1 postgres postgres 44984 May 21 13:49 pg_controldata
-rwxr-xr-x. 1 postgres postgres 70768 May 21 13:49 pg_createsubscriber
-rwxr-xr-x. 1 postgres postgres 62296 May 21 13:49 pg_ctl
-rwxr-xr-x. 1 postgres postgres 447368 May 21 13:49 pg_dump
-rwxr-xr-x. 1 postgres postgres 117680 May 21 13:49 pg_dumpall
-rwxr-xr-x. 1 postgres postgres 36952 May 21 13:49 pg_isready
-rwxr-xr-x. 1 postgres postgres 92000 May 21 13:49 pg_receivewal
-rwxr-xr-x. 1 postgres postgres 54544 May 21 13:49 pg_recvlogical
-rwxr-xr-x. 1 postgres postgres 53808 May 21 13:49 pg_resetwal
-rwxr-xr-x. 1 postgres postgres 194496 May 21 13:49 pg_restore
-rwxr-xr-x. 1 postgres postgres 108584 May 21 13:49 pg_rewind
-rwxr-xr-x. 1 postgres postgres 41120 May 21 13:49 pg_test_fsync
-rwxr-xr-x. 1 postgres postgres 32632 May 21 13:49 pg_test_timing
-rwxr-xr-x. 1 postgres postgres 171856 May 21 13:49 pg_upgrade
-rwxr-xr-x. 1 postgres postgres 95928 May 21 13:49 pg_verifybackup
-rwxr-xr-x. 1 postgres postgres 95968 May 21 13:49 pg_waldump
-rwxr-xr-x. 1 postgres postgres 41184 May 21 13:49 pg_walsummary
-rwxr-xr-x. 1 postgres postgres 10875088 May 21 13:49 postgres
-rwxr-xr-x. 1 postgres postgres 729400 May 21 13:49 psql
-rwxr-xr-x. 1 postgres postgres 83816 May 21 13:49 reindexdb
-rwxr-xr-x. 1 postgres postgres 88344 May 21 13:49 vacuumdb
-rwxr-xr-x. 1 postgres postgres 41200 May 21 13:49 vacuumlo
-rwxr-xr-x. 1 postgres postgres 2167 May 21 13:49 postgresql-17-check-db-dir
-rwxr-xr-x. 1 postgres postgres 9617 May 21 13:49 postgresql-17-setup
[postgres@pgdb02 ~]$

Database (Data) Directory Layout

[postgres@pgdb02 ~]$ ls -l /pgData/pgsql17/data
total 68
drwx------. 9 postgres postgres    89 Jun 30 19:40 base
-rw-------. 1 postgres postgres    30 Jul  3 14:30 current_logfiles
drwx------. 2 postgres postgres  4096 Jul  3 14:58 global
drwx------. 2 postgres postgres   162 Jul  3 00:00 log
drwx------. 2 postgres postgres     6 Jun 22 21:11 pg_commit_ts
drwx------. 2 postgres postgres     6 Jun 22 21:11 pg_dynshmem
-rw-------. 1 postgres postgres  5798 Jun 29 23:45 pg_hba.conf
-rw-------. 1 postgres postgres  2640 Jun 22 21:11 pg_ident.conf
drwx------. 4 postgres postgres    68 Jul  3 14:30 pg_logical
drwx------. 4 postgres postgres    36 Jun 22 21:11 pg_multixact
drwx------. 2 postgres postgres     6 Jun 22 21:11 pg_notify
drwx------. 2 postgres postgres     6 Jun 22 21:11 pg_replslot
drwx------. 2 postgres postgres     6 Jun 22 21:11 pg_serial
drwx------. 2 postgres postgres     6 Jun 22 21:11 pg_snapshots
drwx------. 2 postgres postgres     6 Jul  3 14:29 pg_stat
drwx------. 2 postgres postgres     6 Jun 22 21:11 pg_stat_tmp
drwx------. 2 postgres postgres    18 Jun 22 21:11 pg_subtrans
drwx------. 2 postgres postgres    19 Jul  2 18:32 pg_tblspc
drwx------. 2 postgres postgres     6 Jun 22 21:11 pg_twophase
-rw-------. 1 postgres postgres     3 Jun 22 21:11 PG_VERSION
lrwxrwxrwx. 1 postgres postgres    18 Jun 22 21:11 pg_wal -> /pgWal/pgsql17/wal
drwx------. 2 postgres postgres    18 Jun 22 21:11 pg_xact
-rw-------. 1 postgres postgres    88 Jun 22 21:11 postgresql.auto.conf
-rw-------. 1 postgres postgres 30881 Jun 29 23:37 postgresql.conf
-rw-------. 1 postgres postgres    55 Jul  3 14:30 postmaster.opts
-rw-------. 1 postgres postgres    89 Jul  3 14:30 postmaster.pid
[postgres@pgdb02 ~]$

Name	Directory or File	Description
base	Directory	Contains subdirectories for each database. Each database is stored using its own database OID.
current_logfiles	File	Stores information about the log file currently being written by the logging collector process.
global	Directory	Contains cluster-wide shared system catalog tables such as pg_database, pg_authid, pg_tablespace, etc.
log	Directory	Stores PostgreSQL server logs and error log files.
pg_commit_ts	Directory	Stores transaction commit timestamp data when track_commit_timestamp is enabled.
pg_dynshmem	Directory	Stores files used by PostgreSQL dynamic shared memory subsystem.
pg_hba.conf	File	Client authentication configuration file. Controls which users and hosts can connect.
pg_ident.conf	File	Stores user name mapping rules between operating system users and database users.
pg_logical	Directory	Stores metadata and status information used for logical replication and logical decoding.
pg_multixact	Directory	Stores multitransaction status information used for shared row-level locking.
pg_notify	Directory	Stores LISTEN and NOTIFY asynchronous notification status data.
pg_replslot	Directory	Stores physical and logical replication slot information.
pg_serial	Directory	Stores metadata related to committed serializable transactions.
pg_snapshots	Directory	Stores exported transaction snapshot files.
pg_stat	Directory	Stores permanent statistics data used by PostgreSQL statistics subsystem.
pg_stat_tmp	Directory	Stores temporary statistics files generated by statistics collector.
pg_subtrans	Directory	Stores subtransaction status information for nested transactions.
pg_tblspc	Directory	Contains symbolic links pointing to user-created tablespaces.
pg_twophase	Directory	Stores prepared transaction state files used for two-phase commit.
PG_VERSION	File	Stores the PostgreSQL major version number of the database cluster.
pg_wal	Directory (Symlink)	Stores WAL (Write Ahead Log) files for crash recovery and replication. Example: /pgWal/pgsql17/wal
pg_xact	Directory	Stores transaction commit status information and transaction metadata logs.
postgresql.auto.conf	File	Stores configuration parameters automatically updated by ALTER SYSTEM.
postgresql.conf	File	Main PostgreSQL configuration file containing server parameter settings.
postmaster.opts	File	Stores command-line options used when PostgreSQL server was last started.
postmaster.pid	File	Stores Postmaster PID, startup time, port number, socket path, listen address, and shared memory details.

Difference between Base Directory and Global Directory

In PostgreSQL, both the Base Directory and the Global Directory reside within the PGDATA (the data cluster) folder. The primary difference lies in the scope and type of data they store.

postgres=# \db
       List of tablespaces
    Name    |  Owner   | Location
------------+----------+----------
 pg_default | postgres | <----- pg_default physical location: Located at $PGDATA/base/
 pg_global  | postgres | <----- pg_global physical location: Located at $PGDATA/global/
(2 rows)

postgres=#

-- List of Files and Directoires from Global Directory

[postgres@lxceftsgvdb01 ~]$ ls -la /pgData/pgsql17/data/global
total 580
drwx------.  2 postgres postgres  4096 Jul  3 16:03 .
drwx------. 19 postgres postgres  4096 Jul  3 16:01 ..
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 1213
-rw-------.  1 postgres postgres 24576 Jul  3 16:01 1213_fsm
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 1213_vm
-rw-------.  1 postgres postgres     0 Jul  3 16:01 1214
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 1232
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 1233
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 1260
-rw-------.  1 postgres postgres 24576 Jul  3 16:01 1260_fsm
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 1260_vm
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 1261
-rw-------.  1 postgres postgres 24576 Jul  3 16:01 1261_fsm
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 1261_vm
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 1262
-rw-------.  1 postgres postgres 24576 Jul  3 16:01 1262_fsm
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 1262_vm
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 2396
-rw-------.  1 postgres postgres 24576 Jul  3 16:01 2396_fsm
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 2396_vm
-rw-------.  1 postgres postgres 16384 Jul  3 16:01 2397
-rw-------.  1 postgres postgres 16384 Jul  3 16:01 2671
-rw-------.  1 postgres postgres 16384 Jul  3 16:01 2672
-rw-------.  1 postgres postgres 16384 Jul  3 16:01 2676
-rw-------.  1 postgres postgres 16384 Jul  3 16:01 2677
-rw-------.  1 postgres postgres 16384 Jul  3 16:01 2694
-rw-------.  1 postgres postgres 16384 Jul  3 16:01 2695
-rw-------.  1 postgres postgres 16384 Jul  3 16:01 2697
-rw-------.  1 postgres postgres 16384 Jul  3 16:01 2698
-rw-------.  1 postgres postgres     0 Jul  3 16:01 2846
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 2847
-rw-------.  1 postgres postgres     0 Jul  3 16:01 2964
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 2965
-rw-------.  1 postgres postgres     0 Jul  3 16:01 2966
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 2967
-rw-------.  1 postgres postgres     0 Jul  3 16:01 3592
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 3593
-rw-------.  1 postgres postgres     0 Jul  3 16:01 4060
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 4061
-rw-------.  1 postgres postgres     0 Jul  3 16:01 4175
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 4176
-rw-------.  1 postgres postgres     0 Jul  3 16:01 4177
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 4178
-rw-------.  1 postgres postgres     0 Jul  3 16:01 4181
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 4182
-rw-------.  1 postgres postgres     0 Jul  3 16:01 4183
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 4184
-rw-------.  1 postgres postgres     0 Jul  3 16:01 4185
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 4186
-rw-------.  1 postgres postgres     0 Jul  3 16:01 6000
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 6001
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 6002
-rw-------.  1 postgres postgres     0 Jul  3 16:01 6100
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 6114
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 6115
-rw-------.  1 postgres postgres     0 Jul  3 16:01 6243
-rw-------.  1 postgres postgres     0 Jul  3 16:01 6244
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 6245
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 6246
-rw-------.  1 postgres postgres  8192 Jul  3 16:01 6247 
-rw-------.  1 postgres postgres 16384 Jul  3 16:01 6302
-rw-------.  1 postgres postgres 16384 Jul  3 16:01 6303
-rw-------.  1 postgres postgres  8192 Jul  3 16:06 pg_control
-rw-------.  1 postgres postgres   524 Jul  3 16:01 pg_filenode.map
-rw-------.  1 postgres postgres 28724 Jul  3 16:03 pg_internal.init
[postgres@lxceftsgvdb01 ~]$

-- List of Files and Directories from Base Directory 

[postgres@lxceftsgvdb01 ~]$ ls -la /pgData/pgsql17/data/base
total 40
drwx------.  5 postgres postgres   33 Jul  3 16:01 .
drwx------. 19 postgres postgres 4096 Jul  3 16:01 ..
drwx------.  2 postgres postgres 8192 Jul  3 16:03 1
drwx------.  2 postgres postgres 8192 Jul  3 16:01 4
drwx------.  2 postgres postgres 8192 Jul  3 16:02 5
[postgres@lxceftsgvdb01 ~]$

1. The Global Directory (`pg_global`)

The global directory contains data that is shared across the entire database cluster.

Path: $PGDATA/global/
Scope: Cluster-wide.
Contents: It stores “Shared System Catalogs.” These are tables that contain information applicable to every database in the instance.
Key Files:
- pg_database: Information about all databases in the cluster.
- pg_authid: User roles and passwords (since a user can log into any database in the cluster).
- pg_tablespace: Information about defined storage locations.
Identification: Objects in this directory always have an OID (Object Identifier), but they do not belong to a specific database ID.

Lists only actual shared/global catalog tables (no indexes, no TOAST tables) from the global directory

-- Let's look at inside postgres global directory (only actual tables, excluding indexes and TOAST)

[postgres@lxceftsgvdb01 ~]$ cat list_global_tables.sh
#!/bin/bash
cd $PGDATA
# Header
printf "%-8s | %-40s | %-10s | %-15s | %-10s\n" \
"OID" "OBJECT_NAME" "TABLEOID" "SCHEMA" "TABLESPACE"

printf -- "-----------------------------------------------------------------------------------------------\n"

# Loop through files in global directory
for i in $(find global -maxdepth 1 -type f | awk -F/ '{print $2}' | egrep '^[0-9]' | egrep -v 'fsm|vm')
do
    # Get object details from oid2name
    result=$(oid2name -x -S -q -o $i)

    # Parse fields
    oid=$(echo $result | awk '{print $1}')
    name=$(echo $result | awk '{print $2}')
    tableoid=$(echo $result | awk '{print $3}')
    schema=$(echo $result | awk '{print $4}')
    db=$(echo $result | awk '{print $5}')

    # Check relation type from pg_class
    relkind=$(psql -At -c "SELECT relkind FROM pg_class WHERE oid=$oid;" 2>/dev/null)

    # Show only actual tables (relkind = r)
    if [ "$relkind" = "r" ]; then
        printf "%-8s | %-40s | %-10s | %-15s | %-10s\n" \
        "$oid" "$name" "$tableoid" "$schema" "$db"
    fi
done
[postgres@lxceftsgvdb01 ~]$

[postgres@lxceftsgvdb01 ~]$ ./list_global_tables.sh
OID      | OBJECT_NAME                              | TABLEOID   | SCHEMA          | TABLESPACE
-----------------------------------------------------------------------------------------------
1262     | pg_database                              | 1262       | pg_catalog      | pg_global
2964     | pg_db_role_setting                       | 2964       | pg_catalog      | pg_global
1213     | pg_tablespace                            | 1213       | pg_catalog      | pg_global
1260     | pg_authid                                | 1260       | pg_catalog      | pg_global
1261     | pg_auth_members                          | 1261       | pg_catalog      | pg_global
1214     | pg_shdepend                              | 1214       | pg_catalog      | pg_global
2396     | pg_shdescription                         | 2396       | pg_catalog      | pg_global
6000     | pg_replication_origin                    | 6000       | pg_catalog      | pg_global
3592     | pg_shseclabel                            | 3592       | pg_catalog      | pg_global
6243     | pg_parameter_acl                         | 6243       | pg_catalog      | pg_global
6100     | pg_subscription                          | 6100       | pg_catalog      | pg_global
[postgres@pgdb02 ~]$

[postgres@lxceftsgvdb01 ~]$ tree /pgData/pgsql17/data/global
/pgData/pgsql17/data/global
├── 1213
├── 1213_fsm
├── 1213_vm
├── 1214
├── 1232
├── 1233
├── 1260
├── 1260_fsm
├── 1260_vm
├── 1261
├── 1261_fsm
├── 1261_vm
├── 1262
├── 1262_fsm
├── 1262_vm
├── 2396
├── 2396_fsm
├── 2396_vm
├── 2397
├── 2671
├── 2672
├── 2676
├── 2677
├── 2694
├── 2695
├── 2697
├── 2698
├── 2846
├── 2847
├── 2964
├── 2965
├── 2966
├── 2967
├── 3592
├── 3593
├── 4060
├── 4061
├── 4175
├── 4176
├── 4177
├── 4178
├── 4181
├── 4182
├── 4183
├── 4184
├── 4185
├── 4186
├── 6000
├── 6001
├── 6002
├── 6100
├── 6114
├── 6115
├── 6243
├── 6244
├── 6245
├── 6246
├── 6247
├── 6302
├── 6303
├── pg_control
├── pg_filenode.map
└── pg_internal.init

0 directories, 63 files
[postgres@lxceftsgvdb01 ~]$

2. The Base Directory (`pg_base`)

The base directory contains the actual user data and system catalogs that are private to each specific database.

Path: $PGDATA/base/
Scope: Database-specific.
Structure: Inside base, there is a subdirectory for every database in the cluster, named after the database’s OID (e.g., $PGDATA/base/16384/).
Contents:
- User Tables and Indexes: The actual data you insert into your tables.
- Local System Catalogs: Tables like pg_class, pg_attribute, and pg_type which describe the structure of that specific database.
Identification: If you want to find where a specific table is stored, you look in base/[Database OID]/[Table Filenode]

[postgres@pgdb02 ~]$ psql -c "SELECT oid, datname FROM pg_database order by oid;"
 oid |  datname
-----+-----------
   1 | template1
   4 | template0
   5 | postgres
(3 rows)

[postgres@pgdb02 ~]$
[postgres@lxceftsgvdb01 ~]$ ls /pgData/pgsql17/data/base | sort -n
1  <---- template1
4  <---- template0
5  <---- postgres
[postgres@pgdb02 ~]$

-- Let's look at inside postgres Base directory (only actual tables, excluding indexes and TOAST)

[postgres@lxceftsgvdb01 ~]$ cat list_base_tables.sh
#!/bin/bash

export PGHOME=/usr/pgsql-17
export PGDATA=/pgData/pgsql17/data
export PATH=$PGHOME/bin:$PATH

DBNAME="postgres"

# Get database OID
DBOID=$(psql -At -d $DBNAME -c "SELECT oid FROM pg_database WHERE datname='$DBNAME'")

# Header
printf "%-8s | %-40s | %-10s | %-15s | %-10s\n" \
"OID" "OBJECT_NAME" "TABLEOID" "SCHEMA" "Tablespace"

printf -- "-----------------------------------------------------------------------------------------------\n"

# Use absolute path ($PGDATA/base/)
for i in $(find $PGDATA/base/$DBOID -maxdepth 1 -type f | awk -F/ '{print $NF}' | egrep '^[0-9]' | egrep -v 'fsm|vm')
do
    # Get object details
    result=$(oid2name -d $DBNAME -x -q -o $i)

    # Parse fields
    oid=$(echo "$result" | awk '{print $1}')
    name=$(echo "$result" | awk '{print $2}')
    tableoid=$(echo "$result" | awk '{print $3}')
    schema=$(echo "$result" | awk '{print $4}')
    db=$(echo "$result" | awk '{print $5}')

    # Check relation type
    relkind=$(psql -At -d $DBNAME -c "SELECT relkind FROM pg_class WHERE oid=$oid;" 2>/dev/null)

    # Show only actual tables
    if [ "$relkind" = "r" ]; then
        printf "%-8s | %-40s | %-10s | %-15s | %-10s\n" \
        "$oid" "$name" "$tableoid" "$schema" "$db"
    fi
done
[postgres@lxceftsgvdb01 ~]$

[postgres@lxceftsgvdb01 ~]$ ./list_base_tables.sh

OID      | OBJECT_NAME                              | TABLEOID   | SCHEMA          | TABLESPACE
-----------------------------------------------------------------------------------------------
1255     | pg_proc                                  | 1255       | pg_catalog      | pg_default
1247     | pg_type                                  | 1247       | pg_catalog      | pg_default
1249     | pg_attribute                             | 1249       | pg_catalog      | pg_default
1259     | pg_class                                 | 1259       | pg_catalog      | pg_default
2604     | pg_attrdef                               | 2604       | pg_catalog      | pg_default
2606     | pg_constraint                            | 2606       | pg_catalog      | pg_default
2611     | pg_inherits                              | 2611       | pg_catalog      | pg_default
2610     | pg_index                                 | 2610       | pg_catalog      | pg_default
2617     | pg_operator                              | 2617       | pg_catalog      | pg_default
2753     | pg_opfamily                              | 2753       | pg_catalog      | pg_default
2616     | pg_opclass                               | 2616       | pg_catalog      | pg_default
2601     | pg_am                                    | 2601       | pg_catalog      | pg_default
2602     | pg_amop                                  | 2602       | pg_catalog      | pg_default
2603     | pg_amproc                                | 2603       | pg_catalog      | pg_default
2612     | pg_language                              | 2612       | pg_catalog      | pg_default
2995     | pg_largeobject_metadata                  | 2995       | pg_catalog      | pg_default
2613     | pg_largeobject                           | 2613       | pg_catalog      | pg_default
2600     | pg_aggregate                             | 2600       | pg_catalog      | pg_default
2619     | pg_statistic                             | 2619       | pg_catalog      | pg_default
3381     | pg_statistic_ext                         | 3381       | pg_catalog      | pg_default
3429     | pg_statistic_ext_data                    | 3429       | pg_catalog      | pg_default
2618     | pg_rewrite                               | 2618       | pg_catalog      | pg_default
2620     | pg_trigger                               | 2620       | pg_catalog      | pg_default
3466     | pg_event_trigger                         | 3466       | pg_catalog      | pg_default
2609     | pg_description                           | 2609       | pg_catalog      | pg_default
2605     | pg_cast                                  | 2605       | pg_catalog      | pg_default
3501     | pg_enum                                  | 3501       | pg_catalog      | pg_default
2615     | pg_namespace                             | 2615       | pg_catalog      | pg_default
2607     | pg_conversion                            | 2607       | pg_catalog      | pg_default
2608     | pg_depend                                | 2608       | pg_catalog      | pg_default
3602     | pg_ts_config                             | 3602       | pg_catalog      | pg_default
3603     | pg_ts_config_map                         | 3603       | pg_catalog      | pg_default
3600     | pg_ts_dict                               | 3600       | pg_catalog      | pg_default
3601     | pg_ts_parser                             | 3601       | pg_catalog      | pg_default
3764     | pg_ts_template                           | 3764       | pg_catalog      | pg_default
3079     | pg_extension                             | 3079       | pg_catalog      | pg_default
2328     | pg_foreign_data_wrapper                  | 2328       | pg_catalog      | pg_default
1417     | pg_foreign_server                        | 1417       | pg_catalog      | pg_default
1418     | pg_user_mapping                          | 1418       | pg_catalog      | pg_default
3118     | pg_foreign_table                         | 3118       | pg_catalog      | pg_default
3256     | pg_policy                                | 3256       | pg_catalog      | pg_default
826      | pg_default_acl                           | 826        | pg_catalog      | pg_default
3394     | pg_init_privs                            | 3394       | pg_catalog      | pg_default
3596     | pg_seclabel                              | 3596       | pg_catalog      | pg_default
3456     | pg_collation                             | 3456       | pg_catalog      | pg_default
3350     | pg_partitioned_table                     | 3350       | pg_catalog      | pg_default
3541     | pg_range                                 | 3541       | pg_catalog      | pg_default
3576     | pg_transform                             | 3576       | pg_catalog      | pg_default
2224     | pg_sequence                              | 2224       | pg_catalog      | pg_default
6104     | pg_publication                           | 6104       | pg_catalog      | pg_default
6237     | pg_publication_namespace                 | 6237       | pg_catalog      | pg_default
6106     | pg_publication_rel                       | 6106       | pg_catalog      | pg_default
6102     | pg_subscription_rel                      | 6102       | pg_catalog      | pg_default
14382    | sql_features                             | 14382      | information_schema | pg_default
14387    | sql_implementation_info                  | 14387      | information_schema | pg_default
14392    | sql_parts                                | 14392      | information_schema | pg_default
14397    | sql_sizing                               | 14397      | information_schema | pg_default
[postgres@lxceftsgvdb01 ~]$

-- Let's look at inside postgres Base directory (Complete List)

[postgres@lxceftsgvdb01 ~]$ tree /pgData/pgsql17/data/base/5
/pgData/pgsql17/data/base/5
├── 112
├── 113
├── 1247
├── 1247_fsm
├── 1247_vm
├── 1249
├── 1249_fsm
├── 1249_vm
├── 1255
├── 1255_fsm
├── 1255_vm
├── 1259
├── 1259_fsm
├── 1259_vm
├── 1417
├── 1418
├── 14382
├── 14382_fsm
├── 14382_vm
├── 14385
├── 14386
├── 14387
├── 14387_fsm
├── 14387_vm
├── 14390
├── 14391
├── 14392
├── 14392_fsm
├── 14392_vm
├── 14395
├── 14396
├── 14397
├── 14397_fsm
├── 14397_vm
├── 14400
├── 14401
├── 174
├── 175
├── 2187
├── 2224
├── 2228
├── 2328
├── 2336
├── 2337
├── 2579
├── 2600
├── 2600_fsm
├── 2600_vm
├── 2601
├── 2601_fsm
├── 2601_vm
├── 2602
├── 2602_fsm
├── 2602_vm
├── 2603
├── 2603_fsm
├── 2603_vm
├── 2604
├── 2605
├── 2605_fsm
├── 2605_vm
├── 2606
├── 2606_fsm
├── 2606_vm
├── 2607
├── 2607_fsm
├── 2607_vm
├── 2608
├── 2608_fsm
├── 2608_vm
├── 2609
├── 2609_fsm
├── 2609_vm
├── 2610
├── 2610_fsm
├── 2610_vm
├── 2611
├── 2612
├── 2612_fsm
├── 2612_vm
├── 2613
├── 2615
├── 2615_fsm
├── 2615_vm
├── 2616
├── 2616_fsm
├── 2616_vm
├── 2617
├── 2617_fsm
├── 2617_vm
├── 2618
├── 2618_fsm
├── 2618_vm
├── 2619
├── 2619_fsm
├── 2619_vm
├── 2620
├── 2650
├── 2651
├── 2652
├── 2653
├── 2654
├── 2655
├── 2656
├── 2657
├── 2658
├── 2659
├── 2660
├── 2661
├── 2662
├── 2663
├── 2664
├── 2665
├── 2666
├── 2667
├── 2668
├── 2669
├── 2670
├── 2673
├── 2674
├── 2675
├── 2678
├── 2679
├── 2680
├── 2681
├── 2682
├── 2683
├── 2684
├── 2685
├── 2686
├── 2687
├── 2688
├── 2689
├── 2690
├── 2691
├── 2692
├── 2693
├── 2696
├── 2699
├── 2701
├── 2702
├── 2703
├── 2704
├── 2753
├── 2753_fsm
├── 2753_vm
├── 2754
├── 2755
├── 2756
├── 2757
├── 2830
├── 2831
├── 2832
├── 2833
├── 2834
├── 2835
├── 2836
├── 2836_fsm
├── 2836_vm
├── 2837
├── 2838
├── 2838_fsm
├── 2838_vm
├── 2839
├── 2840
├── 2840_fsm
├── 2840_vm
├── 2841
├── 2995
├── 2996
├── 3079
├── 3079_fsm
├── 3079_vm
├── 3080
├── 3081
├── 3085
├── 3118
├── 3119
├── 3164
├── 3256
├── 3257
├── 3258
├── 3350
├── 3351
├── 3379
├── 3380
├── 3381
├── 3394
├── 3394_fsm
├── 3394_vm
├── 3395
├── 3429
├── 3430
├── 3431
├── 3433
├── 3439
├── 3440
├── 3455
├── 3456
├── 3456_fsm
├── 3456_vm
├── 3466
├── 3467
├── 3468
├── 3501
├── 3502
├── 3503
├── 3534
├── 3541
├── 3541_fsm
├── 3541_vm
├── 3542
├── 3574
├── 3575
├── 3576
├── 3596
├── 3597
├── 3598
├── 3599
├── 3600
├── 3600_fsm
├── 3600_vm
├── 3601
├── 3601_fsm
├── 3601_vm
├── 3602
├── 3602_fsm
├── 3602_vm
├── 3603
├── 3603_fsm
├── 3603_vm
├── 3604
├── 3605
├── 3606
├── 3607
├── 3608
├── 3609
├── 3712
├── 3764
├── 3764_fsm
├── 3764_vm
├── 3766
├── 3767
├── 3997
├── 4143
├── 4144
├── 4145
├── 4146
├── 4147
├── 4148
├── 4149
├── 4150
├── 4151
├── 4152
├── 4153
├── 4154
├── 4155
├── 4156
├── 4157
├── 4158
├── 4159
├── 4160
├── 4163
├── 4164
├── 4165
├── 4166
├── 4167
├── 4168
├── 4169
├── 4170
├── 4171
├── 4172
├── 4173
├── 4174
├── 5002
├── 548
├── 549
├── 6102
├── 6104
├── 6106
├── 6110
├── 6111
├── 6112
├── 6113
├── 6116
├── 6117
├── 6175
├── 6176
├── 6228
├── 6229
├── 6237
├── 6238
├── 6239
├── 826
├── 827
├── 828
├── pg_filenode.map
├── pg_internal.init
└── PG_VERSION

0 directories, 299 files
[postgres@lxceftsgvdb01 ~]$

BRIGHT DBA

ORACLE DBA – Tips and Techniques

Tag Archives: Database Internals

How PostgreSQL Replay Uncommitted Data During Crash Recovery

How PostgreSQL Replay Uncommitted Data During Crash Recovery

How ABORT/ROLLBACK Works in PostgreSQL

How ABORT Works in PostgreSQL

WAL Commit Process

PostgreSQL WAL Commit Process

1. The Modification (In-Memory)

2. The `COMMIT` Command Issued

3. Flushing to Disk (`XLogFlush`)

4. Acknowledgment to the Client

Crucial Architectural Concepts

Database Directory Layout

PostgreSQL Physical Directory Layout

Difference between Base Directory and Global Directory

1. The Global Directory (`pg_global`)

2. The Base Directory (`pg_base`)

1. The Modification (In-Memory)

2. The COMMIT Command Issued

3. Flushing to Disk (XLogFlush)

4. Acknowledgment to the Client

Crucial Architectural Concepts

Difference between Base Directory and Global Directory

1. The Global Directory (pg_global)

2. The Base Directory (pg_base)

2. The `COMMIT` Command Issued

3. Flushing to Disk (`XLogFlush`)

1. The Global Directory (`pg_global`)

2. The Base Directory (`pg_base`)