Tag Archives: PostgreSQL Performance

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/

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/

Change PostgreSQL WAL Directory Path (pg_wal)

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

Change WAL Directory Path (pg_wal) in PostgreSQL

Table of Contents

0. Aim
1. Verify Existing pg_wal Directory
2. Create the New Directory
3. Stop PostgreSQL Service
4. Copy WAL Files to New Location
5. Backup Old WAL Directory
6. Create Symlink
7. Fix Permissions
8. Start PostgreSQL Service
9. Verify WAL Functionality
10. Remove Old WAL Directory (Optional)

0. Aim

To change the PostgreSQL 17 WAL File directory from its default location to new mount point

From : /pgData/pgsql17/data/pg_wal

TO : /pgWal/pgsql17/wal

1. Verify Existing pg_wal directory


[postgres@lxicbpgdsgv01 ~]$ psql
psql (17.6)
Type "help" for help.

postgres=# SELECT current_setting('data_directory') || '/pg_wal' AS wal_directory;
        wal_directory
-----------------------------
 /pgData/pgsql17/data/pg_wal  <----- 
(1 row)

postgres=# exit
[postgres@lxicbpgdsgv01 ~]$ ls -lrth /pgData/pgsql17/data/pg_wal
total 48M
drwx------. 2 postgres postgres   6 Sep 30 21:50 summaries
-rw-------. 1 postgres postgres 16M Oct  8 04:24 000000010000000000000008
-rw-------. 1 postgres postgres 16M Oct  8 16:08 000000010000000000000006
-rw-------. 1 postgres postgres 16M Oct  8 16:08 000000010000000000000007
drwx------. 2 postgres postgres  43 Oct  8 16:08 archive_status
[postgres@lxicbpgdsgv01 ~]$

[postgres@lxicbpgdsgv01 ~]$ du -sh /pgData/pgsql17/data/pg_wal
48M     /pgData/pgsql17/data/pg_wal
[postgres@lxicbpgdsgv01 ~]$

2. Create the New Directory on a new disk

[root@lxicbpgdsgv01 ~]# mkdir -p /pgWal/pgsql17/wal
[root@lxicbpgdsgv01 ~]# chown postgres:postgres /pgWal/pgsql17/wal
[root@lxicbpgdsgv01 ~]# chmod 700 /pgWal/pgsql17/wal
[root@lxicbpgdsgv01 ~]#

3. Stop PostgreSQL Service

[root@lxicbpgdsgv01 ~]# systemctl stop postgresql-17.service
[root@lxicbpgdsgv01 ~]#
[root@lxicbpgdsgv01 ~]# ps -ef | grep postgres
root        6887    6721  0 16:08 pts/0    00:00:00 grep --color=auto postgres
[root@lxicbpgdsgv01 ~]#

4. Copy Existing WAL Files to New Location

[postgres@lxicbpgdsgv01 ~]$ nohup rsync -avh --progress /pgData/pgsql17/data/pg_wal/ /pgWal/pgsql17/wal/ > rsync_pgwal.log 2>&1 &
[1] 6943
[postgres@lxicbpgdsgv01 ~]$
[postgres@lxicbpgdsgv01 ~]$
[1]+  Done                    nohup rsync -avh --progress /pgData/pgsql17/data/pg_wal/ /pgWal/pgsql17/wal/ > rsync_pgwal.log 2>&1
[postgres@lxicbpgdsgv01 ~]$

