安装、架构、命令与优化一、PostgreSQL 是什么1.1 核心定位PostgreSQL简称 PG是全球最强大的开源对象关系型数据库ORDBMS源自伯克利血统纯正完全开源、协议宽松、无版权风险。全能型数据库支持关系型数据、JSON、数组、枚举、GIS、向量PGVector一套库搞定业务 AIGIS标准合规高度兼容 SQL 标准支持窗口函数、CTE、复杂子查询高并发可靠MVCC 多版本并发ACID 强事务适合高并发 OLTP生态爆炸PostGIS、TimescaleDB、PGVector、Citus 等扩展覆盖物联网、时序、AI 向量社区与就业国内大厂、云厂商、金融、政企全面拥抱 PG岗位需求暴涨1.3 典型应用场景企业级系统ERP、CRM、OAWeb 高并发电商、社交、SaaS数据分析数仓、BI、离线计算GIS 地理信息地图、物流、LBS 服务物联网 / 时序IoT 设备采集、监控数据AI 向量检索大模型特征存储、语义检索二、PostgreSQL 安装OpenEuler 源码编译 DNF 两种方式本文以OpenEuler 24 PostgreSQL 16.3为例提供两种生产常用安装方式你可根据场景选择。方式一源码编译安装适合需要自定义路径、开启特殊编译参数、控制版本的场景。1. 安装依赖bash运行yum -y install gcc gcc-* make libicu libicu-devel readline-devel zlib zlib-devel2. 下载、解压、编译bash运行# 解压源码 tar zxvf postgresql-16.3.tar.gz cd postgresql-16.3 # 配置安装路径 ./configure --prefix/usr/local/pgsql # 编译 安装 make make install3. 创建用户与数据目录bash运行# 创建专用用户禁止 root 运行 PG adduser postgres # 创建数据目录 mkdir -p /usr/local/pgsql/data chown postgres:postgres /usr/local/pgsql/data4. 配置环境变量bash运行cat /etc/profile EOF export LD_LIBRARY_PATH/usr/local/pgsql/lib export PATH/usr/local/pgsql/bin:$PATH EOF source /etc/profile5. 初始化数据库集群bash运行su - postgres initdb -D /usr/local/pgsql/data6. 启动、连接bash运行# 启动 pg_ctl -D /usr/local/pgsql/data -l logfile start # 连接 psql方式二DNF 一键安装适合快速搭建、不想折腾编译的场景。bash运行# 安装 dnf install -y postgresql-server # 初始化 postgresql-setup --initdb # 切换用户登录 su - postgres psql✅ 补充修改默认密码sqlALTER USER postgres PASSWORD 你的强密码;允许远程连接修改postgresql.confinilisten_addresses * port 5432修改pg_hba.confinihost all all 0.0.0.0/0 md5设置开机自启systemdbash运行# 编译安装需手动写 serviceDNF 安装自动注册 systemctl enable postgresql systemctl start postgresql三、PostgreSQL 核心架构逻辑 物理3.1 逻辑架构从上到下plaintext数据库集簇Cluster └── 多个数据库Database └── 多个模式Schema └── 表、视图、索引、函数、序列等对象集簇一个 PG 实例管理的所有数据库总称数据库逻辑隔离单元不同库之间无法直接跨库查询Schema库内对象隔离解决同名表冲突多用户共用库必备对象表、索引、视图、触发器、函数等重点PG 一个实例下多库库下多 SchemaMySQL 是一个实例下多库无 Schema 强隔离。3.2 物理架构文件到底存在哪PG 所有数据都在$PGDATA目录核心目录作用目录 / 文件作用base/各数据库真实数据文件表、索引global/全局系统表用户、数据库信息pg_wal/WAL 预写日志崩溃恢复、主从复制pg_hba.conf客户端访问认证白名单、密码方式postgresql.conf核心配置内存、连接、性能PG_VERSIONPG 主版本号3.3 表空间关系Oracle表空间 ↔ 数据库 一对多PG表空间 ↔ 数据库 多对多pg_default默认表空间用户表默认存放pg_global系统共享表空间四、PostgreSQL 最常用命令4.1 连接命令bash运行# 本地连接 psql -U postgres -d postgres # 远程连接 psql -h 192.168.x.x -p 5432 -U postgres -d postgres4.2 psql 元命令sql# 查看库 \l # 切换库 \c dbname # 查看表 \dt # 查看表结构 \d tablename # 查看用户 \du # 退出 \q4.3 DDL 基础实战sql-- 创建数据库 CREATE DATABASE mydb; -- 创建 Schema CREATE SCHEMA user_schema; -- 创建表含主键、唯一、非空、默认时间 CREATE TABLE user_schema.users ( id SERIAL PRIMARY KEY, username VARCHAR(32) NOT NULL UNIQUE, email VARCHAR(64) NOT NULL UNIQUE, create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- 插入、查询、更新、删除 INSERT INTO user_schema.users (username, email) VALUES (zhangsan, zhangsanqq.com); SELECT * FROM user_schema.users;五、文档外补充安全、配置、监控、优化5.1 安全最佳实践禁止postgres超级用户直连业务业务使用最小权限用户密码复杂度 定期更换pg_hba.conf只放信任 IP不开放 0.0.0.0/0开启 SSL 远程连接5.2 核心配置优化8G 内存机器参考ini# 连接 max_connections 500 listen_addresses * # 内存 shared_buffers 2GB # 建议 物理内存 25% work_mem 16MB # 单会话排序/哈希内存 maintenance_work_mem 512MB # 维护操作VACUUM、CREATE INDEX effective_cache_size 6GB # 优化器可用缓存 # WAL wal_level replica max_wal_size 8GB min_wal_size 1GB # 并行查询PG16 增强 max_parallel_workers 8 max_parallel_workers_per_gather 45.3 日常监控 SQLsql-- 查看当前连接 SELECT pid,usename,datname,client_addr,state FROM pg_stat_activity; -- 杀死异常连接 SELECT pg_terminate_backend(pid); -- 查看表大小 SELECT pg_size_pretty(pg_total_relation_size(tablename));5.4 PostgreSQL 16 新特性并行能力增强并行 FULL/RIGHT JOIN、并行聚合COPY 性能提升 3 倍 SQL:2023 支持ANY_VALUE、数字下划线分隔EXPLAIN GENERIC_PLAN通用执行计划适合参数化查询索引 INCLUDE覆盖索引避免回表六、常见问题与避坑无法远程连接检查listen_addresses、pg_hba.conf、防火墙端口 5432中文乱码初始化时指定UTF8编码性能差缺少索引、work_mem太小、未做 VACUUM不能用 root 启动PG 强制要求专用用户提升安全性