数据库操作

​专栏内容：

  • postgresql内核源码分析
  • 手写数据库toadb
  • 并发编程

​开源贡献：

  • toadb开源库

个人主页：我的主页
管理社区：开源数据库
座右铭：天行健，君子以自强不息；地势坤，君子以厚德载物.

系列文章

  • 入门准备
  • postgrersql基础架构
  • 快速使用
  • 初始化集群
  • 数据库服务管理
  • psql客户端使用
  • 创建数据库
  • 数据库操作

前言

postgresql 数据库是一款通用的关系型数据，在开源数据库中能与商业数据媲美，在业界也越来越流行。

因为是开源数据库，不仅公开源码，还有很多使用案例，好用的插件，所以它的慢慢变成了数据库的先驱和标准，通过postgresql可以很好从使用到原理，彻底搞懂；

如果是学习编程，也可以学到丰富的编程知识，数据结构，编程技巧，它里面还有很多精妙的架构设计，分层思想，可以灵活定制的思想。

本专栏主要介绍postgresql 入门使用，数据库维护管理，通过这些使用来了解数据库原理，慢慢了解postgresql是什么样的数据库，能做那些事情，以及如何做好服务，最关键的是这些知识都是面试的必备项。

概述

本文主要分享数据库系统中最基本的管理单位database，对它的一系列管理操作，主要从以下几方面展开：

  • 查询所有数据库
  • 进入指定数据库
  • 创建数据库
  • 修改数据库
  • 删除数据库
  • 查看数据库的统计

查看所有数据库

当作为DBA进行维护时，我们需要知道当前集群中有多少数据库，查看方法常用以下几种；

  • 通过SQL查询数据字典
  • 通过psql命令查看数据库概要列表

通过SQL查看数据字典

在数据库系统中，所有数据的组织信息，我们统称为数据字典，数据字典呈现出来的逻辑形式，就是一系列的表，因为它是数据库系统自动生成，并且自动维护的，我们也叫做系统表。

对于database的信息，也记录在一张系统表当中，它就是 pg_database, 先来看一下它的表中有那些字段；

postgres=# \d pg_database Table "pg_catalog.pg_database" Column | Type| Collation | Nullable | Default----------------+-----------+-----------+----------+--------- oid| oid | | not null | datname| name| | not null | datdba | oid | | not null | encoding | integer | | not null | datlocprovider | "char"| | not null | datistemplate| boolean | | not null | datallowconn | boolean | | not null | datconnlimit | integer | | not null | datfrozenxid | xid | | not null | datminmxid | xid | | not null | dattablespace| oid | | not null | datcollate | text| C | not null | datctype | text| C | not null | daticulocale | text| C || daticurules| text| C || datcollversion | text| C || datacl | aclitem[] | ||Indexes:"pg_database_oid_index" PRIMARY KEY, btree (oid), tablespace "pg_global""pg_database_datname_index" UNIQUE CONSTRAINT, btree (datname), tablespace "pg_global"Tablespace: "pg_global"

各字段的含义解释如下：

字段名描述
oid数据库的唯一标识符，它在系统内部使用；
datname数据库的名称；
datdba数据库所有者的用户OID（对象标识符）；
encoding数据库使用的字符编码；
datlocprovider此数据库的区域设置提供程序：= libc，= icuci；
datistemplate这是一个标志，指示数据库是否是一个模板。如果为 true，则此数据库可由任何具有权限的用户克隆;如果为 false，则只有超级用户或数据库所有者才能克隆它。
datallowconn如果为 false，则没有人可以连接到此数据库。这用于保护数据库不被更改。
datconnlimit这是数据库当前打开的连接数限制。-1 表示没有限制，-2 表示数据库无效。
datfrozenxid此数据库中的比此值大的所有事务 ID都已替换为冻结事务ID；用于事务号的回收机制；
datminmxid此数据库中的比引值大的所有multixact ID 都已替换为事务ID；用于事务号的回收机制；
dattablespace数据库使用的表空间OID（对象标识符）。
datcollate此数据库的LC_COLLATE；
datctype此数据库的LC_CTYPE；
daticulocale此数据库的 ICU 区域设置 ID；
daticurules此数据库的 ICU 排序规则；
datcollversion特定于提供程序的排序规则版本。这在创建数据库时记录，然后在使用数据库时进行检查，以检测排序规则定义中可能导致数据损坏的更改。
datacl访问权限;

重点了解几个字段，在系统内部每个数据库对象都有唯一标识 OID,也就是一个编号，对应一个表空间，也就是存储层次；另外还有访问权限的限制，并不是随意都可以访问；
特别的，我们需要知道，database的所有者是谁，也就是datdba字段，因为它可是这个database的老大；

好了，了解之后，就很简单，我们查询一下就知道当前数据库系统有多少个database了；

