PostgreSQL高级篇

发表于 2022-12-05 更新于 2024-03-25 分类于 OS ， devops ， backend ， postgresql 阅读次数：本文字数： 3.5k 阅读时长 ≈ 3 分钟

1. PG简介

PostgreSQL是一个免费的对象-关系型数据库服务器(ORDBMS), 在灵活的BSD许可证下发行.
PostgreSQL开发者把它念作post-gress-Q-L.
PostgreSQL的Slogan是世界上最先进的开源关系型数据库.
开源界的Oracle, 去O首选

PostgreSQL官网
https://www.postgresssql.org/

PostgreSQL中文社区
https://www.postgres.cn/v2/home

中国数据库流行度排行
https://www.modb.pro/dbRank

全球数据库流行度趋势
https://db-engines.com/en/ranking

1.1. PG的历史

PostgreSQl最初设想于1986年, 当时被叫做Barkley Postgre Project.

该项目一直到1994年都处于演讲和修改中, 直到开发人员Andrew Yu和Jolly Chen在Postgres中添加了一个SQL(Structured Query Language, 结构化查询语言)翻译程序, 该版本叫做Postgre95, 在开放源代码社区发放. 开始以社区的形式运作.

1996年, 再次对Postgre95做了较大的改动, 并将其作为PostgreSQL6.0版本发布. 该版本的Postgre提高了后端的速度, 包括增强型SQL92标准以及重要的后端特性(包括子选择, 默认值, 约束和触发器)

2005年, 发布8.0版本, 开始支持Windows系统环境

PostgreSQL 9.0: 支持64位Windows系统, 异步流数据复制, Hot Standby;

PostgreSQL 9.1: 支持数据同步复制, unlogged tables, serializable snapshot isolation, FDW外部表.

此版本后, PostgreSQL开始得到中国多个行业用户的关注, 开始有应用于电信, 保险, 制造业等边缘系统.

目前生产环境主流的版本是PostgreSQL 12

2021-09-30, PostgreSQL全球开发组宣布, 功能更为强大的开源数据库, PostgreSQL 14版本正式发布!

1.2. PG的社区

PG为什么没有被商业公司控制?

纯社区

Sponsor 占比:

服务| Daas提供商: 29%
最终用户: 37%
数据库厂商: 21%
云厂商

最终用户深度参与, 使得PG社区像滚雪球越滚越大.

AWS -> bigsql
MS -> citus
IBM -> redhat

sponsors:

Pivotal, 2ndQuadrant, Rackspace, NTTGroup, VMWare, Zalando,
Crunchy Data, DALIBO, meetme, CONOVA, EDB, Google, data egret,
PGX, Postgres, Yandex, IBM, Fujitsu, Redpill, Amazone.

他们为什么要贡献核心代码?

最终用户

希望社区长久, 期望可以享受免费, 可持续发展的, 开源的, 不被任何商业公司, 不被任何国家控制的企业级数据库. 去O, 去DB2, 去Sybase;
不靠数据库赚钱;
PG用到的人越多, 越多人背书, 使用越靠谱(事实也是如此);
抛砖引玉, 企业投入两个研发持续贡献(一年可能一两百万), 实际上整个PG社区有数千人在共享, 对最终用户来说, 简直是赚到了. 使用商业数据库, 除了License等成本, 依旧需要投管理, 研发, 外包资源, 一年数千万甚至上亿, 公司越大, 越有动力去贡献社区. 从趋势来看, 给PG贡献代码的大客户只会越来越多;

云厂商

开源数据库与厂商发生利益冲突, 纷纷改协议;
数据库市场巨大;
自研是最佳选择, 但是自研有一些问题: 譬如需要培养生态, 需要市场背书, 需要大量研发资源, 可能需要重复造轮子;
BASE PG的好处:
- 免去自己培养生态,
- 避免重复造轮子,
- PG的代码基础非常不错(开源界的Oracle)
- 防止其他厂商控制PG失去市场主导能力(AWS, Google, IBM, 微软都已经成为PG社区的赞助商)

数据库厂商

