PostgreSQL®是什么？

2022年6月9日 → 公元2022年6月9日

什么是PostgreSQL®？

在本页面中，我们将介绍PostgreSQL的内部和外部。

这篇帖子是一个全面介绍PostgreSQL的入门指南。它涵盖了许多问题。

• PostgreSQLデータベースとは何ですか？

 

• PostgreSQLは何をするのか、何が特別なのか？

 

• PostgreSQL はどのように動作するのか？

 

PostgreSQL はどのようにデータアーキテクチャに適合するのですか？

在这篇文章中

1. 什么是PostgreSQL，为什么需要关注它？
2. PostgreSQL做了什么？
3. PostgreSQL的使用示例和用户。
4. PostgreSQL的功能。
5. PostgreSQL是如何运作的？
6. Aiven提供的PostgreSQL。
7. 在Aiven上开始使用PostgreSQL。
8. 获取更多阅读资料。

什么是PostgreSQL，为什么应该使用它？

定义和用途

PostgreSQL，也称为Postgres，是一种灵活的开源对象关系型数据库管理系统。PostgreSQL能够处理非常多样的用例，包括从单机到数据仓库以及具有大量并发用户的Web服务。PostgreSQL使用SQL并进行了扩展（这也是其名称的由来），不仅可以处理事务数据，还可以广泛扩展到各种用例。

PostgreSQL得到了由企业和个人贡献者组成的全球开发团队的强力支持。

请参考(Postgre)SQL的概念和术语，了解最常见的适用于PostgreSQL的SQL术语和概念的定义。

PostgreSQL有什么作用？

作为关系数据库，PostgreSQL将数据存储在包含元组的表中，这些元组用于表示实体（如文档和人）和关系（如作者关系）。关系保存了实体属性（如标题）的固定类型属性，并与主键一起存在。属性类型可以是原子类型（如整数、浮点数、布尔值）或结构化类型（如数组、嵌套JSON、存储过程等）。

PostgreSQL支持具有ACID属性的事务。也就是说，事务必须支持四个属性：

交易要么完全成功，要么完全失败。

所有写入数据库的数据必须遵循现有的约束、触发器、级联以及相关组合。

通过**-同时执行控制**功能来确保数据不会被恶意交易或同时执行交易破坏。

通常情况下，已完成的交易会被记录在预先写入日志等地方。

PostgreSQL的使用案例和用户

根据DB-Engines的排名，PostgreSQL是第4个最受欢迎的数据库管理系统（也是最受欢迎的开源DBMS）。

今天，除了企业用户之外，许多第三方供应商、咨询公司和独立团体也从PostgreSQL派生出产品，并提供咨询、支持和附加功能扩展。还有一些与人工智能（AI）和机器学习（ML）集成的版本，以及许多云端和硬件优化版本。在我们的博客中，还有更多关于PostgreSQL使用的示例。

使用Kafka和PostgreSQL构建数据流水线
使用PostgreSQL的行业主要集中在计算机软件行业，其次是信息技术和服务行业。其他行业包括医院和医疗、高等教育、金融服务、通信、教育、营销和广告等。政府机构，包括FBI，也是用户。

PostgreSQL 的特点

有关PostgreSQL支持的功能类型，请参考PostgreSQL。

可以自动更新的视图

除了提供SQL支持外，PostgreSQL还提供了可以自动更新的视图。这意味着当事务更新了视图提供的数据时，查询结果的视图会自动更新。

物質化されたビュー

物化视图是包含指定查询结果的数据对象。可以通过查询直接使用物化视图，就像查询数据库表一样。此外，物化视图基本上是某一时刻数据的快照，可以按照时间表更新。

触发 (chinese)

触发器会根据对表格或视图的事务或其他更改执行代码。当项目在清单中更新时，为了追踪目的，如果希望自动将更新时间和执行操作的用户名记录到专用列中。

外键

参照: 主键和外键的存在是为了确保子表和父表之间有有效的引用。例如，订单表的每一行都需要具有与订单本身相关联的有效客户引用。

存储过程

存储过程存储在数据库的数据字典中，可以组合多个SQL存储过程。存储过程通常为应用程序提供常用的数据验证、数据操作、访问控制和其他方法的访问。存储过程由用户定义，并在需要时执行。

发布/订阅逻辑复制

PostgreSQL提供了发布/订阅的逻辑复制方法。基本上，PostgreSQL实例会作为应用程序和事件消费者之间的事件缓存进行运作。

理解 PostgreSQL 的逻辑复制。（请参考未来的 PostgreSQL 博客文章中涉及复制协议的新改进点。）

