企业官网建设流程全解析

• 数据工程
• ETL
• 数据集成
• 数据库

导读

本指南以 pgloader 官方教程中 SQLite 迁移章节为核心，讲解如何把 SQLite 嵌入式数据库（.sqlite/.db文件，甚至远程 HTTP 地址上的 zip 压缩包）一键迁移到 PostgreSQL。读完本文，你将掌握单条命令行迁移、sqlite.load加载命令文件写法、WITH子句的全部可选行为、默认类型转换规则以及表级过滤（INCLUDING/EXCLUDING）的实战用法，并理解 pgloader 底层是如何借助 SQLite 元数据自动建表、建索引、建外键的。

SQLite 的嵌入式特性让它在单机小规模场景下非常顺手，但当项目需要更高并发、需要多人协作与集中管理时，迁移到 PostgreSQL 往往是自然之选——而 pgloader 恰好为此设计，一条命令即可完成 schema、数据、约束、主键和外键的完整迁移。

一、单命令行极速迁移：一条命令搞定一切

最简单的用法是把 SQLite 文件当作数据源，直接给出目标 PostgreSQL 连接串即可：

$ createdb chinook $ pgloader https://github.com/lerocha/chinook-database/raw/master/ChinookDatabase/DataSources/Chinook_Sqlite_AutoIncrementPKs.sqlite pgsql:///chinook

以 Chinook 示例库为例，pgloader 会自动完成：建 schema、迁移数据、还原约束、主键与外键等全部工作。官方文档同时展示了一个有趣的细节：Chinook schema 中playlisttrack表带有多个主键定义，而 PostgreSQL 不允许这种做法，因此迁移过程会记录一条错误（但不会中断整体迁移）：

ERROR Database error 42P16: multiple primary keys for table "playlisttrack" are not allowed QUERY: ALTER TABLE playlisttrack ADD PRIMARY KEY USING INDEX idx_66873_sqlite_autoindex_playlisttrack_1;

随后 pgloader 输出一份完整的统计报告，包含read / imported / errors三个维度，例如该次迁移的节选：

table name read imported errors total time ----------------------- --------- --------- --------- -------------- fetch 0 0 0 0.877s fetch meta data 33 33 0 0.033s Create Schemas 0 0 0 0.003s Create SQL Types 0 0 0 0.006s Create tables 22 22 0 0.043s Set Table OIDs 11 11 0 0.012s ----------------------- --------- --------- --------- -------------- album 347 347 0 0.023s artist 275 275 0 0.023s customer 59 59 0 0.021s employee 8 8 0 0.018s invoice 412 412 0 0.031s genre 25 25 0 0.021s invoiceline 2240 2240 0 0.034s mediatype 5 5 0 0.025s playlisttrack 8715 8715 0 0.040s playlist 18 18 0 0.016s track 3503 3503 0 0.111s ----------------------- --------- --------- --------- -------------- COPY Threads Completion 33 33 0 0.313s Create Indexes 22 22 0 0.160s Index Build Completion 22 22 0 0.027s Reset Sequences 0 0 0 0.017s Primary Keys 12 0 1 0.013s Create Foreign Keys 11 11 0 0.040s Create Triggers 0 0 0 0.000s Install Comments 0 0 0 0.000s ----------------------- --------- --------- --------- -------------- Total import time 15607 15607 0 1.669s

从这份报告可以清楚看到 pgloader 的迁移流水线：fetch meta data（读取 SQLite 元数据）→Create Schemas→Create SQL Types→Create tables→ 逐表COPY数据 →Create Indexes→Reset Sequences→Primary Keys→Create Foreign Keys。其中Primary Keys一行12 / 0 / 1正是上面那条 42P16 错误造成的。

命令行一行搞定适用于常规场景；遇到特殊需求（如自定义类型转换、表过滤、只迁移部分表）时，就需要用 pgloader 命令文件（command file）来精确控制迁移行为。

二、加载命令文件（sqlite.load）：可控迁移的基石

