2ndQuadrant | PostgreSQL
PostgreSQL Solutions for the Enterprise
+39 0574 159 3000
  • Contact Us
  • EN
    • FR
    • IT
    • ES
    • DE
  • Support & Services
    • Support
      • 24/7 PostgreSQL Support
      • Developer Support
      • IBM Z Production Support
    • DBA Services
      • Remote DBA
      • Database Monitoring
    • Consulting Services
      • Health Check
      • Performance Tuning
      • Database Security Audit
      • PostgreSQL Upgrade
      • Kubernetes for Postgres and BDR
    • Migration Services
      • Migrate to PostgreSQL
      • Migration Assessment
  • Products
    • PostgreSQL with High Availability
    • BDR
    • 2ndQPostgres
    • pglogical
      • Installation instruction for pglogical
      • Documentation
    • repmgr
    • Barman
    • Postgres Cloud Manager
    • SQL Firewall
    • Postgres-XL
    • OmniDB
    • Postgres Installer
    • 2UDA
  • Downloads
    • Postgres Installer
    • 2UDA – Unified Data Analytics
  • Postgres Learning Center
    • Webinars
      • BDR Overview
    • Whitepapers
      • Highly Available Postgres Clusters
      • AlwaysOn Postgres
      • BDR
      • PostgreSQL Security Best Practices
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • Healthcare Software Solutions (HSS)
        • Navionics
    • Training
      • Training Catalog and Scheduled Courses
        • Advanced Development & Performance
        • Linux for PostgreSQL DBAs
        • BDR
        • PostgreSQL Database Administration
        • PostgreSQL Data Warehousing & Partitioning
        • PostgreSQL for Developers
        • PostgreSQL Immersion
        • PostgreSQL Immersion for Cloud Databases
        • PostgreSQL Security
        • Postgres-XL-10
        • Practical SQL
        • Replication, Backup & Disaster Recovery
        • Introduction to PostgreSQL and Kubernetes
    • Books
      • PostgreSQL 11 Administration Cookbook
      • PostgreSQL 10 Administration Cookbook
      • PostgreSQL High Availability Cookbook – 2nd Edition
      • PostgreSQL 9 Administration Cookbook – 3rd Edition
      • PostgreSQL Server Programming Cookbook – 2nd Edition
      • PostgreSQL 9 Cookbook – Chinese Edition
    • PostgreSQL
      • PostgreSQL – History
      • Who uses PostgreSQL?
      • PostgreSQL FAQ
      • PostgreSQL vs MySQL
      • Business Case for PostgreSQL
      • Security Information
    • Events
    • Blog
  • About Us
    • About 2ndQuadrant
    • What Does “2ndQuadrant” Mean?
    • 2ndQuadrant’s Passion for PostgreSQL
    • Ask Simon
    • News
    • Careers
    • Team Profile
  • Blog
  • Menu
You are here: Home / Blog / Craig's PlanetPostgreSQL / How to find out which PostgreSQL table a file on disk corresponds to
craig.ringer

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

November 6, 2015/0 Comments/in Craig's PlanetPostgreSQL /by craig.ringer

Sometimes you need to determine which table a file on disk corresponds to. You have a path full of numbers like base/16499/19401 and you want to make sense of it. You might be looking at an error message that mentions a file name, for example, like:

ERROR:  could not read block 11857 of relation base/16396/3720450: read only 0 of 8192 bytes

Viewing the path of a relation

You can see the path of a table using:

SELECT pg_relation_filepath('tablename');

but what about the reverse, getting the relation name from the path? There’s a function named pg_filenode_relation that looks handy for that … but to use it you already need to be connected to the particular database the file corresponds to, which means you need to know that.

Structure of file paths

Here’s how to find out the database and table a file path refers to on a modern PostgreSQL. (Older versions used a different format, see this blog).

There are three main patterns for paths:

* For files in the default tablespace, base/database_oid/filenode id for the relation
* For files in other tablespaces: pg_tblspc / tablespace_oid / tablespace_version_subdir / database_oid / filenode id for the relation
* For shared relations (see below): global/filenode id for the relation

Shared relations are discussed at the end. For the first two, which are the main ones you’ll encounter, the last part is the same, the database oid and the relation oid.