数据导入的变更

此外，还有一种变更数据捕获的方法。换句话说，可以回滚和重放先前的数据库变更，这是灾难恢复所必需的关键功能。

文本搜索

PostgreSQL可以支持根据文本内容和通配符组合来搜索列值的SQL查询。

SELECT ˶* from books WHERE column_name LIKE 'Zen%';`Copy to clipboard

可以返回包含类似《ZTM保养摩托车与禅》或《禅肉禅骨》等书名的记录。通配符也可以放在搜索字符串的开头。在相同的查询中，如果搜索字符串为%Zen%，则会返回类似上述的结果。此外，还会返回标题中不是以”Zen”作为首个单词的书，如《禅三法宝》或《白隐禅师的禅宗要旨》等。

除了文本搜索外，PostgreSQL还支持tsvector（文本搜索）数据类型。

可以使用函数 to_tsvector 将字符串进行分词并生成一个列表，用于创建 tsvector。而 to_tsquery 则用于查询特定的单词或短语是否出现在 tsvector 中。

递归查询 Guī

PostgreSQL提供了14种方法用于生成递归查询。此外，它还有新的SEARCH和CYCLE功能，对于递归查询非常方便。

对Json语言的支持

PostgreSQL支持优秀的JSON语言。您可以将表的列定义为JSON或JSONB数据类型，并使用JSON函数来解析和操作其内容。

同时执行

PostgreSQL是最著名的一個支持並行ACID事務的開源數據庫之一。為了實現這一點，PostgreSQL可以使用多個鎖來將數據庫的更改進行串行化。如果有同時的更改，第一個事務可以繼續進行（因為鎖已被呼叫）。

以下是一个示例。假设你打开了两个终端窗口。在 psql 会话中，执行以下命令：

 CREATE TABLE records (
 header text NOT NULL primary key、
 info json NOT NULL
 );

 BEGIN；
 ALTER TABLE records ADD COLUMN recent_update timestamp;`クリップボードにコピーする

