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

本文涉及的产品
云原生数据库 PolarDB PostgreSQL 版,标准版 2核4GB 50GB
云原生数据库 PolarDB MySQL 版,通用型 2核8GB 50GB
简介: PG技术大讲堂 PostgreSQL数据库管理

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数据库管理 的内容,欢迎进群一起探讨交流,钉钉交流群:35,82,24,60,钉钉群有专门讲解公开课

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

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
打赏
0
0
1
0
118
分享
相关文章
【SQL技术】不同数据库引擎 SQL 优化方案剖析
不同数据库系统(MySQL、PostgreSQL、Doris、Hive)的SQL优化策略。存储引擎特点、SQL执行流程及常见操作(如条件查询、排序、聚合函数)的优化方法。针对各数据库,索引使用、分区裁剪、谓词下推等技术,并提供了具体的SQL示例。通用的SQL调优技巧,如避免使用`COUNT(DISTINCT)`、减少小文件问题、慎重使用`SELECT *`等。通过合理选择和应用这些优化策略,可以显著提升数据库查询性能和系统稳定性。
74 9
【Meetup回顾 第1期】竟是这样的国产数据库,YashanDB技术内幕曝光
YashanDB是一款基于统一内核,支持单机/主备、共享集群、分布式等多种部署方式,覆盖OLTP/HTAP/OLAP交易和分析混合负载场景的新型数据库系统;YashanDB同时提供开发平台、运维平台和迁移平台3大工具平台以满足数据全生命周期管理。
40 2
【Meetup回顾 第1期】竟是这样的国产数据库,YashanDB技术内幕曝光
瑶池数据库大讲堂|PolarDB HTAP:为在线业务插上实时分析的翅膀
瑶池数据库大讲堂介绍PolarDB HTAP,为在线业务提供实时分析能力。内容涵盖MySQL在线业务的分析需求与现有解决方案、PolarDB HTAP架构优化、针对分析型负载的优化(如向量化执行、多核并行处理)及近期性能改进和用户体验提升。通过这些优化,PolarDB HTAP实现了高效的数据处理和查询加速,帮助用户更好地应对复杂业务场景。
1月17日|阿里云云谷园区,PolarDB V2.0技术沙龙,畅聊国产数据库
为了助力国产化项目顺利推进,阿里云邀请企业开发者和数据库负责人到云谷园区,与PolarDB V2.0技术专家面对面交流。扫描海报二维码报名,我们将根据信息为您申请入园。欢迎参与,共同探讨PolarDB的最新技术和应用!
解密时序数据库的未来:TDengine Open Day技术沙龙精彩回顾
在数字化时代,开源已成为推动技术创新和知识共享的核心力量,尤其在数据领域,开源技术的涌现不仅促进了行业的快速发展,也让更多的开发者和技术爱好者得以参与其中。随着物联网、工业互联网等技术的广泛应用,时序数据库的需求愈发强烈,开源的兴起更是为这一技术的创新与普及提供了强有力的支持。
52 3
|
2月前
|
学习 MongoDB:打开强大的数据库技术大门
MongoDB 是一个基于分布式文件存储的文档数据库,由 C++ 编写,旨在为 Web 应用提供可扩展的高性能数据存储解决方案。它与 MySQL 类似,但使用文档结构而非表结构。核心概念包括:数据库(Database)、集合(Collection)、文档(Document)和字段(Field)。MongoDB 使用 BSON 格式存储数据,支持多种数据类型,如字符串、整数、数组等,并通过二进制编码实现高效存储和传输。BSON 文档结构类似 JSON,但更紧凑,适合网络传输。
84 15
选择适合自己的数据库多实例负载均衡技术
【10月更文挑战第23天】选择适合自己的数据库多实例负载均衡技术需要全面考虑多种因素。通过深入的分析和评估,结合自身的实际情况,能够做出明智的决策,为数据库系统的高效运行提供有力保障。
152 61
java版药品不良反应智能监测系统源码,采用SpringBoot、Vue、MySQL技术开发
基于B/S架构,采用Java、SpringBoot、Vue、MySQL等技术自主研发的ADR智能监测系统,适用于三甲医院,支持二次开发。该系统能自动监测全院患者药物不良反应,通过移动端和PC端实时反馈,提升用药安全。系统涵盖规则管理、监测报告、系统管理三大模块,确保精准、高效地处理ADR事件。
147 1
【技术开发】接口管理平台要用什么技术栈?推荐:Java+Vue3+Docker+MySQL
该文档介绍了基于Java后端和Vue3前端构建的管理系统的技术栈及功能模块,涵盖管理后台的访问、登录、首页概览、API接口管理、接口权限设置、接口监控、计费管理、账号管理、应用管理、数据库配置、站点配置及管理员个人设置等内容,并提供了访问地址及操作指南。
MySQL自增ID耗尽应对策略:技术解决方案全解析
在数据库管理中,MySQL的自增ID(AUTO_INCREMENT)属性为表中的每一行提供了一个唯一的标识符。然而,当自增ID达到其最大值时,如何处理这一情况成为了数据库管理员和开发者必须面对的问题。本文将探讨MySQL自增ID耗尽的原因、影响以及有效的应对策略。
255 3

热门文章

最新文章