PostgreSQL入门介绍

标签：PostgreSQL 入门 数据库 介绍 查询 复制 支持 SQL

一、PostgreSQL 背景及主要功能介绍

1、背景

PG数据库，全称为PostgreSQL数据库，是一款开源的关系型数据库管理系统（RDBMS）。其起源可以追溯到20世纪80年代末和90年代初，由加拿大的计算机科学家Michael Stonebraker及其团队在加州大学伯克利分校启动。该项目旨在创建一个强大的、开源的关系型数据库管理系统，作为早期关系型数据库系统Ingres的继承者。Michael Stonebraker之前曾领导过Ingres项目，拥有丰富的数据库系统开发经验。

2、主要功能

PostgreSQL 是一个功能强大的开源对象-关系数据库系统，它支持几乎所有的 SQL 标准，并且提供了许多高级功能，使其适用于各种复杂的应用场景。以下是 PostgreSQL 的一些主要功能介绍：

1）ACID 兼容的事务处理：

PostgreSQL 完全支持 ACID（原子性、一致性、隔离性、持久性）事务处理，确保数据的一致性和完整性。

2）多版本并发控制（MVCC）：

通过 MVCC，PostgreSQL 允许读取操作不加锁，从而提高了数据库的并发性能。这意呀着即使在有大量并发写入的情况下，读取操作也不会被阻塞。

3）复杂查询和索引：

PostgreSQL 支持复杂的 SQL 查询，包括子查询、连接（JOINs）、窗口函数等。它还支持多种索引类型，如 B-tree、GiST、GIN、BRIN、SP-GiST、RUM 和 Hash 索引，以及全文搜索和地理空间索引，以优化查询性能。

4）大型对象支持：

PostgreSQL 允许存储和检索大型对象（如图片、视频文件等），这些对象可以像普通数据类型一样被查询和修改。

5）触发器、存储过程和函数：

支持使用 PL/pgSQL（PostgreSQL 的过程语言）编写触发器、存储过程和函数，允许在数据库层面上实现复杂的业务逻辑。

6）数据完整性和约束：

支持各种数据完整性和约束，如主键、外键、唯一约束、检查约束等，确保数据的准确性和可靠性。

7）扩展性和可定制性：

PostgreSQL 提供了丰富的扩展接口，允许开发者通过编写自定义函数、数据类型、索引方法等来扩展数据库的功能。此外，PostgreSQL 社区还提供了大量的扩展模块，如 PostGIS（用于地理空间数据）、pgCrypto（用于加密）等。

8）高可用性和容错性：

通过流复制、逻辑复制、热备等技术，PostgreSQL 可以实现高可用性和容错性，确保在硬件故障或软件错误时数据的可用性和服务的连续性。

9）国际化支持：

PostgreSQL 支持多种字符集和排序规则，能够存储和处理多种语言的数据，包括 Unicode 字符集。

10）网络透明性：

PostgreSQL 支持客户端/服务器模型，客户端和服务器可以位于不同的机器上，通过网络进行通信。这使得 PostgreSQL 非常适合分布式和远程访问的应用场景。

11）安全性：

PostgreSQL 提供了多种安全特性，如 SSL/TLS 加密、行级安全、角色和权限管理等，以保护数据免受未授权访问和泄露。

3、和MySql对比

PostgreSQL和MySQL都是广泛使用的开源关系型数据库管理系统（RDBMS），它们在多个方面存在显著的差异。以下是对两者在主要功能、性能、扩展性、数据类型支持、事务处理等方面的详细对比：

1）功能支持

PostgreSQL：

支持更丰富的数据类型，如数组、范围、JSON、XML、几何类型等，适合处理复杂数据。
提供了强大的SQL标准遵循，支持更多的SQL特性和语法，如窗口函数、递归查询等。
支持触发器、存储过程、自定义函数等高级功能，允许在数据库层面上实现复杂的业务逻辑。
提供了丰富的扩展接口，允许开发者通过编写自定义函数、数据类型、索引方法等来扩展数据库的功能。

MySQL：

