pg数据库查看和建表空间

① 如何查询表和索引所在的表空间

一 查询某个表所在表空间的简单方法

Postgresql 提供类似" \ "命令很方便得到相关信息，命令如下：
skytf=> \d test_2
Table "skytf.test_2"
Column | Type | Modifiers
--------+-----------------------+-----------
id | integer |
obj_id | integer | not null
name | character varying(64) |
Indexes:
"idx_hash_name" hash (name)
"idx_test_2" btree (id, obj_id)
Tablespace: "tbs_skytf_idx"

备注：如果这个表的表空间为当前数据库的默认表空间，那么上面则不会显示 Tablespace 信息，
相反，则会显示这张有的表空间，例如上面的表 test_2 的表空间为 tbs_skytf_idx，而
表空间 "tbs_skytf_idx" 不是数据库 skytf 的默认表空间， 那么如何查询数据库的默认
表空间呢，可以通过以下命令查询。

--1.1 查询数据库的默认表空间
skytf=> select datname,dattablespace from pg_database where datname='skytf';
datname | dattablespace
---------+---------------
skytf | 14203070
(1 row)

skytf=> select oid,spcname from pg_tablespace where oid=14203070;
oid | spcname
----------+-----------
14203070 | tbs_skytf
(1 row)

备注：通过以上查出数据库 skytf 的默认表空间为 tbs_skytf。

二 批量查询数据库表和索引的表空间
--2.1 查询表和索引所在的表空间
select relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)), tb.spcname
from pg_class a, pg_tablespace tb
where a.reltablespace = tb.oid
and a.relkind in ('r', 'i')
order by a.relpages desc;

备注：上面只取了部分结果，这个查询能够查询表和索引所处的表空间，但是有一点需要注意，这个查询
仅显示表空间不是数据库默认表空间的数据库对像，而我们通常需要查出位于数据库默认表空间的
对像，显然上面的查询不是我们想要的，接下来看另一个查询。

--2.2 查询位于默认数据库表空间的对像
select relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)),reltablespace,relowner
from pg_class a
where a.relkind in ('r', 'i')
and reltablespace='0'
order by a.relpages desc;

备注：这个查询加入限制条件 reltablespace='0'，即可查找出位于当前数据库默认表空间的
数据库表和索引。 通常这才是我们想要的结果，接下来可以把部分表转移到其它表空间上去，转移
的方法可以用 "ALTER TABLE move tablespace "或者重建索引移表空间等方法，这里不详细介绍。

--2.3 查询在某个表空间上的对像

select relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)),reltablespace,relowner
from pg_class a, pg_tablespace tb
where a.relkind in ('r', 'i')
and a.reltablespace=tb.oid
and tb.spcname='tablespace_name'
order by a.relpages desc;

--2.4 手册上对于 pgclass 视图的 reltablespace 字段解释
The tablespace in which this relation is stored. If zero, the database is default tablespace is
implied. (Not meaningful if the relation has no on-disk file.)

② postgresql怎么查看表的创建时间

方法一：通过查找表数据文件方式
这种方法通过查找表的数据文件的方式从而确定表的创建时间，但是这种方法并不能准备查询表的创建
时间，而且有时候，这种方法得到的信息还有可能是错误的，下面大致演示下。

--1.1 创建表并插入数据

francs=> create table test_ctime (id int4 primary key ,name varchar(32));
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_ctime_pkey" for table "test_ctime"
CREATE TABLE
francs=> insert into test_ctime select generate_series(1,10000),'create_time test';
INSERT 0 10000
francs=> \d test_ctime;
Table "francs.test_ctime"
Column | Type | Modifiers
--------+-----------------------+-----------
id | integer | not null
name | character varying(32) |
Indexes:
"test_ctime_pkey" PRIMARY KEY, btree (id)

francs=> \dt+ test_ctime;
List of relations
Schema | Name | Type | Owner | Size | Description
--------+------------+-------+--------+--------+-------------
francs | test_ctime | table | francs | 536 kB |
(1 row)

备注：表创建好了，接下来演示如何定位表的物理文件。

