Wednesday, June 3, 2015

Postgresql: What does those numbers in the tablespace means

Trying to find out what those numbers represent. I know there are tablespace but pg_tablespace does not yield much information.

localhost:/storage/seat/vpostgres/eventtblsp/PG_9.3_201306121/16384 # ll |sort -rn|head -5
total 12704
-rw------- 1 vpostgres users 6193152 Jun  3 16:13 35489
-rw------- 1 vpostgres users 1040384 Jun  3 16:13 41471
-rw------- 1 vpostgres users  983040 Jun  3 16:13 41468
-rw------- 1 vpostgres users  851968 Jun  3 16:13 35499



VCDB=# SELECT pg_catalog.pg_relation_filenode(c.oid) as "Object ID", relname as "Object Name",
VCDB-# relkind='r', o.rolname as "Owner"
VCDB-# FROM pg_catalog.pg_class c
VCDB-#          JOIN pg_catalog.pg_authid o ON o.oid=c.relowner
VCDB-#          JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
VCDB-#          JOIN pg_catalog.pg_database d ON d.datname = pg_catalog.current_database()  WHERE  relfilenode=35489;


 Object ID |  Object Name  | ?column? | Owner
-----------+---------------+----------+-------
     35489 | vpx_event_arg | t        | vc
(1 row)


Not sure why postgresql is such a hassle that most of the critical information represented by numbers and not easily tracked down.