支持多种存储引擎，如InnoDB（默认）、MyISAM等，每种存储引擎都有自己的特点和优缺点。
提供了基本的SQL支持，但在某些高级特性和语法上可能不如PostgreSQL全面。
支持触发器、存储过程等，但相对于PostgreSQL来说，功能可能较为有限。

2）性能与扩展性

PostgreSQL：

在处理复杂查询和高并发写入时表现优异，特别是在启用并行查询和分区功能后。
垂直扩展能力较强，能够处理更大规模的数据和更复杂的查询。
支持多版本并发控制（MVCC），提高了数据库的并发性能。

MySQL：

在处理高并发读取和简单的写入操作时表现良好，特别是在使用InnoDB存储引擎时。
水平扩展能力较强，通过主从复制、分片等技术可以轻松实现读写分离和负载均衡。
同样支持MVCC，但实现方式可能与PostgreSQL有所不同。

3）数据类型与事务处理

PostgreSQL：

提供了更丰富的数据类型支持，如前面所述。
完全支持ACID事务，确保数据的一致性和可靠性。
支持更高级的事务隔离级别，如序列化隔离级别。

MySQL：

数据类型支持相对有限，但足以满足大多数应用场景。
在特定的存储引擎（如InnoDB）中支持ACID事务。
支持多种事务隔离级别，但默认的可重复读隔离级别在某些情况下可能不足以防止并发更新问题。

4）安全性与合规性

PostgreSQL：

提供了多种安全特性，如SSL/TLS加密、行级安全、角色和权限管理等。
严格遵循SQL标准，有助于减少因SQL方言差异导致的安全问题。

MySQL：

同样提供了基本的安全特性，如用户认证、权限管理等。
在安全性方面可能不如PostgreSQL那么严格，但足以满足大多数应用场景的需求。

5）使用场景

PostgreSQL：

更适合需要高级功能、复杂查询和更严格数据完整性的场景，如金融、电信、ERP、CRM等领域。
也广泛应用于数据分析、地理信息系统（GIS）、科学研究和大型企业应用程序等领域。

MySQL：

更适合业务逻辑相对简单、数据可靠性要求较低的互联网场景，如Web应用程序、小型到中型规模的数据存储需求等。
在处理大量事务和高并发读取方面表现良好，也适合用于数据驱动型应用程序。

PostgreSQL和MySQL各有优势，选择哪个数据库系统取决于具体的应用场景、性能要求、数据规模以及开发团队的熟悉程度等因素。在实际应用中，建议根据业务需求进行详细的评估和测试，以选择最合适的数据库系统。

二、版本简介

1、早期版本（1986-1999）

前身：PostgreSQL的前身是Ingres，由Université de Genève和International Computer Science Institute在1977年开发。
开源：1994年，一群开发者决定将Ingres的代码开放源码，并将其命名为PostgreSQL，意为“Post SQL”（后SQL）。
早期发展：在1995年，Michael Widenius成立了PostgreSQL Global Development Group，推动了项目的全球化发展。
重要版本：
PostgreSQL 5.0：实际上对应的是Postgres95的版本，提供了更强大的功能和更好的性能，如外键约束、触发器、视图等。
PostgreSQL 6.1：作为第一个稳定的版本，在1999年发布，引入了多字节字符集支持等重要改进。

2、功能扩展与采纳期（2000-2004）

功能扩展：在这个阶段，PostgreSQL获得了大量的功能添加和改进，包括高级事务处理能力、支持多种数据类型和索引类型，以及性能改进。
采纳：PostgreSQL开始成为一些大型企业和组织的备选数据库解决方案，以其高性价比、高可扩展性和高可靠性等特点受到青睐。

3、可扩展性与高可用期（2005-2010）

scalability和高可用性：PostgreSQL的scalability和高可用性得到了进一步的改进，包括分区表、事务id、及集群功能等。
主要应用：这个时期，PostgreSQL成为了一些规模较大的企业和组织的主要数据库解决方案，能够满足复杂商业需求。

4、广泛采纳与创新期（2011年至今）

广泛采纳：PostgreSQL已经成为全球最流行的开源数据库之一，广泛应用于IT、金融、医疗、政府机构等领域。
新功能与改进：

