Tag Archives: Database Management

PostgreSQL Backup and Restore Using pg_dumpall

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

PostgreSQL Backup and Restore Using pg_dumpall and psql

Table of Contents

0. Introduction

Backup:

1. Backup All Databases
2. Backup Users/Roles Definition
3. Backup Tablespaces Definition
4. Backup Schema Only (No Data)
5. Backup Data Only (No Schema)
6. Backup Data as INSERT Commands
7. Backup Global Objects Only (No Databases)

Restore:

8. Restore on Same Host

9. Restore to Another Host (Different Tablespace Paths)

Click to expand pg_dumpall –help
[postgres@lxtrdpgdsgv01 ~]$ pg_dumpall --help
pg_dumpall extracts a PostgreSQL database cluster into an SQL script file.

Usage:
  pg_dumpall [OPTION]...

General options:
  -f, --file=FILENAME          output file name
  -v, --verbose                verbose mode
  -V, --version                output version information, then exit
  --lock-wait-timeout=TIMEOUT  fail after waiting TIMEOUT for a table lock
  -?, --help                   show this help, then exit

Options controlling the output content:
  -a, --data-only              dump only the data, not the schema
  -c, --clean                  clean (drop) databases before recreating
  -E, --encoding=ENCODING      dump the data in encoding ENCODING
  -g, --globals-only           dump only global objects, no databases
  -O, --no-owner               skip restoration of object ownership
  -r, --roles-only             dump only roles, no databases or tablespaces
  -s, --schema-only            dump only the schema, no data
  -S, --superuser=NAME         superuser user name to use in the dump
  -t, --tablespaces-only       dump only tablespaces, no databases or roles
  -x, --no-privileges          do not dump privileges (grant/revoke)
  --binary-upgrade             for use by upgrade utilities only
  --column-inserts             dump data as INSERT commands with column names
  --disable-dollar-quoting     disable dollar quoting, use SQL standard quoting
  --disable-triggers           disable triggers during data-only restore
  --exclude-database=PATTERN   exclude databases whose name matches PATTERN
  --extra-float-digits=NUM     override default setting for extra_float_digits
  --if-exists                  use IF EXISTS when dropping objects
  --inserts                    dump data as INSERT commands, rather than COPY
  --load-via-partition-root    load partitions via the root table
  --no-comments                do not dump comments
  --no-publications            do not dump publications
  --no-role-passwords          do not dump passwords for roles
  --no-security-labels         do not dump security label assignments
  --no-subscriptions           do not dump subscriptions
  --no-sync                    do not wait for changes to be written safely to disk
  --no-table-access-method     do not dump table access methods
  --no-tablespaces             do not dump tablespace assignments
  --no-toast-compression       do not dump TOAST compression methods
  --no-unlogged-table-data     do not dump unlogged table data
  --on-conflict-do-nothing     add ON CONFLICT DO NOTHING to INSERT commands
  --quote-all-identifiers      quote all identifiers, even if not key words
  --restrict-key=RESTRICT_KEY  use provided string as psql \restrict key
  --rows-per-insert=NROWS      number of rows per INSERT; implies --inserts
  --use-set-session-authorization
                               use SET SESSION AUTHORIZATION commands instead of
                               ALTER OWNER commands to set ownership

Connection options:
  -d, --dbname=CONNSTR     connect using connection string
  -h, --host=HOSTNAME      database server host or socket directory
  -l, --database=DBNAME    alternative default database
  -p, --port=PORT          database server port number
  -U, --username=NAME      connect as specified database user
  -w, --no-password        never prompt for password
  -W, --password           force password prompt (should happen automatically)
  --role=ROLENAME          do SET ROLE before dump

If -f/--file is not used, then the SQL script will be written to the standard
output.

Report bugs to <pgsql-bugs@lists.postgresql.org>.
PostgreSQL home page: <https://www.postgresql.org/>
[postgres@lxtrdpgdsgv01 ~]$

 

0. Introduction

Note: pg_dumpall does not support custom format backups.

The pg_dumpall utility is used to back up an entire PostgreSQL environment, including:

* Roles and users
* Tablespaces
* All databases (schemas and data)

It is especially useful for:

* Full cluster migrations
* Disaster recovery
* Environment replication across dev, QA, and prod

When restoring to a different host, you'll need to:

* Update tablespace paths (using tools like sed)
* Pre-create required tablespace directories

This ensures compatibility and successful restoration across different environments.

Backup


1. Backup ALL databases

