2019獨(dú)角獸企業(yè)重金招聘Python工程師標(biāo)準(zhǔn)>>>

查看表空間的語法幫助

postgres=# \h create tablespace Command: CREATE TABLESPACE Description: define a new tablespace Syntax: CREATE TABLESPACE tablespace_name[ OWNER { new_owner | CURRENT_USER | SESSION_USER } ]LOCATION 'directory'[ WITH ( tablespace_option = value [, ... ] ) ]

創(chuàng)建表空間

連接coordinator：

create tablespace tst_tbs location '/home/shboss/antdb/data/tablespace';

需要在集群涉及到的主機(jī)上都存在location指定的目錄，否則報(bào)錯(cuò)：

postgres=# create tablespace tst_tbs location '/home/shboss/antdb/data/tablespace'; ERROR: Fail to process utility query on remote node. DETAIL: ERROR: directory "/home/shboss/antdb/data/tablespace" does not exist

在各個(gè)主機(jī)上創(chuàng)建目錄：

remote_cmd "mkdir -p /home/shboss/antdb/data/tablespace"

再次連接coordinator創(chuàng)建表空間：

postgres=# create tablespace tst_tbs location '/home/shboss/antdb/data/tablespace'; CREATE TABLESPACE

查看表空間信息

數(shù)據(jù)庫級別：

postgres=# \dbList of tablespacesName | Owner | Location ------------+--------+------------------------------------pg_default | shboss | pg_global | shboss | tst_tbs | shboss | /home/shboss/antdb/data/tablespace (3 rows)postgres=# select * from pg_tablespace ;spcname | spcowner | spcacl | spcoptions ------------+----------+--------+------------pg_default | 10 | | pg_global | 10 | | tst_tbs | 10 | | (3 rows)

文件系統(tǒng)：

coord節(jié)點(diǎn)：

[shboss@localhost1 pg_tblspc]$ pwd /home/shboss/antdb/data/coord/pg_tblspc

datanode 節(jié)點(diǎn)：

lrwxrwxrwx 1 shboss shboss 34 Jul 23 15:25 164514 -> /home/shboss/antdb/data/tablespace [shboss@localhost1 pg_tblspc]$ pwd /home/shboss/antdb/data/db1/pg_tblspc

表空間路徑：

[shboss@localhost1 pg_tblspc]$ cd /home/shboss/antdb/data/tablespace [shboss@localhost1 tablespace]$ ll total 12 drwx------ 2 shboss shboss 4096 Jul 23 15:25 PG_9.6_201608131_coord1 drwx------ 2 shboss shboss 4096 Jul 23 15:25 PG_9.6_201608131_coord5 drwx------ 2 shboss shboss 4096 Jul 23 15:25 PG_9.6_201608131_db1_2 [shboss@localhost1 tablespace]$ tree . . ├── PG_9.6_201608131_coord1 ├── PG_9.6_201608131_coord5 └── PG_9.6_201608131_db1_2

在表空間內(nèi)建表

create table test_tbs (id int) tablespace tst_tbs;

查看表空間中的數(shù)據(jù)文件：

[shboss@localhost1 tablespace]$ tree . . ├── PG_9.6_201608131_coord1 │?? └── 13603 │?? └── 476507 ├── PG_9.6_201608131_coord5 │?? └── 13603 │?? └── 18025 └── PG_9.6_201608131_db1_2└── 13597└── 164515

使用其他普通用戶在tst_tbs 表空間下創(chuàng)建表：

bmsql5=> create table test_tbs (id int) tablespace tst_tbs; ERROR: permission denied for tablespace tst_tbs bmsql5=>

解決辦法1：給普通用戶賦權(quán)

postgres=# grant create on tablespace tst_tbs to bmsql5_ora_fdw; GRANT

再次建表成功：

bmsql5=> create table test_tbs (id int) tablespace tst_tbs; CREATE TABLE bmsql5=> \d+ test_tbsTable "bmsql5_ora_fdw.test_tbs"Column | Type | Modifiers | Storage | Stats target | Description --------+---------+-----------+---------+--------------+-------------id | integer | | plain | | Tablespace: "tst_tbs" Distribute By: HASH(id) Location Nodes: ALL DATANODESbmsql5=>

在創(chuàng)建之前，可以查詢當(dāng)前用戶表空間tst_tbs 是否有權(quán)限：

bmsql5=> select has_tablespace_privilege('tst_tbs','create');has_tablespace_privilege --------------------------t (1 row)

revoke權(quán)限后再次查詢：

-- superuser postgres=# revoke create on tablespace tst_tbs from bmsql5_ora_fdw; REVOKE -- 普通用戶 bmsql5=> select has_tablespace_privilege('tst_tbs','create');has_tablespace_privilege --------------------------f (1 row)

解決辦法2：修改表空間的屬主為表的創(chuàng)建用戶

alter tablespace tst_tbs owner to bmsql5_ora_fdw;

再次建表成功：