--1.2 定位表所在的表空间

francs=> select relname,relfilenode,reltablespace from pg_class where relname='test_ctime';
relname | relfilenode | reltablespace
------------+-------------+---------------
test_ctime | 24650 | 0
(1 row)

备注：在 PostgreSQL 的逻辑结构体系中，表位于数据库中，同时表位于表空间上，面表空间对应系统上一个
文件目录，每个表由一个或者多个文件组成； 根据上面的结果，表 test_ctime 的 reltablespace
值为 0,表示位于所属数据库的默认表空间，注意 relfilenode 值为 24650。

--1.3 查询数据库 francs 的默认表空间

francs=> select oid,datname,dattablespace from pg_database where datname='francs';
oid | datname | dattablespace
-------+---------+---------------
16386 | francs | 16385

备注：上面查出数据库 francs 的默认表空间的 oid 为 16385。

--1.4 查找 oid 为 16385 的表空间

francs=> select oid,* from pg_tablespace where oid=16385;
oid | spcname | spcowner | spcacl | spcoptions
-------+------------+----------+-----------------------------------------+------------
16385 | tbs_francs | 10 | {postgres=C/postgres,francs=C/postgres} |
(1 row)

备注：查了半天才查到表 test_ctime 的默认表空间为 tbs_francs，这里之所以饶这么大圈，是为
了展示 postgresql 中的一些逻辑结构关系，如果自己对环境比较熟悉，可以直接定位到
哪个表空间。

--1.5 查询表空间 tbs_francs 对应的物理目录

francs=> \db
List of tablespaces
Name | Owner | Location
------------+----------+------------------------------------------
pg_default | postgres |
pg_global | postgres |
tbs_francs | postgres | /database/1922/pgdata1/pg_tbs/tbs_francs
(3 rows)

备注：表空间 tbs_francs 的数据目录为 /database/1922/pgdata1/pg_tbs/tbs_francs。

--1.6 进入数据目录

[postgres@redhat6 16386]$ cd /database/1922/pgdata1/pg_tbs/tbs_francs
[postgres@redhat6 tbs_francs]$ ll
total 4.0K
drwx------. 4 postgres postgres 4.0K May 22 10:35 PG_9.2_201204301
[postgres@redhat6 tbs_francs]$ cd PG_9.2_201204301/
[postgres@redhat6 PG_9.2_201204301]$ ll
total 16K
drwx------. 2 postgres postgres 12K Jun 26 19:03 16386
drwx------. 2 postgres postgres 4.0K May 22 10:37 pgsql_tmp

备注：根据前面的步骤 1.3 查询的信息知道 16386 为数据库 francs 的 oid。 再根据步骤 1.2 的信息知道
表 test_ctime 的 relfilenode 值为 24650

--1.7 查找表 test_ctime 的数据文件

[postgres@redhat6 16386]$ ll 24650
-rw-------. 1 postgres postgres 512K Jun 26 18:57 24650

备注：根据数据文件 24650 知道表的创建时间为 2012-06-26 18:57。但这种方法并不准确，因为
表上的操作可能导致表重新生成文件，接着演示。

--1.8 cluster 表

francs=> cluster verbose test_ctime using test_ctime_pkey;
INFO: clustering "francs.test_ctime" using index scan on "test_ctime_pkey"
INFO: "test_ctime": found 0 removable, 10000 nonremovable row versions in 64 pages
DETAIL: 0 dead row versions cannot be removed yet.
CPU 0.00s/0.03u sec elapsed 0.08 sec.
CLUSTER
francs=> select relname,relfilenode,reltablespace from pg_class where relname='test_ctime';
relname | relfilenode | reltablespace
------------+-------------+---------------
test_ctime | 24655 | 0
(1 row)

备注：表 test_ctime 经过 cluster 操作后，重新生成了数据文件，文件号由原来的 24650 变成了 24655

--1.9 系统上再次查询表数据文件

