如何把数据从SQLite迁移到PostgreSQL

##

如何把数据从SQLite迁移到PostgreSQL

1、DB-Engines 中的SQLite 和 PostgreSQL

SQLite 和 PostgreSQL 在 DB-Engines 排名中近三年保持稳定，这一现象可以从多个角度分析其背后的原因和意义：

1. 技术定位的稳定性

• SQLite 作为嵌入式数据库，长期占据轻量级应用场景（移动设备、IoT、本地应用等）。它的无需服务端、零配置特性在特定领域几乎没有替代品，因此需求稳定。
• PostgreSQL 作为功能完备的企业级关系型数据库，在复杂查询、扩展性、标准兼容性上持续领先，尤其适合需要 ACID 事务和高级功能的场景（如地理空间数据、JSON 处理）。其开源属性也避免了商业数据库的供应商锁定风险。

2. 生态成熟度

• 两者均已发展数十年（SQLite 始于 2000，PostgreSQL 始于 1989），生态工具链（如 ORM 支持、监控工具）和社区知识积累已高度成熟，用户迁移成本高。
• 新数据库（如 CockroachDB、TimescaleDB）更多瞄准细分领域（分布式、时序数据），而非直接挑战它们的核心市场。

3. 排名方法的局限性

• DB-Engines 排名综合了技术讨论（Stack Overflow）、招聘需求、文献提及等指标，而非纯粹的性能或市场份额。两者的长期高排名反映了持续的开发者关注度和企业采用率，但可能掩盖了某些垂直领域的波动（如 PostgreSQL 在云原生场景的增长）。

4. 行业趋势的印证

• SQLite 的稳定性：移动应用和边缘计算的增长巩固了其地位。即使 NoSQL 流行，本地存储仍需关系模型（如 Android 的 Room 库基于 SQLite）。
• PostgreSQL 的韧性：云厂商（AWS RDS、Azure PostgreSQL）的托管服务降低了使用门槛，同时其通过扩展（如 PostGIS、pgvector）不断适应新需求（GIS、AI 向量搜索），抵消了部分 NoSQL 的冲击。

5. 潜在启示

• 创新不等于替代：NewSQL 和 NoSQL 的兴起并未削弱这两者在核心场景的价值，说明数据库市场是分层演化而非零和博弈。
• 长期维护的重要性：两者的持续更新（如 PostgreSQL 的 JSONB 性能优化、SQLite 的 WASM 支持）证明持续迭代比颠覆性创新更能维持竞争力。

排名不变并不意味着停滞，而是反映了它们在各自细分领域已形成近乎垄断的“最优解”地位。未来除非出现范式级技术变革（如量子数据库？），否则这种稳定性可能延续。对于用户而言，选择它们几乎是低风险决策，但也需关注新兴需求（如 HTAP、Serverless 数据库）是否超出它们的覆盖范围。

2、SQLite安装和测试

2.1、编译安装SQLite

Ref doc: https://sqlite.org/src/doc/trunk/doc/compile-for-unix.md

mkdir -p /sqlite/app
cd /sqlite/app
dnf install gcc make tcl-dev
wget https://sqlite.org/tmp/tcl9.0.0.tar.gz
tar -zxvf tcl9.0.0.tar.gz 
cd tcl9.0.0/unix/
./configure
[root@tom sqlite]# which tclsh9.0 
/usr/local/bin/tclsh9.0cd /sqlite/app
wget https://www.sqlite.org/2025/sqlite-src-3490100.zip
unzip sqlite-src-3490100.zip mv sqlite-src-3490100 sqlite
cd sqlite/
./configure --enable-all --with-tclsh=/usr/local/bin/tclsh9.0
make sqlite3.c
make sqlite3
make sqldiff
make sqlite3_rsync
make tclextension-install
make devtest
make releasetest
make sqlite3_analyzervi ~/.bash_profile
/sqlite/app/sqlite

2.2、数据测试

Ref doc: https://www.sqlite.org/cli.html

[root@tom sqlite]# ./sqlite3 db01
SQLite version 3.49.1 2025-02-18 13:38:58
Enter ".help" for usage hints.
sqlite> create table t1(id int,name varchar(20));
sqlite> insert into t1 values(1,'tom');
sqlite> insert into t1 values(2,'jerry');
sqlite> select * from t1;
1|tom
2|jerry
sqlite> .mode table
sqlite> select * from t1;
+----+-------+
| id | name  |
+----+-------+
| 1  | tom   |
| 2  | jerry |
+----+-------+
sqlite> .exit                       

