PostgreSQL 专题：从安装到 pgx 集成到备份全通

PostgreSQL 每年发行一个大版本（约 9–10 月），每个大版本社区支持 5 年。2024 年到 17、2025 年到 18、2026 年 8 月 18.6 补丁发布——你现在（2026-10）站在 18 线的"支持窗口中点"，正是版本纪律规则 2 要的位置。

1.1 PG vs MySQL vs SQLite（分工，引用 08 §8.1）

一句话选型：工作台本地默认 SQLite 跑通（契约如此）；当业务需要 JSONB、全文检索、复杂报表或合规审计时，切到 PG——本模块教你这一刀切下去怎么切。

你的环境：Windows 本机开发 + 1GB Linux 服务器。本机用 Docker Desktop 起容器（干净、可一键删了重来）；服务器按纪律不装 Docker，直接 apt 装原生包（1GB 内存扛不住 Docker Desktop 的开销，见 versions.md 备注）。

2.1 路 ①：Windows 本机 Docker Desktop（docker compose）

前提：Docker Desktop 已装（版本 4.94.0，见 09 模块）。新建目录 pg-lab/，放两个文件：

docker-compose.yml

services:
  pg18:
    image: postgres:18.6
    container_name: pg18
    restart: unless-stopped
    environment:
      POSTGRES_USER: appuser
      POSTGRES_PASSWORD: 'Dev#2026Strong'   # 本机开发密码，别照搬到服务器
      POSTGRES_DB: appdb
      TZ: Asia/Shanghai
    ports:
      - "5432:5432"
    volumes:
      - pgdata:/var/lib/postgresql/data      # 命名卷，容器删了数据还在

volumes:
  pgdata:

启动与验证：

cd pg-lab
docker compose up -d
docker compose ps                 # 看到 pg18 状态 Up
docker compose exec pg18 psql -U appuser -d appdb -c "select version();"

端口冲突预案：若本机 5432 被占（装过原生 PG），把左边端口改成 "15432:5432"，后面所有连接串端口用 15432。

2.2 路 ②：Linux 服务器 apt（Debian 13 / Ubuntu 26.04）

Debian 13 与 Ubuntu 26.04 的官方仓库已打包 PostgreSQL 18。1GB 服务器不要 Docker，直接装：

# Debian 13 trixie / Ubuntu 26.04 官方源自带 PG 18
sudo apt update
sudo apt install -y postgresql-18

sudo systemctl enable --now postgresql
systemctl status postgresql --no-pager      # active (exited) 是正常的，主进程在

装完默认：unix socket 在 /var/run/postgresql/，超级用户 postgres，监听 127.0.0.1:5432。外网默认不开放——1GB 小服务器上 PG 只给本机 Go 程序连，绝不对公网开 5432（防火墙也别放）。

2.3 首次连接：psql 建库、建用户

Docker 里已由 compose 环境变量建好 appuser/appdb。服务器上手动建（与本机对齐，方便代码同一份 DSN）：

# 服务器：切到 postgres 超级用户进 psql
sudo -u postgres psql

-- 在 psql 里执行：建业务用户与库（密码用你自己的强密码）
CREATE USER appuser WITH PASSWORD 'Prod#2026Xy7!' CREATEDB;
CREATE DATABASE appdb OWNER appuser ENCODING 'UTF8';
\q

# 验证：以 appuser 连入自己的库
psql -h 127.0.0.1 -U appuser -d appdb

pg_hba.conf 一句话（别背，知道在哪改即可）

客户端认证规则在 pg_hba.conf（Debian 在 /etc/postgresql/18/main/pg_hba.conf）。本机 Docker 默认 md5/scram 密码认证；服务器上默认"本机 127.0.0.1 用 scram-sha-256 密码、unix socket 用 peer"。你只需要记住：应用连不上、报 pg_hba 拒绝时，去这文件看最后匹配到的那行认证方式——本模块 §8 报错表第 2 条给了改法。改完执行 SELECT pg_reload_conf(); 热加载。

只讲你写 Go 代码和排查问题时真用得上的。教科书式铺开的部分，等阶段四再深潜。

3.1 schema：库下面的一层命名空间

一个 database 里可以有多个 schema（默认 public）。工作台这种单应用单库，永远用 public 就够，不要发明 schema；schema 留给"一个库装多个隔离应用"的场景。

3.2 数据类型（重点记这 6 个）

