当前位置: fuhua-pet->优技培训 > PG技术大讲堂 - Part 10:PostgreSQL数据库管理

PG技术大讲堂 - Part 10:PostgreSQL数据库管理

2023-03-16作者:firstyuding来源:www.firstyuding.com

PostgreSQL从小白到专家,是从入门逐渐能力提升的一个系列教程,内容包括对PG基础的认知、包括安装使用、包括角色权限、包括维护管理、、等内容,希望对热爱PG、学习PG的同学们有帮助,欢迎持续关注CUUG PG技术大讲堂。

Part 10:PostgreSQL数据库管理

内容1:PostgreSQL数据库结构

内容2:PostgreSQL数据库级权限管理

内容3:PG数据库级环境参数设置

内容4:PostgreSQL数据库级属性修改


10.1、数据库结构

数据库集簇逻辑结构每个数据库存储的对象(表、索引、视图等等)是独立的、私有的,每个数据库类似于每个房间,从房间中取东西,就需要到房间里面;同理,要访问某个数据库中的对象,就需要登录到指定的数据库中。

PostgreSQL数据库结构

数据库集群是由PostgreSQL服务器管理的数据库的集合。PostgreSQL中的“数据库集群”一词并不意味着“一组数据库服务器”。PostgreSQL服务器在单个主机上运行,并管理单个数据库群集。 

 数据库是数据库对象的集合。在关系数据库理论中,数据库对象是用来存储或引用数据的数据结构。堆(heap)表是一个典型的例子,它有很多类似于索引、序列、视图、函数等等。在PostgreSQL中,数据库本身也是数据库对象,在逻辑上彼此分离。所有其他数据库对象(如表、索引等)都属于各自的数据库。

PostgreSQL数据库属主

· Postgres中的数据库属主属于创建者,只要有createdb的权限就可以创建数据库,数据库属主不一定拥有存放在该数据库中其它用户创建的对象的访问权限。

· 数据库在创建后,允许public角色连接,即允许任何人连接。

· 数据库在创建后,不允许除了超级用户和owner之外的任何人在数据库中创建schema。

· 数据库在创建后,会自动创建名为public的schema,这个schema的all权限已经赋予给了public角色,即允许任何人在里面创建对象,但对己存在的其它用户的表不具有任何权限。


10.2、数据库权限

CREATE:可以在指定数据库创建schema的权限

CONNECT:可以连接到指定数据库的权限TEMPORARY:可以创建临时表的权限ALL:指定数据库所有的权限语法:

GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] } 

ON DATABASE 数据库名称 [, ...] 

TO role_specification [, ...] [ WITH GRANT OPTION ]

由于数据库在创建后,允许public角色连接,即允许任何人连接。所以如果要取消某个用户对指定数据库连接的权限,需要先取消public的连接权限,再取消该用户的连接权限。

--授权用户连接数据库的权限

grant connect on database db_name to user_name;

--撤销用户连接数据库的权限

revoke connect on database db_name from public;

revoke connect on database db_name from user_name;

--查看哪些用户有某个数据库的connect权限

select datname,datacl from pg_database where datname='db_name';


10.3、数据库环境设置

PostgreSQL参数设置分为实例级、数据库级、用户级和会话级,而有些参数可以在所有级别中设置,优先级顺序为会话级>用户级>数据库级>实例级。

数据库参数配置语法:

ALTER DATABASE 名称 SET 配置参数 { TO | = } { 值 | DEFAULT }

ALTER DATABASE 名称 SET 配置参数 FROM CURRENT

ALTER DATABASE 名称 RESET 配置参数

ALTER DATABASE 名称 RESET ALL

配置示例(一):

--设置数据库搜索路径:

alter database postgres set search_path to "$user", public, schema_name;

--配置连接某个库时可使用的工作内存

alter database postgres set work_mem = '8MB'; 

 --配置连接某个库时可使用的维护内存

alter database postgres set maintenance_work_mem TO '256MB';

配置示例(二):

--配置连接某个库后使用的时区

alter database postgres set TimeZone to cet;alter database postgres set DateStyle to SQL, DMY;(重新登录生效)

--配置连接某个库后执行语句最多时长(执行1秒超时)

alter database postgres set statement_timeout =1000;

--配置连接某个库后默认的客户端编码,配置客户端编码为gbk,适用于数据库编码为utf8,应用程序编码为gbk的应用

alter database postgres set client_encoding to gbk;

配置示例(三):

--配置某个库使用日志记录级别(设置后,对这个数据库的访问不记录日志)

alter database postgres set log_statement=none;

--配置连接某个库后的wal日志写盘级别(设置后,该库的更新操作只要求本地提交)

alter database postgres set synchronous_commit to local;

--配置连接某个库后禁用某个规划器(禁用indexonlyscan扫描)

alter database postgres set enable_indexonlyscan to off;

