一、PostgreSQL介绍
PostgreSQL是一款功能强大的开源关系型数据库系统，其底层由C语言实现。
PostgreSQL的开源协议与Linux内核的开源协议相同，为BDS协议，该协议与MIT开源协议类似，简单来说，用户可以对PostgreSQL进行封装并进行商业化收费。
PostgreSQL的名称由来：它最初叫Ingres，后来为解决Ingres中的一些问题，作为后续的Ingres版本，便命名为postgre。
PostgreSQL的版本迭代较为频繁，目前最新的正式发布版本已达15.RELEASE。
PGSQL的版本选择通常有两种情况：
* 若追求稳定运行，建议选用12.x版本。
* 若想体验新特性，可选用14.x版本。
PGSQL支持跨版本升级，且一般不会出现重大问题。
PGSQL社区十分活跃，大约每三个月发布一个版本，这意味着许多常见的BUG能得到及时修复。
PGSQL在国外应用较为广泛，国内目前仍以MySQL为主。
不过国内不少国产数据库是基于PGSQL进行二次封装的，例如华为的GaussDB以及腾讯的Tbase等。实际上很多原先使用Oracle的公司直接迁移到了PGSQL。而且国内众多云产品也支持PGSQL。
由于PGSQL开源，有诸多数据迁移工具可助力快速从MySQL、SQLServer、Oracle等迁移至PGSQL内部，比如pgloader这类数据迁移工具。
PGSQL的官方网址：https://www.postgresql.org/
PGSQL的国内社区：http://www.postgres.cn/v2/home
二、PostgreSQL和MySQL的区别
技术并无优劣之分，关键在于是否契合业务需求，能否解决业务问题。同时也需考量社区活跃度与更新频次。
MySQL存在几处不足：
* MySQL的数据类型不够丰富。
* MySQL不支持序列概念（Sequence）。
* 网上可用的MySQL插件相对较少。
* MySQL的性能优化监控工具不多，定位问题成本较高。
* MySQL的主从复制缺乏官方同步策略，同步问题较难解决。
* 尽管MySQL开源，但不够彻底。
相较于MySQL，PostgreSQL具备以下特点：
* PostgreSQL的数据类型极为丰富。
* PostgreSQL支持序列概念。
* PostgreSQL的插件十分丰富。
* PostgreSQL支持主从复制同步操作，可实现数据的0丢失。
* PostgreSQL的MVCC实现与MySQL不同，PostgreSQL一行数据会存储多个版本，最多可存储40亿个事务版本。
三、PostgreSQL的安装
我们仅在Linux环境下进行安装，不建议在Windows下安装。
Linux版本建议选用7.x版本，最好是7.6或7.8版本。
前往官网获取安装方式
选择PGSQL版本和Linux发行版本
获取命令后，直接在Linux中执行即可
# 下载PGSQL的rpm包
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# 安装PGSQL12的软件程序，需下载，需等待一会儿，一般不会失败，即便失败也会自动寻找镜像
sudo yum install -y postgresql12-server
# 数据库初始化
sudo /usr/pgsql-12/bin/postgresql-12-setup initdb
# 设置开机启动项并设为开机自启
sudo systemctl enable postgresql-12
# 启动PGSQL
sudo systemctl start postgresql-12
这属于Windows下的傻瓜式安装，一般不会出错。若出错，可能是以下情况：
* 安装Linux时需选择最小安装
* 你的Linux无法连接外网
* Linux中的5432端口可能被占用
PostgreSQL不建议用root管理，安装成功后默认会创建一个postgres用户
使用前需切换到postgres用户
su postgres
切换到postgres用户后，直接输入psql即可进入PostgreSQL客户端
# 进入命令行
psql
# 查看有哪些库，新安装时通常有三个库，分别是postgres、template0、template1
\l
不推荐下载Windows版本使用
若非要下载：https://sbp.enterprisedb.com/getfile.jsp?fileid=1258242
四、PostgreSQL的配置
需要配置两个方面的信息，一是PostgreSQL的远程连接配置，二是PostgreSQL的日志配置。
PostgreSQL的主要配置文件位于数据目录下的postgresql.conf和pg_hba.conf
这些配置文件存放在
# 该目录下
/var/lib/pgsql/12/data
上图可见，PostgreSQL的核心文件归postgres用户所有，操作时尽量不用root用户，最好先切换到postgres用户操作。
4.1 远程连接配置
PostgreSQL默认不支持远程连接，这与MySQL类似
- MySQL通过grant命令追加用户，而PostgreSQL需通过修改配置文件来指定用户是否可远程连接。
直接修改pg_hba.conf配置文件
用户、对应数据库及连接方式的编写模板
# 第一部分
local表示本地连接，host表示可指定连接的ADDRESS
# 第二部分
database填写数据库名，写all表示所有库均可连接
# 第三部分
user填写连接的用户，写all表示所有用户均可连接
# 第四部分
address表示哪些IP地址可连接
# 第五部分
method加密方式，此处无需过多关注，直接用md5
# 直接进行一个痛快的配置，允许任意地址的全部用户连接所有数据库
host all all 0.0.0.0/0 md5
为实现远程连接，除用户级配置外，还需修改服务级配置
服务级配置在postgresql.conf中
发现默认情况下PGSQL仅允许localhost连接，将其配置为*即可解决问题
注意，为使配置生效，务必重启
# postgres密码无需关注，直接用root用户
sudo systemctl restart postgresql-12
4.2 配置数据库的日志
查看postgresql.conf文件
PostgreSQL默认仅保存7天日志，循环覆盖。
# 表示日志开启。
logging_collector = on
# 日志存放路径，默认放到当前目录下的log里
log_directory = 'log'
# 日志文件名，默认以postgresql为前缀，星期作为后缀
log_filename = 'postgresql-%a.log'
# 默认一周后日志文件会被覆盖
log_truncate_on_rotation = on
# 一天一个日志文件
log_rotation_age = 1d
# 一个日志文件无大小限制
log_rotation_size = 0
五、PostgreSQL的基操
仅在psql命令行（客户端）执行了一次\l，查看所有库信息
可直接基于psql查看信息，也可进入命令行后进行具体操作
可直接基于psql操作
可输入psql --help查看psql命令
能直接进入命令行是因为psql默认以postgres用户连接本地pgsql，所以可直接进入
下图为默认连接方式
后续均基于psql命令行（客户端）操作
命令无需死记，使用时可找帮助文档，在psql命令行中输入
\help可查看数据库级命令
\?可查看服务级命令
5.1 用户操作
创建用户命令很简单
# 区别在于create user默认有连接权限，create role无，不过可通过选项设置
CREATE USER 名称 [ [ WITH ] 选项 [ ... ] ]
create role 名称 [ [ WITH ] 选项 [ ... ] ]
创建一个超级管理员用户
create user root with SUPERUSER PASSWORD 'root';
退出psql命令行
尝试用root用户登录psql命令
psql -h 192.168.11.32 -p 5432 -U root -W
发现仅创建用户无法登录，需创建一个root库
create database root;
可在不退出psql时直接切换数据库
也可退出psql后基于psql命令切换用户和数据库
若要修改用户信息或删除用户，可查看
# 修改用户通过ALTER命令操作
# 删除用户通过DROP命令操作
若要查看所有用户信息
5.2 权限操作
权限操作前需掌握PGSQL的逻辑结构
逻辑结构图
可见PGSQL一个数据库中有多个schema，每个schema下有相应库表信息，权限粒度比MySQL更细。
在PGSQL中，权限管理分多层
server、cluster、tablespace级别：一般通过pg_hba.conf配置
database级别：通过命令操作grant
namespace、schema级别：较少使用……不深入了解
对象级别：通过grant命令设置
后续若需对database或对象级别做权限控制，直接用grant命令操作即可
# 查看grant命令
\help grant
小任务
创建一个用户（自己名字）
创建一个数据库
在该数据库下创建一个schema（数据库默认有public schema）
将该schema的权限赋予用户
在该schema下创建一张表
将表的select、update、insert权限赋予用户
完成上述操作
-- 准备用户
create user laozheng with password 'laozheng';
-- 准备数据库
create database laozheng;
-- 切换数据库
\c laozheng;
-- 创建schema
create schema laozheng;
-- 将schema的拥有者改为laozheng用户
alter schema laozheng owner to laozheng;
-- 将laozheng库下laozheng schema中的表的增、改、查权限赋予laozheng用户
grant select,insert,update on all tables in schema laozheng to laozheng;
-- 用postgres用户先创建一张表
create table laozheng.test(id int);
-- 切换到laozheng用户。
\c laozheng -laozheng
-- 报错：
-- 致命错误: 对用户"-laozheng"的对等认证失败
-- Previous connection kept
-- 上述方式失败，原因是匹配连接方式时基于pg_hba.conf文件从上往下找
-- 找到第一个local，匹配上。发现连接方式是peer。
-- peer代表用当前系统用户连接PostgreSQL
-- 当前系统用户只有postgres，无laozheng，无法用peer连接
-- 构建laozheng用户时，发现PostgreSQL所有文件拥有者和所属组都是postgres，且只能由拥有者操作
-- 基于上述问题，不采用本地连接即可。
-- 采用远程连接。
psql -h 192.168.11.32 -p 5432 -U laozheng -W
-- 这样跳过local连接方式匹配，直接锁定到后面的host，host连接方式是md5，md5是密码加密。
-- 登录后，直接输入
\dn
-- 查看到当前database下有两个schema
这种权限赋予方式，可用管理员用户构建整体表结构，进而分配指定用户不同权限，避免用户误操作。
六、图形化界面安装
有多种图形化界面可连接PGSQL，如收费的Navicat。
也可使用PostgreSQL官方提供的免费图形化界面。
官方提供的：https://www.pgadmin.org/
直接点击下载~~~
https://www.postgresql.org/ftp/pgadmin/pgadmin4/v6.9/windows/
傻瓜式安装~~~
打开pgAdmin
添加新连接
直接save即可连接到老郑信息
可切换语言
七、数据类型
PGSQL支持的类型极为丰富，大多类型与MySQL对应
| 名称 | 说明 | 对比MySQL |
|---|---|---|
| 布尔类型 | boolean，标准布尔类型，只能存储true、false | |
| MySQL中虽无对应boolean，但有替代类型，数值的tinyint类型与PGSQL的boolean均占1字节。 | ||
| 整型 | smallint（2字节）、integer（4字节）、bigint（8字节） | 与MySQL无异。 |
| 浮点型 | decimal、numeric（与decimal相同，精准浮点型）、real（float）、double precision（double）、money（货币类型） | |
| 与MySQL基本无异，MySQL支持float、double、decimal，无货币类型。 | ||
| 字符串类型 | varchar(n)（character varying）、char(n)（character）、text | |
| 与MySQL基本无异。 PGSQL存储的varchar类型可存储1G，MySQL存储量不同。 |
||
| 日期类型 | date（年月日）、time（时分秒）、timestamp（年月日时分秒）（time和timestamp可设时区） | |
| 与MySQL基本无异。 MySQL有datetime。 |
||
| 二进制类型 | bytea-存储二进制类型 | MySQL支持，为blob |
| 位图类型 | bit(n)（定长位图）、bit varying(n)（可变长度位图） | 存储0、1，MySQL支持，使用较少。 |
| 枚举类型 | enum，与Java的enum类似 | MySQL支持。 |
| 几何类型 | 点、直线、线段、圆…… | MySQL无，开发中少用 |
| 数组类型 | 类型后追加[]表示存储数组 | MySQL无 |
| JSON类型 | json（存储JSON数据文本）、jsonb（存储JSON二进制） | 可存储JSON，MySQL8.x支持 |
| ip类型 | cidr（存储ip地址） | MySQL不支持 |
| 等等 | http://www.postgres.cn/docs/12/datatype.html |
八、PostgreSQL基本操作&数据类型
8.1 单引号和双引号
在
相关文章
