Tag Archives: PostgreSQL internals

How ABORT/ROLLBACK Works in PostgreSQL

PostgreSQL, , , , , , , , , , , , , , ,

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.

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

WAL Commit Process

PostgreSQL, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

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.

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

Database Directory Layout

PostgreSQL, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

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 ~]$
NameDirectory or FileDescription
baseDirectoryContains subdirectories for each database. Each database is stored using its own database OID.
current_logfilesFileStores information about the log file currently being written by the logging collector process.
globalDirectoryContains cluster-wide shared system catalog tables such as pg_database, pg_authid, pg_tablespace, etc.
logDirectoryStores PostgreSQL server logs and error log files.
pg_commit_tsDirectoryStores transaction commit timestamp data when track_commit_timestamp is enabled.
pg_dynshmemDirectoryStores files used by PostgreSQL dynamic shared memory subsystem.
pg_hba.confFileClient authentication configuration file. Controls which users and hosts can connect.
pg_ident.confFileStores user name mapping rules between operating system users and database users.
pg_logicalDirectoryStores metadata and status information used for logical replication and logical decoding.
pg_multixactDirectoryStores multitransaction status information used for shared row-level locking.
pg_notifyDirectoryStores LISTEN and NOTIFY asynchronous notification status data.
pg_replslotDirectoryStores physical and logical replication slot information.
pg_serialDirectoryStores metadata related to committed serializable transactions.
pg_snapshotsDirectoryStores exported transaction snapshot files.
pg_statDirectoryStores permanent statistics data used by PostgreSQL statistics subsystem.
pg_stat_tmpDirectoryStores temporary statistics files generated by statistics collector.
pg_subtransDirectoryStores subtransaction status information for nested transactions.
pg_tblspcDirectoryContains symbolic links pointing to user-created tablespaces.
pg_twophaseDirectoryStores prepared transaction state files used for two-phase commit.
PG_VERSIONFileStores the PostgreSQL major version number of the database cluster.
pg_walDirectory (Symlink)Stores WAL (Write Ahead Log) files for crash recovery and replication. Example: /pgWal/pgsql17/wal
pg_xactDirectoryStores transaction commit status information and transaction metadata logs.
postgresql.auto.confFileStores configuration parameters automatically updated by ALTER SYSTEM.
postgresql.confFileMain PostgreSQL configuration file containing server parameter settings.
postmaster.optsFileStores command-line options used when PostgreSQL server was last started.
postmaster.pidFileStores 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 ~]$
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: https://www.linkedin.com/in/rajasekhar-amudala/

Types of Shutdown in PostgreSQL

PostgreSQL, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

Types of Shutdown in PostgreSQL

PostgreSQL Shutdown TypeOracleBehavior
Smart (SIGTERM)SHUTDOWN NORMALThe server stops accepting new connections but allows existing sessions to continue running normally. PostgreSQL shuts down only after all active sessions disconnect and their transactions complete successfully.
Fast (SIGINT – Default)SHUTDOWN IMMEDIATEThe server stops accepting new connections and immediately terminates all active sessions. Any uncommitted transactions are rolled back, and PostgreSQL performs a clean shutdown gracefully.
Immediate (SIGQUIT)SHUTDOWN ABORTThe server terminates all processes instantly without performing a proper shutdown. No cleanup or checkpoint occurs, so PostgreSQL performs crash recovery by replaying WAL files during the next startup.
Shutdown TypeSignalCommand ExampleBehavior
SmartSIGTERMpg_ctl stop -D /pgData -m smartStops new connections, waits for existing sessions to finish normally, then shuts down.
Fast (Default)SIGINTpg_ctl stop -D /pgdata -m fastStops new connections, terminates active sessions, rolls back uncommitted transactions, and shuts down cleanly.
ImmediateSIGQUITpg_ctl stop -D /pgdata -m immediateStops immediately without proper shutdown. Recovery from WAL files happens during next startup.

Difference Between Reload and Restart

  • When we change server configuration parameters, PostgreSQL needs to read those changes before they become active.
  • Reload applies the new configuration without stopping or restarting the database service. Existing connections and database activity continue normally.
  • Some configuration parameter changes cannot be applied through reload and will only take effect after a restart.
  • Restart stops the PostgreSQL server gracefully, ends all running activities, releases resources, closes open files, and then starts the server again with the new configuration.

 

