How to determine which files on the disk correspond to PostgreSQL tables
Sometimes you need to determine which file on the disk corresponds to the table. You have a path 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:
You can see the path to the table using:
but what about the reverse process, getting the name of the object from the path to it? There is a pg_filenode_relation function that seems appropriate for this ... but in order to use it, you must be connected to the specific database to which this file belongs, which means knowing this connection.
')
Here's how to determine the path to the tables and databases in the modern version of PostgreSQL. (Older versions use a different format that you can read about here ).
There are 3 main path options:
Common relationships will be discussed at the end. For the first two options, which are the most common, which you will most often encounter, the last part of the path is identical, the oid base and the oid relationship.
Please note that I used the wording " filenode id relations " and not " oid relations ". This is due to the fact that PostgreSQL has a relfilenode map in the file called pg_relfilenode.map for each database / table space. The names of the table files do not necessarily coincide with their oids from pg_class , and they may change after running VACUUM FULL, TRUNCATE, and others. For example:
So. How to turn this path back into a relationship name?
Suppose you got an error from the beginning of this article. It can be divided into several parts:
then consider what each of them means.
First, you need to connect to any database in this PostgreSQl process and run:
(or any other oid base that you have). This will return the name of the database.
After that, you need to connect to this database.
If you are using version 9.4, or more recent, then the following part is simple for you:
(0 means “default tablespace”)
This function performs the inverse transformation of relfilenode for you. Thus, it will simply show you the name of the table. It will not be shown a link to any schema if the received table name belongs to the current search_path ; You can use SET search_path = ''; before executing the function, in order to specify the path up to the scheme.
You must be connected to the correct database, or the wrong answer will be received, or no answer at all.
If you are using version 9.3, or older, you need to connect to the database in which the table is located and execute the following query to pg_class :
(or any other obtained table relfilenode id).
This will tell you which table this error belongs to.
Well, it usually helps.
Relfilenode can also be null, which in turn means that the file is located via pg_relfilenode.map . This is a typical scenario for general and some system directories, their indexes, TOAST tables, etc. For example, it can be pg_database , pg_class and pg_proc .
Did you notice that the schema (namespace) does not appear in the path?
In the PostgreSQL schema, it is the only namespace within the database. They have no influence on where tables are physically stored on disk.
The recent incident I encountered was the following error:
This is not the default tablespace, since the path starts with pg_tblspc.
The process of finding the table is actually the same. You can ignore the pg_tblspc / nnn / PG_n.n_nnnnnn / part and focus directly on the database_oid / relation_oid , as described above for cases with a default table space. For this it is worth understanding what the path means.
Thus, the error text is divided into the following parts:
We have already discussed the part about database oid and tabular relfilenode id. They do not differ from the tablespace, they just start in a different place.
So what about the part with the tablespace?
pg_tblspc is a directory in the PostgreSQL data directory that contains symbolic links to all table space locations (or NTFS, connection points for them). Each symbolic link is named after the oid of the tablespace. This is how PostgreSQL finds table spaces. SQL commands to tablespaces operate on these links.
Oid refers to the pg_tablespace entry for the tablespace, as seen from:
Inside the tablespace directory, there is another directory named after the PostgreSQL version. It is static for this version and the only use for this is the multiple access of several PostgreSQL processes to a single tablespace, for example, during pg_upgrade. As a rule, there is only one entry.
In general, the structure is the same as for the base / paths - first the database oid, then the oid relationship.
There is a third category of errors, in case you observe it, then you are definitely in trouble. PostgreSQL has shared directories — tables that have the same content in each database. They live in a special tablespace with relfilenode id 16709.
Paths to them begin with global instead of base and they lack a component with a database oid.
Shared directories are not marked relfilenode in pg_class . That is, you cannot see, for example, pg_database from pg_class . pg_filenode_relation returns null, regardless of whether to call it with the default table space oid, or with the global table space 1664 oid.
Figuring this out is a topic for a subsequent article with disassembled links.
Of course, if you experience problems with shared directories, you will most likely not be able to launch the database in principle.
Database damage should not happen. But it can happen anyway. These can be problems with hardware, kernel bugs, or file systems, SSDs that lie about making reliable disk tides, buggy storage networks, and of course the bugs of PostgreSQL itself. If you suspect damage to the database, before doing anything, read and follow the advice on the damage wiki page .
To see how this all works, run the relpathbackend macro in src / include / common / relpath.h. It calls the GetRelationPath in src / common / relpath.c.
The manual describes the structure of the database on disk. Link
ERROR: could not read block 11857 of relation base/16396/3720450: read only 0 of 8192 bytes Looking for a relationship path
You can see the path to the table using:
SELECT pg_relation_filepath('tablename'); but what about the reverse process, getting the name of the object from the path to it? There is a pg_filenode_relation function that seems appropriate for this ... but in order to use it, you must be connected to the specific database to which this file belongs, which means knowing this connection.
')
The structure of the path to the file
Here's how to determine the path to the tables and databases in the modern version of PostgreSQL. (Older versions use a different format that you can read about here ).
There are 3 main path options:
- For files in the default tablespace, base / database_oid / filenode id for the relation
- For files from other tablespaces: pg_tblspc / tablespace_oid / tablespace_version_subdir / database_oid / filenode id relations
- For general relationships: global / filenode relationship id
Common relationships will be discussed at the end. For the first two options, which are the most common, which you will most often encounter, the last part of the path is identical, the oid base and the oid relationship.
Please note that I used the wording " filenode id relations " and not " oid relations ". This is due to the fact that PostgreSQL has a relfilenode map in the file called pg_relfilenode.map for each database / table space. The names of the table files do not necessarily coincide with their oids from pg_class , and they may change after running VACUUM FULL, TRUNCATE, and others. 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 to turn this path back into a relationship name?
Database id's and filenode ids relationships
Suppose you got an error from the beginning of this article. It can be divided into several parts:
- base: in the default tablespace
- 16396: in the database with oid 16396
- 3720450 filenode id for a table with oid 3720450
then consider what each of them means.
Oid database definition
First, you need to connect to any database in this PostgreSQl process and run:
select datname from pg_database where oid = 16396 (or any other oid base that you have). This will return the name of the database.
After that, you need to connect to this database.
Inverse transformation of relfilenodes on 9.4 version
If you are using version 9.4, or more recent, then the following part is simple for you:
SELECT pg_filenode_relation(0, 3720450); (0 means “default tablespace”)
This function performs the inverse transformation of relfilenode for you. Thus, it will simply show you the name of the table. It will not be shown a link to any schema if the received table name belongs to the current search_path ; You can use SET search_path = ''; before executing the function, in order to specify the path up to the scheme.
You must be connected to the correct database, or the wrong answer will be received, or no answer at all.
Reverse conversion relfilenodes on version 9.3
If you are using version 9.3, or older, you need to connect to the database in which the table is located and execute the following query to pg_class :
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 any other obtained table relfilenode id).
This will tell you which table this error belongs to.
No results?
Well, it usually helps.
Relfilenode can also be null, which in turn means that the file is located via pg_relfilenode.map . This is a typical scenario for general and some system directories, their indexes, TOAST tables, etc. For example, it can be pg_database , pg_class and pg_proc .
What about the circuit?
Did you notice that the schema (namespace) does not appear in the path?
In the PostgreSQL schema, it is the only namespace within the database. They have no influence on where tables are physically stored on disk.
Other tablespace paths
The recent incident I encountered was the following error:
ERROR: could not truncate file "pg_tblspc / 16709 / PG_9.3_201306121 / 16499/19401" to 8 blocks: Permission denied This is not the default tablespace, since the path starts with pg_tblspc.
The process of finding the table is actually the same. You can ignore the pg_tblspc / nnn / PG_n.n_nnnnnn / part and focus directly on the database_oid / relation_oid , as described above for cases with a default table space. For this it is worth understanding what the path means.
Thus, the error text is divided into the following parts:
- pg_tblspc: this is not the default tablespace
- 16709: this is a tablespace with an oid of 16709
- PG_9.3_201306121: PostgreSQL 9.3 is used with the directory version 201306121.
- 16499: database with oid 16499
- 19401 table with relfilenode id 19401
We have already discussed the part about database oid and tabular relfilenode id. They do not differ from the tablespace, they just start in a different place.
So what about the part with the tablespace?
pg_tblspc is a directory in the PostgreSQL data directory that contains symbolic links to all table space locations (or NTFS, connection points for them). Each symbolic link is named after the oid of the tablespace. This is how PostgreSQL finds table spaces. SQL commands to tablespaces operate on these links.
Oid refers to the pg_tablespace entry for the tablespace, as seen from:
select spcname from pg_tablespace where oid = 16709; Inside the tablespace directory, there is another directory named after the PostgreSQL version. It is static for this version and the only use for this is the multiple access of several PostgreSQL processes to a single tablespace, for example, during pg_upgrade. As a rule, there is only one entry.
In general, the structure is the same as for the base / paths - first the database oid, then the oid relationship.
Global (common) tables
There is a third category of errors, in case you observe it, then you are definitely in trouble. PostgreSQL has shared directories — tables that have the same content in each database. They live in a special tablespace with relfilenode id 16709.
Paths to them begin with global instead of base and they lack a component with a database oid.
Shared directories are not marked relfilenode in pg_class . That is, you cannot see, for example, pg_database from pg_class . pg_filenode_relation returns null, regardless of whether to call it with the default table space oid, or with the global table space 1664 oid.
Figuring this out is a topic for a subsequent article with disassembled links.
Of course, if you experience problems with shared directories, you will most likely not be able to launch the database in principle.
Dealing with damage
Database damage should not happen. But it can happen anyway. These can be problems with hardware, kernel bugs, or file systems, SSDs that lie about making reliable disk tides, buggy storage networks, and of course the bugs of PostgreSQL itself. If you suspect damage to the database, before doing anything, read and follow the advice on the damage wiki page .
Insides
To see how this all works, run the relpathbackend macro in src / include / common / relpath.h. It calls the GetRelationPath in src / common / relpath.c.
The manual describes the structure of the database on disk. Link
Source: https://habr.com/ru/post/275125/
All Articles