PostgreSQL 10：

实现了声明式分区，支持同步复制多个standby，以及逻辑复制等功能。

PostgreSQL 11：

完善了分区表功能，增加了哈希分区、支持创建主键、外键、索引、触发器等。

PostgreSQL 12（2019年发布）：

提升了性能，引入了逻辑复制功能，改进了索引性能等。

PostgreSQL 15（2022年发布）：

在性能提升上做出了显著努力，引入了MERGE命令，改善了开发人员的体验，并提供了更多用于监测数据库状态的能力。

PostgreSQL 16（2023年9月发布）：

继续提升性能，特别是在查询并行性、批量数据加载和逻辑复制方面，为开发人员和管理员提供了更多功能。

三、当前版本

当前最新稳定版本为PostgreSQL 16，在性能、逻辑复制、SQL功能、安全性、监控与诊断等多个方面进行了全面的改进和增强，为数据库管理员和开发人员提供了更加高效、灵活和安全的数据库解决方案。说明如下：

1. 性能提升

并行查询优化：PostgreSQL 16 改进了查询执行时的性能，支持更多的并行查询，包括外连接和全连接查询的并行执行，以及聚合函数 string_agg 和 array_agg 的并行支持。
COPY命令性能提升：优化了使用 COPY 命令并发批量加载数据的性能，效率提高到了 300%。
SIMD CPU加速：引入了基于 SIMD（单指令流多数据流）技术的 CPU 加速，可以优化 ASCII 字符串和 JSON 字符串处理，以及数组和子事务搜索。

2. 逻辑复制增强

备节点逻辑复制：在 PostgreSQL 16 之前，用户只能在主节点创建逻辑复制的发布者。新版本增加了在备节点执行逻辑解码的功能，从而提供了更多复制选项，例如使用压力更小的备节点作为复制源。
性能改进：逻辑复制的性能得到了提升，包括订阅者并行应用大型事务，使用索引而不是主键执行 UPDATE 或 DELETE 操作，以及支持初始化时使用二进制格式复制表。
双向逻辑复制：PostgreSQL 16 引入了双向逻辑复制功能，允许在两个不同的发布者表之间进行数据复制，提高了数据可用性和容错能力。

3. SQL功能和语法增强

SQL/JSON标准支持：进一步实现了操作 JSON 数据的 SQL/JSON 标准，包括 SQL/JSON 构造函数（如 JSON_ARRAY()、JSON_ARRAYAGG() 等）以及检查函数（如 IS JSON）。
ANY_VALUE聚合函数：增加了 SQL 标准中的 ANY_VALUE 聚合函数，可以从一组聚合数据中返回一个任意值。
非十进制整数常量：支持非十进制的整数常量，例如十六进制（0xff）、八进制（0o777）和二进制（0b101010），并允许使用下划线作为千分位分隔符（如 5_432）。

4. 安全性与认证

Kerberos证书委托：支持 Kerberos 证书委托，允许扩展插件（如 postgres_fdw 和 dblink）使用已验证的凭据连接到其他服务。
新的客户端连接参数：增加了几个新的面向安全的客户端连接参数，如 require_auth，用于指定客户端可以接受的服务器认证方法。
pg_hba.conf和pg_ident.conf的改进：支持在这两个文件中使用正则表达式匹配用户名和数据库名，并允许引用其他文件。
5. 监控与诊断
I/O统计信息：引入了 pg_stat_io 视图，提供详细的 I/O 访问模式统计信息，有助于数据库管理员更好地监控和优化数据库性能。
查询跟踪算法改进：提升了 auto_explain 的可读性，以及 pg_stat_statements 和 pg_stat_activity 使用查询跟踪算法的准确性。

6. 其他改进

预定义角色：增加了一些预定义的角色，如 pg_maintain（执行维护操作）和 pg_create_subscription（创建逻辑复制订阅），方便用户授予特定功能权限。
页面冻结策略改进：改进了页面冻结策略，提高了 VACUUM 和其他维护操作的性能。
文本排序规则支持：改进了文本排序规则的通用支持，提供了定义文本排序的规则，并使用 ICU 作为默认排序规则。
构建系统更新：引入了 Meson 构建系统，最终将取代 Autoconf。

