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。