在另一个psql会话中尝试执行以下命令：

 SELECT \* FROM records;`クリップボードにコピーする

可信度和灾害恢复

在将全页图像写入磁盘之前，PostgreSQL会定期将其写入预写日志（WAL）。这使得PostgreSQL能够通过重新播放写入日志来从崩溃中恢复。

如果系统内存在阻碍部分写入的情况，可以将postgresql.conf文件中的`full_page_writes`参数切换为关闭状态。

您可以在Aiven的博客中进一步阅读有关此话题的内容：数据与灾害恢复。

WAL的每个记录通过CRC-32校验和进行校验，以验证（在恢复操作时）记录的内容是否正确。然而，一些项目如数据页和内部数据结构等存在绕过校验的情况。

安全

PostgreSQL有多种处理各类安全问题的方法。例如，除非在启动服务器端时使用 -i 开关，否则客户端连接默认仅允许通过本地的Unix套接字进行（即不允许通过TCP/IP套接字）。此外，还可以在PG_DATA目录下的pg_hba.conf文件中将客户端连接列入黑名单。

对于对象的超级用户权限是PostgreSQL中用于执行各种操作的选项之一。例如，只有PostgreSQL的超级用户帐户才能读取DB文件本身。通过授予这些权限，可以复制表数据，创建自动发布项以自动发布数据，创建订阅等。这些权限也对于逻辑复制非常重要。

实际上，每个PostgreSQL用户都需要一个用户名和密码。您可以将用户分配到不同的组，并基于组权限来分配或限制对表或行的访问。

PostgreSQL还提供许多加密选项，如特定列加密、数据分区加密和网络加密。

为了防止服务器被伪装，通过创建指向重新配置的弱点套接字文件的符号链接来实现功能。

扩展性

PostgreSQL是一种目录驱动（catalog-driven）的数据库。数据字典，即系统目录，存储了有关数据库本身、单元、列、数据类型、函数、访问方法等信息。这些目录本身就是表，并且用户可以查询和修改它们。

许多用户选择使用PostgreSQL进行应用程序和数据结构的快速原型设计，因为他们可以直接修改数据库并能够动态加载可更改的代码以及添加用户定义函数（UDF）。

对于许多针对特定行业和业务的组织来说，扩展性非常重要。如果传统的数据库系统不够用，并且业务用户本身不是数据库专家，即使更改数据库也不会影响可用性，也不需要改变核心数据库引擎。

实际上，用户正在寻求无缝的解决方案。在 PostgreSQL 中，可以无缝地创建自定义数据类型、函数、聚合、操作符、操作符类（用于索引访问）、数据封装器等，为特定领域的解决方案提供工具和插件。

一种可能情况是，扩展功能本身可以成为一个完整的产品。具体例子包括PostGIS已经通过改进来支持基于地理坐标的位置查询。此外，[Timescale]是专为时序数据优化的PostgreSQL。

我将介绍一些与其他工具一起使用PostgreSQL的方法：

• PostgRESTでREST APIを素早く開発する

 

• Pulumi でマルチリージョンの PostgreSQL を立ち上げる方法

 

• あなたのGoアプリケーションのためのAiven for PostgreSQL

 

あなたのLaravelプロジェクトにAivenデータベースマジックを追加

工具支持

最近的开发者们可以享受到许多超越psql的工具来与数据库进行交互。pgAdmin是全面支持PostgreSQL，并深受DBA和开发者喜爱的多年工具。还有特定于Aiven的配置步骤。此外，连接功能的终端前端工具也非常方便。

JetBrains的DataGrip是一款受欢迎的集成开发环境(IDE)，可在多平台上运行，并且适用于各种数据库。usql是一种能够在多个数据库中以本机方式运行的通用命令行接口。最后，OmniDB是针对PostgreSQL和其他数据库的完整协作工作空间。

除了核心的PostgreSQL外，Aiven的开发人员还对pghoard（用于备份和恢复）、pglookout（用于复制监控和故障转移守护程序）以及许多PostgreSQL扩展，包括各种连接器，做出了贡献。在Aiven版本中可运行的扩展功能完整列表请参见此处。

在可视化数据方面，可以选择开源分析工具Grafana。借助Aiven平台，可以将PostgreSQL和Grafana进行集成，并详细说明如何做到。Grafana也是监测PostgreSQL指标的最佳工具。

或者，您可以查看如何使用pgwatch2监控指标的方法。

关于编排，你可以使用Terraform来管理PostgreSQL数据库。

将管理PostgreSQL数据库委托给他人。

在上述内容中，我们提到了PostgreSQL如何支持数据操作者，并介绍了许多令人兴奋的功能。然而，这并不是数据库管理的全部。无论使用何种数据库，都需要进行许多日常操作来实现高可用性、稳定的性能和坚如弹丸的数据库安全性。

随着数据库和应用程序的增长，这些例行操作将消耗大量时间，并造成意外错误带来的巨大成本。因此，在某一时刻，您可能会考虑利用专家支持来管理PostgreSQL数据库，或者使用外部支持来执行数据库管理任务。

经常发生错误的常见操作包括以下几种：

定期的なバックアップと、必要なときのデータのリストア；

メジャーバージョンアップやフェイルオーバー手順の制御を行う簡単な方法を含め、最新のセキュリティアップデートやパッチを常に把握しておくこと；

コネクション・プーリングの助けを借りて、データベースへの非常に多くの接続を維持する。

依赖托管服务可以在使用和管理方面带来优势。

委托管理PostgreSQL数据库并不是一个简单的选择。然而，这可能是双向门的决定。最终，如果你坚持选择开源解决方案，你随时可以恢复对服务器的管理。

还有其他许多选择。

在PostgreSQL的官方网页上，有列举了许多功能，包括支持的数据类型、更详细的数据完整性功能、支持货币功能、灾难恢复功能、安全功能、可扩展性，以及包括国际字符支持在内的文本搜索功能。

PostgreSQL的工作原理是什么？

PostgreSQL的核心要求数据完全结构化，并符合特定的模式。

PostgreSQL经过进化，现在支持JSON/JSONB、XML以及其他常见的数据输入输出格式。

因为PostgreSQL是一个开源的数据库管理系统，所以PostgreSQL用户可以在不重新编译的情况下切换语言，并且可以持续地使数据库发展。虽然对SQL的兼容性是持续进行的，但是也总是在不断地添加新功能。让我们来看看在PostgreSQL中，对象是如何创建和操作的。

由於SQL非常廣泛，因此我們只觸及基本的部分。

DDL – 数据定义语言

数据定义语言（DDL）用于定义数据结构，并包含以下命令：

• データ型宣言

 

• CREATE` – 新しいデータベース、テーブル、インデックス、プロシージャを作成する。

 

• ALTER` – 既存のデータベースオブジェクトを変更する。

 

• DROP` – 既存のデータベース、テーブル、インデックスまたはプロシージャを削除する。

 