Notice that I say “filenode id for the relation” not “relation oid”. That’s because PostgreSQL has a relfilenode map in a file named pg_relfilenode.map for each database/tablespace. Tables’ file names aren’t necessarily the same as their oids in pg_clas and can can change when VACUUM FULL, TRUNCATE, etc are run. For example:

test=> select pg_relation_filepath('a');
 pg_relation_filepath 
----------------------
 base/16385/101565
(1 row)

test=> VACUUM FULL a;
VACUUM
test=> select pg_relation_filepath('a');
 pg_relation_filepath 
----------------------
 base/16385/101577
(1 row)

So. How do you turn that path back into the relation name?

Database oids and relation filenode ids

Say you get the error given at the start of this post. We can break it down into parts:

  • base: in the default tablespace
  • 16396: in the database with oid 16396
  • 3720450 the filenode id for the table with oid 3720450

then find out what each part is.

Finding a database by oid

First, connect to any database on that PostgreSQL instance and run:

select datname
from pg_database
where oid = 16396

(or whatever your database’s oid is). This will give you the database name.

Then connect to that database.

Reverse mapping relfilenodes on 9.4

If you’re on 9.4 or newer the next part is easy:

SELECT pg_filenode_relation(0, 3720450);

(the 0 means “default tablespace”; see below for info on tablespaces).

That function handles the relfilenode mapping etc for you. So it’ll just show you the table name. It won’t be schema_qualified if it’s on the current search_path; you can SET search_path = ''; first to force it to be qualified.

You must be connected to the correct database or you’ll an incorrect or missing result.

Reverse mapping relfilenodes on 9.3

If you’re on 9.3 or below, connect to the database the table is in and query pg_class with:

select 
    n.nspname AS tableschema, 
    c.relname AS tablename
from pg_class c
inner join pg_namespace n on (c.relnamespace = n.oid)
where c.relfilenode = 3720450;

(or whatever your table’s relfilenode id is).

This will tell you the table the error relates to.

No results?

Well. That usually works.

The relfilenode can also be zero, in which case the file is located via the pg_relfilenode.map. That’s typical for shared catalogs and some system catalogs, their indexes, TOAST tables, etc. pg_database, pg_class and pg_proc for example.

So if your query returns no results, it might be a file managed via the relfilenode map, you might be connected to the wrong DB, or you might have corruption meaning that Pg has no idea what table it is.

How do you tell? That’s a topic for a follow-up post.

What about the schema?

Did you notice that the schema (namespace) doesn’t appear in the path anywhere?

PostgreSQL’s schemas are purely a namespace within the database. They don’t have any effect on where tables are stored on the disk.

Other tablespace paths

A recent case I was looking at was the error:

ERROR: could not truncate file "pg_tblspc / 16709 / PG_9.3_201306121 / 16499/19401" to 8 blocks: Permission denied

This is in a non-default tablespace, since it begins with pg_tblspc.

The process to find the table its self is actually the same. You can ignore the pg_tblspc/nnn/PG_n.n_nnnnnn/ part and just focus on the following database_oid/relation_oid, as described in “Default tablespace paths” above. It’s worth understanding what the path means, though.

So the filename pattern breaks down to:

  • pg_tblspc: it’s in a non-default tablespace
  • 16709: it’s in the tablespace with oid 16709
  • PG_9.3_201306121: used by PostgreSQL 9.3 with catalog version 201306121.
  • 16499: in the database with oid 16499
  • 19401 the table with relfilenode id 19401

We’ve already discussed the part about the database oid and table relfilenode id. They’re the same with a tablespace, they just start off in a different location.

So what about the tablespace part?

pg_tblspc is a directory inside the PostgreSQL data directory, which contains symbolic links to all the tablespace locations (or on NTFS, junction points for them). Each symlink is named after the tablespace’s oid. This is how PostgreSQL finds tablespaces. The tablespace SQL commands manipulate these links.

The oid corresponds to the pg_tablespace entry for the tablespace, as seen from:

select spcname
from pg_tablespace
where oid = 16709;

Within the tablespace’s directory there’s another directory named for the PostgreSQL version. It’s constant for that version, and its only purpose is to let multiple PostgreSQL instances share a tablespace – say, during a pg_upgrade. Usually there’s only one entry.