配置示例(四):

--配置连接某个库后执行出错时中断连接(对新会话生效)

alter database postgres set exit_on_error to on;

--重新连接后

select pg_backend_pid();

--执行错误会导致连接中断

select * from d1;

配置示例(五):

--查看所有个性化配置

\drds

--查询数据库的连接数限制只能查看数据字典表

select datname,datconnlimit from pg_database

--设置某个个性化设置为默认值

ALTER DATABASE postgres reset exit_on_error;

--设置所有个性化设置为默认值

ALTER DATABASE postgres reset ALL;


10.4、数据库属性修改

数据库的属性我们可以进行修改,修改范围是数据库名字、属主、表空间。

ALTER DATABASE 名称 RENAME TO 新的名称

ALTER DATABASE 名称 OWNER TO { 新的属主 | CURRENT_USER | SESSION_USER }

ALTER DATABASE 名称 SET TABLESPACE 新的表空间

示例:

--修改数据库名字

ALTER DATABASE newdb2 RENAME TO newdb3;

--修改数据库属主

ALTER DATABASE newdb3 OWNER TO u1;

--修改新表空间的名字

ALTER DATABASE newdb3 SET TABLESPACE new_tbl;

以上就是Part 10 - PostgreSQL数据库管理 的内容,欢迎进群一起探讨交流QQ交流群:752027153

微信交流群:联系客服拉你进微信PG交流群

钉钉交流群:35822460,钉钉群有专门讲解公开课

往期课程,联系cuug咨询老师

  • PostgreSQL手册-》sql语法-》语法结构-》操作符优先级
  • PostgreSQL手册-》sql语法-》值表达式-》聚合表达式
  • PostgreSQL手册-》sql语法-》值表达式-》函数调用
  • 8月份证书来啦!工信人才PostgreSQL能力提升证书!
  • PostgreSQL技术大讲堂 - 第26讲:PG-FDW部署
  • 工业和信息化部人才交流中心与国家发展和改革委员会价格监测中心签署战略合作协议
  • 国内唯一 · PostgreSQL认证红头文件(8月) - 工信人才 [2023] 185号
  • PostgreSQL和MySQL对比:数据复制
  • 国内唯一PostgreSQL认证培训红头文件【2023年】
  • PostgreSQL技术大讲堂 - 第27讲:Oracle-FDW部署
  • PG中级认证 红头文件
  • PostgreSQL认证培训红头文件【2023-08】
  • OceanBase 数据库简介概述
  • OceanBase 体系结构及系统架构
  • OceanBase与Oracle对比:SQL引擎 比较
  • OceanBase与Oracle对比:OB暂不支持的功能
  • OceanBase与Oracle对比:数据字典视图和性能视图
  • OceanBase与Oracle对比:备份恢复
  • OceanBase与Oracle对比:安全特性 比较
  • OceanBase与Oracle对比:数据库对象管理
  • OceanBase与Oracle对比:字符集和字符序【表格】
  • OceanBase与Oracle对比:过程性语言 比较
  • OceanBase与Oracle对比:SQL语法 比较
  • OceanBase与Oracle对比:内建函数【表格比较】
  • OceanBase与Oracle对比:SQL 数据类型【表格比较】
  • OceanBase企业版和社区版的功能差异
  • PolarDB for PostgreSQL 云原生数据库的优势
  • PolarDB for PostgreSQL简介
  • PostgreSQL技术大讲堂 - 第28讲:索引内部结构
  • 7月20日 恭喜CUUG 韩同学获得Oracle OCP 19c证书
  • 8月,CUUG常同学PostgreSQL能力评价认证证书!
  • 8月,CUUG代同学PostgreSQL能力评价认证证书!
  • 8月,CUUG刘同学PostgreSQL能力评价认证证书!
  • 8月,CUUG马同学PostgreSQL能力评价认证证书!
  • 8月,CUUG王同学PostgreSQL能力评价认证证书!
  • 8月,CUUG于同学PostgreSQL能力评价认证证书!
  • 8月,CUUG臧同学PostgreSQL能力评价认证证书!
  • 8月,CUUG赵同学PostgreSQL能力评价认证证书!
  • PostgreSQL 14中文手册发布
  • 恭喜CUUG 陈同学获得Oracle OCP 19c证书
  • 恭喜CUUG 赵同学获得Oracle OCP 19c证书
  • PG认证--PostgreSQL管理员能力提升培训班
  • PolarDB For PostgreSQL跨机并行查询功能
  • PolarDB PostgreSQL版产品架构(图)
  • PolarDB-PG 架构之存储计算分离架构
  • PolarDB MySQL版和PostgreSQL版相同产品架构
  • PolarDB分布式版产品架构
  • PolarDB数据库优势何在
  • 什么是PolarDB数据库
  • PolarDB数据库有几种引擎