PostgreSQL 简介与安装

1.1 PostgreSQL 是什么

PostgreSQL（官方读作 "post-gress-Q-L"，社区常称 "Postgres" 或 "PG"）诞生于 1986 年加州大学伯克利分校的 POSTGRES 项目，1996 年更名为 PostgreSQL 并采用开源许可证（类 BSD）。经过近 40 年的持续发展，它已成为全球功能最完整、SQL 标准支持最好的开源关系型数据库。

PostgreSQL 的核心设计哲学是：优先满足 SQL 标准与数据正确性，而非追求极限性能。这使得它在数据完整性、事务语义和丰富功能方面远超 MySQL，但也意味着在某些极简场景下配置更复杂。

1.2 PostgreSQL vs MySQL 核心差异

特性	PostgreSQL 17	MySQL 8
SQL 标准支持	极高，递归 CTE、窗口函数、WITH TIES、MERGE 等	部分支持，某些特性需变通
ACID 合规	完整 ACID，所有操作默认事务安全	InnoDB 引擎支持，但 DDL 不可回滚
JSON 支持	JSONB（二进制存储，可索引），性能极强	JSON 类型，不支持 GIN 索引
数组类型	原生支持 integer[]、text[] 等	不支持，需用 JSON 或关联表代替
全文搜索	内置 tsvector/tsquery，支持中文扩展	内置 FULLTEXT，功能较弱
DDL 事务	DDL 可回滚（ALTER TABLE 在事务中）	DDL 立即提交，不可回滚
MVCC 实现	堆表存多版本，无 Undo Log 概念	Undo Log 存储旧版本
扩展系统	CREATE EXTENSION，PostGIS/TimescaleDB 等	插件机制，扩展生态相对弱
并发模型	多进程（每连接一个进程）	多线程（每连接一个线程）
默认端口	5432	3306

版本历史与当前 LTS

PostgreSQL 每年发布一个主版本（通常在9月），每个主版本支持 5 年。

版本	发布时间	EOL	重要新特性
PG 17 (当前最新)	2024-09	2029-11	增量排序优化、MERGE 增强、逻辑复制槽故障转移
PG 16 (LTS)	2023-09	2028-11	逻辑复制可从 Standby 发布、更多并行化、pg_stat_io
PG 15	2022-10	2027-11	MERGE 语句、pg_walinspect、排序性能提升
PG 14	2021-09	2026-11	Pipeline mode、连接池优化、JSONB 下标访问

1.3 安装方式

方式一：macOS — Homebrew

SHELL# 安装 PostgreSQL 17（推荐）
brew install postgresql@17

# 启动服务（开机自启）
brew services start postgresql@17

# 将 psql 加入 PATH（按 brew 安装完成后的提示操作）
echo 'export PATH="/opt/homebrew/opt/postgresql@17/bin:$PATH"' >> ~/.zshrc
source ~/.zshrc

# 验证安装
psql --version
# psql (PostgreSQL) 17.x

# 首次连接（用当前系统用户名，无需密码）
psql postgres

方式二：macOS — Postgres.app（图形化最简方式）

1. 访问 postgresapp.com 下载 Postgres.app
2. 拖入 Applications，点击 Initialize 即可启动
3. 点击 "Open psql" 直接进入命令行，无需配置环境变量
4. 支持同时运行多个 PG 版本，适合本地开发

方式三：Linux — apt（Ubuntu/Debian）

SHELL# 添加官方 APT 仓库
sudo apt install -y curl ca-certificates
sudo install -d /usr/share/postgresql-common/pgdg
sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail \
  https://www.postgresql.org/media/keys/ACCC4CF8.asc

sudo sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] \
  https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" \
  > /etc/apt/sources.list.d/pgdg.list'

sudo apt update
sudo apt install -y postgresql-17

# 服务管理
sudo systemctl start postgresql
sudo systemctl enable postgresql
sudo systemctl status postgresql

# 切换到 postgres 系统用户并连接
sudo -u postgres psql

方式四：Linux — yum（RHEL/CentOS/Fedora）

SHELL# 安装 PGDG 仓库 RPM（以 RHEL 9 为例）
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# 禁用系统默认 PostgreSQL 模块
sudo dnf -qy module disable postgresql

# 安装
sudo dnf install -y postgresql17-server postgresql17-contrib

# 初始化数据目录
sudo /usr/pgsql-17/bin/postgresql-17-setup initdb

# 启动并设置开机自启
sudo systemctl enable --now postgresql-17

方式五：Docker（最快上手）

SHELL# 拉取官方镜像并启动容器
docker run -d \
  --name pg17 \
  -e POSTGRES_PASSWORD=mypassword \
  -e POSTGRES_DB=myapp \
  -p 5432:5432 \
  -v pg17_data:/var/lib/postgresql/data \
  postgres:17-alpine

# 连接到容器内的 psql
docker exec -it pg17 psql -U postgres -d myapp

# 使用 docker-compose（推荐开发环境）

YAML (docker-compose.yml)version: '3.9'
services:
  postgres:
    image: postgres:17-alpine
    environment:
      POSTGRES_PASSWORD: devpassword
      POSTGRES_USER: myuser
      POSTGRES_DB: mydb
    ports:
      - "5432:5432"
    volumes:
      - pg_data:/var/lib/postgresql/data
      - ./init.sql:/docker-entrypoint-initdb.d/init.sql
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U myuser -d mydb"]
      interval: 5s
      timeout: 5s
      retries: 5

volumes:
  pg_data:

1.4 psql 命令行工具

psql 是 PostgreSQL 官方命令行客户端，功能强大。连接命令格式：

SHELL# 完整格式
psql -h localhost -p 5432 -U myuser -d mydb