3.3 索引：三种你真会用到的

纪律：不要盲目给每个列建索引。先有慢查询（§3.5），再用 EXPLAIN 确认没走索引，再建。写多读少的表，索引是负担。

3.4 MVCC 与事务隔离（一张表够用）

PG 用 MVCC：读不阻塞写、写不阻塞读，靠多版本实现。默认隔离级别是 READ COMMITTED（够用，别轻易调 SERIALIZABLE）。四个级别你只需知道：

并发写冲突怎么办：MVCC 下两个事务改同一行，后提交的那个会阻塞等前者提交；若前者回滚，后者拿到的是新值。Go 里事务越短越好：别把网络请求、RPC 包在事务里。

3.5 EXPLAIN ANALYZE：慢查询怎么优化（一个完整例子）

场景：tasks 表 10 万行，下面这条查询慢：

-- 找某用户未完成、最近的 20 条任务
EXPLAIN ANALYZE
SELECT * FROM tasks
WHERE user_id = 7 AND done = false
ORDER BY created_at DESC LIMIT 20;

EXPLAIN ANALYZE 会真的执行这条 SQL 并打印实际耗时。看输出：

• 看到 Seq Scan on tasks（全表扫描）+ actual time 几百 ms → 没走索引。
• 建联合索引后再跑：

CREATE INDEX CONCURRENTLY idx_tasks_user_undone
ON tasks (user_id, created_at DESC) WHERE done = false;
-- CONCURRENTLY：建索引不锁表（大表必加）；部分索引只含未完成行

再跑 EXPLAIN ANALYZE：变成 Index Scan using idx_tasks_user_undone，耗时降到亚毫秒级。优化流程就这三步：EXPLAIN 看 Seq Scan → 建合适索引 → 再 EXPLAIN 验证。

4.1 DDL / DML / 查询速查

-- DDL：建表（工作台 Task 模型对应的 PG 写法）
CREATE TABLE tasks (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title       text NOT NULL,
  note        text,
  done        boolean NOT NULL DEFAULT false,
  tags        text[] NOT NULL DEFAULT '{}',      -- 数组列
  meta        jsonb NOT NULL DEFAULT '{}'::jsonb,-- JSONB 列
  created_at  timestamptz NOT NULL DEFAULT now(),
  updated_at  timestamptz NOT NULL DEFAULT now()
);

-- DML
INSERT INTO tasks (title) VALUES ('写 PG 专题');
UPDATE tasks SET done = true WHERE id = 1;
DELETE FROM tasks WHERE id = 1;

-- 事务（psql 里）
BEGIN;
  UPDATE tasks SET done = true WHERE id = 1;
  INSERT INTO tasks (title) VALUES ('善后');
COMMIT;          -- 出错就 ROLLBACK;

JSONB 查询（PG 特色）

-- meta 里存了 {"source":"web","priority":3}
SELECT title, meta->>'source' AS source
FROM tasks
WHERE meta @> '{"source":"web"}'::jsonb;     -- @> 包含查询，走 GIN 索引
-- 数字取值：meta->>'priority' 是文本，转数字用 (meta->>'priority')::int

全文检索 tsvector（简介，够用）

-- 给 note 建全文索引（中文需配 zhparser，本模块只讲英文/拼音场景）
ALTER TABLE tasks ADD COLUMN tsv tsvector
  GENERATED ALWAYS AS (to_tsvector('simple', coalesce(note,''))) STORED;
CREATE INDEX ON tasks USING GIN (tsv);

SELECT title FROM tasks
WHERE tsv @@ plainto_tsquery('simple', '备份 恢复');

中文全文检索需要额外装 zhparser 扩展，1GB 小服务器上不急；先用 pg_trgm 三元组模糊搜索过渡（CREATE EXTENSION pg_trgm; + GIN 索引）。

窗口函数（简介：排名/去重）

-- 每个用户最新的 1 条任务
SELECT DISTINCT ON (user_id) user_id, id, title, created_at
FROM tasks
ORDER BY user_id, created_at DESC;
-- 通用窗口：行号/累计
SELECT user_id, title,
       ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
FROM tasks;

4.2 常用 psql 命令（背这 8 个就够）

这是本模块的心脏。三方案都给"建表 → CRUD → 事务"完整可运行示例，附 go.mod。方案 A（pgx）是 PG 首选驱动，性能最好、特性最全；方案 B（GORM）是工作台接入方式；方案 C（sqlc）简介。

