How to determine which files on disk correspond to the PostgreSQL table

Sometimes you need to determine what file on disk corresponds to the table. You have a way full of numbers, such as base/16499/19401 and you want to understand it. You can look at the error message that mentions the file name, for example:

the

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

finding the way relations

You can see the path to the table using:

the

SELECT pg_relation_filepath('tablename');

but what about the reverse process, obtaining the name of the object out of the way to him? There is a function pg_filenode_relation, which seems appropriate for this... but to use it, you must be connected to a specific database to which it belongs, which involves the knowledge of this context.

the path Structure to the file

Here is how you can define the path to the databases and tables in modern versions of PostgreSQL. (Older versions use a different format, which you can read here).

There is 3 basic ways:

the

the
• To files in the tablespace by default, the base/database_oid/filenode id for the relation
the
• To files from other tablespaces: pg_tblspc / tablespace_oid / tablespace_version_subdir / database_oid / filenode id relationships
the
• For relationship: global/filenode id relationships

General relationships will be discussed in the end. For the first two options, which are the most common that you will often encounter, the last part of the path is identical, the base oid and oid relationship.

Notice I used the phrase "filenode id relationships", "oid relationship". This is because PostgreSQL has a map relfilenode file named pg_relfilenode.map for each database/table space. The file names of the tables does not necessarily coincide with their oid'AMI of pg_class, and they may change after the launch of VACUUM FULL, TRUNCATE and others. For example:

the

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

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

So. How to turn this way back in the name of the relationship?

OIDs of the database and the ids filenode relations

Suppose You received an error from the beginning of this article. It can be broken into several parts:

the

the
• base: table space by default
the
• 16396: database oid'om 16396
the
• 3720450 filenode id for the table with oid'om 3720450

then consider what it means to each of them.

database Definition for the oid

First, you need to connect to any database in the same PostgreSQl process and execute:

the

select datname
from pg_database
where oid = 16396

(or any other base oid that you have). This will return You the database name.

After that you must connect to the database.

Inverse transform relfilenodes 9.4 version

If You are using version 9.4 or newer than, then You the next part is simple:

the

SELECT pg_filenode_relation(0, 3720450);

(0 means "tablespace default")

This function performs inverse transformation relfilenode for you. Thus, it will simply show You the table name. It will not be shown the connection with some scheme, if the obtained name of the table belongs to the current search_path; you Can use SET search_path = "; before the function is executed, in order to was the path up to the scheme.

You must be connected to correct database, or get an incorrect answer or even a reply is received.

Inverse transform relfilenodes 9.3 version

If you are using version 9.3 or older, you must connect to the database in which the table and execute the following query to pg_class:

the

select 
n.nspname AS tableschema, 
c.relname AS tablename

inner  join  pg_namespace n on (c.relnamespace = n.oid)
where c.relfilenode = 3720450;

(or any other received relfilenode id of the table).

This will tell You what table is this error.

No results?

Well, it usually helps.

Relfilenode can also be null, this in turn means that the file is located by pg_relfilenode.map. This is a typical scenario for General and some system directories, indexes, TOAST tables, etc. For example, it can be pg_database, pg_class and pg_proc.

What about scheme?

Did you notice that the schema (namespace) is not included in the path?

In PostgreSQL, the schema is just a namespace within the database. They have no influence on where the table is physically stored on disk.

Other ways tablespaces

A recent case that I encountered was the following error:

the

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

This is not a tabular space by default, since the path starts with pg_tblspc.

The process of finding the tables is actually the same. You can ignore pg_tblspc/nnn/PG_n.n_nnnnnn/ part and focus directly on the database_oid/relation_oid, as described above for the cases with the table space by default. For that, you should understand that means the path.

Therefore, the error text is divided into the following parts:

the

the
• pg_tblspc: this is not a tabular space by default
the
• 16709: the table space with oid'om 16709
the
• PG_9.3_201306121: use PostgreSQL 9.3 with the version of directory 201306121.
the
• 16499: database oid'om 16499
the
• 19401 table with relfilenode id 19401

We have already discussed the part about oid'e database and table relfilenode id. They do not differ from the table space, just starting in a different place.

So what about the part with the table space?

pg_tblspc is a directory in the data directory of PostgreSQL, which contains a symlink to all the provisions of the table space (or on NTFS junction points for them). Each symlink is named after the tablespace oid. So it finds the PostgreSQL table space. SQL commands for table spaces operate these links.

Oid refers to the pg_tablespace records for the table space, as seen from the:

the

select spcname
from pg_tablespace
where oid = 16709;

Inside the directory table space, there is another directory having the name corresponding to the version of PostgreSQL. It's static for this version and the only use of this is the multiple access multiple PostgreSQL processes to one tablespace, for example, during pg_upgrade. Usually there is only one entry.

In General, the structure is the same as base/ ways — first, database oid, then the oid of the relation.

Global (shared) table

There is a third category of errors, in case You're, then You are definitely in trouble. PostgreSQL has a General catalog table that have the same content in each database. They live in a special tablespace with the relfilenode id 16709.

Paths starting with global is base and there is no component with oid'om the database.

Shared directories are not marked relfilenode pg_class. That is, You will not be able to see, for example, pg_database of pg_class. pg_filenode_relation returns null, regardless of whether to raise it with oid'om table spaces by default, or oid'om global tablespace 1664.

Figuring out this is the topic for the subsequent article with the parsed links.

Of course, if you are experiencing problems with shared directories, you most likely will not be able in principle to run the database.

dealing damage

Database corruption should not happen. But it may happen anyway. This can be a problem with the hardware, bugs in the kernel or the file system of SSD who lie about committing a reliable disk tides, buggy storage area network, and of course bugs in the PostgreSQL. If You suspect database corruption before anything, read and act on the advice of the wiki page about damage.

Inside

To see how it all works, then run the macro relpathbackend src/include/common/relpath.h. It causes GetRelationPath src/common/relpath.c.

The manual describes the structure of the database on disk. Link.

Article based on information from habrahabr.ru