四、java使用代码示例

在Java中使用PostgreSQL数据库，你需要使用JDBC（Java Database Connectivity）API来建立与数据库的连接并执行SQL语句。以下是一个简单的示例，展示了如何使用JDBC连接PostgreSQL数据库，并执行一个简单的查询操作。
首先，确保你已经添加了PostgreSQL JDBC驱动到你的项目依赖中。如果你使用Maven，可以在你的pom.xml文件中添加如下依赖（注意替换为最新版本）：

<dependency>  
    <groupId>org.postgresql</groupId>  
    <artifactId>postgresql</artifactId>  
    <version>42.5.0</version> <!-- 使用时请检查最新版本 -->  
</dependency>

以下是一个简单的Java代码示例，展示如何连接到PostgreSQL数据库并执行查询：

import java.sql.Connection;  
import java.sql.DriverManager;  
import java.sql.ResultSet;  
import java.sql.Statement;  
  
public class PostgreSQLJDBCExample {  
  
    public static void main(String[] args) {  
  
        // 数据库URL，注意替换为你的数据库地址、数据库名、端口等  
        String url = "jdbc:postgresql://localhost:5432/yourdatabase";  
  
        // 数据库用户  
        String user = "yourusername";  
  
        // 数据库密码  
        String password = "yourpassword";  
  
        try {  
            // 加载PostgreSQL JDBC驱动  
            Class.forName("org.postgresql.Driver");  
  
            // 建立连接  
            Connection conn = DriverManager.getConnection(url, user, password);  
  
            // 创建Statement对象  
            Statement stmt = conn.createStatement();  
  
            // 执行查询  
            String sql = "SELECT version();"; // 只是一个示例，你可以换成你的SQL查询  
            ResultSet rs = stmt.executeQuery(sql);  
  
            // 处理查询结果  
            while (rs.next()) {  
                // 假设我们的查询返回了PostgreSQL的版本号  
                System.out.println(rs.getString(1));  
            }  
  
            // 关闭资源  
            rs.close();  
            stmt.close();  
            conn.close();  
  
        } catch (Exception e) {  
            e.printStackTrace();  
            System.err.println(e.getClass().getName() + ": " + e.getMessage());  
            System.exit(0);  
        }  
    }  
}

请注意，你需要将url、user和password变量替换为你的PostgreSQL数据库的实际连接信息。此代码示例还演示了如何加载JDBC驱动、建立数据库连接、执行SQL查询、处理查询结果以及关闭资源。在实际的生产环境中，处理数据库连接和查询时应该更加小心，包括使用连接池来管理数据库连接，以及处理可能的异常和错误等。

五、总结

PostgreSQL 是一款功能强大的开源关系型数据库管理系统，起源于20世纪80年代末，旨在提供高性能、可扩展和可靠的数据存储解决方案。它支持几乎所有SQL标准，并提供了如ACID事务、MVCC、复杂查询、大型对象支持、触发器、存储过程等高级功能。PostgreSQL 与 MySQL 相比，在数据类型支持、SQL标准遵循、扩展性等方面更具优势，适用于金融、电信、ERP等复杂应用场景。
PostgreSQL 经过多年发展，版本不断迭代，当前最新版本为 PostgreSQL 16，在性能、逻辑复制、SQL功能、安全性等方面进行了大量改进。例如，增强了并行查询能力，提升了 COPY 命令性能，引入了双向逻辑复制，并支持更多的 SQL 标准和语法。
在Java中使用PostgreSQL，需通过JDBC API建立连接并执行SQL语句。这通常涉及添加JDBC驱动依赖到项目中，然后编写代码来加载驱动、建立连接、执行查询并处理结果。PostgreSQL JDBC 驱动是连接Java应用与PostgreSQL数据库的桥梁，使Java开发者能够方便地在应用中集成和使用PostgreSQL数据库。

标签：PostgreSQL,入门,数据库,介绍,查询,复制,支持,SQL
From： https://blog.csdn.net/qq_40921573/article/details/142013060