mkdir pgdemo && cd pgdemo
go mod init pgdemo
go get github.com/jackc/pgx/v5@v5.11.0
go get gorm.io/gorm@v1.31.2
go get gorm.io/driver/postgres
go get github.com/google/uuid

5.1 方案 A（首选）：pgx v5 —— database/sql 接口与 pgxpool 连接池

pgx 有两种用法：①包装成 database/sql 接口（迁移成本低）；②直接用 pgxpool（原生、更快、类型支持更好）。PG 项目推荐直接用 pgxpool。完整单文件示例：

文件：pgxpool_demo.go

package main

import (
	"context"
	"fmt"
	"log"
	"time"

	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgxpool"
)

type Task struct {
	ID        int64
	Title     string
	Done      bool
	CreatedAt time.Time
}

func main() {
	ctx := context.Background()
	dsn := "postgres://appuser:Dev%232026Strong@localhost:5432/appdb?sslmode=disable"

	// 连接池参数：1GB 服务器上 MaxConns 压小（见 §7.3）
	cfg, err := pgxpool.ParseConfig(dsn)
	if err != nil { log.Fatal(err) }
	cfg.MaxConns = 4                      // 本机可 10；1GB 服务器 4 起步
	cfg.MinConns = 1
	cfg.MaxConnLifetime = 30 * time.Minute
	cfg.MaxConnIdleTime = 5 * time.Minute

	pool, err := pgxpool.NewWithConfig(ctx, cfg)
	if err != nil { log.Fatal(err) }
	defer pool.Close()

	// 0. 建表
	_, err = pool.Exec(ctx, `
CREATE TABLE IF NOT EXISTS tasks (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title      text NOT NULL,
  done       boolean NOT NULL DEFAULT false,
  created_at timestamptz NOT NULL DEFAULT now()
)`)
	if err != nil { log.Fatal(err) }

	// 1. 插入（命名参数 @title）
	var id int64
	err = pool.QueryRow(ctx,
		`INSERT INTO tasks (title) VALUES (@title) RETURNING id`,
		pgx.NamedArgs{"title": "pgxpool 第一条任务"},
	).Scan(&id)
	if err != nil { log.Fatal(err) }
	fmt.Println("inserted id =", id)

	// 2. 单条查询（Scan 到结构体）
	var t Task
	err = pool.QueryRow(ctx,
		`SELECT id, title, done, created_at FROM tasks WHERE id = $1`, id,
	).Scan(&t.ID, &t.Title, &t.Done, &t.CreatedAt)
	if err != nil { log.Fatal(err) }
	fmt.Printf("row = %+v\n", t)

	// 3. 批量插入（CopyFrom，最快的批量写法）
	rows := [][]any{{"批量一"}, {"批量二"}, {"批量三"}}
	n, err := pool.CopyFrom(ctx,
		pgx.Identifier{"tasks"}, []string{"title"},
		pgx.CopyFromRows(rows))
	if err != nil { log.Fatal(err) }
	fmt.Println("copied rows =", n)

	// 4. 事务（转账式：短事务，包住两条写）
	tx, err := pool.Begin(ctx)
	if err != nil { log.Fatal(err) }
	defer tx.Rollback(ctx) // 提交后再 Rollback 是无害的 no-op

	if _, err = tx.Exec(ctx,
		`UPDATE tasks SET done = true WHERE id = $1`, id); err != nil {
		log.Fatal(err)
	}
	if _, err = tx.Exec(ctx,
		`INSERT INTO tasks (title) VALUES ('事务里的第二条')`); err != nil {
		log.Fatal(err)
	}
	if err = tx.Commit(ctx); err != nil { log.Fatal(err) }
	fmt.Println("transaction committed")

	// 5. 多行查询（Rows 遍历，记得 Close）
	rs, err := pool.Query(ctx,
		`SELECT id, title, done, created_at FROM tasks ORDER BY id DESC LIMIT 5`)
	if err != nil { log.Fatal(err) }
	defer rs.Close()
	for rs.Next() {
		var row Task
		if err := rs.Scan(&row.ID, &row.Title, &row.Done, &row.CreatedAt); err != nil {
			log.Fatal(err)
		}
		fmt.Printf("list: %d %s\n", row.ID, row.Title)
	}
}

运行：go run pgxpool_demo.go。

