数据库操作

专栏内容：

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

开源贡献：

toadb开源库

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

系列文章

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

前言

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

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

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

本专栏主要介绍sql>postgresql 入门使用，数据库维护管理，通过这些使用来了解数据库原理，慢慢了解sql>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了；

sql">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)

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

sql_118">psql查看数据库列表

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

db_factory=> \l
                                                        List of databases
    Name     |  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 hr
psql (16beta1)
Type "help" for help.

db_factory=>

创建数据库

sql">CREATE DATABASE db_factory;

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

修改数据库

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

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

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

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

数据库属性修改

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

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

数据库名称修改

数据库名称变更

sql">postgres=# \c postgres manager
You 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的所有权限。

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

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

sql">postgres=# \c postgres manager
You 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权限；

sql">postgres=# create user vp createdb password '123456';
CREATE ROLE

将manager作为新用户的成员

sql">postgres=# grant manager to vp;
GRANT ROLE

然后用 manager用户登录数据库 db_factory2，进行拥有者切换

sql">postgres=# \c db_factory2 manager
You are now connected to database "db_factory2" as user "manager".
db_factory2=> alter database db_factory2 owner to vp;
ALTER DATABASE

运行时参数修改

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

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

sql">ALTER DATABASE db_factory2 SET enable_indexscan TO off;

删除数据库

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

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

命令删库

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

[senllang@hatch bin]$ ./dropdb --help
dropdb removes a PostgreSQL database.

Usage:
  dropdb [OPTION]... DBNAME

Options:
  -e, --echo                show 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
  -?, --help                show this help, then exit

Connection 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, --password            force password prompt
  --maintenance-db=DBNAME   alternate maintenance database

Report bugs to <pgsql-bugs@lists.sql>postgresql.org>.
PostgreSQL home page: <https://www.sql>postgresql.org/>

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

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

SQL删库

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

sql">DROP DATABASE db_factory2;

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

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

查看数据库的统计

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

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

sql">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 | che
cksum_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)