PostgreSQL 01 开启新世界-柚子小站

到目前为止，对于数据库的认识就四个字：会用就行。知识的来源主要就是MySQL必知必会和以前看的一些培训班视频，都着重在如何取数和查询，没有任何关于数据库本身的知识。但是在之前的Spring 重新拿起来看的过程中，发现用Java编Web应用，操作数据库是非常重要的一环，绝对不能像原来一样会用就行，必须深入一些了解事务管理等内容，以及数据库和Hibernate的使用。这样原来打算复习Spring的工作一下变成了最后编写Web应用之前的一条主线：Spring MVC 和两条重要的支线: Hibernate + 数据库了。数据库方面，我在给公司编写CMS系统的时候，实际上就使用了PostgreSQL 10，使用这个数据库的原因一方面是当时Django 2 By Example这本书就用了这个数据库，第二也是当时简单看了一下，说PostgreSQL比MySQL这种要先进一些。所以就使用了。现在的支线是数据库方面，这次就来认真的学习一下PostgreSQL了。同时这也是对于数据库基础概念的一次学习。以及对于SQL的学习。

PostgreSQL的相关资源
安装PostgreSQL
初步使用
基础配置
database
权限入门

PostgreSQL的相关资源

对于开发来说，想看一个新玩意，肯定是找到官网直接从文档开始。不过这是建立在已经了解数据库的基础规范上的，好比你研究Quartz之前已经一直在用Java，而不是说从来没接触过Java，直接要用Quartz，那就相当于你还要有Java的知识储备。对于我现在，还是要把数据库当成一个全新的概念来看待，而不是简单的就用PgSQL。找了一下各种资源，列出在这里：

PostgreSQL中文技术站的新手指引，其中列出了很多链接，有PostgreSQL9.6和10的中文翻译的手册，非常方便，还有一些图书可供参考。
W3C的SQL指南，SQL也是很重要的一环，以前没有系统的接触过SQL。
国内PostgreSQL的大牛德哥的Git，内容相当多，从入门到顶尖技术全有。
PostgreSQL 10 中文文档，这东西以后经常翻了.

然后发现PostgreSQL每年9月发布一个大版本，我在当时写CMS项目的时候用的还是10版，今天一看已经12版了，变化真是太快了。考虑到用新不用旧，我打算安装PostgreSQL 12，然后学习还是按照10版的来学习。而且核心内容应该是没有什么变化的。

安装PostgreSQL

在腾讯云的CentOS 7.6系统上安装的过程如下:

到官网安装指引页面中选好版本和操作系统版本, 之后会生成命令
yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm, 先安装rpm文件
yum install postgresql12, 然后直接安装postgresql12
yum install postgresql12-server, 还有server包

之后初始化数据库:

        ./usr/pgsql-12/bin/postgresql-12-setup initdb
        systemctl enable postgresql-12
        systemctl start postgresql-12

跟着做了一遍, 毫无问题, 前边安装都OK, 主要就是initdb的时候, /var/lib/pgsql/12/data 下创建了默认的数据库配置文件与数据库文件. 在执行systemctl start postgresql-12之后, 看到控制台打印出:

Dec 23 15:32:32 VM_0_14_centos postmaster[16282]: 2019-12-23 15:32:32.604 CST [16282] LOG:  listening on IPv6 addre... 5432
Dec 23 15:32:32 VM_0_14_centos postmaster[16282]: 2019-12-23 15:32:32.604 CST [16282] LOG:  listening on IPv4 addre... 5432
Dec 23 15:32:32 VM_0_14_centos postmaster[16282]: 2019-12-23 15:32:32.608 CST [16282] LOG:  listening on Unix socke...5432"
Dec 23 15:32:32 VM_0_14_centos postmaster[16282]: 2019-12-23 15:32:32.615 CST [16282] LOG:  listening on Unix socke...5432"

看来已经可以使用了. Linux下边PostgreSQL会创建一个用户叫postgres, 也去查看一下:

cat /etc/passwd

//用户最下边多了一个:
postgres:x:26:26:PostgreSQL Server:/var/lib/pgsql:/bin/bash

这是在服务器上安装, 在自己的电脑上则需要安装pgAdmin4管理软件, 这个软件和postgreSQL是互相独立的. 图形化界面可以非常方便的看到数据库中的各种对象.

初步使用

