🌻🌻 目录 🌻🌻
- 一、PostgreSQL 简介
- 1.1、PG 的历史
- 1.2、PG的社区
- 1.2.1 纯社区
- 1.2.2 完善的组织结构
- 1.2.3 开源许可独特性
- 1.3 、PostgreSQL与MySQL的比较
- 二、PostgresQL的下载安装
- 2.1、Windows上安装 PostgreSQL
- 2.2、远程 连接 PostgreSQL
- 2.3、Linux上安装 PostgreSQL
- 三、PostgresQL的基本使用
- 3.1、登录
- 3.2、数据库操作
- 3.3、数据库表操作
- 3.3.1 创建数据库表（自增与mysql有区别）
- 3.3.2 数据表插入数据（与mysql相同）
- 3.3.3 修改删除表数据操作（与mysql相同）
- 3.4、Schema
- 3.5、如何备份PostgreSQL数据库(了解)
- 3.6、用户操作(了解)
- 3.7、PostgreSQL 角色管理(了解)
- 3.8、控制台常用命令总结
要点:
- 📈
PostgreSQL全球入门数据库排名第四，超越MongoDB和Redis。- 💼 国内企业及政府部门因数据安全考虑，转用PostgreSQL替代Oracle。
- 📄
PostgreSQL的BSD许可证允许定制后商业化，区别于MySQL的GPL许可证。- 📘 课程内容：
PostgreSQL特性与历史、跨平台安装、开发者常用操作总结。
一、PostgreSQL 简介
- PostgreSQl是一个免费的对象-关系数据库服务器(ORDBMS)，在灵活的
BSD许可证下发行。- PostgreSQL开发者把它念作post-gress-Q-L。
- PostgreSQL 的 Slogan 是"世界上最先进的开源关系型数据库”。
- “开源界的Oracle”,去O首选
- PostgreSQL官网
https://www.postgresql.org/- PostqreSQL中文社区
http://www.postgres.cn/v2/home
- 全球数据库排行
https://db-engines.com/en/
- 国产数据库排行
https://www.modb.pro/dbRank
1.1、PG 的历史
- PostgreSQL最初设想于1986年，当时被叫做Berkley Postgres Project。
- 该项目一直到1994年都处于演进和修改中，直到开发人员AndtrewYu和)oly Chen在Postgres中添加了一个SQl(Strnctured Queny Language，结构化査询语言)翻译程序，该版本叫做Postqres95，在
开放源代码社区发放。- 开始以社区的形式运作。
- 1996年，再次对Postgres95做了较大的改动，并将其作为PostgresSQL6.0版发布。该版本的Postgres提高了后端的速度，包括增强型SQL92标准以及重要的后端特性(包括子选择、默认值、约束和触发器)。
- 2005年，发布8.0版本，开始支持windows系统环境。
- PostgreSQL9.0:支持64位windows系统，异步流数据复制、HotStandby;
- PostgreSQL9.1:支持数据同步复制，unlogged tabels、serializable snapshotisolation、FDW 外部表。此版本后，PostgresQl开始得到中国多个行业用户的关注，开始有应用于电信、保险、制造业等边缘系统。
- 目前生产环境主流的版本是
PostgreSQL12- 2021-09-30，PostgreSQL全球开发组宣布，功能最为强大的开源数据库，PostgreSQL14版本正式发布!
1.2、PG的社区
PG为什么没有被商业公司控制?
1.2.1 纯社区
他们为什么要贡献核心代码?
最终用户
- 希望社区长久，期望可以享受免费的、可持续发展的、开源的、不被任何商业公司、不被任何国家控制的企业级数据库。去0，去DB2，去Sybase;
- 不靠数据库赚钱;
- PG用到的人越多，越多人背书，使用越靠谱(事实也是如此);
- 抛砖引玉，企业投入2个研发持续贡献(一年可能一两百万)，实际上整个PG社区有数千人在贡献，对最终用户来说，简直赚到了，使用商业数据库，除了LICENSE等成本依旧需要投管理、研发、外包资源，一年数干万甚至上亿，公司越大，越有动力去贡献社区，从趋势来看，给PG贡献代码的大客户只会越来越多!
云厂商
- 开源数据库与云厂商发生利益冲突，纷纷改协议;
- 数据库市场巨大:
- 自研是最佳选择，但是自研有一些问题:警如需要培养生态，需要市场背书，需要大量研发资源，可能需要重复造轮子
- BASE PG的好处:
1、免去自己培养生态，
2 避色雷何造轮子
3、PG的代码基础非常不错(开源界的Oracle)
4、防止其他厂商控制PG失去市场主导能力(AWS,google,IBM,微软都已成为PG社区的赞助商)
数据库厂商
- 推一款新的商业数据库，通常都需要背书，小厂产品，谁为你背书?
1、有技术的厂商，很难挑战已有的数据库市场格局
2、有渠道的厂商，需要抓住窗口期，快速占领市场，避免重复造轮子。需要一款可以无法律风险，二次分发的开源数据库，唯有PG可以贡献核心代码，社区所有的用户都可以为之背书。
数据库服务IDaas服务提供商
- 开源产品的服务提供商，能力如何体现?
当然是你的架构能力，优化能力，管理能力，FIX BUG的能力
最好能贡献核心代码，有PG为你背书
1.2.2 完善的组织结构
1.2.3 开源许可独特性
(Postgres遵守BSD许可证发行)却使开发者们得以获取源代码并进一步开发系统。
BSD许可协议(英语:Berkeley software Distribution icens)是自由软件中使用最广泛的许可协议之一。BSD就是遵照这个许可证来发布，也因此而得名 BSD许可协议。BSD包最初所有者是加州大学的董事会，这是由于 BSD源自加州大学伯克利分校。BSD开始后，BSD许可协议得以修正，使得以后许多BSD变种，都采用类似风格的条款。跟其他条款相比，从GNU通用公共许可证(GPL)到限制重重的著作权(copyright)，BSD许可证比较宽松，甚至跟公有领域更为接近。“Take it down to the copy centerand make asmany copies as you want”，可以说，GPL强迫后续版本必须一样是自由软件，BSD的后续版本可以选择要继续是BSD或其他自由软件条款或封闭软件等等。
众所周知，MySQL被Oracle所控制，MySQL同时使用了GPL和一种商业许可(称为双重许可)。GPL(GeneralPubic icense)是公共许可，遵循了GPL的软件是公共的。如果某软件使用了GPL软件，那么该软件也需要开源，如果不开源，就不能使用GPL软件，这和是否把该软件商用与否是没关系的。
如果无法满足GPL，就需要获得商业许可，通过与0racle公司联系，制定解决方案，受Oracle公司约束。
同为开源软件，PostgreSQL源码使用自由友好、商业应用不受任何公司实体所控制，而MySQL则在一定程度上有所限制。
1.3 、PostgreSQL与MySQL的比较
PostgresQL相对于MySQL的优势
- 1)在SQL的标准实现上要比MVSQL完善，而目功能实现比较严谨。
- 2)对表连接支持较完整，优化器的功能较完整，支持的索引类型很多，复杂查询能力较强
- 3)PG主表采用堆表存放，MySQL采用索引组织表，能够支持比MySQL更大的数据量。
- 4)PG的主备复制属于物理复制，相对于MvSQL基于biniog的逻辑复制，数据的一致性更加可靠，复制性能更高，对主机性能的影响也更小。
- 5)PostgreSQL支持ISON和其他NOSQL功能，如本机XML支持和使用HSTORE的键值对，它还支持索引SON数据以加快访问速度，特别是10版本JSONB更是强大,
- 6)PostgresQL完全免费，而且是BSD协议，如果你把PostgreSQL改一改，然后再拿去卖钱，也没有人管你，这一点很重要，这表明了PostgreSOl数据库不会被其它公司控制。相反，MySQL现在主要是被Oracle公司控制。
MySQL相对于PG的优势
- 1)
innodtb的基于回滚段实现的MVCC机制，相对PG新老数据一起存放的基于XD的MVCC机制，是占优的。新老数据一起存放，需要定时触 发VACUUM，会带来多余的!0和数据库对象加锁开销，引起数据库整体的并发能力下降。而且VACUUM清理不及时，还可能会引发数据膨胀。- 2)MSQL采用索引组织表，这种存储方式非常适合基于主键匹配的査询、删改操作，但是对表结构设计存在约束,
- 3)MySQL的优化器较简单，系统表、运算符、数据类型的实现都很精简，非常适合简单的查询操作。
- 4)MySQL相对于PG在国内的流行度更高，PG在国内显得就有些落寞了。
- 5)MySQL的存储引擎插件化机制，使得它的应用场景更加广泛，比如除了innodb适合事务处理场景外，myisam适合静态数据的查询场景。
总结
- 从应用场景来说，PG更加适合严格的企业应用场景(比如金融、电信、ERP、CRM)，但不仅仅限制于此，PostgreSQL的json，jsonb，hstore等数据格式，特别适用于一些大数据格式的分析;而MVSQL更加适合业务逻辑相对简单、数据可靠性要求较低的互联网场量(比如go0gqle、(acebook、albaba)，当然现在MVSQL的在innodb引擎的大力发展，功能表现良好
扩展阅读:
PostgresQL 是中国第一的开源数据库?
https://cloud.tencent.com/developer/article/1847734
解密:为什么国产数据库使用PostgreSQl而不是MySQL
https://www.iidba.com/thread-290491-1-1.html
神仙打架:PG 和 MySQL 到底哪个更好用?
https://dbaplus.cn/news-11-3235-1.html
二、PostgresQL的下载安装
2.1、Windows上安装 PostgreSQL
(1) 下载 PostgreSQL 14.0
- 访问 官网下载地址
Windows版本
https://www.postgresql.org/download/- 本地资源库获取
双击下载安装包，开始安装
- ① 官网界面显示
- ② 选择系统，比如我都 Windows
- ③ 下载安装
- ④ 选择 右边 windows
(2) 安装
- ① 右击程序运行如下：
- ② 修改磁盘路径
- ③ 密码默认输入
postgres即可
- ④ PostgreSQL端口为
5432，傻瓜式安装即可
- ⑤ 最终完成安装。
（3） 启动登录(图像界面显示)
- ① 打开开始菜单进行开启
- ② 输入密码自定义密码
- ③ 汉化处理（设置后关闭打开就会生效）
- ④ 验证是否正常
select * from pg_database;
（3） 启动登录(命令行)
- ① 启动
- ② 验证是否正常
select * from pg_database;
2.2、远程 连接 PostgreSQL
出现这个错误是还没修改配置
修改配置：
- 1.打开
postgresql安装目录的data子目录
- 2.修改
pg_hba.conf文件:在IPV4部分添加新的一行:host all all 0.0.0.0/0 md5
- 控制面板–>系统与安全–>Windows 防火墙,关闭防火墙。
- 重启服务即可
- 5.远程还是连接不上，我用的navicat 是
12版的，下载15版本就可以
- 版本升级为Navicat15就可以啦
- 上面获取失败可通过网盘下载： Navicat15链接 提取码:
8h4x
2.3、Linux上安装 PostgreSQL
(1) 下载 PostgreSQL 14.0
- 访问 官网下载地址
Linux版https://www.postgresql.org/ftp/source/v14.0/述- 本地资源库下载获取
- ① 选择Linux
- ② 压缩包安装方式
- ③ 命令行安装方式（下面用到此方法安装）
(2) Linux 安装 PostgreSQL 14.0
- ① 虚拟机准备
- ② 官网下载获取RPM
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
- ③ 安装 PostgreSQL服务
sudo yum install -y postgresql14-server
- ④ 初始化数据库
sudo /usr/pgsql-14/bin/postgresql-14-setup initdb
- ⑤启动SQL服务以及设置自启动
#设置PostgresQL服务为开机自启动
sudo systemctl enable postgresql-14#启动PostgreSQL服务
sudo systemctl start postgresql-14
(2) 修改 postgres 账号密码
- PostgreSQL安装成功之后，会默认创建一个名为
postgres的linux用户，初始化数据库后，会有名为postgres的数据库，来存储数据库的基础信息，例如用户信息等等，相当于MySQL中默认的名为mysql数据库。- postgres数据库中会初始化一名超级用户
postgres为了方便我们使用postgres账号进行管理，我们可以修改该账号的密码- 进入PostgreSQL命令行,通过
su命令切换linux用户为postgres会自动进入命令行
su postgrespsql
- ① 修改密码也可以跳过这步(
下面修改就可以啦)
ALTER USER postgres WITH PASSWORD "NewPassword';
退出：
\qexit;
(3) 配置远程访问
- ① 开放端口(因为我的防火墙是关闭的)
sudo firewall-cmd --add-port=5432/tcp --permanent#修改配置文件
vi /var/lib/pgsql/14/data/postgresql.conf#将监听地址修改为
#默认1isten_addresses配置是注释掉的，所以可以直接在配置文件开头加入该行
listen_addresses='*'
# 允许所有IP访问
#修改配置文件
vi /var/lib/pgsq1/14/data/pg_hba.conf#在问价尾部加入
host all al1 0.0.0.0/0 md5# 重启PostgreSQL服务
sudo systemctl restart postgresql-14
- ② 再次远程访问
- ③ 进入修改密码：
su postgrespsqlalter user postgres with password 'postgres';
#验证
select * from pg_database;
三、PostgresQL的基本使用
3.1、登录
#psg1 -h 服务器 -U用户名 -d数据库 -p 端口地址 //-U 是大写
psql-U dbuser -d exampledb -h 127.0.0.1 -p 5432$psql (连接数据库，默认用户和数据库都是postgres)
#相当于系统用户posteres以同名数据库用户的身份，登录数据库，这是不用输入密码的。如果一切正常，系统提示符会变为"pstgres=#"，表示这时己经进入了数据库控制台。
3.2、数据库操作
第一种创建方式：
#创建数据库CREATE DATABASE mydb;
#查看所有数据库
\l#切换当前数据库
\c mydb#删除数据库
drop database <dbname>
第二种创建方式：
su postgres#这个里面就是可以使用createdb的，上面不可的
createdb mydb; psql -d mydb;\c postgres;
\l
这里刷新也是有的：
3.3、数据库表操作
3.3.1 创建数据库表（自增与mysql有区别）
3.3.2 数据表插入数据（与mysql相同）
创建表格时每列都必须使用数据类型。PotgreSQL中主要有三种数据类型:
- 数值数据类型
- 字符串数据类型
- 日期/时间数据类型
字符串字符串类型包括
char(size)，character(size):固定长度字符串，size 规定了需存储的字符数，由右边的空格补齐;varchar(size)，character varying(size):可变长度字符串，size 规定了需存储的字符数;text:可变长度字符串。
日期/时间
表示日期或时间的数据类型有:
timestamp:日期和时间:date:日期，无时间;time:时间;
其他数据类型类型还有布尔值 boolean(true 或 false)，货币数额 money 和 几何数据等。
\c mydb;#创建表
create table test(id int,name varchar(20));#在表中插入数据
insert into test values(1,'Daniel');#查看当前数据库下所有表\d#查看表结构，相当于desc
\d test
PostgreSQl使用序列来标识字段的自增长，数据类型有smalserial、serial和 bigserial，这些属性类似于 MySQL 数据库支持的 AUTO_INCREMENT 属性,SMALLSERIAL、SERIAL和 BIGSERIAL 范围:
示例
\c mydb#自增不需要 数据类型 默认为Integer
create table test2(id serial primary key,name varchar(30));insert into test2(name) values('hollow');
insert into test2(name) values('Daniel');select * from test2;
3.3.3 修改删除表数据操作（与mysql相同）
select * from test2 where id = 2;update test2 set name = '小金' where id = 2;delete from test2 where id=1;
3.4、Schema
- PostgreSQL模式(SCHEMA)可以看着是一个表的集合。
- 一个模式可以包含
视图、索引、数据类型、函数和操作符等。- 相同的对象名称可以被用于不同的模式中而不会出现冲突，例如
schema1和myschemà都可以包含名为 mytable 的表。
- 使用模式的优势:
- 允许多个用户使用一个数据库并且不会互相干扰。
- 将数据库对象组织成逻辑组以便更容易管理。
- 第三方应用的对象可以放在独立的模式中，这样它们就不会与其他对象的名称发生冲突，模式类似于操作系统层的目录，但是模式不能嵌套。
#创建schema:
create schema myschema;create table myschema.company(id int,name varchar(20));#删除schema
drop schema myschema;
#删除一个模式以及其中包含的所有对象:
DROP SCHEMA myschema CASCADE;
3.5、如何备份PostgreSQL数据库(了解)
- 如果您在生产环境中使用
PostgreSQL，请务必采取预防措施以确保用户的数据不会丢失。
单数据库
PostgreSQL提供了 pg_dump 实用程序来简化备份单个数据库的过程。 必须以对要备份的数据库具有读取权限的用户身份运行此命令。
以postgres 用户身份登录:
sudo su- postgres
- 通过运行以下命令将数据库的内容转储到文件中。替换 dbname 为要备份的数据库的名称。
pg_dump dbname >dbname.bak
- 生成的备份文件
dbname.bak可以使用scp传输到另一台主机，也可以存储在本地以供以后使用。要演示恢复丢失的数据，请删除示例数据库,并在其位置创建一个空数据库:使用psql恢复数据库
psql test < dbname.bak
备份格式有几种选择:
*.bak:压缩二进制格式
*.sql:明文转储
*.tar : tarball
注意:默认情况下，
postgreSQL将忽略备份过程中发生的任何错误。这可能导致备份不完整，要防止这种情况，您可以使用-1选项运行pg_dump命令。这会将整个备份过程视为单个事务，这将在发生错误时阻止部分备份。
所有数据库
- 由于
pg_dump一次只创建一个数据库的备份，因此它不会存储有关数据库角色或其他群集范围配置的信息。 要存储此信息并同时备份所有数据库，可以使用pg_dumpall创建备份文件
pg dumpall >pg backup.bak
从备份还原所有数据库:
psql -f pg backup.bak postgres
#备份数据库
$pg_dump -U postgres -f /tmp/postgres.sql postgres(导出postgres数据库保存为postgres.sql)$pgdump-Upostgres -f /tmp/postgres.sgl-t test postgres(导出postgres数据库中表test的数据)$ pg_dump -U postgres -Ft-f /tmp/postgres.tar postgres(导出postgres数据库以tar形式压缩保存为postgres.tar)#恢复数据库
$psql-U postgres -f /tmp/postgres.sglbk1(恢复postgres.sql数据到bk01数据库)#pg_restore -- 从pg_dump创建的备份文件中恢复PostgresQL数据库,用于恢复出pg_dump转储的任何非纯文本格式中的PostgresQL数据库$pg_restore -U postgres-dbk01 /tmp/postgres.tar(恢复postgres.tar数据到bk01数据库)
3.6、用户操作(了解)
#创建用户并设置密码
CREATE USERtusername'WITH PASSWORDpassword';CREATE USER test WITH PASSWORD "test";#修改用户密码
$ALTER USERusername" WITH PASSWORD "password";#数据库授权,赋予指定账户指定数据库所有权限
$GRANT ALL PRIVILEGES ON DATABASE 'dbname' To 'username";#将数据库mydb权限授权于 test
GRANT ALL PRIVILEGES ON DATABASE mydb To test;#但此时用户还是没有读写权限，需要继续授权表
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO xxx;
#注意，该sq1语句必须在所要操作的数据库里执行#移除指定账户指定数据库所有权限
REVOKE ALL PRIVILEGES ON DATABASE mydb from test#删除用户
drop user test#查看用户
\du
注意:要指定数据库名，如果不指定默认使用与用户同名的数据库。
peer不是常用的方式!最常用的方式是通过密码远程登陆。
3.7、PostgreSQL 角色管理(了解)
在PostgreSQL 里没有区分用户和角色的概念，"
CREATE USER"为"CREATE ROLE”的别名，这两个命令几平是完全相同的，唯一的区别是"CREATE USER"命令创建的用户默认带有LOGIN属性，而"CREATE ROLE"命令创建的用户默认不带LOGIN属性。
3.8、控制台常用命令总结
\password 命令(设置密码)\q 命令(退出)\h: 查看SQL命令的解释，比如\hselect\?: 查看psql命令列表。\l : 列出所有数据库。\c [database_name]:连接其他数据库\d: 列出当前数据库的所有表格。\d [table_name]:列出某一张表格的结构。\du: 列出所有用户。