5.1.1 database/sql 包装用法（迁移旧代码时）

import _ "github.com/jackc/pgx/v5/stdlib"
import "database/sql"

db, err := sql.Open("pgx", dsn)   // 驱动名固定 "pgx"
// 之后就是标准 database/sql 那套 db.QueryRow / db.Exec / tx

选型结论：新写的 PG 项目直接上 pgxpool（原生接口，支持 jsonb/数组/任何类型，不用 ORM 也写得很爽）；需要 ORM 时才走方案 B。

5.2 方案 B：GORM PG 驱动 —— 工作台接入方式

工作台契约（workbench-contract.md §2/§3）：后端 Gin + GORM，默认 glebarez/sqlite，由环境变量 DB_DRIVER=sqlite|mysql 切换。接 PG 时照同一结构扩展一个分支，不新增端点、不改模型字段。

差异点：DSN 与 SQLite 完全不同

工作台 internal/db/db.go 的 PG 分支（照契约结构改）

package db

import (
	"fmt"
	"os"

	"github.com/glebarez/sqlite" // 工作台默认驱动
	"gorm.io/driver/postgres"   // PG 驱动
	"gorm.io/gorm"
)

// Open 按 DB_DRIVER 环境变量选驱动；模型（User/Task）与契约 §5 完全一致，不改。
func Open() (*gorm.DB, error) {
	switch os.Getenv("DB_DRIVER") {
	case "postgres":
		dsn := os.Getenv("DB_DSN") // 见下方 .env
		return gorm.Open(postgres.Open(dsn), &gorm.Config{})
	default: // sqlite（工作台默认）
		path := os.Getenv("DB_PATH")
		if path == "" { path = "./workbench.db" }
		return gorm.Open(sqlite.Open(path), &gorm.Config{})
	}
}

本地切 PG 的 .env（只加这几行，其他不动）

# .env（工作台根目录）
PORT=8080
JWT_SECRET=换成你自己的随机串
DB_DRIVER=postgres
DB_DSN=host=localhost user=appuser password=Dev#2026Strong dbname=appdb port=5432 sslmode=disable TimeZone=Asia/Shanghai

模型仍是契约 §5 的 User/Task，GORM 自动迁移会在 PG 上建成 bigint 主键 + timestamptz 友好的列。启动 go run ./cmd/server，访问 /healthz 与任务 CRUD 全部照常——这就是"同一结构、切换驱动"的目标。

5.3 方案 C（简介）：sqlc v1.31.1 生成类型安全代码

sqlc 不写 ORM，而是你写原生 SQL，它生成类型安全的 Go 代码（编译期检查、无反射、性能接近手写）。三步最小示例：

# 1. 装 sqlc v1.31.1（本机开发用，不上服务器）
go install github.com/sqlc-dev/sqlc/cmd/sqlc@v1.31.1

# 2. 项目里放 sqlc.yaml + query.sql + 建表 schema.sql

sqlc.yaml

version: "2"
sql:
  - engine: "postgresql"
    schema: "schema.sql"
    queries: "query.sql"
    gen:
      go:
        package: "sqlcgen"
        out: "./internal/sqlcgen"

query.sql

-- name: GetTask :one
SELECT id, title, done, created_at FROM tasks WHERE id = $1;

-- name: ListUndone :many
SELECT id, title FROM tasks WHERE done = false ORDER BY id DESC LIMIT $1;

-- name: CreateTask :one
INSERT INTO tasks (title) VALUES ($1) RETURNING id, title, done, created_at;

运行 sqlc generate，得到 internal/sqlcgen/ 下带类型的 Go 代码：调用 q.GetTask(ctx, 5) 直接返回 (Task, error)，字段类型由 sqlc 从 PG schema 推导。适合：SQL 写得熟、要编译期安全、不想用 ORM 的中大型服务；工作台阶段不必上，知道有这条路即可。

5.4 三方案怎么选（一页决策）

6.1 JSONB 存半结构化

-- 订单里塞一段结构常变的 ext_info（收件人备注/渠道参数），不用加列
CREATE TABLE orders (
  id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  ext    jsonb NOT NULL DEFAULT '{}'::jsonb
);
-- 查询"所有来自 web 渠道的订单"——配 GIN 索引后很快
CREATE INDEX ON orders USING GIN (ext jsonb_path_ops);
SELECT id FROM orders WHERE ext @> '{"channel":"web"}';

