postgres db文件-哪个文件代表特定的表/索引？ [英] postgres db files - which file represents the specific table/index?

问题描述

当我进入 sql-8.2 / base / 来检查我的表占用多少空间时，有很多文件用数字命名。我怎么能找到存储我的特定表和该表索引的特定文件？

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?

例如，我按日期排序（最新的），但是有几个在那个特定时期：

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

推荐答案

每个目录代表数据库（通过创建数据库创建）。该数字是数据库的oid。要查看oid及其名称，请运行以下语句：

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:

select oid, datname
from pg_database;

在每个目录中，每个文件都对应 pg_class ，其中oid与目录中文件的编号匹配：

Inside each directory each file corresponds to the an entry in pg_class where the oid matches the number of the file in the directory:

通过运行以下语句，您可以看到oid及其与之相关的关系：

You can see the oids and to which relation they relate by running the statement:

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;

您可能还想查看手册

• 确定磁盘使用情况


• 数据库文件布局


• 系统目录

• Determining Disk Usage
• Database File Layout
• System catalogs

Btw：如果您确实仍在运行8.2，则应尽快升级。

Btw: if you are really still running 8.2 you should upgrade as soon as possible.

这篇关于postgres db文件-哪个文件代表特定的表/索引？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！