PostgreSQL

一、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虽然开源，but，不够彻底。

PostgreSQL相对MySQL上述问题的特点：

• 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管理，在安装成功postgreSQL后，他默认会给你创建一个用户：postgres

玩PGSQL前，先切换到postgres

su postgres

奇幻到postgres用户后，直接输入psql即可进入到postgreSQL提供的客户端

# 进入命令行
psql
#查看有哪些库，如果是新安装的，有三个库，一个是postgres,template0,template1 
postgres=# \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给mysql.user追加用户，一般是采用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去玩

可以数据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命令行

编写psql命令尝试去用root用户登录

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/v8.6/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。MySQL没有这个货币类型。
字符串类型	varchar(n)（character varying），char(n)（character），text	这里和MySQL基本没区别。<br />PGSQL存储的varchar类型，可以存储一个G。MySQL好像存储64kb（应该是）。
日期类型	date（年月日），time（时分秒），timestamp（年月日时分秒）（time和timestamp可以设置时区）	没啥说的，和MySQL基本没区别。<br />MySQL有个datetime。
二进制类型	bytea-存储二进制类型	MySQL也支持，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 单引号和双引号

在PGSQL中，写SQL语句时，单引号用来标识实际的值。双引号用来标识一个关键字，比如表名，字段名。

-- 单引号写具体的值，双引号类似MySQL的``标记，用来填充关键字
-- 下面的葡萄牙会报错，因为葡萄牙不是关键字
select 1.414,'卡塔尔',"葡萄牙";

8.2 数据类型转换

第一种方式：只需要在值的前面，添加上具体的数据类型即可

-- 将字符串转成位图类型
select bit '010101010101001';

第二种方式：也可以在具体值的后面，添加上 ::类型 ，来指定

-- 数据类型
select '2011-11-11'::date;
select '101010101001'::bit(20);
select '13'::int;

第三种方式：使用CAST函数

-- 类型转换的完整写法
select CAST(varchar '100' as int);

8.3 布尔类型

布尔类型简单的丫批，可以存储三个值，true，false，null

-- 布尔类型的约束没有那么强，true，false大小写随意，他会给你转，同时yes，no这种他也认识，但是需要转换
select true,false,'yes'::boolean,boolean 'no',True,FaLse,NULL::boolean;

boolean类型在做and和or的逻辑操作时，结果

字段A	字段B	a and b	a or b
true	true	true	true
true	false	false	true
true	NULL	NULL	true
false	false	false	false
false	NULL	false	NULL
NULL	NULL	NULL	NULL

8.4 数值类型

8.4.1 整型

整型比较简单，主要就是三个：

• smallint、int2：2字节
• integer、int、int4：4字节
• bigint、int8：8字节

正常没啥事就integer，如果要存主键，比如雪花算法，那就bigint。空间要节约，根据情况smallint

8.4.2 浮点型

浮点类型就关注2个（其实是一个）

• decimal(n,m)：本质就是numeric，PGSQL会帮你转换
• numeric(n,m)：PGSQL本质的浮点类型

针对浮点类型的数据，就使用 numeric

8.4.3 序列

MySQL中的主键自增，是基于auto_increment去实现。MySQL里没有序列的对象。

PGSQL和Oracle十分相似，支持序列：sequence。

PGSQL可没有auto_increment。

序列的正常构建方式：

create sequence laozheng.table_id_seq;
-- 查询下一个值
select nextval('laozheng.table_id_seq');
-- 查询当前值
select currval('laozheng.table_id_seq');

默认情况下，seqeunce的起始值是0，每次nextval递增1，最大值9223372036854775807

告诉缓存，插入的数据比较多，可以指定告诉缓存，一次性计算出20个后续的值，nextval时，就不可以不去计算，直接去高速缓存拿值，效率会有一点点的提升。

序列大多数的应用，是用作表的主键自增效果。

-- 表自增
create table laozheng.xxx(
    id int8 default nextval('laozheng.table_id_seq'),
    name varchar(16)
);
insert into laozheng.xxx (name) values ('xxx');
select * from laozheng.xxx;

上面这种写法没有问题，但是很不爽~很麻烦。

PGSQL提供了序列的数据类型，可以在声明表结构时，直接指定序列的类型即可。

bigserial相当于给bigint类型设置了序列实现自增。

• smallserial(对应smallint)
• serial（对应int）
• bigserial（对应bigint)

-- 表自增（爽）
create table laozheng.yyy(
    id bigserial,   
    name varchar(16)
);
insert into laozheng.yyy (name) values ('yyy');

在drop表之后，序列不会被删除，但是序列会变为不可用的状态。
因为序列在使用se