PostgreSQL 入门教程——从 MySQL 迁移到 PG

, , ,

PostgreSQL 入门教程——从 MySQL 迁移到 PG

作者： CaoZH
日期： 2025-08-15
本文为原创教程

PostgreSQL（简称 PG）在 2025 年的 DB-Engines 排名中稳居第四，仅次于 Oracle、MySQL 和 SQL Server，而且在 Stack Overflow 开发者调查中连续多年被评为”最受喜爱的数据库”。

如果你用惯了 MySQL 想试试 PG，或者新项目在选型，这篇文章会帮你快速上手。

MySQL vs PostgreSQL 对比

特性 MySQL PostgreSQL
许可证 双许可（GPL/商业） PostgreSQL 许可
ACID InnoDB ✅ 完整支持 ✅
JSON ✅ 基础 ✅ 极强 + 索引
全文搜索 ❌ 基础 ✅ 强大（tsvector）
窗口函数 8.0+ ✅ 天生支持
CTE 递归 8.0+ ✅ 天生支持
数组类型 ❌ ✅
自定义类型 ❌ ✅
GIS 空间数据 插件 ✅ PostGIS
并发控制 MVCC ✅ 更成熟的 MVCC

一、安装 PostgreSQL

1
2
3
4
5
6
7
8
9
10
11
12
13
# Ubuntu/Debian
sudo apt update
sudo apt install -y postgresql postgresql-contrib

# macOS
brew install postgresql@16

# 启动
sudo systemctl start postgresql
sudo systemctl enable postgresql

# 验证
psql --version

首次登录

1
2
3
4
5
# 切换到 postgres 系统用户
sudo -u postgres psql

# 或者用本地 socket 登录
psql -U postgres

创建数据库和用户

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 创建用户
CREATE USER myapp WITH PASSWORD 'password123';

-- 创建数据库（指定编码和所有者）
CREATE DATABASE myapp
  ENCODING 'UTF8'
  LC_COLLATE 'zh_CN.UTF-8'
  LC_CTYPE 'zh_CN.UTF-8'
  OWNER myapp;

-- 授权
GRANT ALL PRIVILEGES ON DATABASE myapp TO myapp;

-- 退出
\q

二、数据类型差异

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
-- PostgreSQL 独有数据类型

-- 数组
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    tags TEXT[],            -- 字符串数组
    ratings INTEGER[3]      -- 固定长度数组
);
INSERT INTO posts (tags) VALUES (ARRAY['java', 'python', 'vue']);

-- JSON/JSONB（比 MySQL 强大很多）
CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    payload JSONB
);
-- JSONB 支持索引和高级查询
CREATE INDEX idx_payload ON events USING GIN (payload);

-- 网络地址类型
CREATE TABLE servers (
    ip INET,                -- IPv4/IPv6
    mac MACADDR             -- MAC 地址
);

-- 范围类型
CREATE TABLE reservations (
    room_id INTEGER,
    booked_during TSRANGE   -- 时间范围
);

-- 枚举类型
CREATE TYPE order_status AS ENUM ('pending', 'paid', 'shipped', 'completed');
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    status order_status DEFAULT 'pending'
);

三、高级查询特性

CTE 和递归查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- CTE（公用表表达式）
WITH user_orders AS (
    SELECT user_id, COUNT(*) as order_count, SUM(amount) as total
    FROM orders
    WHERE created_at >= '2025-01-01'
    GROUP BY user_id
)
SELECT u.name, uo.order_count, uo.total
FROM users u
JOIN user_orders uo ON u.id = uo.user_id
ORDER BY uo.total DESC;

-- 递归查询（MySQL 8.0 才支持，PG 诞生就有）
WITH RECURSIVE category_tree AS (
    -- 根节点
    SELECT id, name, parent_id, 1 as level
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    -- 递归子节点
    SELECT c.id, c.name, c.parent_id, ct.level + 1
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree ORDER BY level, id;

窗口函数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- 排名
SELECT
    name,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank,
    DENSE_RANK() OVER (ORDER BY salary DESC) as overall_rank
FROM employees;

-- 移动平均
SELECT
    date,
    amount,
    AVG(amount) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as moving_avg_7d
FROM daily_sales;

-- 累计求和
SELECT
    date,
    amount,
    SUM(amount) OVER (ORDER BY date) as cumulative
FROM monthly_revenue;

全文搜索

1
2
3
4
5
6
7
8
9
-- 创建全文搜索索引
CREATE INDEX idx_articles_search ON articles USING GIN (to_tsvector('simple', title || ' ' || content));

-- 搜索
SELECT id, title,
       ts_rank(to_tsvector('simple', title || ' ' || content), plainto_tsquery('simple', 'postgresql 教程')) as rank
FROM articles
WHERE to_tsvector('simple', title || ' ' || content) @@ plainto_tsquery('simple', 'postgresql 教程')
ORDER BY rank DESC;

四、连接配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
# Spring Boot 连接 PostgreSQL
spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/myapp
    username: myapp
    password: password123
    driver-class-name: org.postgresql.Driver
    hikari:
      maximum-pool-size: 10
      minimum-idle: 5

  jpa:
    database: POSTGRESQL
    hibernate:
      ddl-auto: update
    show-sql: false
1
2
3
4
5
6
<!-- Maven 依赖 -->
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.7.0</version>
</dependency>

五、备份与恢复

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 备份
pg_dump -U myapp -h localhost myapp > myapp_backup.sql

# 压缩备份
pg_dump -U myapp myapp | gzip > myapp_$(date +%Y%m%d).sql.gz

# 恢复
psql -U myapp -h localhost myapp < myapp_backup.sql

# 只恢复指定表
pg_restore -U myapp -d myapp -t users myapp_backup.dump

# 远程备份
pg_dump -U myapp -h 192.168.1.100 -p 5432 myapp > backup.sql

自动备份脚本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
#!/bin/bash
# pg_backup.sh
BACKUP_DIR="/var/backups/postgresql"
DB_NAME="myapp"
DB_USER="myapp"
RETENTION_DAYS=30

mkdir -p $BACKUP_DIR
BACKUP_FILE="$BACKUP_DIR/${DB_NAME}_$(date +%Y%m%d_%H%M%S).sql.gz"

pg_dump -U $DB_USER $DB_NAME | gzip > $BACKUP_FILE

# 删除超过 30 天的备份
find $BACKUP_DIR -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete

echo "备份完成: $BACKUP_FILE"

六、常用管理命令

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- PostgreSQL 特有命令（在 psql 中使用 \ 开头）
\l                    -- 查看所有数据库
\dt                   -- 查看所有表
\d table_name         -- 查看表结构
\di                   -- 查看所有索引
\du                   -- 查看所有用户
\df                   -- 查看所有函数
\dn                   -- 查看所有模式
\x                    -- 扩展显示（行转列）
\q                    -- 退出 psql
\! clear              -- 清屏

-- 性能相关
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@test.com';
SELECT * FROM pg_stat_activity;           -- 查看当前连接
SELECT * FROM pg_stat_user_tables;         -- 表访问统计

七、总结

PostgreSQL 比 MySQL 更适合以下场景：

场景 推荐 原因
✅ JSON 数据处理 PG JSONB + GIN 索引
✅ 地理空间数据 PG PostGIS
✅ 复杂报表查询 PG 窗口函数、CTE
✅ 数据完整性要求高 PG 约束、触发器更完善
✅ 全文搜索 PG tsvector 原生支持

如果项目涉及以上场景，PG 是比 MySQL 更好的选择。

首发于 CaoZH 的笔记

CaoZHProgrammer

学习使我快乐。