要精确控制迁移流程，需要把操作写进一个command文件，再交给 pgloader 解析执行。官方教程给出的最小示例（对应仓库中的 test/sqlite.load 一类文件）：

load database from 'sqlite/Chinook_Sqlite_AutoIncrementPKs.sqlite' into postgresql:///pgloader with include drop, create tables, create indexes, reset sequences set work_mem to '16MB', maintenance_work_mem to '512 MB';

命令逐段拆解如下：

关键点在于：pgloader 会充分利用 SQLite 文件内的元数据（meta-data），自动生成一个足以承载源数据的 PostgreSQL 数据库结构，然后再把数据灌进去——建表、建索引、恢复主键/外键都是自动完成的，无需手工编写 DDL。

如果数据源是远程 HTTP 地址，pgloader 会先下载文件，再解压（unziped）后读取，这一点在官方教程的运行日志中可以看到明确的Fetching 'https://...'记录。

三、运行命令文件并读懂输出

执行方式非常简单：

$ pgloader sqlite.load ... LOG Starting pgloader, log system is ready. ... LOG Parsing commands from file "/Users/dim/dev/pgloader/test/sqlite.load" ... WARNING Postgres warning: table "album" does not exist, skipping ... WARNING Postgres warning: table "artist" does not exist, skipping ... WARNING Postgres warning: table "customer" does not exist, skipping ... WARNING Postgres warning: table "employee" does not exist, skipping ... WARNING Postgres warning: table "genre" does not exist, skipping ... WARNING Postgres warning: table "invoice" does not exist, skipping ... WARNING Postgres warning: table "invoiceline" does not exist, skipping ... WARNING Postgres warning: table "mediatype" does not exist, skipping ... WARNING Postgres warning: table "playlist" does not exist, skipping ... WARNING Postgres warning: table "playlisttrack" does not exist, skipping ... WARNING Postgres warning: table "track" does not exist, skipping

随后是精简版的统计输出（教程中为便于在线阅读做过编辑）：

table name read imported errors time ---------------------- --------- --------- --------- -------------- create, truncate 0 0 0 0.052s Album 347 347 0 0.070s Artist 275 275 0 0.014s Customer 59 59 0 0.014s Employee 8 8 0 0.012s Genre 25 25 0 0.018s Invoice 412 412 0 0.032s InvoiceLine 2240 2240 0 0.077s MediaType 5 5 0 0.012s Playlist 18 18 0 0.008s PlaylistTrack 8715 8715 0 0.071s Track 3503 3503 0 0.105s index build completion 0 0 0 0.000s ---------------------- --------- --------- --------- -------------- Create Indexes 20 20 0 0.279s reset sequences 0 0 0 0.043s ---------------------- --------- --------- --------- -------------- Total streaming time 15607 15607 0 0.476s

官方教程特别提醒读者注意两点：

1. WARNING ... does not exist, skipping是预期行为：因为目标库为空，而命令里带了include drop，pgloader 会执行DROP TABLE IF EXISTS，空库中自然没有这些表，于是产生这些警告，无需担心。
2. 输出结果经过编辑：真实终端输出包含更多行（如带时间戳的 LOG 行），本文与官方文档展示的是精简版，便于浏览。

四、WITH 子句选项详解：控制迁移每一步

参考官方参考手册 docs/ref/sqlite.rst，从 SQLite 迁移时WITH子句支持的选项如下。SQLite 数据源的默认 WITH 子句是：no truncate、create tables、include drop、create indexes、reset sequences、downcase identifiers、encoding 'utf-8'。

4.1 目标表清理类

