タイトル通り。最近、SQLServerからPostgreSQLへの切り替えをやってる。CreateTableしてー、CSVにデータはいてー、Postgreにインポートしてー。みたいなのはググればいっぱい出てきますが、正直、
クッソだるい
何がだるいかとゆーと、ってか実際には上の感じの作業はしてないので、もしかしたらすんなり行くのかもしれませんが、十中八九、以下の点ではまるのが目に見えている。
- テキスト系で改行とかエスケープしないとダメなのあったらどーせ無理じゃない?
- 100を超えるテーブルのCreate文なんか流したくない
- 100を超えるテーブルのインポートなんかしたくない
- バイナリ型ってCSV吐いただけで行けるのかしら?
とゆーわけで、PostgreSQLをC#でごにょごにょするのに慣れる意味も含めてプログラムでやった。
SQLServerへの接続とかはデフォでほぼ行けるからどーでもいいとして、PostgreSQLを扱う場合は「Npgsql」を使う。インストールはNugetから。
あと、一件づつインサートとかはありえないので、.netでいうBulkCopyもしたい。
プログラムの流れは↓の感じ。
①SQLServerで移行したいDBのテーブルの定義を丸ごと取得 ②①の中でPostgre用のCreateTableを作っておく。 ③Postgreに②のCreateTableを発行 ④SQLServer側のテーブルをSelect *して、Postgreに順次BulkCopy ⑤Postgre側にIndexつける ⑥VACUUM実行
大したことはやってないけど、確実に手でやるより早い。
①のSQLServerの定義取得は↓の感じのクエリで丸ごととってくる
select X.object_id ,X.name as tablename ,cast(Y.column_id as int) as column_id ,Y.name as colname ,cast(Y.system_type_id as int) as system_type_id ,TYPE_NAME(Y.system_type_id) as typename ,cast(Y.max_length as int) as max_length ,cast(Y.precision as int) as precision ,cast(Y.scale as int) as scale ,cast(Y.is_nullable as int) as is_nullable ,cast(Y.is_identity as int) as is_identity ,cast(isnull(Z.is_primary_key,0) as int) as is_primary_key from ( select * from sys.tables where type = 'U' and name not like '%[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]%' ) as X inner join ( select * from sys.columns ) as Y on X.object_id = Y.object_id left join ( select X.object_id,Y.column_id,Z.is_primary_key from sys.columns as X inner join sys.index_columns as Y on X.object_id = Y.object_id and X.column_id = Y.column_id inner join sys.indexes as Z on Y.object_id = Z.object_id and Y.index_id = Z.index_id and X.object_id = Z.object_id where Z.is_primary_key = 1 ) as Z on Y.object_id = Z.object_id and Y.column_id = Z.column_id order by X.object_id,Y.column_id
主キーの判定がちょっとメンドー。あと、テーブル名で西暦8ケタ付きのバックアップぽいテーブルは除くようにしてみたりしてる。あと、数値系の項目ってどれがbyte型か調べるのめんどーになったので、全部強制的にIntに変換。ランボー過ぎかな?
定義が取れれば、Create文はテキトーに作れるでしょう。
そしたら、Postgre側にテーブル作成を投げる。下の感じ。公式のままだ。
//EF使ってるけど、クエリを発行したいのでEFの接続文字列を無理やり取得 string constr= this.Database.Connection.ConnectionString; using (var conn = new NpgsqlConnection(constr)) { conn.Open(); using (var cmd = new NpgsqlCommand()) { cmd.Connection = conn; try { //何回か流すようにテーブルドロップ。初回は落ちるのでtryしておく。これもランボー。 cmd.CommandText = String.Format("DROP TABLE dbo.\"{0}\";",pDef.tablename); cmd.ExecuteNonQuery(); } catch { } cmd.CommandText = pDef.createSql; cmd.ExecuteNonQuery(); } }
そしたら次はBulkCopy。ロジックは抜粋で。
using (SqlConnection cn_ = new SqlConnection(ConnectionString)) { cn_.Open(); SqlCommand command = new SqlCommand(); command.CommandTimeout = 3600;//タイムアウトの設定 command.CommandText = "select * from [テーブル名]"; command.Connection = cn_; //SQLServerにSelect投げる using (SqlDataReader sqlDr = command.ExecuteReader()) { DataTable schemaDt = sqlDr.GetSchemaTable(); //Postgreにつなぐ using (var conn = new NpgsqlConnection(pgConStr)) { conn.Open(); //Postgreのbulk using (var writer = conn.BeginBinaryImport(String.Format("COPY dbo.\"{0}\" ({1}) FROM STDIN (FORMAT BINARY)", "テーブル名","テーブルの列のリスト"))) { while (sqlDr.Read())//SQLServerのSelect結果 { writer.StartRow();//コピーする行ごとに必要っぽい for(int i=0;i< schemaDt.Columns.Count; i++) { //列毎にカキカキ writer.Write(sqlDr[i],getColDef(pDef, i)); } } } } } } //SQLServerのデータ型に応じてPostgre側の型定義を返す public NpgsqlDbType getColDef(MSDBTableDef pDef, int i) { switch (pDef.coldeflist[i].system_type_id) { case 56://int return NpgsqlDbType.Integer; case 127://bigint return NpgsqlDbType.Bigint; case 167://varchar case 231://nvarchar if(pDef.coldeflist[i].max_length < 0) return NpgsqlDbType.Text; else return NpgsqlDbType.Varchar; case 106://decimal return NpgsqlDbType.Numeric; case 61://datetime return NpgsqlDbType.Timestamp; case 165://varbinary return NpgsqlDbType.Bytea; } return NpgsqlDbType.Varchar; }
Npgsqlのバルクコピーは↓の感じで、Objectの配列として渡してもOKな気がする。
object[] dtArray = new object[schemaDt.Columns.Count]; sqlDr.GetValues(dtArray); writer.WriteRow(dtArray);
ただ、公式のこの辺に「NpgsqlDbTypeでデータの型をちゃんと指定する事を激しくお勧めする」って書いてあるのでそーした。データ型の指定はいつの時代になってもとっても重要。
あと、インデックスはデータ入れ終わってから、まとめてやった方が多分よいと思う。速度的な意味で。
最後に、対象のDBに対して「VACUUM」ってコマンドを投げてあげる。不要領域の削除とかなんかもろもろやるらし。テーブルロックとかされる場合もあるらしいので、誰か触ってる時にはやらない方がいい。
でだ、NpgsqlのCopyはいいんだけど、Writeのタイミングで書きに行ってるわけじゃないよね?なんだかいつ実行してるのかロジック見る感じでよくわからない。多分、usingしてるし、Disposeされるときにやってるのかなと愚考。個人的に明示してくれる方がわかりやすい。