PostgreSQL初体验

最新推荐文章于 2025-05-20 22:27:03 发布

霖檬ing

最新推荐文章于 2025-05-20 22:27:03 发布

阅读量770

点赞数 35

文章标签： postgresql 数据库

本文链接：https://blog.csdn.net/Lemon__ing/article/details/148083286

版权

PostgreSQL简介

‌一、基础定义与历史演进‌

PostgreSQL 是一款‌对象-关系型数据库管理系统‌（ORDBMS），源自加州大学伯克利分校的POSTGRES项目（1986年）。其发展历程可分为三个阶段：

‌Ingres时期‌（1970年代）：奠定关系型数据库研究基础
‌Postgres时期‌（1980-1996年）：引入对象关系模型与复杂数据类型
‌PostgreSQL时期‌（1996年至今）：开源化并逐步集成现代数据库技术（如MVCC、GIS支持）

二、特点

开源与自由：

BSD许可：允许用户自由使用、修改和分发代码，无商业使用限制
社区驱动：全球900+贡献者组成的开源生态，年更新1个主版本
企业级免费：OLAP/OLTP混合负载能力媲美商业数据库（如Oracle）

标准符合性：高度符合SQL标准，支持复杂的查询语法、子查询、窗口函数、公共表表达式（CTE）等高级特性，使得开发者可以更加灵活地编写高效、易读地SQL代码；
数据类型丰富：类别典型类型特色案例基础类型NUMERIC(1000,500)精确计算金融数据几何类型CIRCLE, PATH地理信息系统存储文档类型JSONB（带索引）毫秒级检索10GB JSON数据自定义类型CREATE TYPE RGB AS (r,g,b)图像处理专用数据类型；
事务与并发：其采用多版本并发控制（MVCC）机制，确保了在高并发环境下的数据一致性和隔离性。同时还支持复杂的事务处理，包括嵌套事务、保存点等，为开发者提供了强大的事务管理能力；
扩展性：

可插拔设计：通过EXTENSION机制加载PostGIS(空间数据库)、TimescaleDB(时序数据库)等扩展
多语言支持：支持PL/pgSQL（原生）、PL/Python、PL/R等11种过程语言
并行查询：8.0版本起支持最多128个worker并行执行

安全性：

WAL日志：支持同步/异步复制，故障恢复精确到秒级
PITR：允许回滚到任意时间点（最小精度1秒）
Tablespace：支持SSD/HDD混合存储策略

三、优势

一、开源与生态优势‌

‌BSD许可证自由‌：允许商业闭源使用且无专利风险，相比GPL许可的MySQL更受企业青睐
‌全球开发者社区‌：900+贡献者持续迭代，年均发布1个主版本（如2025年将发布PostgreSQL 16）5
‌云厂商全支持‌：AWS/Azure/阿里云均提供托管服务，兼容性优于其他开源数据库

‌二、功能完备性‌

维度	技术实现	对比优势
‌数据模型‌	支持关系型+JSONB+时序+空间数据，单库实现多范式融合	Oracle需多产品组合实现
‌SQL兼容‌	实现SQL2016标准160项，支持递归查询、窗口函数等复杂分析	MySQL仅兼容92项
‌事务性能‌	MVCC+SSI隔离级别，读写并发量可达10万QPS（SSD环境）	避免Oracle的高授权成本

‌三、扩展能力图谱‌

‌垂直扩展‌
- 自定义数据类型（如RGB色彩模型）
- 支持11种编程语言编写存储过程（PL/Python等）
‌水平扩展‌
- Citus扩展实现分布式表（Sharding）
- 逻辑解码支持实时数据同步到Kafka
‌领域扩展‌
- PostGIS：GIS处理性能超Oracle Spatial 30%
- TimescaleDB：时序数据压缩比达20:15

‌四、企业级可靠性‌

‌数据安全‌：WAL日志+PITR实现秒级RPO（参考华瑞指数云快照技术）
‌高可用‌：同步复制+自动故障转移，可用性达99.999%
‌混合负载‌：OLTP与OLAP性能均衡，TPC-H测试超MySQL 2.3倍

四、系统架构

前端应用
  ↓
查询解析器 → 重写系统 → 优化器
                      ↓
执行器 ← 存储系统（堆文件/索引）
  ↑