[postgres@lxtrdpgdsgv01 ~]$ nohup pg_dumpall -U postgres -v -f /pgBackup/pgsql15/backup/backup_all_databases_10OCT2025.sql > /pgBackup/pgsql15/backup/backup_all_databases_10OCT2025.log 2>&1 &
[1] 5610
[postgres@lxtrdpgdsgv01 ~]$ 
[postgres@lxtrdpgdsgv01 ~]$ cat /pgBackup/pgsql15/backup/backup_all_databases_10OCT2025.log | grep -i /pgBackup/pgsql15/backup/backup_all_databases_10OCT2025.sql
pg_dumpall: running ""/usr/pgsql-15/bin/pg_dump"  -v -f /pgBackup/pgsql15/backup/backup_all_databases_10OCT2025.sql  -Fa 'user=postgres dbname=template1'"
pg_dumpall: running ""/usr/pgsql-15/bin/pg_dump"  -v -f /pgBackup/pgsql15/backup/backup_all_databases_10OCT2025.sql --create -Fa 'user=postgres dbname=edpua'"
pg_dumpall: running ""/usr/pgsql-15/bin/pg_dump"  -v -f /pgBackup/pgsql15/backup/backup_all_databases_10OCT2025.sql --create -Fa 'user=postgres dbname=gebua'"
pg_dumpall: running ""/usr/pgsql-15/bin/pg_dump"  -v -f /pgBackup/pgsql15/backup/backup_all_databases_10OCT2025.sql --create -Fa 'user=postgres dbname=orcl'"
pg_dumpall: running ""/usr/pgsql-15/bin/pg_dump"  -v -f /pgBackup/pgsql15/backup/backup_all_databases_10OCT2025.sql  -Fa 'user=postgres dbname=postgres'"
[postgres@lxtrdpgdsgv01 ~]$

[postgres@lxtrdpgdsgv01 ~]$ psql
psql (15.14)
Type "help" for help.

postgres=# \c orcl
You are now connected to database "orcl" as user "postgres".
orcl=# SELECT
orcl-#     schemaname || '.' || relname AS table_name,
orcl-#     pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
orcl-#     pg_size_pretty(pg_relation_size(relid)) AS table_size,
orcl-#     pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) AS index_size
orcl-# FROM
orcl-#     pg_catalog.pg_statio_user_tables
orcl-# ORDER BY
orcl-#     pg_total_relation_size(relid) DESC;
    table_name    | total_size | table_size | index_size
------------------+------------+------------+------------
 trd.metrics_high | 799 MB     | 498 MB     | 301 MB
 trd.metrics_mid  | 638 MB     | 398 MB     | 240 MB
 trd.employees    | 493 MB     | 322 MB     | 172 MB
 trd.sales_2023   | 399 MB     | 249 MB     | 150 MB
 trd.sales_2024   | 399 MB     | 249 MB     | 150 MB
 trd.sales_2022   | 399 MB     | 249 MB     | 150 MB
 trd.sales_2021   | 398 MB     | 248 MB     | 150 MB
 trd.metrics_low  | 159 MB     | 99 MB      | 60 MB
 trd.test_data    | 71 MB      | 50 MB      | 21 MB
 trd.emp_summary  | 24 kB      | 8192 bytes | 16 kB
 trd.metrics_rest | 8192 bytes | 0 bytes    | 8192 bytes
(11 rows)

orcl=#


2. Backup users/roles definition

[postgres@lxtrdpgdsgv01 ~]$ nohup pg_dumpall -U postgres --roles-only -v -f /pgBackup/pgsql15/backup/roles.sql > /pgBackup/pgsql15/backup/roles.log 2>&1 &
[1] 5205
[postgres@lxtrdpgdsgv01 ~]$


3. Backup tablespaces definition

[postgres@lxtrdpgdsgv01 ~]$ nohup pg_dumpall -U postgres --tablespaces-only -v -f /pgBackup/pgsql15/backup/tablespaces.sql > /pgBackup/pgsql15/backup/tablespaces.log 2>&1 &
[1] 5183
[postgres@lxtrdpgdsgv01 ~]$


4. Backup dump only the schema, no data

[postgres@lxtrdpgdsgv01 ~]$ nohup pg_dumpall -U postgres --schema-only -v -f /pgBackup/pgsql15/backup/schemas.sql > /pgBackup/pgsql15/backup/schemas.log 2>&1 &
[1] 4890
[postgres@lxtrdpgdsgv01 ~]$


