Thread: find table name

find table name

From
Ashok Chauhan
Date:
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.


Re: find table name

From
greg@turnstep.com
Date:
-----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-----



Re: find table name

From
Thierry Missimilly
Date:
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

Re: find table name

From
greg@turnstep.com
Date:
-----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-----