现在处于最开始的状态, PgSQL的命令行工具是psql, 不过目前只能由postgres用户来使用, 进行切换:

[root@VM_0_14_centos pgsql]# su postgres
bash-4.2$ psql
psql (12.1)
Type "help" for help.

postgres=#

出现了postgres=#提示符, 就已经处在本机的psql的环境中了. 试验一些命令, \l是列出所有数据库:

postgres=# \l
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
-----------+----------+----------+------------+------------+-----------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
(3 rows)

有三个数据库, 分别是postgres, template0和template1, Hmmmm...究竟各有什么用处... \q则是退出psql. 不过此时远程还连不上数据库, 用户也没有配置, 需要进行一些基本的配置.

基础配置

在刚才initdb命令后生成的/var/lib/pgsql/12/data下边, 有若干配置文件:

pg_hba.conf,控制访问权限, 允许哪些用户, 哪些IP地址可以连接到服务器, 还有验证模式
pg_ident.conf, 存在该文件的话, 系统会把操作系统的用户映射到postgreSQL内部的用户来登录. 有些人会映射root到postgreSQL.
postgresql.conf, 包含通用设置, 比如IP地址,内存分配, database的存储位置等.
postgresql.auto.conf, 这个文件从Postgre10开始引入, 优先级比postgresql.conf高, 不允许手动修改该文件, 这个文件保存通过ALTER SYSTEM命令提供的设置,在读取postgresql.conf时自动读取该文件,并且它的设置以相同的方式发生作用. postgresql.auto.conf中的设置覆盖postgresql.conf中的设置

如果不知道这些配置文件存放在哪里, 也可以在psql里查一查:

postgres=# select name, setting From pg_settings where category = 'File Locations';
       name        |                setting
-------------------+----------------------------------------
 config_file       | /var/lib/pgsql/12/data/postgresql.conf
 data_directory    | /var/lib/pgsql/12/data
 external_pid_file |
 hba_file          | /var/lib/pgsql/12/data/pg_hba.conf
 ident_file        | /var/lib/pgsql/12/data/pg_ident.conf
(5 rows)

对于我们手工修改来说, 就是除了auto.conf之外的三个文件了. 然后一个一个配置文件来看. postgresql.conf可以在psql里通过 SELECT * FROM pg_settings, 从pg_settings视图中查询, 内容很多, 都是配置. 其中的context表示是否需要重启即可生效, 如果是postmaster就需要重新启动服务才能生效. 如果是user, 只需要重新加载配置文件即可, 在控制台中使用service postgresql-9.5 reload即可. 我把postgresql.conf下载到本地硬盘来, 里边基本上都是使用#号进行的注释, 有如下几个需要先配置一下:

listen_addresses, 这个指的是监听地址, 默认是localhost, 则外部无法连接进来, 一般可以配置成本机IP地址或者干脆写成listen_addresses = '*', 表示任意地址都可以. 这一行原本是注释掉的, 需要去掉注释才可以.
port, 这个指的是端口, 默认是5432, 如果遭受攻击比较多, 可以修改掉.
max_connections = 100, 这个没有注释, 默认就是100, 表示并发连接数为100.

整个文件几百个配置, 显然是留给真正专业的DBA来看的. 下边来看一看pg_hba.conf, 这个配置文件简单的说就是配置用户的, 打开以后是这个样子:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            ident
# IPv6 local connections:
host    all             all             ::1/128                 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            ident
host    replication     all             ::1/128                 ident

首先要知道的是, PgSQL使用角色来处理用户身份认证, 有可以登录数据库的可登录角色, 组角色和成员角色, 组角色未必有登录权限, 只起到一个权限的集合使用. SUPERUSER权限最高, 可以彻底控制PgSQL服务. PgSQL现在已经不使用用户和组这两个概念, 虽然CREATE USER和CREATE GROUP的SQL语句依然可用, 但最好使用CREATE ROLE语句. 知道了上述知识之后, 上边每一行就表示一个登录配置, 分别是主机, 数据库名, 用户, 地址和验证方式, 用一个或者多个空格分割. 目前可以看到, IPV4的情况下, 只允许从本地的ident方式登录, 也就是把linux系统里的postgres用户映射到PgSQL中的postgres用户. 其他方式都不能登录系统. 常见的验证方式如下:

trust, 无需密码就可以连接
md5, 需要md5加密的密码
ident, 之前说过, 将操作系统用户映射到PgSQL的用户, windows下无法使用ident方式
peer, 从操作内核获取当前用户名, 如果一致就可以连接, 在刚安装好PgSQL, 能够无密码使用postgres用户登录, 就是第一行配置了这个方式.
cert, SSL证书.

其他还有很多, 所以为了能够从外部连接PgSQL, 很显然我们需要允许所有的IP地址, 然后需要md5加密, 也就是密码验证, 才可以登录. 这里有个replication暂时不知道是什么. 在最后添加一行:

host all all 0.0.0.0/0 md5

这一句话表示, 来自所有的IP地址, 所有用户都可以访问, 密码使用md5加密,然后可以把其他配置都给注释掉. 配置好之后, 还需要进入psql, 给postgres用户加上密码:

su - postgres
psql
postgres=# \password postgres

\password postgres 表示为postgres这个role来设置密码, 之后系统会提示连续输入两次密码. 设置好密码, 也修改好之后, 重新启动PgSQL的服务: systemctl restart postgresql-12. 之后在PgAdmin4中, 选择创建新服务器, 输入主机地址和端口, 数据库(现在只有默认创建的postgres数据库), 以及用户名和密码, 就可以成功连接了. PgAdmin4可以在首页的设置中选择设置语言, 为了学习起见, 还是先不要用中文, 看看英文吧. 进去之后可以看到, 一个PgSQL数据库服务器中, 分为三块, 首先是Databases, 也就是数据库对象, 然后是Login/Group Roles, 可以发现里边有很多不具备登录权限的组角色, 以及默认的超级用户postgres. 右击可以查看权限, postgres所有的权限都有. 然后是Tablespaces, 这个表示表空间对象, 其实就是相当于指定物理存储在哪里. 没有什么可以配置的. 所以对于我们操作数据库的程序员来说, 核心就是用户和Databases对象. Databases点进去之后, 目前可以看到有一个postgres数据库, 这是我们登录的时候输入的. 在之前的\l命令中可以看到默认的PgSQL里一共有3个数据库: postgres, template0, template1. 这三个是什么数据库呢? 这个涉及PgSQL的数据库结构. postgres数据库展开之后竟然不是表, 而是一堆没见过的东西: Casts, Catalogs, Event Triggers, Extensions, Foreign Data Wrappers, Languages, Schemas.....这里边不光是PgSQL的内容, 还涉及基础的数据库概念....路漫漫其修远矣....

database和一些术语

PgSQL用标准的术语来说, 是一个DBMS数据库管理系统. 当然我们也会简称其数据库, 或者用database来指代数据库管理系统. 但是database更精准的说法, 应该是指一个DBMS中某一级别的数据存储单位. 一个DBMS可以包含(管理)多个database, PgSQL也一样, 一个PgSQL服务可以包含多个独立的database. 在上边的PgAdmin4中, 在登录的时候就指定了数据库名是postgres. 为什么不指定另外两个默认的template0和template1数据库, 是因为这两个数据库是模板数据库, 如果不作配置, 任何新建的数据库都会以template1作为新数据库的模板. 而template0不要去修改, 这是原始的干净模板, 实际上template1也是从template0复制的, 不直接使用0就是怕搞坏了. 基于模板创建数据库的语法是: CREATE DATABASE my_db TEMPLATE my_template; 指定一个数据库为模板数据库的语法(需要超级用户)是:

UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'mydb';

如果想取消, 就SET datistemplate = FALSE即可. 一旦成为模板数据库, 是无法对其修改的. database下边是什么呢, 以前不清楚的时候, 以为数据库里边就是表. 实际上数据库的下一层逻辑叫做schema. 一个schema我个人理解就可以看成是数据库中存放的所有东西的一个集合, 像一个命名空间一样, 一个database中可以有多个schema. 这些schema不能重名, 但各个schema中可以有重名的表. 查询的时候可以使用 schema1.user 这样来指定schema. postgresql.conf中还有一个search_path变量, 用于配置查找顺序. 数据库初始化之后, 有一个默认的schema叫做public, 所有用户都可以在其中创建表. 关于权限发现一篇文章不错: 对PostgreSQL中权限的理解(初学者必读). 如果是本地使用, 一般的做法是收回public schema的权限, 然后创建一个database 和一个role, 然后指定role为这个数据库的属主. 属主之后就可以在表中创建schema和其他内容. 然后可以再新建两个用户, 给他们对于这个schema中的表的响应权限. 这样就形成了DBA掌握超级用户, 具体应用的负责人掌握某个schema, 然后下边的开发人员根据开发情况来选择具体用户. 所以schema的用法就是对新用户, 或者新的扩展创建一个schema. 而如果不同应用, 最好还是分库而不是单独分schema. 由于连接数据库的时候就必须指定数据库, 所以实际上就是直接操作schema了.