5. Backup dump only the data, not the schema

[postgres@lxtrdpgdsgv01 ~]$ nohup pg_dumpall -U postgres --data-only -v -f /pgBackup/pgsql15/backup/dataonly.sql > /pgBackup/pgsql15/backup/dataonly.log 2>&1 &
[1] 5233
[postgres@lxtrdpgdsgv01 ~]$


6. Backup dump data as INSERT commands, rather than COPY

[postgres@lxtrdpgdsgv01 ~]$ nohup pg_dumpall -U postgres --inserts -v -f /pgBackup/pgsql15/backup/inserts.sql > /pgBackup/pgsql15/backup/inserts.log 2>&1 &
[1] 5274
[postgres@lxtrdpgdsgv01 ~]$


7. Backup dump only global objects, no databases

[postgres@lxtrdpgdsgv01 ~]$ nohup pg_dumpall -U postgres --globals-only -v -f /pgBackup/pgsql15/backup/globals.sql > /pgBackup/pgsql15/backup/globals.log 2>&1 &
[1] 5331
[postgres@lxtrdpgdsgv01 ~]$

Restore


8. Restore on same host


A. Drop Existing Databases (Optional)

 

[postgres@lxtrdpgdsgv01 ~]$ psql
psql (15.14)
Type "help" for help.

postgres=# \l
                                                 List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider |   Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
 edpua     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
 gebua     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
 orcl      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | postgres=CTc/postgres+
           |          |          |             |             |            |                 | =c/postgres
(6 rows)

postgres=# drop database edpua;
DROP DATABASE
postgres=# drop database gebua;
DROP DATABASE
postgres=# drop database orcl;
DROP DATABASE
postgres=# \l
                                                 List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider |   Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | postgres=CTc/postgres+
           |          |          |             |             |            |                 | =c/postgres
(3 rows)

postgres=#

postgres=# \db
             List of tablespaces
    Name    |  Owner   |      Location
------------+----------+---------------------
 edpua_ix   | postgres | /pgIx/pgsql15/edpua
 edpua_tb   | postgres | /pgTb/pgsql15/edpua
 gebua_ix   | postgres | /pgIx/pgsql15/gebua
 gebua_tb   | postgres | /pgTb/pgsql15/gebua
 orcl_ix    | postgres | /pgIx/pgsql15/orcl
 orcl_tb    | postgres | /pgTb/pgsql15/orcl
 pg_default | postgres |
 pg_global  | postgres |
(8 rows)

postgres=#


B. Restore Full Backup

[postgres@lxtrdpgdsgv01 ~]$ nohup psql -U postgres -X -f /pgBackup/pgsql15/backup/backup_all_databases_10OCT2025.sql -d postgres > /pgBackup/pgsql15/backup/backup_all_databases_10OCT2025_restore.log
[1] 4132
[postgres@lxtrdpgdsgv01 ~]$ 

-- OR --

nohup psql -U postgres -f /pgBackup/pgsql15/backup/backup_all_databases_10OCT2025.sql > /pgBackup/pgsql15/backup/backup_all_databases_10OCT2025_restore.log 2>&1 &

-- Log: backup_all_databases_10OCT2025_restore.log


C. Verification

[postgres@lxtrdpgdsgv01 ~]$ psql
psql (15.14)
Type "help" for help.

postgres=# \l
                                                 List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider |   Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
 edpua     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
 gebua     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
 orcl      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | postgres=CTc/postgres+
           |          |          |             |             |            |                 | =c/postgres
(6 rows)

postgres=#


9. Restore to Another Host (Different Tablespace Paths)

A. Copy Backup File

[postgres@lxtrdpgdsgv01 backup]$ scp backup_all_databases_10OCT2025.sql 192.168.2.31:/pgBackup/pgsql15/backup/
postgres@192.168.2.31's password:
backup_all_databases_10OCT2025.sql                 100% 3556MB  51.5MB/s   01:09
[postgres@lxtrdpgdsgv01 backup]$ 

[postgres@pg17 backup]$ ls -ltr backup_all_databases_10OCT2025.sql
-rw-r--r--. 1 postgres postgres 3729147646 Oct  6 06:17 backup_all_databases_10OCT2025.sql
[postgres@pg17 backup]$


B. Extract Tablespace/Database Info