Smart Shutdown (SIGTERM):

[postgres@pgdb02 ~]$ pg_ctl stop -D /pgData/pgsql17/data -m smart
waiting for server to shut down.... done
server stopped
[postgres@pgdb02 ~]$

2026-07-03 14:26:59.741 +08 [3476] LOG:  received smart shutdown request
2026-07-03 14:26:59.752 +08 [3476] LOG:  background worker "logical replication launcher" (PID 3483) exited with exit code 1
2026-07-03 14:26:59.753 +08 [3478] LOG:  shutting down
2026-07-03 14:26:59.755 +08 [3478] LOG:  checkpoint starting: shutdown immediate
2026-07-03 14:26:59.765 +08 [3478] LOG:  checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.002 s, sync=0.003 s, total=0.012 s; sync files=2, longest=0.003 s, average=0.002 s; distance=0 kB, estimate=0 kB; lsn=1/105465F0, redo lsn=1/105465F0
2026-07-03 14:26:59.771 +08 [3476] LOG:  database system is shut down


[postgres@pgdb02 ~]$ pg_ctl start -D /pgData/pgsql17/data
waiting for server to start....2026-07-03 14:27:18.394 +08 [3487] LOG:  redirecting log output to logging collector process
2026-07-03 14:27:18.394 +08 [3487] HINT:  Future log output will appear in directory "log".
 done
server started
[postgres@pgdb02 ~]$

2026-07-03 14:27:18.394 +08 [3487] LOG:  starting PostgreSQL 17.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-14), 64-bit
2026-07-03 14:27:18.396 +08 [3487] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2026-07-03 14:27:18.396 +08 [3487] LOG:  listening on IPv6 address "::", port 5432
2026-07-03 14:27:18.399 +08 [3487] LOG:  listening on Unix socket "/run/postgresql/.s.PGSQL.5432"
2026-07-03 14:27:18.404 +08 [3487] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2026-07-03 14:27:18.413 +08 [3491] LOG:  database system was shut down at 2026-07-03 14:26:59 +08
2026-07-03 14:27:18.425 +08 [3487] LOG:  database system is ready to accept connections

Fast Shutdown (SIGINT – Default):

[postgres@pgdb02 ~]$ pg_ctl stop -D /pgData/pgsql17/data -m fast
waiting for server to shut down.... done
server stopped
[postgres@pgdb02 ~]$

2026-07-03 14:28:26.839 +08 [3487] LOG:  received fast shutdown request
2026-07-03 14:28:26.844 +08 [3487] LOG:  aborting any active transactions
2026-07-03 14:28:26.854 +08 [3487] LOG:  background worker "logical replication launcher" (PID 3494) exited with exit code 1
2026-07-03 14:28:26.860 +08 [3489] LOG:  shutting down
2026-07-03 14:28:26.862 +08 [3489] LOG:  checkpoint starting: shutdown immediate
2026-07-03 14:28:26.874 +08 [3489] LOG:  checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.002 s, sync=0.005 s, total=0.015 s; sync files=2, longest=0.004 s, average=0.003 s; distance=0 kB, estimate=0 kB; lsn=1/105466A0, redo lsn=1/105466A0
2026-07-03 14:28:26.882 +08 [3487] LOG:  database system is shut down

[postgres@pgdb02 ~]$ pg_ctl start -D /pgData/pgsql17/data
waiting for server to start....2026-07-03 14:29:00.574 +08 [3505] LOG:  redirecting log output to logging collector process
2026-07-03 14:29:00.574 +08 [3505] HINT:  Future log output will appear in directory "log".
 done
server started
[postgres@pgdb02 ~]$

2026-07-03 14:29:00.575 +08 [3505] LOG:  starting PostgreSQL 17.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-14), 64-bit
2026-07-03 14:29:00.579 +08 [3505] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2026-07-03 14:29:00.580 +08 [3505] LOG:  listening on IPv6 address "::", port 5432
2026-07-03 14:29:00.582 +08 [3505] LOG:  listening on Unix socket "/run/postgresql/.s.PGSQL.5432"
2026-07-03 14:29:00.587 +08 [3505] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2026-07-03 14:29:00.595 +08 [3509] LOG:  database system was shut down at 2026-07-03 14:28:26 +08
2026-07-03 14:29:00.607 +08 [3505] LOG:  database system is ready to accept connections

