2ndQuadrant is now part of EDB

Bringing together some of the world's top PostgreSQL experts.

2ndQuadrant | PostgreSQL
Mission Critical Databases
  • Contact us
  • Support & Services
    • Support
      • 24/7 PostgreSQL Support
      • Developer Support
    • DBA Services
      • Remote DBA
      • Database Monitoring
    • Consulting Services
      • Health Check
      • Performance Tuning
      • Database Security Audit
      • PostgreSQL Upgrade
    • Migration Services
      • Migrate to PostgreSQL
      • Migration Assessment
  • Products
    • Postgres-BDR ®
    • PostgreSQL High Availability
    • Kubernetes Operators for BDR & PostgreSQL
    • Managed PostgreSQL in the Cloud
    • Installers
      • Postgres Installer
      • 2UDA
    • 2ndQPostgres
    • pglogical
    • Barman
    • repmgr
    • OmniDB
    • SQL Firewall
    • Postgres-XL
  • Downloads
    • Installers
      • Postgres Installer
      • 2UDA – Unified Data Analytics
    • Whitepapers
      • Business Case for PostgreSQL Support
      • AlwaysOn Postgres
      • PostgreSQL with High Availability
      • Security Best Practices
      • BDR
    • 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
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
  • Postgres Learning Center
    • Webinars
      • Upcoming Webinars
      • Webinar Library
    • Whitepapers
      • Business Case for PostgreSQL Support
      • AlwaysOn Postgres
      • PostgreSQL with High Availability
      • Security Best Practices
      • BDR
    • Blog
    • Training
      • Course Catalogue
    • 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
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
    • 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
    • Videos
    • Events
    • PostgreSQL
      • PostgreSQL – History
      • Who uses PostgreSQL?
      • PostgreSQL FAQ
      • PostgreSQL vs MySQL
      • The Business Case for PostgreSQL
      • Security Information
      • Documentation
  • About Us
    • About 2ndQuadrant
    • What Does “2ndQuadrant” Mean?
    • 2ndQuadrant’s Passion for PostgreSQL
    • News
    • Careers
    • Team Profile
  • Blog
  • Menu Menu
You are here: Home1 / Blog2 / Craig's PlanetPostgreSQL3 / 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

Get in touch with us!

Recent Posts

  • Random Data December 3, 2020
  • Webinar: COMMIT Without Fear – The Beauty of CAMO [Follow Up] November 13, 2020
  • Full-text search since PostgreSQL 8.3 November 5, 2020
  • Random numbers November 3, 2020
  • Webinar: Best Practices for Bulk Data Loading in PostgreSQL [Follow Up] November 2, 2020

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 2UDA 9.6 backup Barman BDR Business Continuity community conference database DBA development devops disaster recovery greenplum Hot Standby JSON JSONB logical replication monitoring OmniDB open source Orange performance PG12 pgbarman pglogical PG Phriday postgres Postgres-BDR postgres-xl PostgreSQL PostgreSQL 9.6 PostgreSQL10 PostgreSQL11 PostgreSQL 11 PostgreSQL 11 New Features postgresql repmgr Recovery replication security sql wal webinar webinars

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

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