How do you tell? That’s a topic for a follow-up post. 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. pg_database, pg_class and pg_proc for example. That’s typical for shared catalogs and some system catalogs, their indexes, TOAST tables, etc. The relfilenode can also be zero, in which case the file is located via the pg_relfilenode.map. This will tell you the table the error relates to. (or whatever your table’s relfilenode id is). Inner join pg_namespace n on (c.relnamespace = n.oid) If you’re on 9.3 or below, connect to the database the table is in and query pg_class with: select You must be connected to the correct database or you’ll an incorrect or missing result. 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. That function handles the relfilenode mapping etc for you. (the 0 means “default tablespace” see below for info on tablespaces). If you’re on 9.4 or newer the next part is easy: SELECT pg_filenode_relation(0, 3720450) Finding a database by oidįirst, connect to any database on that PostgreSQL instance and run: select datname 3720450 the filenode id for the table with oid 3720450.Say you get the error given at the start of this post. How do you turn that path back into the relation name? Database oids and relation filenode ids For example: test=> select pg_relation_filepath('a') 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. That’s because PostgreSQL has a relfilenode map in a file named pg_relfilenode.map for each database/tablespace. Notice that I say “filenode id for the relation” not “relation oid”. 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. Shared relations are discussed at the end. * For shared relations (see below): global/ 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 files in the default tablespace, base/ database_oid/ filenode id for the relation (Older versions used a different format, see this blog). Here’s how to find out the database and table a file path refers to on a modern PostgreSQL. You can see the path of a table using: SELECT pg_relation_filepath('tablename') īut 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. 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 You have a path full of numbers like base/16499/19401 and you want to make sense of it. Sometimes you need to determine which table a file on disk corresponds to. PostgreSQL 9 Cookbook – Chinese Edition.PostgreSQL Server Programming Cookbook – 2nd Edition.PostgreSQL 9 Administration Cookbook – 3rd Edition.PostgreSQL High Availability Cookbook – 2nd Edition.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |