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
___________________________________________________________________________________________________
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.)
| Feature | pg_default | pg_global |
|---|---|---|
| Purpose | Stores regular user data | Stores global system data |
| Used by default? | Yes, for tables/indexes | No, 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.
[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>
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=#
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=#
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=#
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/