Thread: find table name
hi In postgresql all databases are stored in /data/base directory in numbers form and all the related tables with that database are stored in that directory again in numbers form. so there is any command or query to see the original table names. just like:- select * from pg_stat_database; for databases. thank you have a nice day.
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 > In postgresql all databases are stored in /data/base directory in > numbers form and all the related tables with that database are stored in > that directory again in numbers form. > so there is any command or query to see the original table names. > just like:- select * from pg_stat_database; for databases. You can find the mapping between the "numbers" on disk and the tables by looking at the "relfilenode", "relname" and "relkind" columns of the pg_class table. However, it is best to not mess with those files at all. What are you trying to do? - -- Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 200312070810 -----BEGIN PGP SIGNATURE----- iD8DBQE/0yb5vJuQZxSWSsgRAoOMAJ9FZsojXzvCVgrM6Tv0B3f+eI0k8QCg6X8C fqQi82DmYdZduq4lwADIou0= =92w+ -----END PGP SIGNATURE-----
Hi the list, I used to run oid2name to find the relation between database names and .../data/base/<dir> but i'm interested also to identify relation between table names, index names and files number as would try to decrease Wait I/O by setting tables on one disk and indexes on another one. The tables pg_class and pg_index do not give the symbolic name with the "numbers". Thierry Missimilly. greg@turnstep.com wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > > > In postgresql all databases are stored in /data/base directory in > > numbers form and all the related tables with that database are stored in > > that directory again in numbers form. > > so there is any command or query to see the original table names. > > just like:- select * from pg_stat_database; for databases. > > You can find the mapping between the "numbers" on disk and the tables by > looking at the "relfilenode", "relname" and "relkind" columns of the > pg_class table. However, it is best to not mess with those files at > all. What are you trying to do? > > - -- > Greg Sabino Mullane greg@turnstep.com > PGP Key: 0x14964AC8 200312070810 > > -----BEGIN PGP SIGNATURE----- > > iD8DBQE/0yb5vJuQZxSWSsgRAoOMAJ9FZsojXzvCVgrM6Tv0B3f+eI0k8QCg6X8C > fqQi82DmYdZduq4lwADIou0= > =92w+ > -----END PGP SIGNATURE----- > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
Attachment
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 > I used to run oid2name to find the relation between database names and > .../data/base/<dir> but i'm interested also to identify relation between table > names, index names and files number as would try to decrease Wait I/O by > setting tables on one disk and indexes on another one. > The tables pg_class and pg_index do not give the symbolic name with the > "numbers". This is probably not what you want to do - there are less drastic ways to get more performance out of PostgreSQL. One thing you can do is move your WAL files to a different disk, as they are very heavily accessed. (this is the pg_xlog directory). There are lots of other things that can help as well - post your hardware and/or queries to the performance list and you should get some good responses: http://tinyurl.com/yc2b - -- Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 200312081935 -----BEGIN PGP SIGNATURE----- iD8DBQE/1RlYvJuQZxSWSsgRAjV4AJ9uvU5e4yStJSWLhEJZXW/TEiiDGgCgmwKM rymCF633HioUBJQ0Oj0obZg= =rf6h -----END PGP SIGNATURE-----