推一款新的商业数据库, 通常都需要背书, 小厂产品, 谁为你背书?
- 有技术的厂商, 很难挑战已有的数据库市场格局
- 有渠道的厂商, 需要抓住窗口期, 快速占领市场, 避免重复造轮子.
  需要一款可以无法律风险, 二次分发的开源数据库, 唯有PG. 可以贡献核心代码, 社区所有的用户都可以为之背书.

数据库服务|Daas服务提高商

开源产品的服务提供商, 能力如何体现?
- 当然是你的架构能力, 优化能力, 管理能力, FIX BUG的能力
- 最好能贡献核心代码, 有PG为你背书

主要贡献者列表:

https://www.postgresql.org/community/contributors

全球赞助商

https://www.postgresql.org/about/sponsors

PostgreSQL中文BBS

https://bbs.pgsqldb.com/client/index.php

PosgreSQL用户

https://www.postgresql.org/about/users

1.3. PostgreSQL许可证

1.4. PostgreSQL与MySQL的比较

PostgreSQL相对于MySQL的优势

在SQL的标准实现上要比MySQL完善, 而且功能实现比较严谨.
对表连接支持较完整, 优化器的功能完善, 支持的索引类型很多, 复杂查询能力较强.
PG主表采用堆表存放, MySQL采用索引组织表, 能够支持比MySQL更大的数据量.
PG的主设备复制属于物理复制, 相对于MySQL基于binlog的逻辑复制, 数据的一致性更加可靠, 复制性能更高, 对主机性能的影响也更小.
PostgreSQL支持JSON和其他NoSQL功能, 如本机XML支持和使用HSTORE的键值对. 它还支持索引JSON数据以加快访问速度, 特别是10版本JSONB更是强大.
PostgreSQL完全免费, 而且是BSD协议, 如果你把PostgreSQL改一改, 然后再拿去卖钱, 也没有人管你, 这一点很重要, 这表明了PostgreSQL数据库不会被其它公司控制. 相反, MySQL现在主要是被Oracle公司控制.

MySQL相对于PG的优势

innodb的基于回滚段实现的MVCC机制, 相对PG新老数据一起存放的基于XID的MVCC机制, 是占优势的. 新老数据一起存放, 需要定时触发VACUUM, 会带来多余的IO和数据库对象加锁开销, 引起数据库整体的并发能力下降. 而且VACUUM清理不及时, 还可能引发数据膨胀.
MySQL采用索引组织表, 这种存储方式非常适合基于主键匹配的查询, 删改操作, 但是对表结构设计存在约束.
MySQL的优化器比较简单, 系统表, 运算符, 数据类型的实现很精简, 非常适合简单的查询操作.
MySQL相对于PG在国内的流行度更高, PG在国内显得就有些落寞了.
MySQL的存储引擎插件化机制, 使得它的应用场景更加广泛, 比如除了innodb适合事务处理场景外, myisam适合静态数据的查询场景.

总结:

从应用场景来看, PG更加适合严格的企业应用场景(比如金融, 电信, ERP, CRM), 但不仅仅限制于此, PostgreSQL的json, jsonb, hstore等数据格式, 特别适用于一些大数据格式的分析; 而MySQL更适合业务逻辑相对简单, 数据可靠性要求较低的互联网场景(比如facebook, google, alibaba), 当然现在MySQL引擎的大力发展, 功能表现良好.

2. 安装PostgreSQL

Windows安装教程可参考 https://www.pengtech.net/database/install_postgresql_on_windows
Linux安装教程可参考 Centos7上安装PostgreSQL 14

3. PostgreSQL的基本使用

3.1. 登录


# psql -h 服务器 -U 用户名 -d 数据库 -p 端口地址 
psql -U dbuser -d exampledb -h 127.0.0.1 -p 5432

$ psql (连接数据库, 默认用户和数据库都是postgres)

# 相对于系统用户postgres以同名数据库用户的身份, 登录数据库, 这是不用输入密码的.
# 如果一切正常, 系统提示符会变成"postgres=#". 表示这时已经进入数据库控制台.

3.2. 创建数据库

创建一个叫exampleDB的数据库

1
2
3


createdb -h localhost -p 5432 -U postgres exampleDB

4. 参考文章

数据库流行度排名