- 浏览: 238992 次
最新评论
一、说明
在数据库运维工作中,经常会有数据目录使用率较高需要调整的情况,通常会给数据库建立多个表空间,
并分别位于不同的盘上,这时需要做的工作就是调整库中现有表和索引的表空间
二、表空间用途
表空间就是一个简单的目录,其主要用途分两个:
1.单独扩展表空间用,一旦磁盘或分区被耗尽,可以创建一个表空间到其他磁盘或分区上面。
2.区分不同对象的存储位置,比如可将索引放入较快磁盘的表空间上,而将固定不变的数据放入较慢磁盘的表空间上。
三、表空间共享
与Oracle数据库中的表空间被独占不同,PostgreSQL的表空间是可以被共享的。
当创建了一个表空间后,这个表空间可以被多个数据库、表、索引等数据库对象使用。达到对象的分离与归类的目的。
在PostgreSQL中有两个系统自建表空间:pg_global和pg_default。
前者是系统全局表空间,存储了关键的共享系统目录。后者是系统全局表空间,存储了关键的共享系统目录。
后者是系统默认表空间,可通过set default tablespace=tablespacename来指定为其他表空间,在建立数据库、表、索引等数据库对象时,
若不指定表空间参数,则系统自动将对象创建到默认表空间中。
四、创建表空间:
mkdir tbs_tina
chmod 777 tbs_tina/
chown postgres:postgres tbs_tina/
create tablespace tbs_tina owner postgres location '/pgtina/tbs_tina';
create tablespace ind_tina owner postgres location '/pgtina/ind_tina';
create table t1(id int) tablespace tbs_tina;
create index ind_t1 on t1(id) tablespace ind_tina; ---可以将表和索引放在不同的表空间
五、用户表空间权限:
has_tablespace_privilege(user, tablespace, privilege) boolean 用户是否有访问表空间的权限 CREATE
has_tablespace_privilege(tablespace, privilege) boolean 当前用户是否有访问表空间的权限 CREATE
tina=# create user sqluser nosuperuser noreplication nocreatedb nocreaterole login encrypted password 'sqlpasswd'; --创建一个普通用户
CREATE ROLE
tina=# select has_tablespace_privilege('sqluser','tbs_tina','create'); --sqluser没有tbs_tina表空间的权限
has_tablespace_privilege
--------------------------
f
(1 row)
tina=# grant create on tablespace tbs_tina to sqluser; ---授权给sqluser
GRANT
tina=# select has_tablespace_privilege('sqluser','tbs_tina','create'); --sqluser可以使用tbs_tina表空间了
has_tablespace_privilege
--------------------------
t
(1 row)
tina=# select has_tablespace_privilege('tbs_tina','create'); ---当前用户postgres 拥有表空间tbs_tina的权限
has_tablespace_privilege
--------------------------
t
(1 row)
六、表空间大小查询:
pg_tablespace_size(oid) bigint 指定 OID 代表的表空间使用的磁盘空间
pg_tablespace_size(name) bigint 指定名字的表空间使用的磁盘空间
tina=# select oid,* from pg_tablespace;
oid | spcname | spcowner | spcacl | spcoptions
-------+------------+----------+------------------------------------------+------------
1663 | pg_default | 10 | |
1664 | pg_global | 10 | |
16525 | tbs_tina | 10 | {postgres=C/postgres,sqluser=C/postgres} |
(3 rows)
tina=# select pg_tablespace_size(16525)/1024 ||'KB'; ---表空间tbs_tina的oid为16525
?column?
----------
4KB
(1 row)
tina=# select pg_tablespace_size('tbs_tina')/1024||'KB'; ---也可以直接使用表空间名
?column?
----------
4KB
(1 row)
tina=# select pg_size_pretty(pg_tablespace_size('tbs_tina'));
pg_size_pretty
----------------
4096 bytes
(1 row)
七、表所在表空间查询
PostgreSQL 提供类似" \ "命令很方便得到相关信息,命令如下:
tina=# \d test1
Table "public.test1"
Column | Type | Modifiers
--------+-----------------------+----------------------------------------------------
id | integer | not null default nextval('test1_id_seq'::regclass)
name | character varying(10) |
tina=# \d t1
Table "public.t1"
Column | Type | Modifiers
--------+---------+-----------
id | integer |
Tablespace: "tbs_tina"
备注:如果这个表的表空间为当前数据库的默认表空间,那么上面则不会显示 Tablespace 信息。
7.1 查询数据库的默认表空间
tina=# select datname,dattablespace from pg_database where datname='tina';
datname | dattablespace
---------+---------------
tina | 1663
(1 row)
tina=# select oid,spcname from pg_tablespace where oid=1663;
oid | spcname
------+------------
1663 | pg_default
(1 row)
7.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',即可查找出位于当前数据库默认表空间的数据库表和索引。
7.3 查询不在默认表空间的表和索引
tina=> 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 >1664 order by a.relpages desc;
relname | relkind | relpages | pg_size_pretty | reltablespace | relowner
---------+---------+----------+----------------+---------------+----------
a | r | 0 | 0 bytes | 16525 | 33979
a | r | 0 | 0 bytes | 16525 | 33979
a | r | 0 | 0 bytes | 16525 | 33979
7.4 查询在某个表空间上的对象
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='tbs_tina' order by a.relpages desc;
relname | relkind | relpages | pg_size_pretty | reltablespace | relowner
---------+---------+----------+----------------+---------------+----------
t1 | r | 0 | 0 bytes | 16525 | 10
(1 row)
八、删除表空间
tina=# drop tablespace ind_tina;
ERROR: tablespace "ind_tina" is not empty
需要先清空表空间内的对象
tina=# drop index ind_t1;
DROP INDEX
tina=# drop tablespace ind_tina;
DROP TABLESPACE
九、改变表空间所指向目录位置
[root@oratest pgtina]# cp -R tbs_tina/ test_tina
tina=# \db tbs_tina
List of tablespaces
Name | Owner | Location
----------+----------+------------------
tbs_tina | postgres | /pgtina/tbs_tina
(1 row)
网上介绍的alter tablespace move 和修改pg_tablespace表的location位置都未验证成功
在数据库运维工作中,经常会有数据目录使用率较高需要调整的情况,通常会给数据库建立多个表空间,
并分别位于不同的盘上,这时需要做的工作就是调整库中现有表和索引的表空间
二、表空间用途
表空间就是一个简单的目录,其主要用途分两个:
1.单独扩展表空间用,一旦磁盘或分区被耗尽,可以创建一个表空间到其他磁盘或分区上面。
2.区分不同对象的存储位置,比如可将索引放入较快磁盘的表空间上,而将固定不变的数据放入较慢磁盘的表空间上。
三、表空间共享
与Oracle数据库中的表空间被独占不同,PostgreSQL的表空间是可以被共享的。
当创建了一个表空间后,这个表空间可以被多个数据库、表、索引等数据库对象使用。达到对象的分离与归类的目的。
在PostgreSQL中有两个系统自建表空间:pg_global和pg_default。
前者是系统全局表空间,存储了关键的共享系统目录。后者是系统全局表空间,存储了关键的共享系统目录。
后者是系统默认表空间,可通过set default tablespace=tablespacename来指定为其他表空间,在建立数据库、表、索引等数据库对象时,
若不指定表空间参数,则系统自动将对象创建到默认表空间中。
四、创建表空间:
mkdir tbs_tina
chmod 777 tbs_tina/
chown postgres:postgres tbs_tina/
create tablespace tbs_tina owner postgres location '/pgtina/tbs_tina';
create tablespace ind_tina owner postgres location '/pgtina/ind_tina';
create table t1(id int) tablespace tbs_tina;
create index ind_t1 on t1(id) tablespace ind_tina; ---可以将表和索引放在不同的表空间
五、用户表空间权限:
has_tablespace_privilege(user, tablespace, privilege) boolean 用户是否有访问表空间的权限 CREATE
has_tablespace_privilege(tablespace, privilege) boolean 当前用户是否有访问表空间的权限 CREATE
tina=# create user sqluser nosuperuser noreplication nocreatedb nocreaterole login encrypted password 'sqlpasswd'; --创建一个普通用户
CREATE ROLE
tina=# select has_tablespace_privilege('sqluser','tbs_tina','create'); --sqluser没有tbs_tina表空间的权限
has_tablespace_privilege
--------------------------
f
(1 row)
tina=# grant create on tablespace tbs_tina to sqluser; ---授权给sqluser
GRANT
tina=# select has_tablespace_privilege('sqluser','tbs_tina','create'); --sqluser可以使用tbs_tina表空间了
has_tablespace_privilege
--------------------------
t
(1 row)
tina=# select has_tablespace_privilege('tbs_tina','create'); ---当前用户postgres 拥有表空间tbs_tina的权限
has_tablespace_privilege
--------------------------
t
(1 row)
六、表空间大小查询:
pg_tablespace_size(oid) bigint 指定 OID 代表的表空间使用的磁盘空间
pg_tablespace_size(name) bigint 指定名字的表空间使用的磁盘空间
tina=# select oid,* from pg_tablespace;
oid | spcname | spcowner | spcacl | spcoptions
-------+------------+----------+------------------------------------------+------------
1663 | pg_default | 10 | |
1664 | pg_global | 10 | |
16525 | tbs_tina | 10 | {postgres=C/postgres,sqluser=C/postgres} |
(3 rows)
tina=# select pg_tablespace_size(16525)/1024 ||'KB'; ---表空间tbs_tina的oid为16525
?column?
----------
4KB
(1 row)
tina=# select pg_tablespace_size('tbs_tina')/1024||'KB'; ---也可以直接使用表空间名
?column?
----------
4KB
(1 row)
tina=# select pg_size_pretty(pg_tablespace_size('tbs_tina'));
pg_size_pretty
----------------
4096 bytes
(1 row)
七、表所在表空间查询
PostgreSQL 提供类似" \ "命令很方便得到相关信息,命令如下:
tina=# \d test1
Table "public.test1"
Column | Type | Modifiers
--------+-----------------------+----------------------------------------------------
id | integer | not null default nextval('test1_id_seq'::regclass)
name | character varying(10) |
tina=# \d t1
Table "public.t1"
Column | Type | Modifiers
--------+---------+-----------
id | integer |
Tablespace: "tbs_tina"
备注:如果这个表的表空间为当前数据库的默认表空间,那么上面则不会显示 Tablespace 信息。
7.1 查询数据库的默认表空间
tina=# select datname,dattablespace from pg_database where datname='tina';
datname | dattablespace
---------+---------------
tina | 1663
(1 row)
tina=# select oid,spcname from pg_tablespace where oid=1663;
oid | spcname
------+------------
1663 | pg_default
(1 row)
7.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',即可查找出位于当前数据库默认表空间的数据库表和索引。
7.3 查询不在默认表空间的表和索引
tina=> 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 >1664 order by a.relpages desc;
relname | relkind | relpages | pg_size_pretty | reltablespace | relowner
---------+---------+----------+----------------+---------------+----------
a | r | 0 | 0 bytes | 16525 | 33979
a | r | 0 | 0 bytes | 16525 | 33979
a | r | 0 | 0 bytes | 16525 | 33979
7.4 查询在某个表空间上的对象
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='tbs_tina' order by a.relpages desc;
relname | relkind | relpages | pg_size_pretty | reltablespace | relowner
---------+---------+----------+----------------+---------------+----------
t1 | r | 0 | 0 bytes | 16525 | 10
(1 row)
八、删除表空间
tina=# drop tablespace ind_tina;
ERROR: tablespace "ind_tina" is not empty
需要先清空表空间内的对象
tina=# drop index ind_t1;
DROP INDEX
tina=# drop tablespace ind_tina;
DROP TABLESPACE
九、改变表空间所指向目录位置
[root@oratest pgtina]# cp -R tbs_tina/ test_tina
tina=# \db tbs_tina
List of tablespaces
Name | Owner | Location
----------+----------+------------------
tbs_tina | postgres | /pgtina/tbs_tina
(1 row)
网上介绍的alter tablespace move 和修改pg_tablespace表的location位置都未验证成功
发表评论
-
pg 锁
2016-01-14 16:26 0pg 锁 ... -
postgresql 的三类日志
2016-01-14 15:59 18401一、PostgreSQL有3种日志: 1)pg_log(数据 ... -
pg存储过程--创建分区表
2016-01-13 15:46 01)将普通表改成按时间字段分区表 调用select fun_c ... -
pg常用自制shell脚本-tina
2016-01-13 15:30 48411)小型监控: 1.在pg库主机上部署,每5分钟执行一次,插入 ... -
postgresql 时间类型和相关函数
2016-01-13 10:41 5330今天来好好学习一下postgresql涉及时间的字段类型和一些 ... -
pg 定期vacuum和reindex
2016-01-07 14:56 8561定期vacuum和reindex: 一 ... -
pg 序列
2016-01-06 16:58 1576一、简介 一个序列对象通常用于为行或者表生成唯一的标识符。 ... -
pg 简单备份和恢复
2016-01-06 15:53 3722pg的备份和恢复 pg_dump ... -
ERROR: invalid page header in block 27073 of relation base/21078/45300926
2016-01-06 15:12 2111突然断网,检查后通知我们UPS断电,db所在主机重启 1、连上 ... -
pg_cancel_backend()和pg_terminate_backend()
2016-01-05 17:42 3515pg_cancel_backend()和pg_terminat ... -
canceling statement due to conflict with recovery
2016-01-05 17:12 1618报错: canceling statement due to ... -
postgresql dblink 使用
2015-12-31 14:33 2007dblink的使用 pg的跨库查询工具 select dbli ... -
root用户不能使用psql或者pg_dump等pg命令
2015-12-24 14:40 6951root用户不能使用psql或者pg_dump等pg命令 [ ... -
postgresql新建库2个常见报错
2015-12-22 16:43 6154今天使用pg建库发现两个报错: ERROR: new c ... -
安装postgresql 9.1.1
2015-12-22 16:25 609安装postgresql 9.1.1 ---版本自选,步骤相同 ... -
pgbadger监控安装和使用
2015-12-21 10:01 1997pgbadger监控安装和使用 https://github ... -
oracle,postgresql,mysql一些使用上的区别记录
2015-12-16 11:38 01.限制行数: select * from ta where ... -
postgresql存储过程实例:已审核证书存入临时表
2015-12-14 16:44 621存储过程实例: 需求: 思路:建立存储过程 代码逻辑: 1 ... -
pg 函数sfa_tmp_sleep()执行越来越慢-sql分析
2015-12-11 09:48 643pg 函数sfa_tmp_sleep()执行越来越慢 ... -
pgpool 主从流复制模式下的安装使用
2015-12-11 09:50 4089pgpool-II 是一个位于 PostgreSQL 服务器和 ...
相关推荐
提供了可以对表空间进行移动的方法,通过搬移表空间可大大降 低数据迁移的速度,提高工作效率。Oracle8i版本为实例讲解!
用psql创建用户和表空间
pgadmin教程-图解 pgAdmin能够检测到连接断开并且询问用户是否重新连接 对象浏览器,对象属性 服务器属性,数据库属性,数据表属性,约束属性,索引属性,触发器属性,函数属性,字段属性,表空间属性,模式属性
postgis-bundle-pg15x64-setup-3.3.2-2.exe是一个针对PostgreSQL 15和64位操作系统的安装程序,它将PostGIS扩展和PostgreSQL数据库一起打包安装,使得用户可以方便地在本地计算机上建立地理空间数据库。该安装程序还...
基于postgresql数据库,安装postgis插件后,创建空间数据表,并使用nodejs + express服务空间数据表,在前端采用Cesium加载空间数据,实现空间数据可视化
本文的目的是在Galois场GF(q),q = 7上的射影3空间中构造曲面和完整弧。A(k,n)-arc是一组k点; 他们中没有n + 1是共面的。 如果((k + 1,n)-arc中不包含A(k,n)-arc,则它是完整的。 在这项工作中,构造了...
一、简介 PostgreSQL 提供了多个系统管理函数来查看表,索引,表空间及数据库的大小,下面详细介绍一下。 二、数据库对象尺寸函数 函数名 返回类型 描述 pg_column_size(any) int 存储一个指定的数值需要的字节...
本安装包中包含pgadmin4-6.8 版本;其中包含了x86、x64位的安装包;可以用来访问postgis空间数据库
dr:提供用于布置表列顺序的工具,以优化ActiveRecord迁移和pg_dump生成SQL模式文件中的磁盘空间使用。 概述和有关PostgreSQL内部原理在进行了描述。 安装 将此行添加到您的应用程序的Gemfile中: gem 'pg_column_...
电压空间矢量切换时间仿真模型,用于比较矢量切换时间的比较
将oracle空间数据转到postgresql中,主要是对两个数据库的函数进行迁移
表空间信息 记忆设定 表和索引的大小和信息 交易编号 复制插槽 公共架构信息 未记录表 等等,请检查示例报告 PG收集器报告标题 PG收集器报告示例 所有样本报告。 PG收集器输出 报告名称: PG Collector脚本将使用...
需要安装Postgis插件都懂的吧,资源是postgreSQL的关于空间数据字段the_geom的一些SQL示例,写的比较模糊,主要是想网上留一份以防丢失。有需要或不明白的找我吧,我研究的也不多,基本的会一点,有需要的可以一起...
pg_bloat_check pg_bloat_check是一个脚本,用于为PostgreSQL表和/或索引提供膨胀报告。 它至少需要Python 2.6中和pgstattuple的contrib模块- 注意,在非常大的数据库或具有许多表的数据库上,使用pgstattuple检查...
D4PG阶段3 一种D4PG实现,用于启用操纵器的航天器,任务是捕获和稳定一块空间碎片。 完全经过模拟训练,它将被部署到卡尔顿大学的航天器邻近操作测试平台实验设施。
java链接postgresql数据库的jar包,9.3版本。
pg_search - 利用PostgreSQL的全文搜索构建ActiveRecord的命名空间
一、恢复磁盘空间: 一、恢复磁盘空间: 一、恢复磁盘空间: 一、恢复磁盘空间: . 56 二、更新规划器统计: 二、更新规划器统计: 二、更新规划器统计: 二、更新规划器统计: 二、更新规划器统计: .57 四、定期...
该系统表记录了数据表、索引(仍然需要参阅pg_index)、序列、视图、复合类型和一些特殊关系类型的元数据。注意:不是所有字段对所有对象类型都有意义。 名字 类型 引用 描述 relname name 数据类型名字...
h3-pg:H3的PostgreSQL绑定,H3是分层的六角形地理空间索引系统