权限入门

需要创建一个数据库,也是为了之后看SQL和Hibernate的时候要用. 先来创建一个角色, 以便成为要创建的数据库的属主:

CREATE ROLE minkolee LOGIN PASSWORD '******';

psql会提示 CREATE ROLE, 说明创建成功, 刷新一下PgAdmin4, 其中也看到了这个新的角色. 之后创建一个数据库, 并指定拥有者:

CREATE DATABASE mydb WITH owner = minkolee;

会提示创建了数据库, 刷新PgAdmin4后, 可以看到连接的PgSQL实例中的Databases对象下, 出现了新创建的mydb数据库. 右键点击查看Properties, 其中的owner显示为minkolee. 这就创建了一个数据库及其属主. 现在我们来换成minkolee登录. 数据库选成自己的 mydb, 登录之后也可以看到postgres数据库, 但是想在postgres中创建schema, 会提示没有权限. 这是因为目前我们的权限仅仅只是mydb的属主. 对于postgres没有权限. 在mydb中, 就可以创建schema和表了. 可以看到实际的SQL命令中后边加上了 AUTHORIZATION minkolee. 一个角色的权限, 有一些是在创建角色和数据库的时候指定的, 比如超级用户权限, 创建database的权限, 登录权限和创建其他用户和角色的权限. GRANT命令则一般用来赋予用户对一些数据库中的对象进行增删改查的权限. 命令的格式是 GRANT 权限 ON 目标schema TO 角色 . GRANT的细节非常多, 用到的时候还是查看官方手册吧, 常见的权限是:

USAGE, 对一个数据库进行任何操作之前, 先得赋予这个权限, 没有这个权限, 其他的权限也没用
SELECT, 查询权限
UPDATE, 新增
ON SEQUENCE, 后续, 如果不加SEQUENCE, 仅仅当前存在于目标SCHEMA上的表才有效, 新创建的表对于用户来说一样没权限.

每个数据库都有一个默认的public schema, 任何用户都有在其上的权限, 系统会默认在上边赋予所有人CONNECT, 执行函数, 创建临时表的权限, 所以一般要收回来. PgSQL中所有用户都属于一个虚拟的public组角色, 所以要从这个组角色上把权限收回来:

REVOKE CREATE ON SCHEMA public from public;

这样就收回了public组用户可以在所有public schema上创建新对象的权限. 然后就可以再创建新用户, 给其具体的权限了. 这里补充一下, 我用PgAdmin4创建了一个新表,发现没法删除, 后来发现可能是PgAdmin4生成的语句有些问题, 所以这里记录下来如何使用命令行登录PgSQL.

需要修改 pg_hba.conf 文件, 默认的是Unix本地采用peer方式, 将local对应的那一行改成md5方式, 即需要验证密码. 这样在系统中切换成postgres用户, 也需要输入密码了. 可见默认数据库叫做postgres其实是为了简便.
使用$ psql -U minkolee -d mydb; 来登录, 之后输入密码, 不指定数据库名称的话, 会用和用户名相同的数据库, 导致找不到数据库无法登录.
登录进来之后, 默认已经使用了mydb, 可以通过\c name, 来切换数据库
\dt 可以查看数据库中所有的关系, 会按照schema和表都列出来. 如果是空就显示Did not find any relations.
之后就可执行命令了, 我就执行了DROP TABLE test;把刚才通过PgAdmin4创建的表给删除了.
创建Schema CREATE SCHEMA my_schema; 创建表的时候都可以使用schema.table的方式来操作.

后边我又反复测试了几个命令, 可以创建SCHEMA对应的表, 删除表和schema. 总算稍微折腾点入门了. 剩下就是来重新看一下数据库关系理论和学一下SQL了, 以及从Java连接PgSQL.