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咨询老师