PostgreSQL数据库移植到SQLite数据库

最近由于工作需要，要将之前使用的PostgreSQL数据库换成SQLite数据库，上网搜集了些资料，下面就整理一下我的处理方法。

思路：使用pg_dump将数据库转存成sql命令文本，然后在SQLite里执行sql命令创建数据库，并导入数据

具体操作步骤

         我们主要可以通过以下三步完成数据库的转换。

1.生成转储脚本文件

           我们可以通过pg_dump生成数据库的转储模式（表结构）和转储数据，然后编辑该文件，在导入SQLite。但是我没有选择这样做，因为将转储模式和转储数据生成到一个文件中，然后编辑文件不是很方便，所以我选择分别将转储模式和转储数据生成到两个文件中，先建立数据表，再导入数据，分两步走。

1.1 生成转储模式文件schema.sql

C:\Program Files\PostgreSQL\9.2\bin>pg_dump -h 192.168.9.210 -U postgres -s YOUR_DB_NAME > E:\schema.sql

C:\Program Files\PostgreSQL\9.2\bin>pg_dump -h 192.168.9.210 -U postgres --data-only --inserts YOUR_DB_NAME > E:\data.sql

2.编辑转储脚本文件

           为什么需要这步呢，因为生成的脚本文件有很多是SQLite不支持的，需要将他们转化成SQLite支持的语句。

2.1 删除‘SET’开头的句子（schema.sql，data.sql）

              你会在文件的开头看到类似SET statement_timeout = 0;   的句子，删除这些句子就可以了，因为SQLite不需要这些东西。

2.2 SQLite不支持通过ALTER添加主键，只能在建表时添加（schema.sql）

将所有类似于：

ALTER TABLE ONLY table_name ADD CONSTRAINT table_name_pkey PRIMARY KEY (id);

2.3 SQLite不支持COMMENT注释（schema.sql）

  删掉所有以COMMENT开头的语句

2.4 SQLite不支持OWNER权限设置（schema.sql）

  删掉以下所有类似的句子

ALTER TABLE public.table_name OWNER TO postgres

 删掉以下类似语句

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog

 将以下PostgreSQL自增语句        

CREATE TABLE table_name (
    id integer NOT NULL,
);

CREATE SEQUENCE table_name_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;


ALTER SEQUENCE table_name_id_seq OWNED BY table_name.id;

ALTER TABLE ONLY table_name ALTER COLUMN id SET DEFAULT nextval('table_name_id_seq'::regclass);
 

CREATE TABLE table_name (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
);

2.7 删除重置序列对象的计数器数值操作语句（data.sql）

              你会看到一些类似于SELECT pg_catalog.setval('MY_OBJECT_id_seq', 10, true);的句子，它在PostgreSQL里的作用是重置序列对象的计数器数值，就是为了保证自增id值的正确性，具体可以查看PostgreSQL序列操作函数。这些也直接删掉就可以了，SQLite里不会用到这些值，并且这些会在SQLite里报错的。

2.8 替换 true =>’t‘ 、false => ’f‘（data.sql）

              如果在生成的INSERT INTO语句中有true和false的值，我们需要将它们分别替换成’t‘和’f‘。例如：

INSERT INTO table_name VALUES (1, true, false);

INSERT INTO table_name VALUES (1, 't', 'f'); 

              第一次导入2MB的数据花了大约整整12分钟，之后Google之，发现SQLite默认情况下是将一条语句添加到一个事务中，这貌似看起来很浪费时间（修改之后，导入数据只花了12秒）。

             在文件的开始添加BEGIN；在文件的结尾添加END；就可以将所有的INSERT INTO添加到一个事务中去处理了。例如：

BEGIN;
-- a lot of INSERT INTO statments
END;

3.1 建立数据库

# sqlite3 databasename.db

sqlite> .read pschema.sql 

sqlite> .read data.sql

好了，大功告成，完成了从PostgreSQL到SQLite的转换。