3、Postgresql安装和测试

3.1、编译安装postgresql

Ref: https://www.postgresql.org/docs/17/install-make.html

3.2、测试

[postgres@tom:/home/postgres]$psql
psql (17.4)
Type "help" for help.postgres=# create database db01;
CREATE DATABASE
postgres=# \q

4、pgloader安装

[root@tom ~]# dnf install -y https://download.postgresql.org/pub/repos/yum/common/redhat/rhel-9-x86_64/pgloader-3.6.10-1PGDG.rhel9.x86_64.rpm
Last metadata expiration check: 5:22:13 ago on Mon 31 Mar 2025 11:17:36 AM CST.
pgloader-3.6.10-1PGDG.rhel9.x86_64.rpm                                                                                 3.0 MB/s |  21 MB     00:06    
Dependencies resolved.
=======================================================================================================================================================Package                          Architecture                   Version                                    Repository                            Size
=======================================================================================================================================================
Installing:pgloader                         x86_64                         3.6.10-1PGDG.rhel9                         @commandline                          21 MTransaction Summary
=======================================================================================================================================================
Install  1 PackageTotal size: 21 M
Installed size: 22 M
Downloading Packages:
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transactionPreparing        :                                                                                                                               1/1 Installing       : pgloader-3.6.10-1PGDG.rhel9.x86_64                                                                                            1/1 Verifying        : pgloader-3.6.10-1PGDG.rhel9.x86_64                                                                                            1/1 Installed:pgloader-3.6.10-1PGDG.rhel9.x86_64                                                                                                                   Complete!
[root@tom ~]# su - postgres
Last login: Mon Mar 31 16:19:39 CST 2025 on pts/1
[postgres@tom:/home/postgres]$pgloader --version
pgloader version "3.6.7~devel"
compiled with SBCL 2.2.10-1.rhel9

5、数据迁移和验证

5.1、准备参数文件

[postgres@tom:/postgresql/backup]$vi db.load
load databasefrom sqlite:///sqlite/sqlite/db01into postgresql://postgres:postgres@localhost:5432/db01with include drop, create tables, create indexes, reset sequencesset work_mem to '16MB', maintenance_work_mem to '512 MB';

注意：

/sqlite/sqlite/db01是sqlite数据库db01数据文件路径

postgres:postgres@localhost:5432/db01是postgresql连接信息

5.2、数据迁移

[postgres@tom:/postgresql/backup]$pgloader db.load 
2025-03-31T09:47:04.014001+01:00 LOG pgloader version "3.6.7~devel"
2025-03-31T09:47:04.018001+01:00 LOG Data errors in '/tmp/pgloader/'
2025-03-31T09:47:04.018001+01:00 LOG Parsing commands from file #P"/postgresql/backup/db.load"
2025-03-31T09:47:04.118005+01:00 LOG Migrating from #<SQLITE-CONNECTION sqlite:///sqlite/sqlite/db01 {1006781FE3}>
2025-03-31T09:47:04.118005+01:00 LOG Migrating into #<PGSQL-CONNECTION pgsql://postgres@localhost:5432/db01 {10067821A3}>
2025-03-31T09:47:04.348014+01:00 LOG report summary resettable name     errors       rows      bytes      total time
-----------------------  ---------  ---------  ---------  --------------fetch          0          0                     0.000sfetch meta data          0          1                     0.041sCreate Schemas          0          0                     0.001sCreate SQL Types          0          0                     0.006sCreate tables          0          2                     0.021sSet Table OIDs          0          1                     0.008s
-----------------------  ---------  ---------  ---------  --------------t1          0          2     0.0 kB          0.024s
-----------------------  ---------  ---------  ---------  --------------
COPY Threads Completion          0          4                     0.028sIndex Build Completion          0          0                     0.000sReset Sequences          0          0                     0.020sPrimary Keys          0          0                     0.000sCreate Foreign Keys          0          0                     0.000sCreate Triggers          0          0                     0.000sInstall Comments          0          0                     0.000s
-----------------------  ---------  ---------  ---------  --------------Total import time          ✓          2     0.0 kB          0.048s

5.3、迁移验证

[postgres@tom:/postgresql/backup]$psql
psql (17.4)
Type "help" for help.postgres=# \c db01
You are now connected to database "db01" as user "postgres".
db01=# \dList of relationsSchema | Name | Type  |  Owner   
--------+------+-------+----------public | t1   | table | postgres
(1 row)db01=# select * from t1;id | name  
----+-------1 | tom2 | jerry
(2 rows)db01=#