事务系统（MVCC）
  ↑
共享缓存 & 后台进程（autovacuum/WAL writer等）

源码安装全流程

‌一、环境准备‌

‌系统依赖安装‌

‌Debian/Ubuntu‌：

sudo apt update && sudo apt install -y build-essential libreadline-dev zlib1g-dev flex bison \ 
libxml2-dev libxslt-dev libssl-dev libsystemd-dev python3-dev cmake ```:ml-citation{ref="1,6" data="citationList"}

‌CentOS/RHEL‌：

sudo yum install -y perl-ExtUtils-Embed readline-devel zlib-devel pam-devel \ 
libxml2-devel libxslt-devel openldap-devel gcc-c++ openssl-devel cmake ```:ml-citation{ref="2,6" data="citationList"}

‌创建专用用户‌

sudo groupadd postgres 
sudo useradd -g postgres postgres 
sudo passwd postgres # 设置用户密码 
```:ml-citation{ref="6,8" data="citationList"}

‌二、源码编译部署‌

‌下载与解压‌

wget https://ftp.postgresql.org/pub/source/v16.2/postgresql-16.2.tar.gz 
tar -zxvf postgresql-16.2.tar.gz 
cd postgresql-16.2 
```:ml-citation{ref="1,3" data="citationList"}

‌编译配置‌
```
./configure --prefix=/opt/postgresql/16 \ 
--enable-debug \ 
--with-perl \ 
--with-python \ 
--with-openssl 
```
- --enable-debug：开启调试模式（开发环境建议启用）
- --prefix：指定安装路径，避免污染系统目录
‌编译与安装‌
```
make -j$(nproc) # 多核并行编译 && make install # 系统级安装 
```
若出现copy_fetch.c编译错误，需修改源码中copy_file_range为copy_file_chunk

‌三、数据库初始化‌

‌创建数据目录‌

sudo mkdir -p /data/postgresql/16 
sudo chown -R postgres:postgres /data/postgresql 
```:ml-citation{ref="6,8" data="citationList"}

‌初始化数据库集群‌

sudo su - postgres 
/opt/postgresql/16/bin/initdb -D /data/postgresql/16 \ 
--locale=en_US.UTF-8 \ 
--encoding=UTF8 
```:ml-citation{ref="3,8" data="citationList"}

‌修改配置文件‌

‌pg_hba.conf‌（访问控制）：

host   all    all 0.0.0.0/0 
md5 ```:ml-citation{ref="4,8" data="citationList"}

‌postgresql.conf‌（核心配置）：

listen_addresses = '*' # 允许远程访问 
max_connections = 500 # 最大连接数 
shared_buffers = 4GB # 共享内存设置 
```:ml-citation{ref="3,6" data="citationList"}

‌四、服务管理‌

‌启停服务‌

# 启动 
/opt/postgresql/16/bin/pg_ctl -D /data/postgresql/16 start 

# 停止 
/opt/postgresql/16/bin/pg_ctl -D /data/postgresql/16 stop 
```:ml-citation{ref="3,7" data="citationList"}

‌设置系统服务（可选）‌

sudo ln -s /opt/postgresql/16/bin/* /usr/local/bin/ 
cp contrib/start-scripts/linux /etc/init.d/postgresql 
chkconfig --add postgresql 
```:ml-citation{ref="6,7" data="citationList"}

‌五、环境验证‌

‌连接测试‌
```
psql -h 127.0.0.1 -U postgres -d postgres 
```
执行SELECT version();验证版本信息

‌性能基准测试‌

CREATE TABLE test_table (id SERIAL PRIMARY KEY, data TEXT); 
INSERT INTO test_table (data) SELECT md5(random()::text) FROM generate_series(1,100000); 
```:ml-citation{ref="8" data="citationList"}

‌六、进阶配置建议‌

‌内核参数优化‌

# /etc/sysctl.conf 
kernel.shmmax=68719476736 
kernel.shmall=4294967296 
fs.file-max=655360 
```:ml-citation{ref="8" data="citationList"}

‌日志管理‌

# postgresql.conf 
logging_collector = on 
log_directory = 'pg_log' 
log_filename = 'postgresql-%Y-%m-%d.log' 
```:ml-citation{ref="3,6" data="citationList"}