# 使用连接字符串（URL 格式）
psql "postgresql://myuser:mypassword@localhost:5432/mydb"

# 使用环境变量（避免密码出现在命令行）
export PGPASSWORD=mypassword
psql -h localhost -U myuser mydb

核心元命令（Meta-Commands）

psql 的元命令以反斜杠 \ 开头，不是 SQL，不需要分号结尾：

命令	功能	等价操作
\l 或 \list	列出所有数据库	MySQL: SHOW DATABASES
\c dbname	切换到指定数据库	MySQL: USE dbname
\dt	列出当前 Schema 的所有表	MySQL: SHOW TABLES
\dt *.*	列出所有 Schema 的所有表	—
\d tablename	查看表结构（列、类型、索引、约束）	MySQL: DESCRIBE tablename
\d+ tablename	详细表信息（含存储参数）	—
\di	列出索引	SHOW INDEX FROM table
\dv	列出视图	SHOW FULL TABLES WHERE Type='VIEW'
\df	列出函数	—
\du	列出用户/角色	SELECT User FROM mysql.user
\dn	列出 Schema	MySQL: SHOW DATABASES
\e	用系统默认编辑器编辑上一条 SQL	—
\i file.sql	执行 SQL 文件	MySQL: SOURCE file.sql
\timing	开关查询耗时显示	—
\x	切换扩展显示模式（列转行，宽表友好）	—
\?	查看所有元命令帮助	—
\h SELECT	查看某条 SQL 语句的语法帮助	—
\q	退出 psql	MySQL: quit / exit

PSQL SESSION-- 连接后的典型操作流程
postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale
-----------+----------+----------+-------------+-------------+------------
 myapp     | myuser   | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

postgres=# \c myapp
You are now connected to database "myapp" as user "myuser".

myapp=# \dt
         List of relations
 Schema |   Name   | Type  |  Owner
--------+----------+-------+---------
 public | orders   | table | myuser
 public | products | table | myuser
 public | users    | table | myuser

myapp=# \d users
                          Table "public.users"
   Column    |            Type             | Nullable |      Default
-------------+-----------------------------+----------+-------------------
 id          | bigint                      | not null | generated always as identity
 username    | character varying(50)       | not null |
 email       | text                        | not null |
 created_at  | timestamp with time zone    | not null | now()
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "users_email_key" UNIQUE CONSTRAINT, btree (email)

1.5 pgAdmin 4 图形工具

pgAdmin 4 是 PostgreSQL 官方维护的图形化管理工具，支持 Web 浏览器和桌面客户端两种形式。

1.6 核心名词解释

PostgreSQL 的对象层次结构与 MySQL 有所不同，理解这些概念是掌握 PG 的基础：

• Cluster（集群） PostgreSQL 中的"集群"指的是一个 PostgreSQL 服务器实例管理的所有数据库的集合，存储在同一个数据目录（PGDATA）下。注意：这不是分布式集群的概念，而是单机实例的管理单元。每个 Cluster 有独立的端口（默认 5432）和配置文件。
• Database（数据库） Cluster 内的独立命名空间。一个 Cluster 可以有多个 Database（如 myapp_prod、myapp_dev）。不同 Database 之间完全隔离，跨库查询需要 dblink 扩展或外部数据包装器（FDW）。这与 MySQL 不同——MySQL 的 Schema 和 Database 是同一概念。
• Schema（模式） Database 内的命名空间，用于组织表、视图、函数等对象。每个 Database 默认有一个 public Schema。可以创建多个 Schema（如 app、audit、archive）实现逻辑隔离。类似文件系统中的"目录"。访问其他 Schema 的表：schema_name.table_name。
• Table（表/关系） Schema 内存储数据的二维结构。PostgreSQL 的表称为"堆表（Heap）"，数据行无序存储在 8 KB 的页（Page/Block）中。每行（Tuple）有系统列：ctid（物理位置）、xmin/xmax（MVCC 可见性控制）。
• Relation（关系） 来自关系代数的术语，在 PostgreSQL 中泛指表、视图、序列、物化视图等所有"类表"对象。系统目录 pg_class 存储所有 Relation 的元数据，relkind 字段区分类型（r=普通表，v=视图，m=物化视图，i=索引，S=序列）。
• WAL（Write-Ahead Log） 预写日志，PostgreSQL 所有数据修改首先写入 WAL，再修改实际数据页。这是崩溃恢复、流复制、逻辑复制的基础。WAL 文件默认存放在 $PGDATA/pg_wal/，每个文件 16 MB，循环使用。
• Tablespace（表空间） 允许将数据库对象存储到不同物理磁盘路径的机制。默认有两个表空间：pg_default（存用户表）和 pg_global（存系统目录）。可将热数据表空间放在 SSD，冷数据归档放在 HDD。
• Role（角色） PostgreSQL 统一用"角色"替代"用户"和"组"的概念。带 LOGIN 权限的角色就是用户，不带 LOGIN 的角色用于权限分组。角色支持继承：GRANT role_name TO user_name。

对象层次可视化

HIERARCHYPostgreSQL Instance (Cluster)
└── pg_global tablespace
    ├── pg_default tablespace
    │   ├── Database: postgres   (系统默认数据库)
    │   ├── Database: template1  (新建数据库的模板)
    │   └── Database: myapp      (你的应用数据库)
    │       ├── Schema: public   (默认 schema)
    │       │   ├── Table: users
    │       │   ├── Table: orders
    │       │   ├── View: active_users
    │       │   └── Function: update_timestamp()
    │       ├── Schema: audit    (审计日志 schema)
    │       │   └── Table: audit_log
    │       └── Schema: archive  (历史数据 schema)
    │           └── Table: orders_2023
    └── custom tablespace: /ssd/pg_data  (高速存储)