bmsql5=> create table test_tbs (id int) tablespace tst_tbs; CREATE TABLE bmsql5=>

解決辦法3：修改數(shù)據(jù)庫的默認(rèn)表空間

alter database bmsql5 set tablespace tst_tbs;postgres=# alter database bmsql5 set tablespace tst_tbs; ALTER DATABASE Time: 323772.143 ms postgres=# postgres=# \l+ List of databasesName | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description -----------+--------+----------+---------+-------+-------------------+---------+------------+--------------------------------------------bmsql5 | shboss | UTF8 | C | C | | 26 GB | tst_tbs | db1 | shboss | UTF8 | C | C | | 1529 MB | pg_default | postgres | shboss | UTF8 | C | C | | 43 MB | pg_default | default administrative connection databasetemplate0 | shboss | UTF8 | C | C | =c/shboss +| 30 MB | pg_default | unmodifiable empty database| | | | | shboss=CTc/shboss | | | template1 | shboss | UTF8 | C | C | =c/shboss +| 30 MB | pg_default | default template for new databases| | | | | shboss=CTc/shboss | | | testdb | shboss | UTF8 | C | C | | 108 MB | pg_default | (6 rows)

這個(gè)操作的耗時(shí)取決于數(shù)據(jù)庫的大小和磁盤的性能。且在數(shù)據(jù)文件移動過程中，該數(shù)據(jù)庫無法連接。

[shboss@localhost1 tablespace]$ psql -d bmsql5 psql: FATAL: database "bmsql5" does not exist DETAIL: It seems to have just been dropped or renamed.

再次建表成功：

bmsql5=> create table test_tbs (id int) tablespace tst_tbs; CREATE TABLE bmsql5=> drop table test_tbs; DROP TABLE bmsql5=> create table test_tbs (id int); CREATE TABLE

碰到的問題：

更改的數(shù)據(jù)庫上有連接的時(shí)候，沒法修改：

postgres=# alter database bmsql5 set tablespace tst_tbs; ERROR: database "bmsql5" is being accessed by other users DETAIL: There is 1 other session using the database.

連接到修改的數(shù)據(jù)庫上也無法執(zhí)行修改操作：

bmsql5=# alter database bmsql5 set tablespace tst_tbs; ERROR: cannot change the tablespace of the currently open database

如果修改的數(shù)據(jù)庫中已經(jīng)有對象在目標(biāo)表空間中，也無法執(zhí)行操作：

postgres=# alter database bmsql5 set tablespace tst_tbs; ERROR: some relations of database "bmsql5" are already in tablespace "tst_tbs" HINT: You must move them back to the database's default tablespace before using this command.

查找指定表空間中的對象：

select relname,reltype,spcname from pg_class c, pg_tablespace tbs where c.reltablespace=tbs.oid and tbs.spcname='tst_tbs';

查看表的表空間

postgres=# \d test_tbsTable "public.test_tbs"Column | Type | Modifiers --------+---------+-----------id | integer | Tablespace: "tst_tbs"

如果表沒有使用所在數(shù)據(jù)庫的默認(rèn)表空間，則會在\d 的時(shí)候顯示。

修改表的表空間

bmsql5=# select pg_relation_filepath('bmsql_stock');pg_relation_filepath ----------------------base/27636/461935 (1 row)bmsql5=# alter table bmsql_stock set tablespace tst_tbs; ALTER TABLE bmsql5=# select pg_relation_filepath('bmsql_stock');pg_relation_filepath -------------------------------------------------------pg_tblspc/476505/PG_9.6_201608131_coord1/27636/476515 (1 row)bmsql5=#

表很大的話，會比較耗時(shí)，是個(gè)挪動文件的過程，同時(shí)發(fā)現(xiàn)filenode是變了的。

查看表空間大小

postgres=# select pg_tablespace_size('tst_tbs');pg_tablespace_size --------------------36757504 (1 row)postgres=# select pg_size_pretty(pg_tablespace_size('tst_tbs'));pg_size_pretty ----------------35 MB (1 row)

刪除表空間

postgres=# drop tablespace tst_tbs; ERROR: tablespace "tst_tbs" is not empty

如果表空間中有對象刪除，則無法刪除。

根據(jù)上面提供的查找指定表空間中的對象 語句找出對象移走或刪除后，再進(jìn)行刪除：

postgres=# drop tablespace tst_tbs; DROP TABLESPACE

查看主機(jī)上的文件：

[shboss@localhost1 tablespace]$ tree . .0 directories, 0 files [shboss@localhost1 tablespace]$ pwd /home/shboss/antdb/data/tablespace

目錄已經(jīng)為空。

轉(zhuǎn)載于:https://my.oschina.net/yafeishi/blog/1861567

超強(qiáng)干貨來襲 云風(fēng)專訪：近40年碼齡，通宵達(dá)旦的技術(shù)人生

總結(jié)

以上是生活随笔為你收集整理的AntDB上使用表空间的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。