如何在PostgreSQL中跨库获取所有表名及数据文件详细信息

一、跨库查询所有表名 #

PostgreSQL系统目录按数据库隔离，默认无法直接跨库查询，但凭借最高权限，可通过以下两种方式实现：

1. SQL层面：使用dblink扩展 #

先创建扩展（仅需执行一次）：

CREATE EXTENSION IF NOT EXISTS dblink;

通过循环遍历所有数据库，跨库连接查询表名：

WITH all_dbs AS (
    SELECT datname FROM pg_database WHERE datistemplate = false
)
SELECT 
    d.datname AS database_name,
    t.schemaname,
    t.tablename
FROM all_dbs d
CROSS JOIN LATERAL dblink(
    'dbname=' || d.datname,
    'SELECT schemaname, tablename FROM pg_tables WHERE schemaname NOT IN (''pg_catalog'', ''information_schema'')'
) AS t(schemaname text, tablename text)
ORDER BY d.datname, t.schemaname, t.tablename;

该方案无需修改内核，通过跨库连接直接拉取各库的pg_tables数据。

2. 内核开发场景：直接读取系统目录文件 #

• 先从pg_database获取所有数据库的OID与名称：

SELECT oid, datname FROM pg_database WHERE datistemplate = false;

• 每个数据库对应PG数据目录下的base/<OID>子目录，目录内的文件命名为<relfilenode>；通过内核级目录访问，可关联对应库pg_class表中的relfilenode与表名映射关系。

二、获取所有数据库文件的大小、位置及名称 #

1. SQL层面批量查询 #

结合dblink、pg_relation_filepath和pg_total_relation_size实现批量查询：

CREATE EXTENSION IF NOT EXISTS dblink;

WITH all_dbs AS (
    SELECT datname FROM pg_database WHERE datistemplate = false
)
SELECT 
    d.datname AS database_name,
    t.schemaname,
    t.tablename,
    t.file_path,
    t.total_size_bytes
FROM all_dbs d
CROSS JOIN LATERAL dblink(
    'dbname=' || d.datname,
    'SELECT 
        schemaname,
        tablename,
        pg_relation_filepath(quote_ident(schemaname) || ''.'' || quote_ident(tablename)) AS file_path,
        pg_total_relation_size(quote_ident(schemaname) || ''.'' || quote_ident(tablename)) AS total_size_bytes
     FROM pg_tables 
     WHERE schemaname NOT IN (''pg_catalog'', ''information_schema'')'
) AS t(schemaname text, tablename text, file_path text, total_size_bytes bigint)
ORDER BY d.datname, t.total_size_bytes DESC;

• pg_total_relation_size返回表+索引+TOAST表的总大小
• pg_relation_filepath返回文件相对PG数据目录的路径

2. 内核级直接访问 #

• 通过SHOW data_directory;获取PG数据目录路径
• 各数据库文件存储于base/<db_oid>下，分区表、大表会存在base/<db_oid>/<relfilenode>.<segment_number>分段文件
• 调用内核relpath()函数获取关系文件绝对路径，结合文件系统调用获取文件大小

注意：直接操作文件系统需注意并发安全，禁止在数据库运行时直接修改文件。

内容的提问来源于stack exchange，提问作者jerryZhang