当前位置：首页 > news >正文

PostgreSQL（PG）(二十二)

news 来源：原创 2024/9/20 12:29:25

🌻🌻 目录 🌻🌻

一、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: 列出所有用户。