试试使用SQLite FDW从PostgreSQL访问SQLite3数据库吧！
首先
我尝试使用SQLite FDW从PostgreSQL访问SQLite数据库。如果您已经安装了PostgreSQL版本9.6、10、11或12和SQLite3，您可以跳过安装PostgreSQL和SQLite，直接开始安装SQLite FDW。
FDW（Foreign Data Wrapper）是什么？
PostgreSQL具有符合SQL2003 SQL/MED规范的外部数据源访问功能，即FDW（Foreign Data Wrapper）功能。通过该功能，可以访问各种数据源。目前，用户已经创建并公开了用于访问商业RDBMS（如Oracle和SQL Server）、OSS RDBMS（如SQLite和MySQL）以及NoSQL数据库（如Cassandra和GridDB）等数据源的FDW。
从源代码中安装PostgreSQL
我正在从源代码安装PostgreSQL。我参考了PostgreSQL的文档。
在CentOS7上创建一个postgres用户。
$ sudo groupadd postgresql
$ sudo useradd -d /home/postgres -g postgres -s /bin/bash postgres
$ sudo passwd postgres
$ su - postgres
从PostgreSQL社区获取PostgreSQL。
$ wget https://ftp.postgresql.org/pub/source/v12.2/postgresql-12.2.tar.bz2
$ tar -jxvf postgresql-12.2.tar.bz2
安装PostgreSQL。
$ cd postgresql-12.2
$ ./configure
$ make
$ make install
$ cd
从源代码编译安装SQLite
从SQLite的源代码安装。参考SQLite安装在Linux上的教程进行安装。
从SQLite社区获取SQLite。
$ wget https://www.sqlite.org/2020/sqlite-autoconf-3310100.tar.gz
$ tar xvfz sqlite-autoconf-3310100.tar.gz
$ mv sqlite-autoconf-3310100 sqlite3.31.1
安装SQLite。
$ cd sqlite3.31.1
$ ./configure
$ make
$ sudo make install
环境设置
设置环境变量LD_LIBRARY_PATH。
$ LD_LIBRARY_PATH=/usr/local/pgsql/lib:/usr/local/lib
$ export LD_LIBRARY_PATH
设定环境变量 PATH。
$ PATH=/usr/local/pgsql/bin:$PATH
$ export PATH
安装SQLite FDW
安装 SQLite FDW。参考 GitHub 上的 readme.md 文件。
根据 PostgreSQL 社区的说法，有两个版本的 SQLite FDW，但我们将使用这个可以进行更新和推送处理的版本。以下是将 SQLite FDW 源代码包含在 PostgreSQL 源代码树中进行安装的方法。
$ cd ~/postgresql-12.2/contrib
$ git clone https://github.com/pgspider/sqlite_fdw.git
$ cd sqlite_fdw
$ make
$ sudo make install
如果pg_config命令的路径被设置正确，可以使用以下命令进行安装。
$ make USE_PGXS=1
$ sudo make install USE_PGXS=1
启动 PostgreSQL。
$ initdb -D /usr/local/pgsql/data
$ postgres -D /usr/local/pgsql/data &
获取样本数据库
从SQLite教程中获取示例数据库。
$ cd /usr/local/pgsql/data/
$ wget https://www.sqlitetutorial.net/wp-content/uploads/2018/03/chinook.zip
$ unzip chinook.zip
让我们使用命令行 Shell (sqlite3) 来查看示例数据库 (chinnook.db)。共有11个表存在。
$ sqlite3 chinook.db
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> .tab
albums employees invoices playlists
artists genres media_types tracks
customers invoice_items playlist_track
SQLite FDW的初始配置设置。
在数据库中使用CREATE EXTENSION导入SQLite FDW。
$ psql
psql (12.2)
Type "help" for help.
postgres=# CREATE EXTENSION sqlite_fdw;
CREATE EXTENSION
使用CREATE SERVER命令定义一个新的外部服务器。本例中将外部服务器命名为sqlite_server。在OPTIONS选项中指定SQLite数据库文件名。
然后确认外部表列表中没有任何内容。
postgres=# CREATE SERVER sqlite_server FOREIGN DATA WRAPPER sqlite_fdw
postgres=# OPTIONS (database '/usr/local/pgsql/data/chinook.db');
CREATE SERVER
postgres=# \dE
Did not find any relations.
创建外部表并进行操作确认
在SQLite中，显示media_types表的列信息。
sqlite> PRAGMA table_info(media_types);
0|MediaTypeId|INTEGER|1||1
1|Name|NVARCHAR(120)|0||0
通过media_types表的列信息，在PostgreSQL中创建media_types表的外部表。
postgres=# CREATE FOREIGN TABLE media_types("MediaTypeId" bigint, "Name" text) SERVER sqlite_server;
CREATE FOREIGN TABLE
查询 media_types 数据表。显示5条记录。
postgres=# SELECT * FROM media_types;
MediaTypeId | Name
-------------+-----------------------------
1 | MPEG audio file
2 | Protected AAC audio file
3 | Protected MPEG-4 video file
4 | Purchased AAC audio file
5 | AAC audio file
(5 rows)
在 media_types 数据表中添加一条记录。通过 SELECT 进行确认，记录已经被添加。
postgres=# INSERT INTO media_types VALUES(6, 'TEXT file');
INSERT 0 1
postgres=# select * from media_types;
MediaTypeId | Name
-------------+-----------------------------
1 | MPEG audio file
2 | Protected AAC audio file
3 | Protected MPEG-4 video file
4 | Purchased AAC audio file
5 | AAC audio file
6 | TEXT file
(6 rows)
在SQLite中，我们尝试SELECT media_types表。您可以查看添加的记录。
sqlite> SELECT * FROM media_types;
1|MPEG audio file
2|Protected AAC audio file
3|Protected MPEG-4 video file
4|Purchased AAC audio file
5|AAC audio file
6|TEXT file
为了进行下一步的操作确认，请删除 media_types 表。
postgres=# DROP FOREIGN TABLE media_types;
使用IMPORT FOREIGN SCHEMA命令批量创建外部表格。
如果只有1个或2个表，我并不介意创建外部表，但如果有11个表，我就不太想创建外部表了。因此，让我们尝试使用IMPORT FOREIGN SCHEMA 来批量创建外部表。IMPORT FOREIGN SCHEMA 可以在外部服务器上创建存在的表的外部表。
postgres=# IMPORT FOREIGN SCHEMA public FROM SERVER sqlite_server INTO public;
IMPORT FOREIGN SCHEMA
在外部表清单中查看，有11个外部表已创建。
postgres=# \dE
List of relations
Schema | Name | Type | Owner
--------+----------------+---------------+----------
public | albums | foreign table | postgres
public | artists | foreign table | postgres
public | customers | foreign table | postgres
public | employees | foreign table | postgres
public | genres | foreign table | postgres
public | invoice_items | foreign table | postgres
public | invoices | foreign table | postgres
public | media_types | foreign table | postgres
public | playlist_track | foreign table | postgres
public | playlists | foreign table | postgres
public | tracks | foreign table | postgres
(11 rows)
让我们尝试使用外部表进行连接查询吧！
也可以使用外部表进行联接的搜索。在tracks表、albums表和artists表中使用内部联接进行搜索。下面这段SQL代码显示的是artistid为10的记录的Track名称、album名称和artist名称。
postgres=# SELECT "TrackId", Tracks."Name" AS Track, Albums."Title" AS Album, Artists."Name" AS Artist
postgres-# FROM Tracks
postgres-# INNER JOIN Albums ON Albums."AlbumId" = Tracks."AlbumId"
postgres-# INNER JOIN Artists ON Artists."ArtistId" = Albums."ArtistId"
postgres-# WHERE Artists."ArtistId" = 10;
TrackId | track | album | artist
---------+----------------------------------------------+--------------------------+--------------
123 | Quadrant | The Best Of Billy Cobham | Billy Cobham
124 | Snoopy's search-Red baron | The Best Of Billy Cobham | Billy Cobham
125 | Spanish moss-"A sound portrait"-Spanish moss | The Best Of Billy Cobham | Billy Cobham
126 | Moon germs | The Best Of Billy Cobham | Billy Cobham
127 | Stratus | The Best Of Billy Cobham | Billy Cobham
128 | The pleasant pheasant | The Best Of Billy Cobham | Billy Cobham
129 | Solo-Panhandler | The Best Of Billy Cobham | Billy Cobham
130 | Do what cha wanna | The Best Of Billy Cobham | Billy Cobham
(8 rows)
使用外部表，并使用内联接进行搜索没有问题。
总结
我使用SQLite FDW从PosgreSQL访问SQLite数据库。以前，必须从SQLite提取数据并加载到PostgreSQL，然后使用它。但是现在使用SQLite FDW，我可以轻松地从PostgreSQL访问。手续也很简单，请随意尝试。
如果在描述中有任何错误或者有任何疑惑，请您通过编辑请求或评论来给予反馈，将不胜感激。
