【数据库系列】postgresql

PostgreSQL 是一款功能强大的开源关系型数据库，以高扩展性、SQL 标准兼容性和丰富的高级功能著称。以下从架构、核心功能、应用场景到优化实践，全面解析 PostgreSQL 的独特优势与使用细节：

​一、架构与核心机制

1. ​多版本并发控制（MVCC）​

   • ​实现原理：每个事务看到的数据快照（Snapshot）独立，通过 xmin 和 xmax 标记数据的可见性，避免读写锁竞争。
   • ​优势：高并发场景下读不阻塞写，写不阻塞读，适合 OLTP 系统。
   • ​清理机制：VACUUM 进程回收旧版本数据空间，AUTO_VACUUM 可配置自动清理。

2. ​存储引擎与表结构

   • ​堆表（Heap Table）​：数据按行存储，通过 TID（行物理地址）快速定位。
   • ​TOAST 机制：自动压缩大字段（如 TEXT、JSONB），超过 2KB 的数据存储到扩展存储区。
   • ​页面结构：默认 8KB 页大小，包含页头、行指针（ItemId）和实际数据行。

3. ​进程模型

   • ​主进程（Postmaster）​：管理连接请求，派生子进程处理查询。
   • ​子进程（Backend）​：每个客户端连接对应一个子进程，独立内存上下文。
   • ​后台进程：包括 BgWriter（脏页刷盘）、Checkpointer（检查点）、WalWriter（WAL 日志写入）等

二、流复制的核心机制

1. ​WAL（Write-Ahead Logging）的作用

   • ​数据持久化基础：所有数据修改（增删改）必须先写入 WAL 文件，再写入数据文件。
   • ​复制基础：流复制的本质是主库将 WAL 记录实时传输到备库，备库重放这些记录以保持数据一致性。

2. ​流复制的核心组件

   • ​主库（Primary）​：
     • ​WAL Sender 进程：负责将 WAL 数据发送到备库。
   • ​备库（Standby）​：
     • ​WAL Receiver 进程：接收主库发送的 WAL 数据。
     • ​Startup 进程：解析并应用接收到的 WAL 记录到本地数据文件。

​三、流复制的工作流程

1. ​主库写入数据

   • 事务提交时生成 WAL 记录，写入本地 pg_wal 目录。
   • WAL Sender 进程读取 WAL 文件，通过网络发送给备库。

2. ​备库接收并应用 WAL

   • WAL Receiver 进程接收 WAL 数据，暂存到备库的 pg_wal 目录。
   • Startup 进程按顺序解析 WAL 记录，重放（Replay）到备库的数据文件中。

3. ​同步与异步模式

   • ​异步复制（默认）​：
     • 主库提交事务后无需等待备库确认，性能高但存在数据丢失风险。
   • ​同步复制：
     • 主库提交事务后需至少一个备库确认 WAL 写入（synchronous_commit = on）。
     • 确保数据零丢失，但增加事务延迟。

四、部署

主：192.168.88.123 

从：192.168.88.124 

版本：9.2.23 

master

[root@master ~]# yum install postgresql-server -y             



[root@master ~]# id postgre