postgresql postgres db 文件 - 哪个文件代表特定的表/索引？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13703886/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
postgres db files - which file represents the specific table/index?
提问by ulkas
when i go into sql-8.2/base/to check how much space does my table take, there are plenty of files named by a number. how can i find the specific file which stores my specific table and index for that table?
当我sql-8.2/base/检查我的表占用了多少空间时，有很多文件以数字命名。如何找到存储我的特定表和该表索引的特定文件？
for example, i ordered the files by date (newest first) but there are several at that particular period:
例如，我按日期（最新的在前）订购了文件，但在那个特定时期有几个：
-rw------- 1 postgres sql 1.0G Dec 4 13:41 15426233
-rw------- 1 postgres sql 149M Dec 4 13:41 15426233.4
-rw------- 1 postgres sql 1.0G Dec 4 13:41 15426233.3
drwx------ 3 postgres sql 75K Dec 4 13:40 .
-rw------- 1 postgres sql 1.0G Dec 4 13:34 15426233.2
-rw------- 1 postgres sql 1.0G Dec 4 13:28 15426233.1
-rw------- 1 postgres sql 3.6M Dec 4 11:23 1249
-rw------- 1 postgres sql 584K Dec 4 11:23 2659
-rw------- 1 postgres sql 672K Dec 4 11:23 2663
-rw------- 1 postgres sql 136K Dec 4 11:23 2662
-rw------- 1 postgres sql 848K Dec 4 11:23 2608
-rw------- 1 postgres sql 2.6M Dec 4 11:23 2658
-rw------- 1 postgres sql 600K Dec 4 11:23 2674
-rw------- 1 postgres sql 56K Dec 4 11:23 2679
-rw------- 1 postgres sql 632K Dec 4 11:23 2673
-rw------- 1 postgres sql 72K Dec 4 11:23 2678
-rw------- 1 postgres sql 1.8M Dec 4 11:22 2619
-rw------- 1 postgres sql 112K Dec 4 11:21 2696
-rw------- 1 postgres sql 1007M Dec 4 11:21 15426228.5
-rw------- 1 postgres sql 1.0G Dec 4 11:19 15426228.4
-rw------- 1 postgres sql 1.0G Dec 4 11:19 15426228.3
-rw------- 1 postgres sql 1.0G Dec 4 11:18 15426228.2
-rw------- 1 postgres sql 1.0G Dec 4 11:17 15426228.1
-rw------- 1 postgres sql 1.0G Dec 4 11:16 15426228
回答by a_horse_with_no_name
Each directory represents a database (created via create database). The number is the oid of the database. To see the oid and its name, run the following statement:
每个目录代表一个数据库（通过创建create database）。该数字是数据库的 oid。要查看 oid 及其名称，请运行以下语句：
select oid, datname
from pg_database;
Inside each directory each file corresponds to the an entry in pg_classwhere the oid matches the number of the file in the directory:
在每个目录中，每个文件都对应一个条目，pg_class其中 oid 与目录中的文件编号匹配：
You can see the oids and to which relation they relate by running the statement:
您可以通过运行以下语句来查看 oids 以及它们与哪些关系相关：
select cl.relfilenode, nsp.nspname as schema_name, cl.relname, cl.relkind
from pg_class cl
join pg_namespace nsp on cl.relnamespace = nsp.oid;
You might also want to check out the manual
您可能还想查看手册
Btw: if you are really still running 8.2 you should upgrade as soon as possible.
顺便说一句：如果你真的还在运行 8.2 你应该尽快升级。
回答by Kuberchaun
select pg_relation_filepath('OID or name of a table, index, sequence, or toast table');
For example:
例如：
select pg_relation_filepath('flush_history');
Returns base/83780/153211 which you will find in your data directory.
http://www.postgresql.org/docs/current/static/functions-admin.html
http://www.postgresql.org/docs/current/static/functions-admin.html