纪律：JSONB 存"结构松散、不常作为查询条件"的字段；核心查询字段仍然拆成独立列+普通索引，不要全塞 jsonb 里再 @> 乱查。

6.2 upsert（ON CONFLICT）

-- 唯一冲突时更新，而不是报错。工作台"同一用户同一任务去重"场景：
INSERT INTO tasks (user_id, title) VALUES (7, '每日备份')
ON CONFLICT (user_id, title)
DO UPDATE SET updated_at = now()
RETURNING id;

6.3 时间与时区：永远 timestamptz

• 列定义用 timestamptz，服务器时区设 Asia/Shanghai（Docker compose 已设 TZ；服务器装完 timedatectl set-timezone Asia/Shanghai）。
• Go 侧 time.Now() 直接写入，读出来是带时区的 time.Time；前端展示再按需转。
• 永远不要用 timestamp without time zone——它存什么时区取决于谁写的，迟早乱。

6.4 分页（两种）

-- ① 偏移分页（页号跳着用，简单但深翻页慢）
SELECT * FROM tasks ORDER BY id DESC LIMIT 20 OFFSET 400;

-- ② 游标分页（列表滚动加载，推荐：记住上一页最后一条的 id）
SELECT * FROM tasks WHERE id < $last_id ORDER BY id DESC LIMIT 20;

6.5 全文检索

见 §4.1 tsvector 简介。英文/数字场景开箱即用；中文上 pg_trgm 过渡、zhparser 进阶。小服务器别一上来上重型搜索引擎，PG 自带的够用就先用着。

7.1 pg_dump / pg_restore 完整命令

# 逻辑备份（自定义压缩格式，可选择性恢复；服务器上跑）
pg_dump -h 127.0.0.1 -U appuser -d appdb -F c -f /backup/appdb_$(date +%F).dump

# 恢复到新库（先建空库）
createdb -h 127.0.0.1 -U appuser appdb_restore
pg_restore -h 127.0.0.1 -U appuser -d appdb_restore /backup/appdb_2026-10-14.dump

# 纯 SQL 文本备份（小库也可用，便于人读）
pg_dump -h 127.0.0.1 -U appuser -d appdb > /backup/appdb_$(date +%F).sql

7.2 cron 每日备份脚本（完整，可直接抄）

/usr/local/bin/pg_backup.sh

#!/usr/bin/env bash
set -euo pipefail

BACKUP_DIR=/backup
RETENTION_DAYS=14                       # 保留 14 天
FILE=appdb_$(date +%F_%H%M).dump
export PGPASSWORD='Prod#2026Xy7!'        # 与建用户时一致；权限 chmod 700 此脚本

mkdir -p "$BACKUP_DIR"
pg_dump -h 127.0.0.1 -U appuser -d appdb -F c -f "$BACKUP_DIR/$FILE"

# 删 14 天前的旧备份
find "$BACKUP_DIR" -name 'appdb_*.dump' -mtime +${RETENTION_DAYS} -delete

echo "[$(date)] backup ok: $FILE"

装 cron：

sudo chmod 700 /usr/local/bin/pg_backup.sh      # 脚本里有密码，权限收紧
sudo crontab -e
# 加一行：每天凌晨 3:10 备份
10 3 * * * /usr/local/bin/pg_backup.sh >> /var/log/pg_backup.log 2>&1

WAL 一句话：WAL（预写日志）默认开启，保证宕机不丢已提交事务；做"时间点恢复（PITR）"才需要归档 WAL——你的 1GB 小工作台用每日 pg_dump 就够，不折腾 PITR。

7.3 1GB 低配服务器调优（postgresql.conf）

PG 默认配置是按大内存服务器写的，1GB 机器必须压。编辑 /etc/postgresql/18/main/postgresql.conf，改完 systemctl restart postgresql：

1GB 机器铁律：PG + Go 二进制 + 系统总共 1GB。max_connections 压小、Go 连接池压小、shared_buffers 别贪大。内存吃紧时先砍连接数，不要先砍 shared_buffers。

照抄报错关键词，对号入座。这些都是真实会踩到的。

9.1 本模块验收（过了才算完）

9.2 版本与来源表

下一步：PG 模式工作台跑通后，进入 18 Redis 专题（缓存/队列）；生产化部署细节（systemd/Nginx/监控）见 20 部署运维专题。