sqlite3でダンプしたファイルをpostgreSQLに落とし込みたい問題

Question

いつもteratailには勉強させてもらっています。
この質問はhttps://teratail.com/questions/139806 から派生しています。

プログラミング初心者で今までで一番の挫折を感じています。
また、初心者にこの作業は難しいでしょうか？
もしよろしければ、解決策など教えていただきたいです。

困っていること

psql -d 注入したいデータベース -U psqlのユーザネーム -W < ./dump.txtを実行したところエラーがでてしまいました。

実行したこと

ec2-user:/environment$ cd NBA_app
ec2-user:/environment/NBA_app (master) $ cd db
ec2-user:/environment/NBA_app/db (master) $ ls
development.sqlite3 dump.txt migrate schema.rb seeds.rb
ec2-user:/environment/NBA_app/db (master) $ psql -d dbname -U ec2-user -W < ./dump.txt

Password for user ec2-user: hogehohge
ERROR: syntax error at or near "PRAGMA"
LINE 1: PRAGMA foreign_keys=OFF;
^
BEGIN
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "schema_migrations_pkey" for table "schema_migrations"
CREATE TABLE
INSERT 0 1
INSERT 0 1
INSERT 0 1
ERROR: type "datetime" does not exist
LINE 1: ...T NULL PRIMARY KEY, "value" varchar, "created_at" datetime N...
^
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: syntax error at or near "AUTOINCREMENT"
LINE 1: CREATE TABLE "players" ("id" integer PRIMARY KEY AUTOINCREME...
^
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: syntax error at or near "AUTOINCREMENT"
LINE 1: CREATE TABLE "teams" ("id" integer PRIMARY KEY AUTOINCREMENT...
^
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: syntax error at or near "AUTOINCREMENT"
LINE 1: CREATE TABLE "posts" ("id" integer PRIMARY KEY AUTOINCREMENT...
^
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ERROR: current transaction is aborted, commands ignored until end of transaction block
ROLLBACK

psql -l　

ec2-user:~/environment/NBA_app/db (master) $ psql -l
Password:
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
dbname | ec2-user | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
ec2-user | ec2-user | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(5 rows)

環境

AWS Cloud9
gem 'rails', '> 5.2.0'
Use sqlite3 as the database for Active Record
gem 'sqlite3', group: :development
本番では
gem 'pg' , '> 1.0.0' , group: :production

Answer 1

sqlite3でダンプしたファイルをpostgreSQLに落とし込めない、、

当然です、そもそもSQLiteとPostgureSQLは同じSQLを冠していますが、
SQLという似たような仕様の命令でデータベースを操作して情報を吸い出したり格納出来るってだけで、
各データベースの細部は大きく異なります。

例えばSQLiteには5種類の型しか存在しません
PostgureSQLのデータ型はこんな感じ

ご覧のようにPostgureSQLにはNone型がありませんので、
同じISO規格部分で共通している構文のみでSQLiteから吸い出したSQL文を流し込もうとしてもNone型が一つでも含まれていれば問答無用で構文エラーになります。

何が言いたいのかというと、
RubyとPythonは同じプログラミング言語だからRubyのコードでPython動くやろ
えっ構文エラー…エンジニア人生で最も大きな挫折と言っているようなもんです。

---

どうすればいいか？ですが、ダンプという概念は諦めましょう。
そもそもSQLiteにはDate型はありませんし、TEXT型は他のDBではオーバーヘッドが大きいので「特に理由もなくTEXT型使うな」と怒られるような型です。

したがってデータを移設したいのであれば、A to Bを自分で書いて制御してやる必要があります。
RubyでSQLiteのファイルを開いてSQLite仕様のSQL文を発行してデータを配列形式で受け取ったら、
for文なりバルクインサートを利用して配列からPostgureSQL仕様のSQL文を生成して格納するのが一般的でしょう。

Comments

[abbey_wakos]

そうなんですね。詳しく回答してくださりありがとうございます。
これで次に進めれる気がしてきました。

難易度はとても高いみたいですね、、。

[miyabi-sun]

基本はエラー文をまずは読むことが大事だと思います。
一見大量の文章がわーっと襲いかかってパニックになりますが、

エラー文を読む上で重要なのは基本的に特定の1行「ERROR: syntax error at or near "AUTOINCREMENT"」だけで、
後はそれが誰それの何処でどんな時刻に発生したかという状況説明が続いているだけです。

今回の本質はPostgureSQLでテーブルを作成しようとしたときに、変な位置にオートインクリメントの記述があり（確かSQLiteだけで使える簡略的なオートインクリメント指定だったかな？）
PostgureSQLではその書き方じゃ駄目だよと言ってるだけです。
このように大量のエラー文の最初の重要な1文を取り出してちゃんと読めるようになればエンジニアとして凄く伸びると思うので頑張ってくださいね。

[abbey_wakos]

ありがとうございます。エラー文の読み方かしこまりました。とても参考になります。

RailsでSQLiteのまま公開するにはどのようにするのがベストなのか調べてもいまいち
分かりません。
分からなかったので、herokuで公開しこのような事になってしまいました。

Railsでデータを作成するとき、pgとsqliteでは方法が違うのですかね。。できればprogateで慣れているsqliteで攻めたいです

Answer 2

私は使ったことがないのですが、pgloader で、sqlite のデータベースをPostgreSQLでロードできるらしいです。
https://github.com/dimitri/pgloader

Comments

[abbey_wakos]

ありがとうございます。時間があればやってみます。

Answer 3

sqlの文法の差では？

Comments

[abbey_wakos]

回答ありがとうございます。
sqlの文法の差ですか。。具体的な解決策はありますか？

[tekka]

マイグレーション用のファイルにsqlは記述されていますか？
されているならsqlのエラー箇所を直接置換してやれば、通ると思いますよ。