How to find out which PostgreSQL table a file on disk corresponds

Create Test Table t1

postgres=# create table t1 (c1 int, c2 timestamp default clock_timestamp());
CREATE TABLE
postgres=# insert into t1 (c1) values (1);
INSERT 0 1
postgres=# select * from t1;
 c1 |            c2             
----+---------------------------
  1 | 2022-08-25 14:48:27.81647
(1 row)

Query pg_relation_filepath for Table t1

postgres=# SELECT pg_relation_filepath('t1');
 pg_relation_filepath 
----------------------
 base/13746/16415
(1 row)

Change Current Path to $PGDATA & base/13746/16415

[postgres@pvdb10a script]$ cd $PGDATA/base/13746
[postgres@pvdb10a 13746]$ ls -l 16415
-rw-------. 1 postgres postgres 8192 Aug 25 14:48 16415

remove file 16415 & query test table t1 again

[postgres@pvdb10a 13746]$ rm -f 16415
postgres=# select * from t1;
ERROR:  could not open file "base/13746/16415": No such file or directory

張貼留言

0 留言