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

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 Architecture

How PostgreSQL Replay Uncommitted Data During Crash Recovery

How PostgreSQL Replay Uncommitted Data During Crash Recovery

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`)

Difference between Base Directory and Global Directory

1. The Global Directory (pg_global)

2. The Base Directory (pg_base)

1. The Global Directory (`pg_global`)

2. The Base Directory (`pg_base`)