Within that the structure is the same as for base/ paths – a database oid, then a relation oid.

Global (shared) tables

There’s a third category of errors, but if you see one you’re probably in trouble. PostgreSQL has shared catalogs – tables that have the same contents in every database. These live in the special tablespace pg_global with relfilenode id 16709.

Their paths begin with global instead of base and they don’t have a database oid component.

Shared catalogs are not listed not be listed by relfilenode in pg_class. So you can’t look up, e.g. pg_database from pg_class. pg_filenode_relation returns null, whether called with the default tablespace oid or with the global tablespace oid 1664.

Finding these is a topic for a followup post dealing with mapped relations.

Of course, if you’re having problems with a shared catalog you probably can’t start the database at all.

Dealing with corruption

Database corruption shouldn’t happen. It can anyway. There can be hardware faults, kernel and filesystem bugs, SSDs that lie about doing reliable disk flushes, buggy SANs, and of course PostgreSQL bugs. If you suspect database corruption, before you do anything else, read and act on the advice in the wiki page on corruption.

The guts

To see how it all works, start with the macro relpathbackend in src/include/common/relpath.h. It calls GetRelationPath in src/common/relpath.c.

The manual discusses the database’s on disk structure; see storage file layout.

Tags: debugging, internals, PostgreSQL
Share this entry
  • Share on Facebook
  • Share on Twitter
  • Share on WhatsApp
  • Share on LinkedIn
0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Search

Recent Posts

  • Barman 2.10 – Recovery of partial WAL files December 11, 2019
  • Setting SSL/TLS protocol versions with PostgreSQL 12 November 27, 2019
  • Webinar: Using SSL with PostgreSQL and pgbouncer [Follow Up] November 14, 2019
  • PostgreSQL 12: Implementing K-Nearest Neighbor Space Partitioned Generalized Search Tree Indexes November 5, 2019
  • Webinar: PostgreSQL Partitioning [Follow up] October 28, 2019

Featured External Blogs

Tomas Vondra's Blog

Our Bloggers

  • Simon Riggs
  • Alvaro Herrera
  • Andrew Dunstan
  • Craig Ringer
  • Francesco Canovai
  • Gabriele Bartolini
  • Giulio Calacoci
  • Ian Barwick
  • Marco Nenciarini
  • Mark Wong
  • Pavan Deolasee
  • Petr Jelinek
  • Shaun Thomas
  • Tomas Vondra
  • Umair Shahid

PostgreSQL Cloud

2QLovesPG 9.6 backup Barman BDR Business Continuity community conference database DBA development devops disaster recovery greenplum Hot Standby JSON JSONB kanban logical decoding logical replication monitoring open source performance PG12 pgbarman pgday pglogical PG Phriday postgres Postgres-BDR postgres-xl PostgreSQL PostgreSQL 9.6 PostgreSQL10 PostgreSQL 11 PostgreSQL11 PostgreSQL 11 New Features postgresql repmgr Recovery release replication sql standby wal webinar
UK +44 (0)870 766 7756

US +1 650 378 1218

Support & Services

24/7 Production Support

Developer Support

Remote DBA for PostgreSQL

PostgreSQL Database Monitoring

PostgreSQL Health Check

PostgreSQL Performance Tuning

Database Security Audit

Upgrade PostgreSQL

PostgreSQL Migration Assessment

Migrate from Oracle to PostgreSQL

Products

HA Postgres Clusters

Postgres-BDR

2ndQPostgres

pglogical

repmgr

Barman

Postgres Cloud Manager

SQL Firewall

Postgres-XL

OmniDB

Postgres Installer

2UDA

Postgres Learning Center

Introducing Postgres

Blog

Webinars

Books

Videos

Training

Case Studies

Events

About Us

About 2ndQuadrant

What does 2ndQuadrant Mean?

News

Careers 

Team Profile

©2001-2019 2ndQuadrant Ltd. All rights reserved | Privacy Policy
  • Twitter
  • LinkedIn
  • Facebook
  • Youtube
  • Mail
Ansible Loves PostgreSQL Benchmarking Postgres-XL
Scroll to top
×