db_factory=> select oid,datname,datdba,dattablespace , datacl from pg_database ;oid| datname | datdba | dattablespace | datacl-------+-------------+--------+---------------+------------------------------------------------ 5 | postgres| 10 |1663 | 1 | template1 | 10 |1663 | {=c/senllang,senllang=CTc/senllang} 4 | template0 | 10 |1663 | {=c/senllang,senllang=CTc/senllang} 16446 | db_factory1 | 10 |1663 | 16445 | db_factory|16451 |1663 | {=Tc/manager,manager=CTc/manager,hr=C/manager}(5 rows)

刚说到访问权限，那么对于系统表的访问权限，因为是字典嘛，默认是所有用户都有权限；

psql查看数据库列表

psql是常用的命令行客户端，它本身提供了很多简单的操作命令，比如查看数据库的列表就是 \l, 在之前分享中也多次用过；

db_factory=> \lList of databasesName |Owner | Encoding | Locale Provider | Collate |Ctype| ICU Locale | ICU Rules | Access privileges-------------+----------+----------+-----------------+-------------+-------------+------------+-----------+----------------------- db_factory| manager| UTF8 | icu | en_US.UTF-8 | en_US.UTF-8 | en-US| | =Tc/manager+ ||| | | || | manager=CTc/manager+ ||| | | || | hr=C/manager db_factory1 | senllang | UTF8 | icu | en_US.UTF-8 | en_US.UTF-8 | en-US| | postgres| senllang | UTF8 | icu | en_US.UTF-8 | en_US.UTF-8 | en-US| | template0 | senllang | UTF8 | icu | en_US.UTF-8 | en_US.UTF-8 | en-US| | =c/senllang+ ||| | | || | senllang=CTc/senllang template1 | senllang | UTF8 | icu | en_US.UTF-8 | en_US.UTF-8 | en-US| | =c/senllang+ ||| | | || | senllang=CTc/senllang(5 rows)

可以看到执行结果，数量是一样，但是信息比从pg_database查到的少一些，它主要提供一个概要信息，如果需要详细信息还是要从系统表中查看。

进入指定数据库

在每次登录时，都是需要指定某个数据库；当然登录的方式可以是图形化客户端，或者是JDBC的连接，也可以是psql；
比如通过psql进入数据库 db_factory ，用户为 hr;

[senllang@hatch bin]$ ./psql -d db_factory-U hrpsql (16beta1)Type "help" for help.db_factory=>

创建数据库

CREATE DATABASE db_factory;

创建数据库详细步骤和方面，已经在之前分享过了，这里不再重复，查看本专栏内容；

修改数据库

很少有人修改数据库的属性，因为数据库一般都是提前规划好的，而且影响比较大。 但是作为DBA需要了解，有那些属性是可以修改，以及修改带来的影响会是什么。

数据库可以被修改的大致主要有以下几类型：

  • 修改数据库的属性；
  • 修改数据库的名称；
  • 修改数据库的表空间；
  • 修改数据库的拥有者onwer；
  • 数据库运行时参数

当然做这些操作时，当前数据库用户为超级管理员用户或者拥有者;

数据库属性修改

数据库属性中的以下值可以进行修改

  • ALLOW_CONNECTIONS 连接属性；
  • CONNECTION LIMIT 连接数量限制；
  • IS_TEMPLATE 模版属性
    这些参数就是pg_database系统表中所列的内容，影响数据库的访问；

数据库名称修改

数据库名称变更

postgres=# \c postgres managerYou are now connected to database "postgres" as user "manager".postgres=> alter database db_factory rename to db_factory2;ALTER DATABASE

manager用户具用createdb权限，同时也是db_factory的拥有者；不能修改当前登录数据库的名称，所以我们登录到其它数据库，再执行rename的SQL语句。

数据库所属表空间变更

在分享存储空间时，提到表空间其实是数据库的物理存储位置；那么变更表空间，也意味着给数据库搬个家，听起来就觉得麻烦。

这个命令的应用场景也很少，只有当前表空间的存储空间不足时，或者当前表空间对应的磁盘需要维护时，给数据库挪个位置，如果数据库中数据比较关键，提前需要做好备份归档。

首先修改的用户必须有对应表空间的使用权限，我们还是用manager用户操作，给manager用户赋予表空间tblspc_test1的所有权限。

postgres=# grant all ON tablespace tblspc_test1 TO manager ;GRANT

切换到以manager用户登录，更换db_factory2的表空间，原来是在默认表空间下；

postgres=# \c postgres managerYou are now connected to database "postgres" as user "manager".postgres=> alter database db_factory2 set tablespace tblspc_test1 ;ALTER DATABASE

数据库的owner变更

owner是这个数据库的所有权限的拥有者，更换之后原来的owner用户会失去权限；

