PostgreSQL数据库

PostgreSQL，作为一个功能强大且开源的对象关系型数据库管理系统(ORDBMS)，自其诞生以来，便以其卓越的性能和丰富的特性赢得了全球开发者和企业的青睐。源自加利福尼亚大学伯克利分校的 PostgreSQL，不仅继承了其前身 Ingres 的精髓，更在不断的发展中推陈出新，成为了现代数据库领域的佼佼者。

特点

开源与自由：PostgreSq完全开源，遵循PostgreSQL许可证，这一特性使得用户可以自由地使用、修改和分发 PostgreSqL，无需担心版权问题，同时也促进了全球开发者的积极参与和贡献。
标准符合性：PostgreSQL高度符合 SQL 标准，支持复杂的查询语法、子查询、窗口函数、公共表表达式(CTE)等高级特性，使得开发者可以更加灵活地编写高效、易读的 SQL 代码。
数据类型丰富：PostgreSQL,提供了丰富的数据类型，包括基本类型(如整数、浮点数、字符串等)、日期和时间类型、数组、枚举、范围类型、JSON、地理空间类型等，这些类型极大地扩展了PostgreSql 的应用范围，使其能够处理各种复杂的数据场景。
事务与并发：PostgreSQL采用多版本并发控制(MVCC)机制，确保了在高并发环境下的数据一致性和隔离性。同时，PostgreSq还支持复杂的事务处理，包括嵌套事务、保存点等，为开发者提供了强大的事务管理能力。
扩展性：PostgreSqL支持扩展和插件机制，允许用户根据需要定义新的数据类型、函数、操作符、索引方法等。这一特性使得 PostgreSQl 能够不断适应新的业务需求和技术发展。
安全性：PostgreSq,提供了细粒度的访问控制、加密传输、审计日志等安全特性，确保了数据库的安全性和数据的保密性。

优势

高性能：PostgreSql通过优化查询计划、支持并行查询、分区表等特性，提供了卓越的性能表现。即使在处理大规模数据和高并发访问时，也能保持高效的响应速度。
高可用性：PostgreSql 支持主从复制、流复制和逻辑复制等多种复制方式，使得数据库系统能够轻松实现高可用性和容灾备份。在发生故障时，能够快速恢复服务，确保业务的连续性。
灵活性：PostgreSQL的丰富数据类型和高级特性使得它能够灵活应对各种复杂的业务场景。无论是处理结构化数据还是非结构化数据，PostgreSqL都能提供强大的支持。
社区支持：PostgreSQL拥有一个活跃的开发者社区和丰富的生态系统。社区中不仅有大量的教程、文档和插件可供使用，还有众多经验丰富的开发者愿意分享经验和解答问题。
成本效益：作为开源软件，PostgreSQL降低了企业的成本投入。同时，其卓越的性能和广泛的应用场景也使得 PostgresqL 成为了许多企业的首选数据库产品。

架构

PostgreSQL 的架构设计体现了其高性能和可扩展性的特点。在逻辑层面上PostgreSqL, 包含了数据库集群、表空间、数据库、Schema、表、索引等结构;在物理层面上，则包括数据文件、日志文件、参数文件、控制文件等物理存储方式。其中，数据块(Page)作为数据读写的基本单位，在PostgreSQL中扮演着至关重要的角色。通过优化数据块的读写效率和布局方式，PostgreSqL能够进一步提高其性能表现。

应用场景

企业应用：如ERP、CRM、HRM等系统，需要处理复杂的事务和查询操作。PostgresqL凭借其高性能和事务处理能力，能够为企业应用提供稳定可靠的数据支持。
数据分析：在数据仓库和商业智能领域，PostgreSQl凭借其丰富的数据类型和高级查询特性，能够轻松应对大规模数据分析和挖掘任务。
Web 应用：对于需要高并发访问和实时数据处理的 Web 应用来说，PostgreSQl的 MVCC 机制和扩展性特性使得其成为了一个理想的选择。
地理信息系统(GIS)：通过PostGIS扩展，PostgreSQL能够支持地理空间数据的存储和分析功能，为GIS 应用提供了强大的数据支持。
物联网与大数据：随着物联网和大数据技术的不断发展，PostgreSqL凭借其高性能、可扩展性和丰富的数据类型特性，在物联网和大数据领域中也得到了广泛的应用。

编译安装PostgreSQL

安装Postgresql的依赖包

[root@localhost ~]# dnf -y install gcc libicu libicu-devel readline-devel zlib zlib-devel

编译安装

解压源码包

[root@localhost ~]# tar zxf postgresql-15.4.tar.gz

切换到目录

[root@localhost ~]# cd postgresql-15.4

指定安装目录

[root@localhost postgresql-15.4]# ./configure --prefix=/usr/local/pgsql

编译以及安装

[root@localhost postgresql-15.4]# make && make install

配置环境

创建用户

[root@localhost postgresql-15.4]# useradd postgres

创建数据存储目录

[root@localhost postgresql-15.4]# cd /usr/local/pgsql/
[root@localhost pgsql ]# mkdir data

更改数据存储目录的归属用户

[root@localhost postgresql-15.4]# chown -R postgres data/

配置环境变量

[root@localhost postgresql-15.4]# vim /etc/profile
末尾添加
export PATH=$PATH:/usr/local/pgsql/bin

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/pgsql/lib

刷新环境变量

[root@localhost postgresql-15.4]# source /etc/profile

登录数据库

[root@localhost postgresql-15.4]# su  - postgres

格式化PG数据库