[postgres@lxicbpgdsgv01 ~]$ cat rsync_pgwal.log
nohup: ignoring input
sending incremental file list
./
000000010000000000000006
         16.78M 100%   81.89MB/s    0:00:00 (xfr#1, to-chk=5/7)
000000010000000000000007
         16.78M 100%   43.84MB/s    0:00:00 (xfr#2, to-chk=4/7)
000000010000000000000008
         16.78M 100%   31.07MB/s    0:00:00 (xfr#3, to-chk=3/7)
archive_status/
archive_status/000000010000000000000006.done
              0 100%    0.00kB/s    0:00:00 (xfr#4, to-chk=0/7)
summaries/

sent 50.34M bytes  received 107 bytes  33.56M bytes/sec
total size is 50.33M  speedup is 1.00
[postgres@lxicbpgdsgv01 ~]$

[postgres@lxicbpgdsgv01 ~]$ ls -lrth /pgWal/pgsql17/wal/
total 48M
drwx------. 2 postgres postgres   6 Sep 30 21:50 summaries
-rw-------. 1 postgres postgres 16M Oct  8 04:24 000000010000000000000008
-rw-------. 1 postgres postgres 16M Oct  8 16:08 000000010000000000000006
-rw-------. 1 postgres postgres 16M Oct  8 16:08 000000010000000000000007
drwx------. 2 postgres postgres  43 Oct  8 16:08 archive_status
[postgres@lxicbpgdsgv01 ~]$

[postgres@lxicbpgdsgv01 ~]$ du -sh /pgWal/pgsql17/wal/
48M     /pgWal/pgsql17/wal/
[postgres@lxicbpgdsgv01 ~]$

5. Move old directory as backup

[postgres@lxicbpgdsgv01 ~]$ mv /pgData/pgsql17/data/pg_wal /pgData/pgsql17/data/pg_wal.bak
[postgres@lxicbpgdsgv01 ~]$

[postgres@lxicbpgdsgv01 ~]$ ls -ld /pgData/pgsql17/data/pg_wal
ls: cannot access '/pgData/pgsql17/data/pg_wal': No such file or directory
[postgres@lxicbpgdsgv01 ~]$

[postgres@lxicbpgdsgv01 ~]$ ls -ld /pgData/pgsql17/data/pg_wal.bak
drwx------. 4 postgres postgres 141 Oct  8 16:08 /pgData/pgsql17/data/pg_wal.bak
[postgres@lxicbpgdsgv01 ~]$

6. Create symlink

[postgres@lxicbpgdsgv01 ~]$ ln -s /pgWal/pgsql17/wal /pgData/pgsql17/data/pg_wal
[postgres@lxicbpgdsgv01 ~]$
[postgres@lxicbpgdsgv01 ~]$ ls -ltr /pgData/pgsql17/data/pg_wal
lrwxrwxrwx. 1 postgres postgres 18 Oct  8 16:16 /pgData/pgsql17/data/pg_wal -> /pgWal/pgsql17/wal
[postgres@lxicbpgdsgv01 ~]$

7. Fix permissions (if required)

[postgres@lxicbpgdsgv01 ~]$ chown -R postgres:postgres /pgWal/pgsql17/wal

8. Start PostgreSQL Service

[root@lxicbpgdsgv01 ~]# systemctl start postgresql-17.service
[root@lxicbpgdsgv01 ~]#
[root@lxicbpgdsgv01 ~]# systemctl status postgresql-17.service
● postgresql-17.service - PostgreSQL 17 database server
     Loaded: loaded (/usr/lib/systemd/system/postgresql-17.service; enabled; preset: disabled)
     Active: active (running) since Wed 2025-10-08 16:20:46 +08; 7s ago
       Docs: https://www.postgresql.org/docs/17/static/
    Process: 7079 ExecStartPre=/usr/pgsql-17/bin/postgresql-17-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
   Main PID: 7084 (postgres)
      Tasks: 8 (limit: 15835)
     Memory: 18.1M
        CPU: 94ms
     CGroup: /system.slice/postgresql-17.service
             ├─7084 /usr/pgsql-17/bin/postgres -D /pgData/pgsql17/data/
             ├─7086 "postgres: logger "
             ├─7087 "postgres: checkpointer "
             ├─7088 "postgres: background writer "
             ├─7090 "postgres: walwriter "
             ├─7091 "postgres: autovacuum launcher "
             ├─7092 "postgres: archiver "
             └─7093 "postgres: logical replication launcher "

Oct 08 16:20:46 lxicbpgdsgv01.rajasekhar.com systemd[1]: Starting PostgreSQL 17 database server...
Oct 08 16:20:46 lxicbpgdsgv01.rajasekhar.com postgres[7084]: 2025-10-08 16:20:46.162 +08 [7084] LOG:  redirecting log output to logging collector process
Oct 08 16:20:46 lxicbpgdsgv01.rajasekhar.com postgres[7084]: 2025-10-08 16:20:46.162 +08 [7084] HINT:  Future log output will appear in directory "log".
Oct 08 16:20:46 lxicbpgdsgv01.rajasekhar.com systemd[1]: Started PostgreSQL 17 database server.
[root@lxicbpgdsgv01 ~]#

9. Verify

-- Load WAL File generation (Testing)

postgres=# -- Create test table
DROP TABLE IF EXISTS wal_test;
CREATE TABLE wal_test (
    id serial PRIMARY KEY,
    data text
);

-- Generate WAL traffic
DO $$
DECLARE
    i integer;
BEGIN
    FOR i IN 1..50 LOOP
        -- INSERT: 10,000 rows
        INSERT INTO wal_test (data)
        SELECT repeat('wal_test_data_', 50)
        FROM generate_series(1, 10000);

        -- UPDATE: 5,000 rows using CTE with LIMIT
        WITH to_update AS (
            SELECT id FROM wal_test WHERE id % 2 = 0 LIMIT 5000
        )
        UPDATE wal_test
        SET data = data || '_updated'
        WHERE id IN (SELECT id FROM to_update);

        -- DELETE: 5,000 rows using CTE with LIMIT
        WITH to_delete AS (
            SELECT id FROM wal_test WHERE id % 3 = 0 LIMIT 5000
        )
        DELETE FROM wal_test
        WHERE id IN (SELECT id FROM to_delete);

        -- Commit to flush WAL
        COMMIT;

        -- Optional pause to slow down the loop
        PERFORM pg_sleep(0.5);
    END LOOP;
END$$;
DROP TABLE
CREATE TABLE
DO
postgres=# exit
[postgres@lxicbpgdsgv01 ~]$
[postgres@lxicbpgdsgv01 ~]$ ls -lrth  /pgWal/pgsql17/wal
total 752M
drwx------. 2 postgres postgres    6 Sep 30 21:50 summaries
-rw-------. 1 postgres postgres  16M Oct  8 16:34 000000010000000000000009
-rw-------. 1 postgres postgres  16M Oct  8 16:34 00000001000000000000000A
-rw-------. 1 postgres postgres  16M Oct  8 16:34 00000001000000000000000B
-rw-------. 1 postgres postgres  16M Oct  8 16:34 00000001000000000000000C
-rw-------. 1 postgres postgres  16M Oct  8 16:34 00000001000000000000000D
-rw-------. 1 postgres postgres  16M Oct  8 16:34 00000001000000000000000E
-rw-------. 1 postgres postgres  16M Oct  8 16:34 00000001000000000000000F
-rw-------. 1 postgres postgres  16M Oct  8 16:34 000000010000000000000010
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000011
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000012
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000013
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000014
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000015
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000016
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000017
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000018
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000019
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000001A
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000001B
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000001C
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000001D
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000001E
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000001F
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000020
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000021
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000022
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000023
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000024
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000025
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000026
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000027
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000028
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000029
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000002A
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000002B
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000002C
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000002D
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000002E
-rw-------. 1 postgres postgres  16M Oct  8 16:35 00000001000000000000002F
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000030
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000031
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000032
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000033
-rw-------. 1 postgres postgres  16M Oct  8 16:35 000000010000000000000034
-rw-------. 1 postgres postgres  16M Oct  8 16:36 000000010000000000000035
drwx------. 2 postgres postgres 4.0K Oct  8 16:36 archive_status
-rw-------. 1 postgres postgres  16M Oct  8 16:36 xlogtemp.7289
[postgres@lxicbpgdsgv01 ~]$

10. Remove Old WAL Directory (Optional, later)

[postgres@lxicbpgdsgv01 ~]$ ls -ld /pgData/pgsql17/data/pg_wal.bak
drwx------. 4 postgres postgres 141 Oct  8 16:08 /pgData/pgsql17/data/pg_wal.bak
[postgres@lxicbpgdsgv01 ~]$ rm -rf /pgData/pgsql17/data/pg_wal.bak
[postgres@lxicbpgdsgv01 ~]$
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 Tablespace

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

PostgreSQL Tablespace Management

Table of Contents

___________________________________________________________________________________________________

0. Introduction
1. How big can a PostgreSQL tablespace grow?
2. Create a Tablespace
3. Create a Database with default tablespace
4. Create Tables on Data tablspace
5. Crate Index on Index tablespaces
6. Create a Dedicated Tablespace for Temp
7. Assign Temp Tablespace to a Database
8. Check Tablespace OIDs
9. Move Table Between Tablespaces
10. Verify Database Directory (Filesystem)
11. Rename tablespace
12. Drop Tablespace
13. Change ownership for Tablespace
14. Verify Symbolic Links
15. How to find tablespace size
___________________________________________________________________________________________________


0. Introduction

What is a Tablespace?

--> Tablespace is a logical structure in which objects are stored.

--> A tablespace is simply a physical storage location on disk where PostgreSQL stores database objects (tables, indexes, etc.). 

--> By default database objects are stored in the current default tablespace of the database.

List tablespaces:

\db+ or select * from pg_tablespace;

 

Types of Tablespaces: Default Tablespaces and Non-Default Tablespaces
I. Default Tablespaces

These come built-in with PostgreSQL:

pg_global – for shared system data
pg_default – for regular user data (tables, indexes, etc.)
Featurepg_defaultpg_global
PurposeStores regular user dataStores global system data
Used by default?Yes, for tables/indexesNo, only used internally
Can store user tables?YesNo
Location$PGDATA/base/$PGDATA/global/
Droppable?NoNo
II. Non-Default Tablespaces :

--> These are created by users to store data in custom locations.
--> Useful for managing storage better (e.g., putting large tables on faster disks).


1. How big can a PostgreSQL tablespace grow?

There is no fixed maximum size for a tablespace in PostgreSQL itself.
The limit depends on your operating system and filesystem where the tablespace directory is located.

For example:

On modern filesystems like ext4 or XFS, a single file can be several terabytes (TB) or even petabytes (PB).
PostgreSQL breaks large tables into multiple files (each typically up to 1 GB) internally, so extremely large tablespaces are supported.
Your disk/storage capacity is the main practical limit.


2. Create a Tablespace

[root@pg17 ~]# mkdir -p /pgData/pgsql15/edbua_tbs01
[root@pg17 ~]# mkdir -p /pgData/pgsql15/edbua_tbs02
[root@pg17 ~]# mkdir -p /pgData/pgsql15/edbua_ixtbs01
[root@pg17 ~]# mkdir -p /pgData/pgsql15/edbua_temptbs01
[root@pg17 ~]# chown postgres:postgres /pgData/pgsql15/edbua*
[root@pg17 ~]# chmod 700 /pgData/pgsql15/edbua*
[root@pg17 ~]#

[root@pg17 ~]# su - postgres
[postgres@pg17 ~]$ psql
psql (15.13)
Type "help" for help.

postgres=# CREATE TABLESPACE edbua_tbs01 LOCATION '/pgData/pgsql15/edbua_tbs01';
CREATE TABLESPACE
postgres=# CREATE TABLESPACE edbua_tbs02 LOCATION '/pgData/pgsql15/edbua_tbs02';
CREATE TABLESPACE
postgres=# CREATE TABLESPACE edbua_ixtbs01 LOCATION '/pgData/pgsql15/edbua_ixtbs01';
CREATE TABLESPACE
postgres=#


3. Create a Database with default tablespace

postgres=# CREATE DATABASE edbua TABLESPACE edbua_tbs01;
CREATE DATABASE
postgres=# 
postgres=# SELECT oid, datname, dattablespace, datistemplate, datdba FROM pg_database WHERE datname = 'edbua';
  oid  | datname | dattablespace | datistemplate | datdba
-------+---------+---------------+---------------+--------
 92666 | edbua   |         92662 | f             |     10
(1 row)

postgres=#


4. Create Tables on Data tablspace

postgres=# \c edbua
You are now connected to database "edbua" as user "postgres".
edbua=# CREATE TABLE emp01 (id int, name text) TABLESPACE edbua_tbs01;
CREATE TABLE
edbua=# CREATE TABLE emp02 (id int, name text) TABLESPACE edbua_tbs01;
CREATE TABLE
edbua=# CREATE TABLE emp03 (id int, name text) TABLESPACE edbua_tbs01;
CREATE TABLE
edbua=#


5. Create Index on Index tablespaces

edbua=# CREATE INDEX emp01_ix ON emp01(name) TABLESPACE edbua_ixtbs01;
CREATE INDEX
edbua=# CREATE INDEX emp02_ix ON emp02(name) TABLESPACE edbua_ixtbs01;
CREATE INDEX
edbua=# CREATE INDEX emp03_ix ON emp03(name) TABLESPACE edbua_ixtbs01;
CREATE INDEX
edbua=# SELECT c.oid, c.relname, c.relkind, t.spcname AS tablespace, c.relfilenode
edbua-# FROM pg_class c
edbua-# LEFT JOIN pg_tablespace t ON c.reltablespace = t.oid
edbua-# WHERE c.relname IN ('emp01','emp02','emp03','emp01_ix','emp02_ix','emp03_ix');
  oid  | relname  | relkind |  tablespace   | relfilenode
-------+----------+---------+---------------+-------------
 92685 | emp01    | r       |               |       92685
 92690 | emp02    | r       |               |       92690
 92695 | emp03    | r       |               |       92695
 92700 | emp01_ix | i       | edbua_ixtbs01 |       92700
 92701 | emp02_ix | i       | edbua_ixtbs01 |       92701
 92702 | emp03_ix | i       | edbua_ixtbs01 |       92702
(6 rows)

edbua=#

If a table or index is created in the database’s default tablespace, PostgreSQL does not explicitly record the tablespace in pg_class.reltablespace.

But when it’s created in a non-default tablespace, PostgreSQL records that tablespace OID in pg_class.reltablespace.

If you want to always see the tablespace name (even when it's the default)

edbua=# SELECT
edbua-#     c.oid,
edbua-#     c.relname,
edbua-#     c.relkind,
edbua-#     COALESCE(t1.spcname, t2.spcname) AS tablespace,
edbua-#     c.relfilenode
edbua-# FROM pg_class c
edbua-# LEFT JOIN pg_tablespace t1 ON c.reltablespace = t1.oid
edbua-# LEFT JOIN pg_database d ON d.datname = current_database()
edbua-# LEFT JOIN pg_tablespace t2 ON d.dattablespace = t2.oid
edbua-# WHERE c.relname IN ('emp01','emp02','emp03','emp01_ix','emp02_ix','emp03_ix');
  oid  | relname  | relkind |  tablespace   | relfilenode
-------+----------+---------+---------------+-------------
 92685 | emp01    | r       | edbua_tbs01   |       92685
 92690 | emp02    | r       | edbua_tbs01   |       92690
 92695 | emp03    | r       | edbua_tbs01   |       92695
 92700 | emp01_ix | i       | edbua_ixtbs01 |       92700
 92701 | emp02_ix | i       | edbua_ixtbs01 |       92701
 92702 | emp03_ix | i       | edbua_ixtbs01 |       92702
(6 rows)

edbua=#

pg_tblspc/<tbs_oid>/PG_15_<catalog_version>/<db_oid>/<relfilenode>

edbua=# SELECT relname, pg_relation_filepath(oid) AS filepath
edbua-# FROM pg_class
edbua-# WHERE relname IN ('emp01','emp02','emp03','emp01_ix','emp02_ix','emp03_ix');
 relname  |                  filepath
----------+---------------------------------------------
 emp01    | pg_tblspc/92662/PG_15_202209061/92666/92685
 emp02    | pg_tblspc/92662/PG_15_202209061/92666/92690
 emp03    | pg_tblspc/92662/PG_15_202209061/92666/92695
 emp01_ix | pg_tblspc/92664/PG_15_202209061/92666/92700
 emp02_ix | pg_tblspc/92664/PG_15_202209061/92666/92701
 emp03_ix | pg_tblspc/92664/PG_15_202209061/92666/92702
(6 rows)

edbua=#


6. Create a Dedicated Tablespace for Temp

postgres=# CREATE TABLESPACE edbua_temptbs01 LOCATION '/pgData/pgsql15/edbua_temptbs01';
CREATE TABLESPACE
postgres=#


7. Assign Temp Tablespace to a Database

postgres=# ALTER DATABASE edbua SET temp_tablespaces = edbua_temptbs01;
ALTER DATABASE
postgres=#

postgres=# \c edbua
You are now connected to database "edbua" as user "postgres".
edbua=# 

edbua=# CREATE TEMP TABLE emp_tmp (id int, name text);
CREATE TABLE
edbua=#
edbua=# SELECT c.oid, c.relname, t.spcname, pg_relation_filepath(c.oid)
edbua-# FROM pg_class c
edbua-# LEFT JOIN pg_tablespace t ON c.reltablespace = t.oid
edbua-# WHERE c.relname = 'emp_tmp';
  oid  | relname |     spcname     |              pg_relation_filepath
-------+---------+-----------------+------------------------------------------------
 92705 | emp_tmp | edbua_temptbs01 | pg_tblspc/92665/PG_15_202209061/92666/t4_92705
(1 row)

edbua=#

pg_tblspc/<tbs_oid>/PG_15_<catalog_version>/<db_oid>/<relfilenode>


8. Check Tablespace OIDs

edbua=# SELECT oid, spcname AS tablespace_name, pg_tablespace_location(oid) AS location FROM pg_tablespace ORDER BY oid;
  oid  | tablespace_name |            location
-------+-----------------+---------------------------------
  1663 | pg_default      |
  1664 | pg_global       |
 24581 | dell_ts_data    | /u01/data/dell_ts_data
 24582 | dell_ts_index   | /u01/data/dell_ts_index
 24594 | dell_tbs        | /pgData/pgsql15/data
 24597 | trd_tb01        | /pgData/pgsql15/data/trd_tb01
 24598 | trd_ix01        | /pgData/pgsql15/data/trd_ix01
 32787 | geb_tb          | /pgData/pgsql15/pgtb/geb_tb
 32788 | geb_ix          | /pgData/pgsql15/pgix/geb_ix
 92662 | edbua_tbs01     | /pgData/pgsql15/edbua_tbs01
 92663 | edbua_tbs02     | /pgData/pgsql15/edbua_tbs02
 92664 | edbua_ixtbs01   | /pgData/pgsql15/edbua_ixtbs01
 92665 | edbua_temptbs01 | /pgData/pgsql15/edbua_temptbs01
(13 rows)

edbua=#


9. Move Table Between Tablespaces

edbua=# ALTER TABLE emp01 SET TABLESPACE edbua_tbs02;
ALTER TABLE
edbua=# ALTER TABLE emp02 SET TABLESPACE edbua_tbs02;
ALTER TABLE
edbua=# ALTER TABLE emp03 SET TABLESPACE edbua_tbs02;
ALTER TABLE
edbua=#

pg_tblspc/<tbs_oid>/PG_15_<catalog_version>/<db_oid>/<relfilenode>

edbua=# select pg_relation_filepath('emp01');
            pg_relation_filepath
---------------------------------------------
 pg_tblspc/92663/PG_15_202209061/92666/92710
(1 row)

edbua=# 

edbua=# SELECT relname, pg_relation_filepath(oid) AS filepath
FROM pg_class
WHERE relname IN ('emp01','emp02','emp03','emp01_ix','emp02_ix','emp03_ix');
 relname  |                  filepath
----------+---------------------------------------------
 emp01    | pg_tblspc/92663/PG_15_202209061/92666/92710
 emp02    | pg_tblspc/92663/PG_15_202209061/92666/92713
 emp03    | pg_tblspc/92663/PG_15_202209061/92666/92716
 emp01_ix | pg_tblspc/92664/PG_15_202209061/92666/92700
 emp02_ix | pg_tblspc/92664/PG_15_202209061/92666/92701
 emp03_ix | pg_tblspc/92664/PG_15_202209061/92666/92702
(6 rows)

edbua=#

Important: Indexes do not move automatically when you move a table.
They remain in their original tablespace (edbua_ixtbs01 in this case).
If you want indexes in edbua_tbs02, you must explicitly run:

edbua=# ALTER INDEX emp01_ix SET TABLESPACE edbua_tbs02;
ALTER INDEX
edbua=# ALTER INDEX emp02_ix SET TABLESPACE edbua_tbs02;
ALTER INDEX
edbua=# ALTER INDEX emp03_ix SET TABLESPACE edbua_tbs02;
ALTER INDEX
edbua=#
edbua=# SELECT c.relname, t.spcname AS tablespace, pg_relation_filepath(c.oid)
edbua-# FROM pg_class c
edbua-# LEFT JOIN pg_tablespace t ON c.reltablespace = t.oid
edbua-# WHERE c.relname IN ('emp01','emp02','emp03','emp01_ix','emp02_ix','emp03_ix');
 relname  | tablespace  |            pg_relation_filepath
----------+-------------+---------------------------------------------
 emp01_ix | edbua_tbs02 | pg_tblspc/92663/PG_15_202209061/92666/92719
 emp02_ix | edbua_tbs02 | pg_tblspc/92663/PG_15_202209061/92666/92720
 emp03_ix | edbua_tbs02 | pg_tblspc/92663/PG_15_202209061/92666/92721
 emp01    | edbua_tbs02 | pg_tblspc/92663/PG_15_202209061/92666/92710
 emp02    | edbua_tbs02 | pg_tblspc/92663/PG_15_202209061/92666/92713
 emp03    | edbua_tbs02 | pg_tblspc/92663/PG_15_202209061/92666/92716
(6 rows)

edbua=#

edbua=# SELECT
edbua-#     c.relname,
edbua-#     COALESCE(t1.spcname, t2.spcname) AS tablespace,
edbua-#     pg_catalog.pg_tablespace_location(COALESCE(c.reltablespace, d.dattablespace)) AS tablespace_path,
edbua-#     current_setting('data_directory') AS data_directory,
edbua-#     CASE
edbua-#         WHEN c.reltablespace = 0 THEN
edbua-#             current_setting('data_directory') || '/base/' || d.oid || '/' || c.relfilenode
edbua-#         ELSE
edbua-#             pg_catalog.pg_tablespace_location(c.reltablespace) || '/PG_' ||
edbua-#             split_part(split_part(version(), ' ', 2), '.', 1) || '_' ||
edbua-#             '202209061/' || d.oid || '/' || c.relfilenode
edbua-#     END AS full_path_guess
edbua-# FROM pg_class c
edbua-# LEFT JOIN pg_tablespace t1 ON c.reltablespace = t1.oid
edbua-# LEFT JOIN pg_database d ON d.datname = current_database()
edbua-# LEFT JOIN pg_tablespace t2 ON d.dattablespace = t2.oid
edbua-# WHERE c.relname IN ('emp01','emp02','emp03','emp01_ix','emp02_ix','emp03_ix');
 relname  | tablespace  |       tablespace_path       |    data_directory    |                     full_path_guess
----------+-------------+-----------------------------+----------------------+---------------------------------------------------------
 emp01_ix | edbua_tbs02 | /pgData/pgsql15/edbua_tbs02 | /pgData/pgsql15/data | /pgData/pgsql15/edbua_tbs02/PG_15_202209061/92666/92719
 emp02_ix | edbua_tbs02 | /pgData/pgsql15/edbua_tbs02 | /pgData/pgsql15/data | /pgData/pgsql15/edbua_tbs02/PG_15_202209061/92666/92720
 emp03_ix | edbua_tbs02 | /pgData/pgsql15/edbua_tbs02 | /pgData/pgsql15/data | /pgData/pgsql15/edbua_tbs02/PG_15_202209061/92666/92721
 emp01    | edbua_tbs02 | /pgData/pgsql15/edbua_tbs02 | /pgData/pgsql15/data | /pgData/pgsql15/edbua_tbs02/PG_15_202209061/92666/92710
 emp02    | edbua_tbs02 | /pgData/pgsql15/edbua_tbs02 | /pgData/pgsql15/data | /pgData/pgsql15/edbua_tbs02/PG_15_202209061/92666/92713
 emp03    | edbua_tbs02 | /pgData/pgsql15/edbua_tbs02 | /pgData/pgsql15/data | /pgData/pgsql15/edbua_tbs02/PG_15_202209061/92666/92716
(6 rows)

edbua=#


10. Verify Database Directory (Filesystem)

edbua=# SELECT oid, datname FROM pg_database WHERE datname = current_database();
  oid  | datname
-------+---------
 92666 | edbua
(1 row)

edbua=#

[root@pg17 ~]# ls -ltr /pgData/pgsql15/edbua_tbs02/PG_15_202209061/92666/*
-rw-------. 1 postgres postgres    0 Sep 23 00:14 /pgData/pgsql15/edbua_tbs02/PG_15_202209061/92666/92710
-rw-------. 1 postgres postgres    0 Sep 23 00:14 /pgData/pgsql15/edbua_tbs02/PG_15_202209061/92666/92711
-rw-------. 1 postgres postgres 8192 Sep 23 00:14 /pgData/pgsql15/edbua_tbs02/PG_15_202209061/92666/92712
-rw-------. 1 postgres postgres    0 Sep 23 00:14 /pgData/pgsql15/edbua_tbs02/PG_15_202209061/92666/92713
-rw-------. 1 postgres postgres    0 Sep 23 00:14 /pgData/pgsql15/edbua_tbs02/PG_15_202209061/92666/92714
-rw-------. 1 postgres postgres 8192 Sep 23 00:14 /pgData/pgsql15/edbua_tbs02/PG_15_202209061/92666/92715
-rw-------. 1 postgres postgres    0 Sep 23 00:14 /pgData/pgsql15/edbua_tbs02/PG_15_202209061/92666/92716
-rw-------. 1 postgres postgres    0 Sep 23 00:14 /pgData/pgsql15/edbua_tbs02/PG_15_202209061/92666/92717
-rw-------. 1 postgres postgres 8192 Sep 23 00:14 /pgData/pgsql15/edbua_tbs02/PG_15_202209061/92666/92718
-rw-------. 1 postgres postgres 8192 Sep 23 00:52 /pgData/pgsql15/edbua_tbs02/PG_15_202209061/92666/92719
-rw-------. 1 postgres postgres 8192 Sep 23 00:52 /pgData/pgsql15/edbua_tbs02/PG_15_202209061/92666/92720
-rw-------. 1 postgres postgres 8192 Sep 23 00:52 /pgData/pgsql15/edbua_tbs02/PG_15_202209061/92666/92721
[root@pg17 ~]#


Issue: Why are there 12 files when I have only 6 objects?

created only: 3 tables: emp01, emp02, emp03 & 3 indexes: emp01_ix, emp02_ix, emp03_ix

But in the tablespace directory, we're seeing 12 files: 3 tables + 3 indexes + 3 TOAST tables + 3 TOAST indexes

edbua=# SELECT
edbua-#     c.relname,
edbua-#     c.relfilenode,
edbua-#     c.relkind,
edbua-#     n.nspname AS schema,
edbua-#     t.spcname AS tablespace
edbua-# FROM
edbua-#     pg_class c
edbua-# JOIN
edbua-#     pg_namespace n ON n.oid = c.relnamespace
edbua-# LEFT JOIN
edbua-#     pg_tablespace t ON c.reltablespace = t.oid
edbua-# WHERE
edbua-#     c.reltablespace = (SELECT oid FROM pg_tablespace WHERE spcname = 'edbua_tbs02')
edbua-# ORDER BY
edbua-#     c.relfilenode;
       relname        | relfilenode | relkind |  schema  | tablespace
----------------------+-------------+---------+----------+-------------
 emp01                |       92710 | r       | public   | edbua_tbs02
 pg_toast_92685       |       92711 | t       | pg_toast | edbua_tbs02
 pg_toast_92685_index |       92712 | i       | pg_toast | edbua_tbs02
 emp02                |       92713 | r       | public   | edbua_tbs02
 pg_toast_92690       |       92714 | t       | pg_toast | edbua_tbs02
 pg_toast_92690_index |       92715 | i       | pg_toast | edbua_tbs02
 emp03                |       92716 | r       | public   | edbua_tbs02
 pg_toast_92695       |       92717 | t       | pg_toast | edbua_tbs02
 pg_toast_92695_index |       92718 | i       | pg_toast | edbua_tbs02
 emp01_ix             |       92719 | i       | public   | edbua_tbs02
 emp02_ix             |       92720 | i       | public   | edbua_tbs02
 emp03_ix             |       92721 | i       | public   | edbua_tbs02
(12 rows)

edbua=#


11. Rename tablespace

edbua=# ALTER TABLESPACE edbua_tbs02 RENAME TO edbua_tbs03;
ALTER TABLESPACE
edbua=#

edbua=# \db+ edbua*
                                                List of tablespaces
      Name       |  Owner   |            Location             | Access privileges | Options |  Size   | Description
-----------------+----------+---------------------------------+-------------------+---------+---------+-------------
 edbua_ixtbs01   | postgres | /pgData/pgsql15/edbua_ixtbs01   |                   |         | 6 bytes |
 edbua_tbs01     | postgres | /pgData/pgsql15/edbua_tbs01     |                   |         | 7789 kB |
 edbua_tbs03     | postgres | /pgData/pgsql15/edbua_tbs02     |                   |         | 48 kB   |
 edbua_temptbs01 | postgres | /pgData/pgsql15/edbua_temptbs01 |                   |         | 6 bytes |
(4 rows)

edbua=#


12. Drop Tablespace

edbua=# drop tablespace edbua_tbs03;
ERROR:  tablespace "edbua_tbs03" is not empty
edbua=#

edbua=# SELECT
edbua-#   CASE
edbua-#     WHEN c.relkind = 'r' THEN
edbua-#       format('ALTER TABLE %I.%I SET TABLESPACE edbua_tbs01;', n.nspname, c.relname)
edbua-#     WHEN c.relkind = 'i' THEN
edbua-#       format('ALTER INDEX %I.%I SET TABLESPACE edbua_tbs01;', n.nspname, c.relname)
edbua-#     WHEN c.relkind = 'S' THEN
edbua-#       format('ALTER SEQUENCE %I.%I SET TABLESPACE edbua_tbs01;', n.nspname, c.relname)
edbua-#   END AS alter_statement
edbua-# FROM pg_class c
edbua-# JOIN pg_namespace n ON n.oid = c.relnamespace
edbua-# WHERE c.reltablespace = (SELECT oid FROM pg_tablespace WHERE spcname = 'edbua_tbs03')
edbua-#   AND c.relkind IN ('r', 'i', 'S')
edbua-#   AND n.nspname NOT IN ('pg_catalog', 'pg_toast', 'information_schema')
edbua-# ORDER BY n.nspname, c.relname;
                     alter_statement
---------------------------------------------------------
 ALTER TABLE public.emp01 SET TABLESPACE edbua_tbs01;
 ALTER INDEX public.emp01_ix SET TABLESPACE edbua_tbs01;
 ALTER TABLE public.emp02 SET TABLESPACE edbua_tbs01;
 ALTER INDEX public.emp02_ix SET TABLESPACE edbua_tbs01;
 ALTER TABLE public.emp03 SET TABLESPACE edbua_tbs01;
 ALTER INDEX public.emp03_ix SET TABLESPACE edbua_tbs01;
(6 rows)

edbua=#

edbua=# ALTER TABLE public.emp01 SET TABLESPACE edbua_tbs01;
 ALTER INDEX public.emp02_ix SET TABLESPACE edbua_tbs01;
 ALTER TABLE public.emp03 SET TABLESPACE edbua_tbs01;
 ALTER INDEX public.emp03_ix SET TABLESPACE edbua_tbs01;ALTER TABLE
edbua=#  ALTER INDEX public.emp01_ix SET TABLESPACE edbua_tbs01;
ALTER INDEX
edbua=#  ALTER TABLE public.emp02 SET TABLESPACE edbua_tbs01;
ALTER TABLE
edbua=#  ALTER INDEX public.emp02_ix SET TABLESPACE edbua_tbs01;
ALTER INDEX
edbua=#  ALTER TABLE public.emp03 SET TABLESPACE edbua_tbs01;
ALTER TABLE
edbua=#  ALTER INDEX public.emp03_ix SET TABLESPACE edbua_tbs01;
ALTER INDEX
edbua=#

edbua=# drop tablespace edbua_tbs03;
DROP TABLESPACE
edbua=# \db+ edbua*
                                                List of tablespaces
      Name       |  Owner   |            Location             | Access privileges | Options |  Size   | Description
-----------------+----------+---------------------------------+-------------------+---------+---------+-------------
 edbua_ixtbs01   | postgres | /pgData/pgsql15/edbua_ixtbs01   |                   |         | 6 bytes |
 edbua_tbs01     | postgres | /pgData/pgsql15/edbua_tbs01     |                   |         | 7837 kB |
 edbua_temptbs01 | postgres | /pgData/pgsql15/edbua_temptbs01 |                   |         | 6 bytes |
(3 rows)

edbua=#


13. Change ownership for Tablespace

edbua=# ALTER TABLESPACE edbua_tbs01 OWNER TO teja;
ALTER TABLESPACE
edbua=# \db+ edbua*
                                                List of tablespaces
      Name       |  Owner   |            Location             | Access privileges | Options |  Size   | Description
-----------------+----------+---------------------------------+-------------------+---------+---------+-------------
 edbua_ixtbs01   | postgres | /pgData/pgsql15/edbua_ixtbs01   |                   |         | 6 bytes |
 edbua_tbs01     | teja     | /pgData/pgsql15/edbua_tbs01     |                   |         | 7837 kB |
 edbua_temptbs01 | postgres | /pgData/pgsql15/edbua_temptbs01 |                   |         | 6 bytes |
(3 rows)

edbua=# ALTER TABLESPACE edbua_tbs01 OWNER TO postgres;
ALTER TABLESPACE
edbua=# \db+ edbua*
                                                List of tablespaces
      Name       |  Owner   |            Location             | Access privileges | Options |  Size   | Description
-----------------+----------+---------------------------------+-------------------+---------+---------+-------------
 edbua_ixtbs01   | postgres | /pgData/pgsql15/edbua_ixtbs01   |                   |         | 6 bytes |
 edbua_tbs01     | postgres | /pgData/pgsql15/edbua_tbs01     |                   |         | 7837 kB |
 edbua_temptbs01 | postgres | /pgData/pgsql15/edbua_temptbs01 |                   |         | 6 bytes |
(3 rows)

edbua=#


14. Verify Symbolic Links

edbua=# show data_directory;
    data_directory
----------------------
 /pgData/pgsql15/data  <----
(1 row)

edbua=#

[root@pg17 pg_tblspc]# ls -ltr /pgData/pgsql15/data/pg_tblspc/*
lrwxrwxrwx. 1 postgres postgres 22 Aug 18 06:36 /pgData/pgsql15/data/pg_tblspc/24581 -> /u01/data/dell_ts_data
lrwxrwxrwx. 1 postgres postgres 23 Aug 18 06:36 /pgData/pgsql15/data/pg_tblspc/24582 -> /u01/data/dell_ts_index
lrwxrwxrwx. 1 postgres postgres 20 Sep  3 09:52 /pgData/pgsql15/data/pg_tblspc/24594 -> /pgData/pgsql15/data
lrwxrwxrwx. 1 postgres postgres 29 Sep  3 11:55 /pgData/pgsql15/data/pg_tblspc/24597 -> /pgData/pgsql15/data/trd_tb01
lrwxrwxrwx. 1 postgres postgres 29 Sep  3 11:55 /pgData/pgsql15/data/pg_tblspc/24598 -> /pgData/pgsql15/data/trd_ix01
lrwxrwxrwx. 1 postgres postgres 27 Sep  4 05:59 /pgData/pgsql15/data/pg_tblspc/32787 -> /pgData/pgsql15/pgtb/geb_tb
lrwxrwxrwx. 1 postgres postgres 27 Sep  4 06:03 /pgData/pgsql15/data/pg_tblspc/32788 -> /pgData/pgsql15/pgix/geb_ix
lrwxrwxrwx. 1 postgres postgres 27 Sep 20 07:20 /pgData/pgsql15/data/pg_tblspc/92662 -> /pgData/pgsql15/edbua_tbs01
lrwxrwxrwx. 1 postgres postgres 29 Sep 20 07:21 /pgData/pgsql15/data/pg_tblspc/92664 -> /pgData/pgsql15/edbua_ixtbs01
lrwxrwxrwx. 1 postgres postgres 31 Sep 20 07:21 /pgData/pgsql15/data/pg_tblspc/92665 -> /pgData/pgsql15/edbua_temptbs01
[root@pg17 pg_tblspc]#


15. How to find tablespace size

edbua=# SELECT spcname, pg_size_pretty(pg_tablespace_size(spcname)) AS size FROM pg_tablespace;
     spcname     |  size
-----------------+---------
 pg_default      | 1660 MB
 pg_global       | 571 kB
 dell_ts_data    | 7761 kB
 dell_ts_index   | 16 kB
 dell_tbs        | 0 bytes
 trd_tb01        | 1615 MB
 trd_ix01        | 2648 kB
 geb_tb          | 13 GB
 geb_ix          | 8950 MB
 edbua_ixtbs01   | 6 bytes
 edbua_temptbs01 | 6 bytes
 edbua_tbs01     | 7837 kB
(12 rows)

edbua=#
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

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/