HGDB view physical files corresponding to database and tablespace

1. Create a table space:

su - highgo
mkdir -p /tmp/data/user_tCREATE TABLESPACE tbls_t OWNER user_t LOCATION '/tmp/data/user_t';

Before you do this, make sure that the directory / TMP / data / user? Has access and that the directory is empty.
2. Create a database, indicating that the tablespace of the database is the newly created user

CREATE DATABASE db_t  TEMPLATE template1 OWNERE user_t TABLESPACE tbls_t; 
//or CREATE DATABASE db_t  TABLESPACE tbls_t;

Connect to database

\c db_t user_t

db_t=# select pg_relation_filepath('pg_class'::regclass);            
pg_relation_filepath             
--------------------------------------------- 
pg_tblspc/57351/PG_9.5_201510051/57352/1259
(1 row)

Note: PG? Relation? Filepath
Database Object Location Functions
it returns the entire file path name (relative to the database cluster's data directory PGDATA) of the relation.

Decomposition:
1) pg_tblspc/57351/PG_9.5_201510051/57352/1259
Represents the relative path in $PGDATA

2) 57351 - corresponding tablespace oid

db_t=# select spcname from pg_tablespace where oid=57351;
1
 spcname 
--------- tbls_t
(1 row)

File points for tablespaces

[highgo@hgdb01 pg_tblspc]$ cd /data/highgo/4.1.1/data/pg_tblspc
[highgo@hgdb01 pg_tblspc]$ ls -l
total 0
lrwxrwxrwx. 1 highgo highgo 16 Dec  8 17:33 57351 -> /tmp/data/user_t

3) 57352 - oid of corresponding database

db_t=# select oid,datname from pg_database where datname=current_database();                                     
  oid  | datname -------+---------
 57352 | db_t
(1 row)  

4) 1259 -- the main fork file name of the corresponding tablespace
Add the suffix fsm, vm, init, etc. after other fork. For more than 1GB (specified at system compile time), add. x after the filename

[highgo@hgdb01 57352]$ ll /data/highgo/4.1.1/data/pg_tblspc/57351/PG_9.5_201510051/57352/1259*
-rw-------. 1 highgo highgo 106496 Dec  8 17:34 /data/highgo/4.1.1/data/pg_tblspc/57351/PG_9.5_201510051/57352/1259
-rw-------. 1 highgo highgo  24576 Dec  8 17:34 /data/highgo/4.1.1/data/pg_tblspc/57351/PG_9.5_201510051/57352/1259_fsm
-rw-------. 1 highgo highgo   8192 Dec  8 17:34 /data/highgo/4.1.1/data/pg_tblspc/57351/PG_9.5_201510051/57352/1259_vm

by polo

Tags: Database

Posted on Fri, 01 May 2020 13:05:58 -0700 by techker