TRUNCATE TABLE` – テーブルを再利用するために空にする。

例如

 データベースライブラリを作成する；

 CREATE TABLE Borrowers (
 id INT PRIMARY KEY、
 first_name VARCHAR(20) not null、
 last_name VARCHAR(50) not null、
 birth_date DATE not null)；

 ALTER TABLE Borrowers ADD COLUMN years_member INT NOT NULL CHECK years_member > 0；

 CREATE TABLE books (
 id INT PRIMARY KEY、
 タイトル VARCHAR(50)、
 著者 VARCHAR(50)
 publication_date DATE not null)；

 /* books'テーブルへの入力後 */

 TRUNCATE TABLE books；

 /* books'テーブルから全てのデータを削除する */`クリップボードにコピーする

DML – 数据操作语言

数据操作语言（DML）是用于操作表格及其内容的一种语言：

• INSERT` – テーブルに新しい行を挿入する。

UPDATE – テーブルの既存の行を更新する。
DELETE` – テーブルから行を削除する。

SELECT – データベースからデータを問い合わせる主なメソッドである。

比如说

 INSERT INTO Borrowers(id,first_name,last_name,birth_date,years_member) VALUES(1,'Riley','Jimbo',1964-05-02,12)；
 INSERT INTO Borrowers(id,first_name,last_name,birth_date,years_member) VALUES(2,'Smith','Bubba',1973-09-21,5)；
 INSERT INTO Borrowers(id,first_name,last_name,birth_date,years_member) VALUES(3,'Eberhardt','Jocelyn',1930-12-22,71)；

 UPDATE Borrowers SET years_member = 1 WHERE id = 1；
 DELETE FROM Borrowers WHERE years_member > 70；

 SELECT * FROM Borrowers WHERE years_member > 5;`クリップボードにコピーする

用于批量插入的简洁语法

有一种简单的方法可以使用一个命令一次插入多行文本：

 INSERT INTO Borrowers (id,first_name,last_name,birth_date,years_member) VALUES
 (4, 'Evelyn', 'Snarfington', 1959-08-03, 8)、
 (5, 'Robert', 'Jones', 2000-01-23, 6)、
 (6, 'Joseph', 'Chu', 1987-11-07, 10);`Copy to clipboard

总述DCL和TCL

DCL（数据控制语言）是用于管理对数据库系统的访问权限和其他控制的语言。它由以下语句组成：

• GRANT` – アクセス権限を付与する。

 

REVOKE` – アクセス権限を取り消す。

TCL – 事务控制语言 – 与数据库的事务有关，由以下语句组成：

• COMMIT` – トランザクションをコミットする。

 

• ROLLBACK` – エラーが発生した場合、トランザクションをロールバックする。

 

• SAVEPOINT` – トランザクションのセーブポイントを設定する。

 

SET_TRANSACTION` – トランザクションの特性を定義する。

查询数据

在PostgreSQL中，几乎所有的查询（与SQL本身类似）是通过SELECT语句来执行的，例如以下示例：

クリップボードにコピーします。

查询可以通过限定条件来筛选结果，以各种方式进行细化。

SELECT \* FROM Borrowers WHERE years_member > 3;`クリップボードにコピーする

将结果限制在特定的数量上

SELECT \* FROM Borrowers LIMIT 5;`クリップボードにコピーする

只查询特定的字段

SELECT id, last_name, years_member FROM Borrowers;`Copy to clipboard

在特定的字段中对查询结果进行排序。

SELECT \* FROM Borrowers ORDER BY years_member DESC;`Copy to clipboard

这样做之类的。

PostgreSQL 在 Aiven 上得到应用。

Aiven for PostgreSQL是面向所有提供者的托管托管解决方案，提供了自动更新和备份功能以确保高可用性。Aiven支持各种扩展，如PostGIS和TimescaleDB，并提供发布/订阅逻辑复制。

Aiven for PostgreSQL只是我们提供的数据平台的一部分。它可以作为整合在包括Aiven for Apache Kafka、OpenSearch、Redis等系统中的一部分。

如果你无法决定选择哪个云服务平台，可以看看最新的GCP、AWS和Azure的基准测试！

PostgreSQL在云端的表现

开始使用Aiven for PostgreSQL时，

首先，最好阅读开发者门户网站的说明。

然而，Aiven for PostgreSQL的设置非常简单。在这里，我将介绍如何使用Aiven控制台开始设置：

此外，您还可以通过使用命令行或REST API直接进行操作。

请查看developer.aiven.io网站上的PostgreSQL部分以获取更详细的信息！

请继续阅读

在 Aiven 的博客中，介绍了 PostgreSQL 的酷炫用法。