[postgres@localhost ~]$ /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data/

启动PG数据库

[postgres@localhost ~]$ /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data/ -l logfile start

登录PG数据库

[postgres@localhost ~]$ psql
psql (15.4)
Type "help" for help.

postgres=#

PostgreSQL结构

数据库集群（Database Cluster）

定义：一个 PostgreSQL 数据库集群是由一个或多个数据库组成的集合，共享同一个数据目录（PGDATA）。
关键点：
- 集群由 initdb 命令初始化。
- 包含系统目录（template0、template1、postgres 等）和用户数据库。

数据库（Database）

定义：数据库是存储数据的容器，包含表、索引、视图等对象。
关键点：
- 每个数据库是独立的，彼此隔离。
- 默认包含 template0（只读模板）、template1（可修改模板）和 postgres（默认数据库）。
- 用户可以创建自定义数据库。

模式（Schema）

定义：模式是数据库内部的命名空间，用于组织对象（如表、函数等）。
关键点：
- 避免对象命名冲突。
- 默认模式为 public。
- 示例：schema_name.table_name。

表（Table）

定义：表是存储数据的二维结构，由行和列组成。
关键点：
- 列定义数据类型（如 integer、text）。
- 支持约束（如 PRIMARY KEY、FOREIGN KEY）。
- 可通过 CREATE TABLE 创建

数据类型（Data Types）

定义：PostgreSQL 支持丰富的数据类型，包括：
- 基本类型：integer、boolean、text、date、timestamp。
- 数组类型：integer[]、text[]。
- 复合类型：用户自定义类型。
- JSON/XML：jsonb、xml。
- 范围类型：int4range、daterange

索引（Index）

定义：索引是加速查询的数据结构，存储列的排序数据。
关键点：
- B-tree：默认索引类型，适用于等值和范围查询。
- Hash：仅支持等值查询。
- GiST、SP-GiST、GIN、BRIN：适用于特殊数据类型（如全文搜索、几何数据）。
- 示例：CREATE INDEX idx_name ON table_name(column_name);

视图（View）

定义：视图是基于查询的虚拟表，不存储数据。
关键点：
- 简化复杂查询。
- 支持物化视图（存储查询结果，需手动刷新）。
- 示例：CREATE VIEW view_name AS SELECT * FROM table_name WHERE condition;

函数（Functions）

定义：函数是可重用的代码块，可接受参数并返回结果。
关键点：
- 语言支持：SQL、PL/pgSQL、Python、Perl 等。
- 触发器函数：在特定事件（如插入、更新）时自动调用。
- 示例：

CREATE OR REPLACE FUNCTION add_numbers(a integer, b integer) RETURNS integer AS

存储过程（Stored Procedures）

定义：存储过程是预编译的代码块，可包含事务控制（如 COMMIT、ROLLBACK）。
关键点：
- PostgreSQL 11+ 引入。
- 通过 CALL 调用。
- 示例：

CREATE OR REPLACE PROCEDURE transfer_funds(sender integer, receiver integer, amount numeric)
LANGUAGE SQL
AS

触发器（Triggers）

定义：触发器是自动执行的函数，响应表上的事件（如 INSERT、UPDATE）。
关键点：
- 行级触发器：对每一行操作执行。
- 语句级触发器：对每个语句执行一次。
- 示例：

CREATE TRIGGER check_balance
BEFORE INSERT OR UPDATE ON accounts
FOR EACH ROW
EXECUTE FUNCTION check_balance_function();

扩展（Extensions）

定义：扩展是打包的功能模块，可动态加载。
关键点：
- 常用扩展：postgis（地理空间数据）、pgcrypto（加密）、hstore（键值存储）。
- 示例：

CREATE EXTENSION postgis;

表空间（Tablespaces）

定义：表空间是磁盘上的存储位置，用于控制表的物理存储。
关键点：
- 默认表空间为 pg_default。
- 可将表或索引存储在特定表空间中。
- 示例：

CREATE TABLESPACE fastspace LOCATION '/mnt/fastdisk/data';
CREATE TABLE fast_table (...) TABLESPACE fastspace;

事务（Transactions）

定义：事务是一组操作，要么全部成功，要么全部失败。
关键点：
- ACID：原子性、一致性、隔离性、持久性。
- 示例：

BEGIN;
INSERT INTO accounts VALUES (1, 1000);
INSERT INTO accounts VALUES (2, 2000);
COMMIT;

并发控制与锁

定义：PostgreSQL 通过多版本并发控制（MVCC）和锁机制管理并发访问。
关键点：
- MVCC：读写不阻塞，通过事务快照实现。
- 锁：行锁、表锁、共享锁、排他锁等

置文件与参数

关键文件：
- postgresql.conf：主配置文件，设置内存、日志等参数。
- pg_hba.conf：控制客户端认证。
- pg_ident.conf：配置用户名映射。

日志与监控

日志类型：
- 错误日志：记录启动、关闭和错误信息。
- 查询日志：记录所有 SQL 语句（需配置）。
- 慢查询日志：记录执行时间超过阈值的查询。
监控工具：
- pg_stat_activity：查看活动会话。
- pg_stat_statements：监控查询性能。

备份与恢复

备份方法：
- 逻辑备份：pg_dump（导出 SQL 脚本）。
- 物理备份：pg_basebackup（复制文件系统）。
恢复方法：
- 使用 pg_restore 恢复逻辑备份。
- 通过 recovery.conf（PostgreSQL 12 之前）或 standby.signal（PostgreSQL 12+）配置恢复。