Immediate Shutdown (SIGQUIT):

[postgres@pgdb02 ~]$ pg_ctl stop -D /pgData/pgsql17/data -m immediate
waiting for server to shut down.... done
server stopped
[postgres@pgdb02 ~]$

2026-07-03 14:29:47.471 +08 [3505] LOG:  received immediate shutdown request
2026-07-03 14:29:47.485 +08 [3505] LOG:  database system is shut down


[postgres@pgdb02 ~]$ pg_ctl start -D /pgData/pgsql17/data
waiting for server to start....2026-07-03 14:30:17.965 +08 [3520] LOG:  redirecting log output to logging collector process
2026-07-03 14:30:17.965 +08 [3520] HINT:  Future log output will appear in directory "log".
. done
server started
[postgres@pgdb02 ~]$

2026-07-03 14:30:17.966 +08 [3520] LOG:  starting PostgreSQL 17.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-14), 64-bit
2026-07-03 14:30:17.966 +08 [3520] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2026-07-03 14:30:17.966 +08 [3520] LOG:  listening on IPv6 address "::", port 5432
2026-07-03 14:30:17.970 +08 [3520] LOG:  listening on Unix socket "/run/postgresql/.s.PGSQL.5432"
2026-07-03 14:30:17.974 +08 [3520] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2026-07-03 14:30:17.983 +08 [3524] LOG:  database system was interrupted; last known up at 2026-07-03 14:29:00 +08
2026-07-03 14:30:19.211 +08 [3524] LOG:  database system was not properly shut down; automatic recovery in progress
2026-07-03 14:30:19.216 +08 [3524] LOG:  redo starts at 1/10546718
2026-07-03 14:30:19.216 +08 [3524] LOG:  invalid record length at 1/10546750: expected at least 24, got 0
2026-07-03 14:30:19.217 +08 [3524] LOG:  redo done at 1/10546718 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
2026-07-03 14:30:19.221 +08 [3522] LOG:  checkpoint starting: end-of-recovery immediate wait
2026-07-03 14:30:19.231 +08 [3522] LOG:  checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.002 s, sync=0.003 s, total=0.012 s; sync files=2, longest=0.002 s, average=0.002 s; distance=0 kB, estimate=0 kB; lsn=1/10546750, redo lsn=1/10546750
2026-07-03 14:30:19.239 +08 [3520] LOG:  database system is ready to accept connections

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: https://www.linkedin.com/in/rajasekhar-amudala/

How SELECT, INSERT, UPDATE, and DELETE Work Internally in PostgreSQL

PostgreSQL, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

 

How SELECT, INSERT, UPDATE, and DELETE work internally in PostgreSQL?

PostgreSQL uses MVCC (Multi-Version Concurrency Control), which means every modification creates a new version of the row instead of overwriting the old one. This is the core concept behind all DML operations.

1. Common Foundation (Applies to All Operations)

  • Heap: Data is stored in heap files (tables). Each row is a tuple.
  • Tuple Header: Every row contains:
    • xmin: Transaction ID that created the row
    • xmax: Transaction ID that deleted/updated the row (null if active)
    • Other visibility information
  • WAL (Write-Ahead Log): All changes are first written to WAL for durability before being applied to data files.
  • Shared Buffers: Data pages are read into memory before being modified.
  • Visibility Rules: A row is visible to a transaction based on its snapshot (which transactions it can see).

2. How SELECT Works

          Visibility Check: For each row, PostgreSQL checks:

    • The row’s xmin must be committed and visible to the current transaction.
    • The row’s xmax must be either null or not visible to the current transaction.

 