[postgres@redhat6 16386]$ ll 24650
-rw-------. 1 postgres postgres 0 Jun 26 19:19 24650
[postgres@redhat6 16386]$ ll 24655
-rw-------. 1 postgres postgres 512K Jun 26 19:19 24655

备注：显然新文件的时间 24655 并不是表 test_ctime 的初始创建时间。

--1.10 vacuum full 表

francs=> vacuum full test_ctime;
VACUUM
francs=> select relname,relfilenode,reltablespace from pg_class where relname='test_ctime';
relname | relfilenode | reltablespace
------------+-------------+---------------
test_ctime | 24659 | 0
(1 row)

备注： vacuum full 操作后，同样产生了新文件，新文件号为 24659

--1.11 系统上再次查询表数据文件

[postgres@redhat6 16386]$ ll 24659
-rw-------. 1 postgres postgres 512K Jun 26 19:22 24659

③ 查看数据库中有哪些表空间

1、查看表空间名字：

select distinct TABLESPACE_NAME from tabs

2、查看几个表空间：

select count(distinct TABLESPACE_NAME) from tabs

④ PG里如何查看表，索引，表空间，数据库大小

--查询一个索引大小 select pg_size_pretty(pg_relation_size('indexname))--查看一张表及此它上的索引总大小 select pg_size_pretty(pg_total_relation_size('tablename')); --查看所有 schema里面索引大小，大到小的顺序排列： select indexrelname,pg_size_pretty( pg_relation_size(relid)) from pg_stat_user_indexes where schemaname = 'schemaname' order by pg_relation_size(relid) desc;--查看所有 schema里面表的大小，从大到小顺序排列： select relname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables where schemaname = 'schemaname' order by pg_relation_size(relid) desc; --查看数据库大小： select pg_database.datname,pg_size_pretty(pg_database_size(pg_database.datname)) AS size from pg_database;--查看表空间大小

⑤ postgresql 查看数据库有多少表

22:27:34
这里可以分成三个部分来理解。
1、查看数据库服务器启动时间
postgres=# SELECT pg_postmaster_start_time();
2、当前时间减去启动时间就是运行时间
postgres=# SELECT current_timestamp - pg_postmaster_start_time();
?column?

⑥ windows server 2008 pg数据库创建表空间报错提示权限不够，怎么解决

在数据库运维工作中,经常会有数据目录使用率较高需要调整的情况,通常会给数据库建立

⑦ postgresql查询表在哪个表空间

psql里 查看所有库：\l 查看所有表： \dt 查看某个表的结构： \d 表名 如果表位于你的自定义模式中，要把模式名加入“搜索路径”。具体来说就是编辑postgres的配置文件：<数据目录>\postgres.conf，编辑 search_path= 这行

⑧ PG里如何查看表，索引，表空间，数据库大小

--查询一个索引大小 select pg_size_pretty(pg_relation_size('indexname))--查看一张表及此它上的索引总大小 select pg_size_pretty(pg_total_relation_size('tablename')); --查看所有 schema里面索引大小，大到小的顺序排列： select indexrelname,pg_size_pretty( pg_relation_size(relid)) from pg_stat_user_indexes where schemaname = 'schemaname' order by pg_relation_size(relid) desc;--查看所有 schema里面表的大小，从大到小顺序排列： select relname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables where schemaname = 'schemaname' order by pg_relation_size(relid) desc; --查看数据库大小： select pg_database.datname,pg_size_pretty(pg_database_size(pg_database.datname)) AS size from pg_database;--查看表空间大小

⑨ postgresql 查看表建立哪些索引

在数据库运维工作中，经常会有数据目录使用率较高需要调整的情况，通常会给数据库建立多个表空间，
并分别位于不同的盘上，这时需要做的工作就是调整库中现有表和索引的表空间，下面简单总结下这块维护
工作的内容，以下都是基于 PostgreSQL 9.0.1 做的测试。

一 查询某个表所在表空间的简单方法

PostgreSQL 提供类似" \ "命令很方便得到相关信息，命令如下：
skytf=> \d test_2
Table "skytf.test_2"
Column | Type | Modifiers
--------+-----------------------+-----------
id | integer |
obj_id | integer | not null
name | character varying(64) |
Indexes:
"idx_hash_name" hash (name)
"idx_test_2" btree (id, obj_id)
Tablespace: "tbs_skytf_idx"
备注：如果这个表的表空间为当前数据库的默认表空间，那么上面则不会显示 Tablespace 信息，
相反，则会显示这张有的表空间，例如上面的表 test_2 的表空间为 tbs_skytf_idx，而
表空间 "tbs_skytf_idx" 不是数据库 skytf 的默认表空间， 那么如何查询数据库的默认
表空间呢，可以通过以下命令查询。

--1.1 查询数据库的默认表空间
skytf=> select datname,dattablespace from pg_database where datname='skytf';
datname | dattablespace
---------+---------------
skytf | 14203070
(1 row)
skytf=> select oid,spcname from pg_tablespace where oid=14203070;
oid | spcname
----------+-----------
14203070 | tbs_skytf
(1 row)
备注：通过以上查出数据库 skytf 的默认表空间为 tbs_skytf。

二 批量查询数据库表和索引的表空间
--2.1 查询表和索引所在的表空间
select relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)), tb.spcname
from pg_class a, pg_tablespace tb
where a.reltablespace = tb.oid
and a.relkind in ('r', 'i')
order by a.relpages desc;

备注：上面只取了部分结果，这个查询能够查询表和索引所处的表空间，但是有一点需要注意，这个查询
仅显示表空间不是数据库默认表空间的数据库对像，而我们通常需要查出位于数据库默认表空间的
对像，显然上面的查询不是我们想要的，接下来看另一个查询。

--2.2 查询位于默认数据库表空间的对像
select relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)),reltablespace,relowner
from pg_class a
where a.relkind in ('r', 'i')
and reltablespace='0'
order by a.relpages desc;

