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

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

2023-03-16作者:gbaiyou来源:www.gbaiyou.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咨询老师

  • PolarDB-X高可用与容灾(WIP)
  • 大连财经学院 - 国产数据库工作室揭牌仪式圆满成功(CUUG)
  • 河北工程技术学院 - 国产数据库工作室揭牌仪式圆满成功
  • postgresql从小白到高手 - 第38讲:数据库备份
  • 11月18日直播!杭州峰会大咖晚宴煮酒论英雄+PG技术大讲堂(34)
  • 11月27日,CUUG新鲜出炉的Oracle DB 19C OCP证书
  • Oracle 19c OCM认证好考吗?CUUG OCM成绩公布
  • Oracle 19c OCM认证考试成绩出炉!- CUUG WDP培训中心
  • 菏泽学院 - 国产数据库工作室揭牌仪式圆满成功
  • 2023年12月,PostgreSQL认证培训红头文件【工信部人才交流中心】
  • 2024年第13届PostgreSQL中国技术大会来啦!
  • OCP认证能不能在家中考试,不去VUE考点考试吗?
  • 恭喜CUUG Guo同学以较高分数通过19c OCM认证考试!
  • Oracle数据库加入AI功能,Database 23c改名为Database 23ai
  • DB-Engines:PostgreSQL is the DBMS of the Year 2023
  • PostgreSQL技术大讲堂 - 第44讲:pg流复制部署
  • PostgreSQL技术大讲堂 - 第45讲:poc-tpcc测试
  • PostgreSQL技术大讲堂 - 第46讲:poc-tpch测试
  • PostgreSQL技术大讲堂 - 第47讲:JMETER工具使用
  • PostgreSQL技术大讲堂 - 第48讲:PG高可用实现keepalived
  • PostgreSQL技术大讲堂 - 第50讲:PG分区表管理
  • 3月30日,工信部人才交流中心PostgreSQL认证考试顺利结束
  • 2024年4月8日,工信人才发布红头文件:PostgreSQL数据库管理人才研修与评测班
  • 恭喜CUUG入选2024年工业和信息化重点领域人才能力评价支撑机构
  • 天津职业技术师范大学《PolarDB开源数据库工作室》授牌仪式顺利完成
  • 温州大学国产开源数据库工作室成功举办PostgreSQL技能培训活动
  • 2024-02-02,恭喜CUUG 刘同学通过Oracle考试获得OCP 19c证书
  • 5月16日,开源驱动教育创新研讨会(青岛站)成功举办-CUUG
  • postgresql技术大讲堂 - 第39讲:数据库完全恢复
  • 温州大学 - 开源国产数据库工作室成立揭牌仪式圆满结束
  • postgresql从小白到高手 - 第37讲:postgres物理备份和恢复概述
  • postgresql从小白到高手 - 第36讲:postgresql逻辑备份
  • postgresql从小白到高手 - 第35讲:中间件PgBouncer部署
  • PostgreSQL PGCP是什么级别的认证?
  • PGCP中级认证考试的三个维度考核
  • 国内PostgreSQL认证,工信部人才交流中心PG技术能力提升培训认证
  • 今天(5月6日),CUUG 赵同学收到19c OCM认证考试证书!
  • 5月17日,PolarDB开源数据库沙龙(青岛站)成功举办-CUUG
  • postgresql技术大讲堂 - 第40讲:数据库不完全恢复
  • PostgreSQL技术大讲堂 - 第41讲:表空间备份与恢复
  • PostgreSQL技术大讲堂 - 第42讲:pg_rman部署与使用
  • PostgreSQL技术大讲堂 - 第43讲:流复制原理
  • 1月17日阿里云PolarDB开发者大会PolarDB DevCon
  • 阿里云PolarDB开发者大会圆满结束,CUUG两次获奖
  • 2024-1-12,恭喜CUUG 王同学获得Oracle OCP证书
  • 2024年首张Oracle OCP证书-CUUG胡同学
  • 北京培黎职业学院 - PolarDB开源国产数据库工作室成立揭牌 - CUUG
  • PostgreSQL技术大讲堂 - 第51讲:老陈与德哥聊一聊数据库调优
  • oracle ocp证书有效期多长时间
  • PostgreSQL技术大讲堂 - 第52讲:与德哥背后的男人们聊如何实现自动性能调优