• include drop：先DROP掉目标库中所有名字出现在 SQLite 源库中的表，再重建。该选项让你可以反复运行同一条命令直到调通所有选项，每次都从干净环境自动开始。注意DROP使用CASCADE，会连带删除引用这些表的所有对象——可能误删不属于本次迁移的其他表，务必谨慎。
• include no drop：不发出任何DROP语句。
• truncate：在向每个目标表装载数据之前执行TRUNCATE。
• no truncate：不执行TRUNCATE。
• disable triggers：装载数据前对目标表执行ALTER TABLE ... DISABLE TRIGGER ALL，COPY完成后再ENABLE TRIGGER ALL。适合向已存在的表装载数据时绕过外键约束与用户触发器；代价是装载后外键约束可能处于无效状态，慎用。

4.2 结构创建类

• create tables：依据 SQLite 文件中的元数据（字段列表与数据类型）创建表，并进行 SQLite→PostgreSQL 的标准类型转换。
• create no tables：跳过建表，目标表必须已存在。此时 pgloader 会从目标库读取元数据、检查类型转换，并在装载前移除约束和索引、装载完成后重新安装。
• create indexes：读取 SQLite 库中全部索引定义，在 PostgreSQL 端创建同样的一组索引。
• create no indexes：跳过索引创建。
• drop indexes：装载数据前先删除目标库索引，数据拷贝结束后再重建——这是经典的“先删索引、批量灌数据、最后建索引”提速策略。

4.3 序列与范围控制

• reset sequences：数据装载结束、索引全部建成后，把所有 PostgreSQL 序列重置为所挂载列当前的最大值（保证新插入行的自增主键不会冲突）。
• reset no sequences：跳过序列重置。注意schema only与data only对该选项无影响。
• schema only：只迁移 schema（含索引，前提是启用了create indexes），不迁移数据。
• data only：只发出COPY语句装载数据，不做任何其他处理。

4.4 编码控制

• encoding：指定解析 SQLite 文本数据所用的编码，默认utf-8。底层实现中 pgloader 会先查询pragma encoding;（见 src/sources/sqlite/sqlite-schema.lisp 的sqlite-pragma-encoding/sqlite-encoding），识别UTF-8、UTF-16、UTF-16le、UTF-16be等实际存储编码，用于逐行解码。

五、默认类型转换规则（Casting Rules）：SQLite 类型如何映射到 PostgreSQL

SQLite 是动态类型系统（类型亲和性），pgloader 通过一套默认转换规则把它映射到 PostgreSQL 强类型。完整定义见 src/sources/sqlite/sqlite-cast-rules.lisp，可归纳为四类：

数值类型：

type tinyint to smallint using integer-to-string type integer to bigint using integer-to-string type float to float using float-to-string type real to real using float-to-string type double to double precision using float-to-string type numeric to numeric using float-to-string type decimal to numeric using float-to-string

文本类型（统一收窄为text并丢弃 typemod，即长度/精度修饰）：

type character to text drop typemod type varchar to text drop typemod type nvarchar to text drop typemod type char to text drop typemod type nchar to text drop typemod type clob to text drop typemod

二进制类型：

type blob to bytea

日期时间类型：

type datetime to timestamptz using sqlite-timestamp-to-timestamp type timestamp to timestamptz using sqlite-timestamp-to-timestamp type timestamptz to timestamptz using sqlite-timestamp-to-timestamp

值得深入说明的源码细节：

1. 整数：integer→bigint，而integer+ 自增（auto-increment）→bigserial（见 sqlite-cast-rules.lisp），这样 SQLite 的INTEGER PRIMARY KEY AUTOINCREMENT在 PostgreSQL 里变成自增主键。
2. ORM 兼容别名：byte[]和裸byte都映射到bytea（issue #1231 的修复），int2/int4/int8这类 PostgreSQL 风格别名也得到支持。
3. 兜底规则：任何未识别的 SQLite 类型都映射为text（与 v4 行为保持一致，见 sqlite-cast-rules.lisp）。
4. 转换函数：integer-to-string会小心处理 SQLite 带引号的整数字符串表示；float-to-string把 Common Lisp 的100.0d0风格转换成 PostgreSQL 接受的100.0；sqlite-timestamp-to-timestamp则处理“整数年份”（如1988→1988-01-01）与0（映射为NULL）等 SQLite 特有的日期形态，三者定义均在 src/utils/transforms.lisp。
5. 默认值归一化：cast方法会把CURRENT_TIMESTAMP(...)、datetime('now')等 SQLite 常用默认值表达式识别并映射为 PostgreSQL 的CURRENT_TIMESTAMP（见 sqlite-cast-rules.lisp）。