备注：这个查询加入限制条件 reltablespace='0'，即可查找出位于当前数据库默认表空间的
数据库表和索引。 通常这才是我们想要的结果，接下来可以把部分表转移到其它表空间上去，转移
的方法可以用 "ALTER TABLE move tablespace "或者重建索引移表空间等方法，这里不详细介绍。

--2.3 查询在某个表空间上的对像

select relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)),reltablespace,relowner
from pg_class a, pg_tablespace tb
where a.relkind in ('r', 'i')
and a.reltablespace=tb.oid
and tb.spcname='tablespace_name'
order by a.relpages desc;

--2.4 手册上对于 pgclass 视图的 reltablespace 字段解释
The tablespace in which this relation is stored. If zero, the database is default tablespace is
implied. (Not meaningful if the relation has no on-disk file.)

⑩ 如何查询表和索引所在的表空间

工作的内容，以下都是基于 PostgreSQL 9.0.1 做的测试。

一 查询某个表所在表空间的简单方法

PostgreSQL 提供类似" \ "命令很方便得到相关信息，命令如下：
skytf=> \d test_2
Table "skytf.test_2"
Column | Type | Modifiers
--------+-----------------------+-----------
id | integer |
obj_id | integer | not null
name | character varying(64) |
Indexes:
"idx_hash_name" hash (name)
"idx_test_2" btree (id, obj_id)
Tablespace: "tbs_skytf_idx"
备注：如果这个表的表空间为当前数据库的默认表空间，那么上面则不会显示 Tablespace 信息，
相反，则会显示这张有的表空间，例如上面的表 test_2 的表空间为 tbs_skytf_idx，而
表空间 "tbs_skytf_idx" 不是数据库 skytf 的默认表空间， 那么如何查询数据库的默认
表空间呢，可以通过以下命令查询。

--1.1 查询数据库的默认表空间
skytf=> select datname,dattablespace from pg_database where datname='skytf';
datname | dattablespace
---------+---------------
skytf | 14203070
(1 row)
skytf=> select oid,spcname from pg_tablespace where oid=14203070;
oid | spcname
----------+-----------
14203070 | tbs_skytf
(1 row)
备注：通过以上查出数据库 skytf 的默认表空间为 tbs_skytf。