[postgres@lxtrdpgdsgv01 backup]$ grep -i "CREATE TABLESPACE" /pgBackup/pgsql15/backup/backup_all_databases_10OCT2025.sql
CREATE TABLESPACE orcl_tb OWNER postgres LOCATION '/pgTb/pgsql15/orcl';
CREATE TABLESPACE orcl_ix OWNER postgres LOCATION '/pgIx/pgsql15/orcl';
CREATE TABLESPACE gebua_tb OWNER postgres LOCATION '/pgTb/pgsql15/gebua';
CREATE TABLESPACE gebua_ix OWNER postgres LOCATION '/pgIx/pgsql15/gebua';
CREATE TABLESPACE edpua_tb OWNER postgres LOCATION '/pgTb/pgsql15/edpua';
CREATE TABLESPACE edpua_ix OWNER postgres LOCATION '/pgIx/pgsql15/edpua';
[postgres@lxtrdpgdsgv01 backup]$
[postgres@lxtrdpgdsgv01 backup]$ grep -i "CREATE DATABASE" /pgBackup/pgsql15/backup/backup_all_databases_10OCT2025.sql
CREATE DATABASE edpua WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE_PROVIDER = libc LOCALE = 'en_US.UTF-8' TABLESPACE = edpua_tb;
CREATE DATABASE gebua WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE_PROVIDER = libc LOCALE = 'en_US.UTF-8' TABLESPACE = gebua_tb;
CREATE DATABASE orcl WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE_PROVIDER = libc LOCALE = 'en_US.UTF-8' TABLESPACE = orcl_tb;
[postgres@lxtrdpgdsgv01 backup]$


C. Create Tablespace Directories on Target

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

postgres=# \l
                                                 List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider |   Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | postgres=CTc/postgres+
           |          |          |             |             |            |                 | =c/postgres
(3 rows)

postgres=#
postgres=# \db
       List of tablespaces
    Name    |  Owner   | Location
------------+----------+----------
 pg_default | postgres |
 pg_global  | postgres |
(2 rows)

postgres=#


# Create Directory 

[postgres@pg17 ~]$ mkdir -p /pgData/pgsql15/orcl_tb
[postgres@pg17 ~]$ mkdir -p /pgData/pgsql15/orcl_ix
[postgres@pg17 ~]$ mkdir -p /pgData/pgsql15/gebua_tb
[postgres@pg17 ~]$ mkdir -p /pgData/pgsql15/gebua_ix
[postgres@pg17 ~]$ mkdir -p /pgData/pgsql15/edpua_tb
[postgres@pg17 ~]$ mkdir -p /pgData/pgsql15/edpua_ix


D. Create Tablespaces in PostgreSQL

postgres=# CREATE TABLESPACE orcl_tb OWNER postgres LOCATION  '/pgData/pgsql15/orcl_tb';
CREATE TABLESPACE
postgres=# CREATE TABLESPACE orcl_ix OWNER postgres LOCATION  '/pgData/pgsql15/orcl_ix';
CREATE TABLESPACE
postgres=# CREATE TABLESPACE gebua_tb OWNER postgres LOCATION '/pgData/pgsql15/gebua_tb';
CREATE TABLESPACE
postgres=# CREATE TABLESPACE gebua_ix OWNER postgres LOCATION '/pgData/pgsql15/gebua_ix';
CREATE TABLESPACE
postgres=# CREATE TABLESPACE edpua_tb OWNER postgres LOCATION '/pgData/pgsql15/edpua_tb';
CREATE TABLESPACE
postgres=# CREATE TABLESPACE edpua_ix OWNER postgres LOCATION '/pgData/pgsql15/edpua_ix';
CREATE TABLESPACE
postgres=#


-- OR --- 
 You can modify the tablespace paths directly in the backup file; however, this approach is not recommended for large backup files due to  reliability concerns.

cd /pgBackup/pgsql15/backup/
# Replace ORCL paths
sed -i 's|/pgTb/pgsql15/orcl|/pgData/pgsql15/orcl_tb|g' backup_all_databases_10OCT2025.sql
sed -i 's|/pgIx/pgsql15/orcl|/pgData/pgsql15/orcl_ix|g' backup_all_databases_10OCT2025.sql

# Replace GEBUA paths
sed -i 's|/pgTb/pgsql15/gebua|/pgData/pgsql15/gebua_tb|g' backup_all_databases_10OCT2025.sql
sed -i 's|/pgIx/pgsql15/gebua|/pgData/pgsql15/gebua_ix|g' backup_all_databases_10OCT2025.sql