如果你需要覆盖或补充默认规则，可以使用CAST子句定义自定义转换规则；SQLite 源的类型解析依赖 src/parsers/parse-sqlite-type-name.lisp 完成类型名 + typemod + 噪声词的拆分。

六、部分迁移：INCLUDING / EXCLUDING 表过滤

不想迁移全部表时，可以在命令文件中使用表名模式过滤：

INCLUDING ONLY TABLE NAMES LIKE—— 用逗号分隔的表名模式列表，限定只迁移匹配的表：

including only table names like 'Invoice%'

EXCLUDING TABLE NAMES LIKE—— 用逗号分隔的表名模式列表，从INCLUDING过滤结果中再排除匹配的表：

excluding table names like 'appointments'

注意顺序语义：EXCLUDING只作用于INCLUDING的结果集。

从源码看，过滤最终被翻译成针对sqlite_master.tbl_name的LIKE子句（src/sources/sqlite/sqlite-schema.lisp 的filter-list-to-where-clause，以及 sql/list-tables.sql 中type='table'且排除sqlite_sequence的查询）。同时外键处理会智能跳过因过滤而缺失的关联表，避免生成无效的外键定义。

七、源码视角：元数据自动发现是如何工作的

整条 SQLite 迁移链路由 src/sources/sqlite/sqlite.lisp 的fetch-metadata驱动，流程如下：

1. 连接与序列探测：open-connection打开 SQLite 文件并执行sqlite-sequence.sql探测是否存在sqlite_sequence目录（sqlite-connection.lisp）。
2. 列定义：对每个表执行PRAGMA table_info（sql/list-columns.sql），并借助find-sequence/find-auto-increment-in-create-sql判断INTEGER PRIMARY KEY AUTOINCREMENT，标记为自增列（sqlite-schema.lisp）。
3. 索引定义：通过PRAGMA index_list+index_info读取索引（sql/list-table-indexes.sql），并特别处理“不在 index_list 中列出”的整数主键隐式索引（add-unlisted-primary-key-index，sqlite-schema.lisp）。
4. 外键定义：通过PRAGMA foreign_key_list读取外键（sql/list-fkeys.sql），支持ON UPDATE/ON DELETE规则还原。
5. 视图：PRAGMA table_info对视图同样有效，因此视图列也能被自动发现；MATERIALIZE VIEWS时则会先在 SQLite 侧建好视图再读取。

数据装载阶段，map-rows用SELECT ... FROM ...逐行拉取，并按目标列类型调用parse-value处理“blob 列返回字符串（base64）”或“text 列返回字节”这类 SQLite 驱动特有的形态（src/sources/sqlite/sqlite.lisp），随后通过 PostgreSQL COPY 协议批量写入。

仓库中的回归测试与样例可用于验证上述行为，例如 test/sqlite.load、test/sqlite-chinook.load、test/sqlite-testpk.load，以及 clojure/test/pgloader/source/sqlite_test.clj；官方测试场景集（含 Chinook、matviews、base64、spaced-path 等）位于 clojure/tests/sqlite/（Windows 路径风格目录由 clojure/tests/Makefile 驱动）。

八、总结与建议

最后提醒两点：include drop的 CASCADE 语义可能波及目标库中其他对象，生产环境请先确认目标库范围；Chinook 这类含“同表多主键”定义的源库在迁移时会触发42P16错误，但不会中断整体迁移——只需在迁移后按需手工修正目标表的主键即可。更完整的通用子句（连接串格式、并行度、before load/after load等）可参考 docs/command.rst 与 docs/ref/pgsql.rst。

• 数据工程
• ETL
• 数据集成
• 数据库