pg_tablespace_location(oid) function is used to check the path of PostgreSQL tablespace location on disk. Tablespace in postgres is the physical location on disk where all data files related to database objects like table and indexes are stored.
Query to check Tablespace Location in PostgreSQL
postgres=# select spcname ,pg_tablespace_location(oid) from pg_tablespace; Example: devdb=# select spcname ,pg_tablespace_location(oid) from pg_tablespace; spcname | pg_tablespace_location -------------+---------------------------------- pg_default | pg_global | devdefault | /pgdata/devdata_tbs/devdefault devdata | /pgdata/devdata_tbs/devdata temp | /pgdata/devdata_tbs/temporary
pg_default and pg_global are the tablespaces which is created automatically during PostgreSQL installation. It’s location is hardcoded that’s why path for these tablespaces are not visible. It reside under PGDATA/base and global directory. Other tablespaces are created manually whose location are visible using above query.
Please note that tablespaces are global objects and can be used to store any database objects in the cluster. So while dropping tablespace be cautious because it may contain other database objects apart from currently connected database.
The post Check Tablespace Location in PostgreSQL appeared first on orahow.