# Replace EDPUA paths
sed -i 's|/pgTb/pgsql15/edpua|/pgData/pgsql15/edpua_tb|g' backup_all_databases_10OCT2025.sql
sed -i 's|/pgIx/pgsql15/edpua|/pgData/pgsql15/edpua_ix|g' backup_all_databases_10OCT2025.sql


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

postgres=# \db
               List of tablespaces
    Name    |  Owner   |         Location
------------+----------+--------------------------
 edpua_ix   | postgres | /pgData/pgsql15/edpua_ix
 edpua_tb   | postgres | /pgData/pgsql15/edpua_tb
 gebua_ix   | postgres | /pgData/pgsql15/gebua_ix
 gebua_tb   | postgres | /pgData/pgsql15/gebua_tb
 orcl_ix    | postgres | /pgData/pgsql15/orcl_ix
 orcl_tb    | postgres | /pgData/pgsql15/orcl_tb
 pg_default | postgres |
 pg_global  | postgres |
(8 rows)

postgres=#


E. Optional: Backup on Target Host

[postgres@pg17 ~]$ nohup pg_dumpall -U postgres -v -f /pgBackup/pgsql15/backup/backup_all_databases_10OCT2025_pg17.sql > /pgBackup/pgsql15/backup/backup_all_databases_10OCT2025_pg17.log 2>&1 &
[1] 4246
[postgres@pg17 ~]$


F. Restore on Target Host

[postgres@pg17 ~]$ nohup psql -U postgres -X -f /pgBackup/pgsql15/backup/backup_all_databases_10OCT2025.sql -d postgres > /pgBackup/pgsql15/backup/backup_all_databases_10OCT2025_restore_pg17.log 2>&1 &
[1] 5213
[postgres@pg17 ~]$ 

-- OR --

nohup psql -U postgres -f /pgBackup/pgsql15/backup/backup_all_databases_10OCT2025.sql > /pgBackup/pgsql15/backup/backup_all_databases_10OCT2025_restore_pg17.log 2>&1 &

-- Log: backup_all_databases_10OCT2025_restore_pg17.log


G. Final Verification

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

postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
 edpua | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc |
gebua | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc |
orcl | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres +
| | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | postgres=CTc/postgres+
| | | | | | | =c/postgres
(6 rows)

postgres=#

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

postgres=# \c orcl
You are now connected to database "orcl" as user "postgres".
orcl=# SELECT
orcl-# schemaname || '.' || relname AS table_name,
orcl-# pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
orcl-# pg_size_pretty(pg_relation_size(relid)) AS table_size,
orcl-# pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) AS index_size
orcl-# FROM
orcl-# pg_catalog.pg_statio_user_tables
orcl-# ORDER BY
orcl-# pg_total_relation_size(relid) DESC;
 table_name | total_size | table_size | index_size
------------------+------------+------------+------------
trd.metrics_high | 799 MB | 498 MB | 301 MB
trd.metrics_mid | 638 MB | 398 MB | 240 MB
trd.employees | 493 MB | 322 MB | 172 MB
trd.sales_2023 | 399 MB | 249 MB | 150 MB
trd.sales_2024 | 399 MB | 249 MB | 150 MB
trd.sales_2022 | 399 MB | 249 MB | 150 MB
trd.sales_2021 | 398 MB | 248 MB | 150 MB
trd.metrics_low | 159 MB | 99 MB | 60 MB
trd.test_data | 71 MB | 50 MB | 21 MB
trd.emp_summary | 24 kB | 8192 bytes | 16 kB
trd.metrics_rest | 8192 bytes | 0 bytes | 8192 bytes
(11 rows)

orcl=#

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 Tablespace

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

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?✅ Yes❌ No
Location$PGDATA/base/$PGDATA/global/
Droppable?❌ No❌ No
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
WhatsApp :
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/

Training

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

🚀 Kickstart Your DBA Career! 🚀

Oracle DBA Online Training

Oracle Core DBA

📅 Start Date: 30-Aug-2025

⏰ Timings: 7 PM – 8 PM IST (1 Hr)

🕒 Duration: 1 Month

💰 Fee: 12000 INR

📧 Email 💬 WhatsApp 📄 Course Content

🔥 Oracle RAC (New Batch)

📅 Start Date: 30-Aug-2025

⏰ Timings: 10 AM – 12 PM IST (2 Hrs)

🕒 Duration: 5 Weekends (Sat & Sun)

💰 Fee: 12000 INR

📧 Email 💬 WhatsApp 📄 Course Content

Please enable JavaScript in your browser to complete this form.