可以执行的前提前，执行者可以是超级管理员，或者是当前的owner用户，同时还是新owner用户的成员，否则不能执行；也就是说，新owner和旧的owner在执行后，会具用相同的权限；如果不需要旧owner用户时，可以从成员中踢除即可；这一限制保证变更过程的平滑；

假设要把db_factory2的拥有者从manager更换为 vp用户；

  • 先用超级管理员用户创建新用户 vp，并且赋予 createdb权限；
postgres=# create user vp createdb password '123456';CREATE ROLE
  • 将manager作为新用户的成员
postgres=# grant manager to vp;GRANT ROLE
  • 然后用 manager用户登录数据库 db_factory2，进行拥有者切换
postgres=# \c db_factory2 managerYou are now connected to database "db_factory2" as user "manager".db_factory2=> alter database db_factory2 owner to vp;ALTER DATABASE

运行时参数修改

数据库中的参数很多，具体可以查看配置文件

比如在数据库 db_factory2 禁用索引扫描，可以执行以下SQL

ALTER DATABASE db_factory2 SET enable_indexscan TO off;

删除数据库

当我们不需要某个数据库时，可以进行删除，释放对应的磁盘空间，同时避免后台数据库服务不断扫描废弃数据库，进行维护任务；

删除数据库的方法，可以用命令dropdb,也可以使用SQL语句；

命令删库

在安装目录下的bin目录下，可以看到dropdb命令，查看帮助如下

[senllang@hatch bin]$ ./dropdb --helpdropdb removes a PostgreSQL database.Usage:dropdb [OPTION]... DBNAMEOptions:-e, --echoshow the commands being sent to the server-f, --force try to terminate other connections before dropping-i, --interactive prompt before deleting anything-V, --version output version information, then exit--if-exists don't report error if database doesn't exist-?, --helpshow this help, then exitConnection options:-h, --host=HOSTNAME database server host or socket directory-p, --port=PORT database server port-U, --username=USERNAME user name to connect as-w, --no-password never prompt for password-W, --passwordforce password prompt--maintenance-db=DBNAME alternate maintenance databaseReport bugs to <pgsql-bugs@lists.postgresql.org>.PostgreSQL home page: <https://www.postgresql.org/>

可以看到，默认情况下，参数是数据库名称就可以了。

这也是推荐的删除数据库的方式，不需要登录到某一个数据库中；

SQL删库

postgresql 还提供了SQL方式，SQL标准中没有这个命令；

DROP DATABASE db_factory2;

当然这个由管理员和拥有者执行，当有该数据库还有连接时，会执行失败；

  • 特别注意，删除数据库是不能撤销的动作，要特别小心；

查看数据库的统计

当然在维护时，我们还可以查看数据库上的一些指标，也就是统计信息，来衡量数据库的状态；

这些信息在pg_stat_database 这张系统表中

postgres=# select * from pg_stat_database where datname='db_factory2'; datid | datname | numbackends | xact_commit | xact_rollback | blks_read | blks_hit | tup_returned | tup_fetched | tup_inserted | tup_updated | tup_deleted | conflicts | temp_files | temp_bytes | deadlocks | checksum_failures | checksum_last_failure | blk_read_time | blk_write_time | session_time | active_time | idle_in_transaction_time | sessions | sessions_abandoned | sessions_fatal | sessions_killed | stats_reset-------+-------------+-------------+-------------+---------------+-----------+----------+--------------+-------------+--------------+-------------+-------------+-----------+------------+------------+-----------+-------------------+-----------------------+---------------+----------------+--------------+-------------+--------------------------+----------+--------------------+----------------+-----------------+------------- 16445 | db_factory2 | 0 |6339 |19 | 218 | 244990 |2750338 | 52930 |134 |12 |94 | 0 |0 |0 | 0 | | | 0 |0 | 56623309.239 | 253.288 |0 | 29 |0 |0 | 0 |(1 row)

前缀是 pg_stat_ 系统表，是一类统计信息记录表，可以看到一些运行状态的统计；比如数据库统计信息中连接数量，事务提交、回滚的数量，等等。这些信息在后面用到时会再详细介绍。

总结

希望通过本篇分享，能对数据库有进一步了解，对于数据库的在系统中的维一标识，拥有者的权限，它是拥有该数据库所有权限，同时每个数据库都在存储位置，对应的就是表空间，这些信息可以通过登录，查看数据库的系统字典，以及统计信息来详细了解。

结尾

非常感谢大家的支持，在浏览的同时别忘了留下您宝贵的评论，如果觉得值得鼓励，请点赞，收藏，我会更加努力！

作者邮箱：study@senllang.onaliyun.com
如有错误或者疏漏欢迎指出，互相学习。

注：未经同意，不得转载！