3. How INSERT Works

  1. Transaction Starts: A new transaction ID (xid) is assigned.
  2. Buffer Allocation: PostgreSQL finds a free slot in a heap page (or allocates a new page).
  3. Tuple Creation: A new row is created with:
    • xmin = current transaction ID
    • xmax = null
  4. WAL Logging: The change is written to the WAL buffer first.
  5. Page Modification: The new tuple is inserted into the shared buffer.
  6. Commit: On commit, the WAL is flushed to disk (fsync), making the change durable.

4. How UPDATE Works

UPDATE in PostgreSQL does not modify the existing row. It creates a new version of the row.

  1. Find the row using the same visibility rules as SELECT.
  2. Mark old row as dead: Set xmax = current transaction ID on the old tuple.
  3. Insert new row: Create a completely new tuple with:
    • xmin = current transaction ID
    • xmax = null
    • Updated column values
  4. Update indexes (if needed): New index entries point to the new tuple.
  5. WAL Logging: Both the old row’s xmax change and the new tuple are logged in WAL.
  6. Commit: Changes become visible to other transactions after commit.

Important: The old row version remains in the table until VACUUM cleans it up.

5. How DELETE Works

DELETE also uses MVCC — it doesn’t remove the row immediately.

  1. Locate the row using visibility rules.
  2. Mark as deleted: Set xmax = current transaction ID on the tuple.
  3. WAL Logging: The change is logged.
  4. Commit: The row becomes invisible to new transactions.
  5. Physical Removal: The row is not physically deleted from disk until VACUUM runs.

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: https://www.linkedin.com/in/rajasekhar-amudala/

PostgreSQL

Uncategorized, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

PostgreSQL DBA Step by Step Learning

#PostgreSQL DBA Topics
1How to Install PostgreSQL ON Linux?
2How to Install PostgreSQL on Linux 7 using source code?
3How to START/STOP PostgreSQL ON Linux?
4How to Create Database in PostgreSQL?
5PostgreSQL User Management
6PostgreSQL pg_hba.conf Guide
7PostgreSQL Change Data Directory
8Understanding WAL Files in PostgreSQL – For Oracle DBAs
9Change PostgreSQL WAL Directory Path (pg_wal)
10Enable Archive Mode in PostgreSQL 17
11How to Disable ARCHIVELOG Mode
12PostgreSQL Tablespace Management
13PostgreSQL pg_dump and pg_restore Guide
14PostgreSQL Backup and Restore Using pg_dumpall and psql
15pg_basebackup – Backup, Restore, and Recovery
16Backup & Restore PostgreSQL DB Cluster to Another Host (No Archive Mode)
17Backup & Restore PostgreSQL DB Cluster on Same Host
18Restore PostgreSQL to New Host using pg_basebackup + WAL Archives
19PostgreSQL PITR – Point in Time Recovery
20Configure Streaming Replication in PostgreSQL
21Manual Failover in PostgreSQL Streaming Replication
22Convert Asynchronous Replication to Synchronous Replication

 

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/

PSQL

PostgreSQL, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

PostgreSQL DBA Step by Step Learning

#PostgreSQL DBA Topics
1How to Install PostgreSQL ON Linux?
2How to Install PostgreSQL on Linux 7 using source code?
3How to START/STOP PostgreSQL ON Linux?
4How to Create Database in PostgreSQL?
5PostgreSQL User Management
6PostgreSQL pg_hba.conf Guide
7PostgreSQL Change Data Directory
8Understanding WAL Files in PostgreSQL – For Oracle DBAs
9Change PostgreSQL WAL Directory Path (pg_wal)
10Enable Archive Mode in PostgreSQL 17
11How to Disable ARCHIVELOG Mode
12PostgreSQL Tablespace Management
13PostgreSQL pg_dump and pg_restore Guide
14PostgreSQL Backup and Restore Using pg_dumpall and psql
15pg_basebackup – Backup, Restore, and Recovery
16Backup & Restore PostgreSQL DB Cluster to Another Host (No Archive Mode)
17Backup & Restore PostgreSQL DB Cluster on Same Host
18Restore PostgreSQL to New Host using pg_basebackup + WAL Archives
19PostgreSQL PITR – Point in Time Recovery
20Configure Streaming Replication in PostgreSQL
21Manual Failover in PostgreSQL Streaming Replication
22Convert Asynchronous Replication to Synchronous Replication

 

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/