Contents
SQLite にエクスポートするテーブル
↓ 旧ブログ(Seesaa)の記事リスト。ここから日付・タイトルなど# \d kenpg_seesaa_list Table "public.kenpg_seesaa_list" +-----------+------+-----------+ | Column | Type | Modifiers | +-----------+------+-----------+ | ymd | date | not null |<= | url | text | not null | | title | text | not null |<= | tags | text | not null |<= | html_file | text | not null | | html | text | not null | | body | text | |<= +-----------+------+-----------+ Indexes: "kenpg_seesaa_list_pkey" PRIMARY KEY, btree (ymd) "kenpg_seesaa_list_url_idx" UNIQUE, btree (url)
手順まとめ
OGR_FDWまとめると ↓ うち
-- 1) create sqlite database $ sqlite3 r:/seesaa_ogrfdw.db sqlite> CREATE TABLE articles (ymd, title, tags, body2); -- 2) create a foreign table on psql # create server seesaa_ogrfdw foreign data wrapper ogr_fdw options ( datasource 'r:/seesaa_ogrfdw.db', format 'SQLite'); # import foreign schema ogr_all from server seesaa_ogrfdw into public; -- 3) export data to the foreign table # insert into public.articles (ymd, title, tags, body2) select ymd,concat (title),concat (tags),concat (body2) from kenpg_seesaa_list;
上の(1)SQLite
psql での実際
まず外部テーブルを入れるサーバの作成。SQLite# create server seesaa_ogrfdw foreign data wrapper ogr_fdw options ( datasource 'r:/seesaa_ogrfdw.db', format 'SQLite'); # \des+ List of foreign servers +---------------+----------+----------------------+-------------------+------+---------+-----------------------------------------------------+-------------+ | Name | Owner | Foreign-data wrapper | Access privileges | Type | Version | FDW Options | Description | +---------------+----------+----------------------+-------------------+------+---------+-----------------------------------------------------+-------------+ | seesaa_ogrfdw | postgres | ogr_fdw | | | | (datasource 'r:/seesaa_ogrfdw.db', format 'SQLite') | | +---------------+----------+----------------------+-------------------+------+---------+-----------------------------------------------------+-------------+ (1 row)
続いて
# import foreign schema ogr_all from server seesaa_ogrfdw into public; # \det+ List of foreign tables +--------+----------+---------------+--------------------+-------------+ | Schema | Table | Server | FDW Options | Description | +--------+----------+---------------+--------------------+-------------+ | public | articles | seesaa_ogrfdw | (layer 'articles') | | +--------+----------+---------------+--------------------+-------------+ (1 row)
以上でエクスポート準備終わり。INSERT
# \d kenpg_seesaa_list Table "public.kenpg_seesaa_list" +-----------+------+-----------+ | Column | Type | Modifiers | +-----------+------+-----------+ | ymd | date | not null | | url | text | not null | | title | text | not null | | tags | text | not null | | html_file | text | not null | | html | text | not null | | body | text | | +-----------+------+-----------+ # \d articles Foreign table "public.articles" +--------+-------------------+-----------+-------------+ | Column | Type | Modifiers | FDW Options | +--------+-------------------+-----------+-------------+ | fid | bigint | | | | ymd | character varying | | | | title | character varying | | | | tags | character varying | | | | body2 | character varying | | | +--------+-------------------+-----------+-------------+ Server: seesaa_ogrfdw FDW Options: (layer 'articles')
↓ 外部テーブルが空の状態から、INSERT
# select * from articles; +-----+-----+-------+------+-------+ | fid | ymd | title | tags | body2 | +-----+-----+-------+------+-------+ +-----+-----+-------+------+-------+ (0 rows) # insert into articles (ymd, title, tags, body2) select ymd,concat (title),concat (tags),concat (body2) from kenpg_seesaa_list; INSERT 0 693 # select count(*) from articles;
結果確認:pg_dump 経由でエクスポートしたものと比較
↓ 書き込んだテーブルの行数を$ sqlite3 r:/seesaa_ogrfdw.db sqlite> select count(*) from articles; 693 $ ls -l r:/*.db -rwxrwx---+ 1 Administrators None 3993600 Aug 30 08:46 seesaa.db -rwxrwx---+ 1 Administrator None 3993600 Sep 4 20:04 seesaa_ogrfdw.db<= $ diff --binary seesaa.db seesaa_ogrfdw.db Binary files seesaa.db and seesaa_ogrfdw.db differ
テーブルの中身も、日本語やローマ数字を含め問題なく前と同様にエクスポートされてました。で、既に旧ブログ記事の検索で使ってます。前の「INSERT
既存テーブルの TEXT 列をそのまま INSERT するとサーバが落ちる
これは残念な、かつ不可解な点。既存テーブルにある# insert into articles (ymd, title, tags, body2) select ymd, title, tags, body2 from kenpg_seesaa_list;server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Succeeded.
↓ こちらがサーバのログ。詳しくは今後調べます。
LOG: server process (PID 4848) was terminated by exception 0xC0000005 DETAIL: Failed process was running: insert into articles (ymd, title, tags, body2) select ymd, title, tags, body2 from kenpg_seesaa_list; HINT: See C include file "ntstatus.h" for a description of the hexadecimal value. LOG: terminating any other active server processes WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. ...
TEXT
とは言え、SQLite