MySQLからPostgreSQLにデータを移行させる際、pgloaderを使うことでスムーズにデータ移行できたので、今回はpgloaderを使ってMySQLからPostgreSQLに移行させる手順を紹介します。 検証していて自分が一番詰まったのが、PostgreSQLとの接続がうまくいかなかったところで、ここが環境によって大きく異なる部分のため、PostgreSQL側をdocker環境にすることで、今回は誰でもpgloaderを使って移行まで1周できるような手順を作成しました。 ぜひ参考にしてみてください。

検証データとしては MySQLとPostgreSQLの速度比較 の国税庁の「法人番号公表サイト」の基本3情報の全件データを利用しました。

MySQLの準備

MySQLとPostgreSQLの速度比較 こちらの記事に従って MariaDBのインストール テストデータ、テーブルの作成（MariaDBの項目のみ）、CSVのインポート を終わらせてください。

PostgreSQLの準備

PostgreSQLのバージョンは、現時点で最新の16を使って構築しようと思います。

# 最新イメージをダウンロード
docker pull postgres:16
# コンテナを起動
docker run --name postgres -p 5432:5432 -e POSTGRES_HOST_AUTH_METHOD=trust -d postgres:16
# postgresqlのクライアントがなければインストール
sudo apt install -y postgresql-client
# sudoなどをつけずこのコマンドで接続できればOK
psql -h 127.0.0.1 -U postgres
# 移行先のデータベースを作成しておく
create database enterprises;

pgloaderの準備

pgloaderのインストールです。

# ubuntu系なら↓でインストール
$ sudo apt-get install pgloader

↓のような設定ファイルを書いて移行する方法がいいです。

cat my.load
LOAD DATABASE
     FROM mysql://root@localhost:3306/enterprises
     INTO pgsql://postgres@localhost:5432/enterprises

 WITH include drop, create tables, create indexes, reset sequences,
      workers = 8, concurrency = 1,
      multiple readers per thread, rows per range = 50000, prefetch rows = 10000

  SET PostgreSQL PARAMETERS
      maintenance_work_mem to '128MB',
      work_mem to '12MB',
      search_path to 'sakila, public, "$user"'

  SET MySQL PARAMETERS
      net_read_timeout  = '120',
      net_write_timeout = '120'

 CAST type bigint when (= precision 20) to bigserial drop typemod,
      type date drop not null drop default using zero-dates-to-null,
      -- type tinyint to boolean using tinyint-to-boolean,
      type year to integer

 MATERIALIZE VIEWS film_list, staff_list
;

細かい設定値はこちら

特定のテーブルのみ移行する／しないなど柔軟に設定できます。

my.loadファイルを指定してpgloaderを実行します

pgloader my.load

終了するとlogが表示されます。

2024-02-07T06:25:27.951000Z LOG report summary reset
             table name       read   imported     errors      total time
-----------------------  ---------  ---------  ---------  --------------
        fetch meta data          2          2          0          0.159s 
         Create Schemas          0          0          0          0.001s 
       Create SQL Types          0          0          0          0.005s 
          Create tables          2          2          0          0.009s 
         Set Table OIDs          1          1          0          0.003s 
 Create MatViews Tables          0          0          0          0.000s 
-----------------------  ---------  ---------  ---------  --------------
enterprises.enterprises    5427398    5427398          0       6m14.021s 
-----------------------  ---------  ---------  ---------  --------------
COPY Threads Completion          8          8          0       6m14.022s 
 Index Build Completion          1          1          0          4.411s 
         Create Indexes          1          1          0          4.392s 
        Reset Sequences          1          1          0          0.029s 
           Primary Keys          1          1          0          0.002s 
    Create Foreign Keys          0          0          0          0.000s 
        Create Triggers          0          0          0          0.001s 
       Install Comments          5          0          5          0.003s 
-----------------------  ---------  ---------  ---------  --------------
      Total import time    5427398    5427398          0       6m18.954s

無事移行できていれば成功です！

$ psql -h 127.0.0.1 -U postgres enterprises
psql (10.23 (Ubuntu 10.23-0ubuntu0.18.04.2), server 16.1 (Debian 16.1-1.pgdg120+1))
Type "help" for help.

enterprises=# select count(sequencenumber) from enterprises.enterprises;

  count  
---------
 5427398
(1 row)

カラムが意図しない型に変換されていたりするので、テーブル構造は全部チェックし直したほうがいいです。