`
LJ你是唯一LT
  • 浏览: 243000 次
社区版块
存档分类
最新评论

pg 表空间

阅读更多
一、说明
   在数据库运维工作中,经常会有数据目录使用率较高需要调整的情况,通常会给数据库建立多个表空间,
并分别位于不同的盘上,这时需要做的工作就是调整库中现有表和索引的表空间

二、表空间用途
  表空间就是一个简单的目录,其主要用途分两个:
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位置都未验证成功
0
0
分享到:
评论

相关推荐

    如何移动Oracle数据库的表空间

    ### 如何移动Oracle数据库的表空间 #### 一、引言 在Oracle数据库管理中,随着业务需求的变化,经常需要将数据库中的数据从一个位置移动到另一个位置。当数据量较大时,传统的导出导入方法可能无法满足效率要求。...

    用psql创建用户和表空间

    根据提供的文件信息,本文将详细解释如何在 PostgreSQL 数据库环境中使用 psql 命令行工具来创建数据库用户以及表空间。尽管示例代码中似乎包含了一些 Oracle 数据库的语法,我们将聚焦于 PostgreSQL 的相关操作。 ...

    PgAdmin4安装包

    9. **扩展支持**: PostgreSQL支持众多扩展,如PostGIS(地理空间数据处理),通过PgAdmin4可以方便地安装和管理这些扩展。 总之,PgAdmin4作为PostgreSQL的重要辅助工具,以其丰富的功能和易用性,成为了数据库管理...

    pg055axibridgepcie_055PG.com_055pg.com_www.055PG.COM_https//:055

    1. AXI接口的基础知识:包括AXI的组成、主要信号、数据宽度和地址空间管理。 2. PCIe协议简介:包括PCIe的版本、数据速率、通道数量以及PCIe层次结构。 3. AXI到PCIe桥接原理:描述如何将AXI总线信号转换为PCIe事务...

    PostGIS空间数据表建立及Cesium可视化空间数据.docx

    空间数据表建立及Cesium可视化空间数据 本文档介绍了如何基于PostgreSQL数据库,安装PostGIS插件后,创建空间数据表,并使用Node.js + Express服务空间数据表,在前端采用Cesium加载空间数据,实现空间数据可视化。...

    pgadmin3客户端

    1. **数据库管理**:pgAdmin3允许用户创建、删除和修改数据库,同时提供数据库对象的浏览和编辑功能,如表、视图、索引、触发器、规则和序列。 2. **查询工具**:内置的SQL编辑器支持编写、执行和保存SQL语句,还...

    GP pg_catalog查询.docx

    3. **pg_class**: 这是最重要的系统目录表之一,它记录了所有表(包括普通表和视图)和其他类型的关系(如索引、序列、表空间等)。运行`SELECT * FROM pg_catalog.pg_class;`以获取数据库中的所有对象信息。 4. **...

    西门子 PG2000中文版软件

    安装PG2000中文版软件时,需确保电脑满足软件的系统需求,例如操作系统版本、内存大小和硬盘空间等。安装过程中可能需要西门子的授权密钥或者序列号来激活软件,以确保其正常运行。 7. **学习与支持**: 西门子...

    ora2pg_spatial.pdf

    在谈论将Oracle数据库中的空间数据迁移到PostgreSQL的过程中,重要的知识点包括Ora2Pg工具的使用、Oracle和PostgreSQL在空间数据类型和结构上的差异、以及迁移过程中需要注意的一些关键技术细节。 首先,Ora2Pg是一...

    oracl创建表空间、用户、授权及数据导入和PostgreSQL导入数据

    本篇文章将深入探讨如何在Oracle中创建表空间、用户以及授权,并讲解如何进行数据导入,同时也会简要介绍在PostgreSQL中导入数据的过程。 首先,我们来看Oracle数据库中的操作: 1. **创建表空间**:在Oracle中,...

    pg_statsinfo-12.0.tar.gz

    1. **性能监控**:pg_statsinfo能够持续跟踪数据库的运行状态,包括查询执行时间、锁等待、表空间使用、索引使用情况等关键指标,这些信息对于识别性能瓶颈和优化数据库配置至关重要。 2. **日志分析**:通过对...

    postgis-bundle-pg15x64-setup-3.3.2-2.exe

    postgis-bundle-pg15x64-setup-3.3.2-2.exe是一个针对PostgreSQL 15和64位操作系统的安装程序,它将PostGIS扩展和PostgreSQL数据库一起打包安装,使得用户可以方便地在本地计算机上建立地理空间数据库。该安装程序还...

    pg8139mac地址硬件修改

    例如,“DRVSPACE.BIN”通常是DOS时代的驱动空间分配工具,“8139*.cfg”可能是针对不同型号或版本的Realtek 8139网络控制器的配置文件,而“COMMAND.COM”是DOS操作系统的命令行解释器,“NT351.EXE”可能是Windows...

    pgRouting-1.03_pg-8.4.2.zip

    总结来说,pgRouting-1.03_pg-8.4.2.zip是pgRouting在PostGIS 8.4.2环境下的一个版本,特别适合在Windows系统上进行地理空间网络分析。虽然较老,但它仍然提供了许多实用的功能,并且对于需要与旧系统兼容的项目,该...

    pg_bigm-1.2-20200228.gz

    需要注意的是,虽然 pg_bigm 可以带来性能提升,但创建和维护这样的索引也会占用额外的存储空间,并可能增加插入和更新操作的开销。因此,在实际应用中,需要根据具体业务需求和数据规模进行权衡。 在业务系统中,...

    PG螺纹标准

    - 在选择PG螺纹产品时,应根据系统的压力等级、介质性质以及安装空间来确定合适的PG螺纹规格。 5. **配合和使用注意事项**: - 为了保证最佳的密封效果,PG螺纹在装配时通常需要配合使用密封圈或者螺纹胶。 - 在...

    linux搭建postgresql、postgis、pg_pathman环境步骤以及需要的软件包

    - 使用pg_pathman进行分区管理,根据具体需求创建分区表。 通过以上步骤,你将在Linux环境中成功搭建起PostgreSQL、PostGIS和pg_pathman的环境。现在你可以开始导入地理空间数据,并利用这个强大的组合来处理、...

    pgAdmin3客户端

    1. **数据库对象管理**:pgAdmin3允许用户轻松创建、修改和删除数据库、表、视图、索引、函数、触发器等各种数据库对象。它提供了直观的图形化界面,使得操作直观易懂。 2. **查询工具**:内置的SQL编辑器支持语法...

    linux中pg11的包

    - PostgreSQL支持许多社区开发的扩展,如PostGIS(地理空间数据处理)和PL/pgSQL(过程语言)。 - 安装扩展通常通过`CREATE EXTENSION`命令完成。 13. **复制与高可用**: - 基于流复制(Streaming Replication...

    pgadmin4-6.8 安装包

    本安装包中包含pgadmin4-6.8 版本;其中包含了x86、x64位的安装包;可以用来访问postgis空间数据库

Global site tag (gtag.js) - Google Analytics