pgLoaderを使用してMySQLデータベースをPostgreSQLに移行する方法
序章
PostgreSQL は、「Postgres」とも呼ばれ、オープンソースのリレーショナルデータベース管理システム(RDBMS)です。 近年、人気が大幅に増加しており、多くの開発者や企業が他のデータベースソリューションからPostgresにデータを移行しています。
特にあるデータベース管理システムから別のデータベース管理システムに移行する場合、データベースを移行する可能性は恐ろしいものになる可能性があります。 pgLoader は、PostgreSQLへの移行プロセスを簡素化することを目的としたオープンソースのデータベース移行ツールです。 MySQLやSQLiteなどのいくつかのファイルタイプとRBDMSからPostgreSQLへの移行をサポートしています。
このチュートリアルでは、pgLoaderをインストールし、それを使用してリモートMySQLデータベースをSSL接続を介してPostgreSQLに移行する方法について説明します。 チュートリアルの終わり近くに、pgLoaderが役立つ可能性のあるいくつかの異なる移行シナリオについても簡単に触れます。
前提条件
このチュートリアルを完了するには、次のものが必要です。
- それぞれがUbuntu18.04を実行している2台のサーバーへのアクセス。 両方のサーバーにファイアウォールと、sudo権限が設定されたroot以外のユーザーが必要です。 これらを設定するには、 Ubuntu18.04の初期サーバー設定ガイドに従ってください。
- MySQLがサーバーの1つにインストールされました。 これを設定するには、 Ubuntu 18.04にMySQLをインストールする方法に関するガイドのステップ1、2、および3に従ってください。 ここにリンクされているすべての前提条件のチュートリアルを完了するには、MySQLインストールのステップ3 で説明されているように、 rootMySQLユーザーをパスワードで認証するように構成する必要があることに注意してください。ガイド。
- 他のサーバーにインストールされているPostgreSQL。 これを設定するには、ガイド Ubuntu18.04にPostgreSQLをインストールして使用する方法のステップ1を完了します。
- MySQLサーバーも、暗号化された接続を受け入れるように構成する必要があります。 これを設定するには、オプションのステップ6 を含め、 Ubuntu18.04でMySQLのSSL/TLSを構成する方法に関するチュートリアルのすべてのステップを完了します。 このガイドに従うときは、必ずPostgreSQLサーバーをMySQLクライアントマシンとして使用してください。pgLoaderを使用してデータを移行するには、PostgresマシンからMySQLサーバーに接続できる必要があります。 。
このガイド全体を通して、MySQLをインストールしたサーバーは「 MySQLサーバー」と呼ばれ、このマシンで実行する必要のあるコマンドは、次のように青い背景で表示されることに注意してください。 :
-
同様に、このガイドでは他のサーバーを「 PostgreSQL」または「Postgres」サーバーと呼び、そのマシンで実行する必要のあるコマンドはすべて赤い背景で表示されます。
-
混乱を避けるために、このチュートリアルに従うときは、これらを覚えておいてください。
ステップ1—(オプション)MySQLでサンプルデータベースとテーブルを作成する
このステップでは、テストデータベースを作成し、ダミーデータを入力するプロセスについて説明します。 このテストケースでpgLoaderの使用を練習することをお勧めしますが、移行するデータベースがすでにある場合は、次のステップに進むことができます。
MySQLサーバーでMySQLプロンプトを開くことから始めます。
- mysql -u root -p
root MySQLユーザーのパスワードを入力すると、MySQLプロンプトが表示されます。
そこから、次のコマンドを実行して新しいデータベースを作成します。 データベースには任意の名前を付けることができますが、このガイドでは名前を付けます source_db
:
- CREATE DATABASE source_db;
次に、このデータベースに切り替えます。 USE
指図:
- USE source_db;
OutputDatabase changed
このデータベース内で、次のコマンドを使用してサンプルテーブルを作成します。 ここでは、このテーブルに名前を付けます sample_table
ただし、別の名前を付けてください。
- CREATE TABLE sample_table (
- employee_id INT PRIMARY KEY,
- first_name VARCHAR(50),
- last_name VARCHAR(50),
- start_date DATE,
- salary VARCHAR(50)
- );
次に、次のコマンドを使用して、このテーブルにサンプルの従業員データを入力します。
- INSERT INTO sample_table (employee_id, first_name, last_name, start_date, salary)
- VALUES (1, 'Elizabeth', 'Cotten', '2007-11-11', '$105433.18'),
- (2, 'Yanka', 'Dyagileva', '2017-10-30', '$107540.67'),
- (3, 'Lee', 'Dorsey', '2013-06-04', '$118024.04'),
- (4, 'Kasey', 'Chambers', '2010-08-18', '$116456.98'),
- (5, 'Bram', 'Tchaikovsky', '2018-09-16', '$61989.50');
これに続いて、MySQLプロンプトを閉じることができます。
- exit
ダミーデータがロードされたサンプルデータベースができたので、次のステップに進み、PostgreSQLサーバーにpgLoaderをインストールします。
ステップ2—pgLoaderをインストールする
pgLoaderは、さまざまなソースからPostgreSQLデータベースにデータをロードできるプログラムです。 PostgreSQLのCOPYコマンドを使用して、ソースデータベースまたはファイル(カンマ区切り値(CSV)ファイルなど)からターゲットPostgreSQLデータベースにデータをコピーします。
pgLoaderはデフォルトのUbuntuAPTリポジトリから入手でき、 apt
指図。 ただし、このガイドでは、pgLoaderを利用します。 useSSL
オプション、SSL接続を介したMySQLからの移行を可能にする機能。 この機能は、バージョン 3.5.1 以降のpgLoaderでのみ使用できますが、デフォルトのUbuntuリポジトリからインストールすると、この記事の執筆時点で、バージョン3.4.1がインストールされます。 したがって、このガイドでは、プロジェクトのGitHubリポジトリのソースコードを使用して最新バージョンのpgLoaderをインストールする方法の概要を説明します。
pgLoaderをインストールする前に、その依存関係をインストールする必要があります。 最近行っていない場合は、Postgresサーバーのパッケージインデックスを更新してください。
- sudo apt update
次に、次のパッケージをインストールします。
sbcl
: CommonLispコンパイラunzip
:のデアーカイバ.zip
ファイルlibsqlite3-dev
:SQLite3の開発ファイルのコレクションgawk
:パターンスキャンおよび処理言語である「GNUawk」の略curl
:URLからデータを転送するためのコマンドラインツールmake
:パッケージのコンパイルを管理するためのユーティリティfreetds-dev
:MSSQLおよびSybaseデータベース用のクライアントライブラリlibzip-dev
:zipアーカイブを読み取り、作成、および変更するためのライブラリ
これらの依存関係をインストールするには、次のコマンドを使用します。
- sudo apt install sbcl unzip libsqlite3-dev gawk curl make freetds-dev libzip-dev
プロンプトが表示されたら、を押してこれらのパッケージをインストールすることを確認します ENTER
.
次に、pgLoader GitHubプロジェクトのリリースページに移動し、最新のリリースを見つけます。 このガイドでは、この記事の執筆時点で最新のリリースバージョン3.6.2を使用します。 Assets メニューまで下にスクロールして、 tar.gz
ソースコードというラベルの付いたファイル。 次に、リンクを次の場所に貼り付けます curl
コマンド、強調表示されたURLを置き換えます:
- curl -fsSLO https://github.com/dimitri/pgloader/archive/v3.6.2.tar.gz
cURL は、データの転送に使用される多くのオペレーティングシステムで使用できるコマンドラインツールです。 渡されたURLに保存されているデータをすべて読み取り、その内容をシステムの出力に出力します。
これ curl
コマンドには、 O
オプション。ファイルの内容をソースファイルと同じ名前のローカルファイルに出力します— v3.6.2.tar.gz
—サーバーの標準出力の代わりに。 このコマンドにはオプションも含まれていることに注意してください -fsSL
これは一緒になって、本質的にcURLにサイレントに失敗するように指示します。 これは、何らかの理由でcURLがGitHubに接続できない場合、結果のエラーコードをローカルファイルに出力しないことを意味します。
ダウンロード後 curl
、次のコマンドでtarballを抽出します。
- tar xvf v3.6.2.tar.gz
これにより、サーバー上に多数の新しいディレクトリとファイルが作成されます。 新しいpgLoader親ディレクトリに移動します。
- cd pgloader-3.6.2/
次に、 make
をコンパイルするユーティリティ pgloader
バイナリ:
- make pgloader
このコマンドがビルドするのに数分かかる場合があります pgloader
バイナリ。
完了したら、バイナリファイルをに移動します /usr/local/bin
ディレクトリ、Ubuntuが実行可能ファイルを探す場所:
- sudo mv ./build/bin/pgloader /usr/local/bin/
次のように、バージョンを確認することで、pgLoaderが正しくインストールされたことをテストできます。
- pgloader --version
Outputpgloader version "3.6.2"
compiled with SBCL 1.4.5.debian
これでpgLoaderがインストールされましたが、移行を開始する前に、PostgreSQLインスタンスとMySQLインスタンスの両方にいくつかの構成変更を加える必要があります。 まず、PostgreSQLサーバーに焦点を当てます。
ステップ3—PostgreSQLの役割とデータベースを作成する
The pgloader
コマンドは、ファイルまたはデータベースから直接ソースデータをコピーし、それをPostgreSQLデータベースに挿入することで機能します。 このため、PostgresデータベースにアクセスできるLinuxユーザーとしてpgLoaderを実行するか、loadコマンドで適切な権限を持つPostgreSQLロールを指定する必要があります。
PostgreSQLは、rolesを使用してデータベースアクセスを管理します。 ロールの構成方法に応じて、データベースユーザーまたはデータベースユーザーのグループのいずれかと考えることができます。 ほとんどのRDBMSでは、次のコマンドを使用してユーザーを作成します。 CREATE USER
SQLコマンド。 ただし、Postgresには次のような便利なスクリプトがインストールされています。 createuser
. このスクリプトは、 CREATE USER
コマンドラインから直接実行できるSQLコマンド。
注: PostgreSQLでは、パスワードではなく、デフォルトで IdentificationProtocolまたはidentの認証方法を使用して、データベースユーザーとして認証します。 これには、PostgreSQLがクライアントのUbuntuユーザー名を取得し、それを許可されたPostgresデータベースのユーザー名として使用することが含まれます。 これにより、多くの場合、セキュリティが強化されますが、外部プログラムをデータベースの1つに接続したい場合にも問題が発生する可能性があります。
pgLoaderは、identメソッドで認証するロールを介してPostgresデータベースにデータをロードできます。ただし、そのロールが、発行するLinuxユーザープロファイルと同じ名前を共有している場合に限ります。 pgloader
指図。 ただし、このプロセスをできるだけ明確にするために、このチュートリアルでは、identメソッドではなくパスワードで認証する別のPostgreSQLロールの設定について説明します。
Postgresサーバーで次のコマンドを実行して、新しい役割を作成します。 に注意してください -P
フラグ、 createuser
新しい役割のパスワードの入力を求めるプロンプトを表示するには:
- sudo -u postgres createuser --interactive -P
最初にプロンプトが表示される場合があります sudo
パスワード。 次に、スクリプトにより、新しい役割の名前を入力するように求められます。 このガイドでは、このロールをpgloader_pgと呼びます。
OutputEnter name of role to add: pgloader_pg
以下のこと、 createuser
この役割のパスワードを入力して確認するように求められます。 手順5で移行を実行するために必要になるため、このパスワードを必ずメモしてください。
OutputEnter password for new role:
Enter it again:
最後に、スクリプトは、新しい役割をスーパーユーザーとして分類する必要があるかどうかを尋ねます。 PostgreSQLでは、スーパーユーザーの役割でデータベースに接続すると、ログインする権利を除いて、データベースのすべての権限チェックを回避できます。 このため、スーパーユーザー権限を軽視しないでください。 PostgreSQLのドキュメントでは、データベースのほとんどの作業を非スーパーユーザーの役割として行うことを推奨しています。 ただし、pgLoaderはデータにアクセスしてテーブルにロードするために幅広い権限を必要とするため、この新しいロールのスーパーユーザー権限を安全に付与できます。 次のように入力してください y
次に押す ENTER
:
Output. . .
Shall the new role be a superuser? (y/n) y
PostgreSQLには、コマンドラインからデータベースを作成できる便利なスクリプトがもう1つ付属しています。 pgLoaderには、ソースデータをロードできるターゲットデータベースも必要なので、次のコマンドを実行して作成します。 このデータベースに名前を付けます new_db
ただし、必要に応じて変更してください。
- sudo -u postgres createdb new_db
エラーがない場合、このコマンドは出力なしで完了します。
専用のPostgreSQLユーザーと、MySQLデータをロードできる空のデータベースができたので、移行を実行する前に、さらにいくつかの変更を加える必要があります。 ソースデータベースにアクセスできる専用のMySQLユーザーを作成し、クライアント側の証明書をUbuntuの信頼できる証明書ストアに追加する必要があります。
ステップ4—MySQLで専用ユーザーを作成して証明書を管理する
スヌーパーからデータを保護することは、データベース管理者の仕事の最も重要な部分の1つです。 あるマシンから別のマシンにデータを移行すると、悪意のある攻撃者が、暗号化されていない場合にネットワーク接続を通過するパケットをsniffする機会が開かれます。 このステップでは、pgLoaderがSSL接続を介して移行を実行するために使用する専用のMySQLユーザーを作成します。
MySQLプロンプトを開くことから始めます。
- mysql -u root -p
MySQLプロンプトから、次を使用します CREATE USER
新しいMySQLユーザーを作成するコマンド。 このユーザーにpgloader_myという名前を付けます。 このユーザーはPostgreSQLサーバーからのみMySQLにアクセスするため、必ず置き換えてください your_postgres_server_ip
PostgreSQLサーバーのパブリックIPアドレスを使用します。 さらに、交換してください password
安全なパスワードまたはパスフレーズを使用:
- CREATE USER 'pgloader_my'@'your_postgres_server_ip' IDENTIFIED BY 'password' REQUIRE SSL;
に注意してください REQUIRE SSL
このコマンドの最後にある句。 これにより、 pgloader_my ユーザーは、安全なSSL接続を介してのみデータベースにアクセスできるようになります。
次に、pgloader_myユーザーにターゲットデータベースとそのすべてのテーブルへのアクセスを許可します。 ここでは、オプションの手順1で作成したデータベースを指定しますが、移行する独自のデータベースがある場合は、代わりにその名前を使用します。 source_db
:
- GRANT ALL ON source_db.* TO 'pgloader_my'@'your_postgresql_server_ip';
次に、を実行します FLUSH PRIVILEGES
権限の変更を有効にして、付与テーブルを再ロードするコマンド:
- FLUSH PRIVILEGES;
この後、MySQLプロンプトを閉じることができます。
- exit
次に、Postgresサーバーに戻り、新しいpgloader_myユーザーとしてMySQLサーバーにログインしてみます。 MySQL用のSSL/TLSの構成に関する前提条件ガイドに従った場合は、すでに mysql-client
PostgreSQLサーバーにインストールされ、次のコマンドで接続できるはずです。
- mysql -u pgloader_my -p -h your_mysql_server_ip
コマンドが成功すると、MySQLプロンプトが表示されます。
-
pgloader_my ユーザーが正常に接続できることを確認したら、先に進んでプロンプトを閉じます。
- exit
この時点で、Postgresマシンからソースデータベースにアクセスできる専用のMySQLユーザーがいます。 ただし、SSLを使用してMySQLデータベースを移行しようとすると、失敗します。
これは、pgLoaderがMySQLの構成ファイルを読み取ることができないため、前提条件の SSL/TLS構成でPostgreSQLサーバーにコピーしたCA証明書またはクライアント証明書を探す場所がわからないためです。ガイド。 ただし、SSL要件を無視するのではなく、MySQLへの接続にSSLが必要な場合、pgLoaderは信頼できる証明書を使用する必要があります。 したがって、この問題を追加することで解決できます。 ca.pem
と client-cert.pem
Ubuntuの信頼できる証明書ストアへのファイル。
これを行うには、 ca.pem
と client-cert.pem
にファイル /usr/local/share/ca-certificates/
ディレクトリ。 これらのファイルの名前も変更して、 .crt
ファイル拡張子。 名前を変更しないと、システムはこれらの新しい証明書を追加したことを認識できません。
- sudo cp ~/client-ssl/ca.pem /usr/local/share/ca-certificates/ca.pem.crt
- sudo cp ~/client-ssl/client-cert.pem /usr/local/share/ca-certificates/client-cert.pem.crt
これに続いて、 update-ca-certificates
指図。 このプログラムは、内の証明書を探します /usr/local/share/ca-certificates
、新しいものを追加します /etc/ssl/certs/
ディレクトリ、および信頼できるSSL証明書のリストを生成します— ca-certificates.crt
—の内容に基づく /etc/ssl/certs/
ディレクトリ:
- sudo update-ca-certificates
OutputUpdating certificates in /etc/ssl/certs...
2 added, 0 removed; done.
Running hooks in /etc/ca-certificates/update.d...
done.
これで、MySQLデータベースをPostgreSQLに移行する準備が整いました。
ステップ5—データの移行
PostgreSQLサーバーからMySQLサーバーへのリモートアクセスを構成したので、移行を開始する準備が整いました。
注:データの整合性に影響を与える可能性のあるアクションを実行する前に、データベースをバックアップすることが重要です。 ただし、pgLoaderを使用して移行を実行する場合、データを削除または変換しないため、これは必要ありません。 コピーするだけです。
とはいえ、慎重に感じていて、移行する前にデータをバックアップしたい場合は、 mysqldump
効用。 詳細については、公式MySQLドキュメントを参照してください。
pgLoaderを使用すると、ユーザーは1つのコマンドでデータベース全体を移行できます。 MySQLデータベースから別のサーバー上のPostgreSQLデータベースに移行する場合、コマンドの構文は次のようになります。
- pgloader mysql://mysql_username:password@mysql_server_ip_/source_database_name?option_1=value&option_n=value postgresql://postgresql_role_name:password@postgresql_server_ip/target_database_name?option_1=value&option_n=value
これには、 pgloader
コマンドと2つの接続文字列。1つ目はソースデータベース用、2つ目はターゲットデータベース用です。 これらの接続文字列は両方とも、接続文字列が指すDBMSのタイプを宣言することから始まり、データベースにアクセスできるユーザー名とパスワード(コロンで区切る)、データベースがインストールされているサーバーのホストアドレス、 pgLoaderが対象とするデータベースの名前、およびpgLoaderの動作に影響を与えるさまざまなオプション。
このチュートリアルの前半で定義したパラメーターを使用すると、次の構造のコマンドを使用してMySQLデータベースを移行できます。 強調表示された値を置き換えて、独自の設定に合わせてください。
- pgloader mysql://pgloader_my:mysql_password@mysql_server_ip/source_db?useSSL=true postgresql://pgloader_pg:postgresql_password@localhost/new_db
このコマンドには、 useSSL
MySQL接続文字列のオプション。 このオプションをに設定することにより true
、pgLoaderはSSLを介してMySQLに接続します。 安全な接続のみを受け入れるようにMySQLサーバーを構成したため、これが必要です。
このコマンドが成功すると、移行がどのように行われたかを説明する出力テーブルが表示されます。
Output. . .
table name errors rows bytes total time
----------------------- --------- --------- --------- --------------
fetch meta data 0 2 0.111s
Create Schemas 0 0 0.001s
Create SQL Types 0 0 0.005s
Create tables 0 2 0.017s
Set Table OIDs 0 1 0.010s
----------------------- --------- --------- --------- --------------
source_db.sample_table 0 5 0.2 kB 0.048s
----------------------- --------- --------- --------- --------------
COPY Threads Completion 0 4 0.052s
Index Build Completion 0 1 0.011s
Create Indexes 0 1 0.006s
Reset Sequences 0 0 0.014s
Primary Keys 0 1 0.001s
Create Foreign Keys 0 0 0.000s
Create Triggers 0 0 0.000s
Install Comments 0 0 0.000s
----------------------- --------- --------- --------- --------------
Total import time ✓ 5 0.2 kB 0.084s
データが正しく移行されたことを確認するには、PostgreSQLプロンプトを開きます。
- sudo -i -u postgres psql
そこから、データをロードしたデータベースに接続します。
- \c new_db
次に、次のクエリを実行して、移行されたデータがPostgreSQLデータベースに保存されているかどうかをテストします。
- SELECT * FROM source_db.sample_table;
注:注意してください FROM
このクエリの句で、 sample_table
内で開催 source_db
スキーマ:
- . . . FROM source_db.sample_table;
これは修飾名と呼ばれます。 さらに進んで、データベースの名前とスキーマおよびテーブルの名前を含めることにより、完全修飾名を指定できます。
- . . . FROM new_db.source_db.sample_table;
PostgreSQLデータベースでクエリを実行する場合、テーブルがデフォルト内に保持されている場合は、これを特定する必要はありません。 public
スキーマ。 ここでそうしなければならない理由は、pgLoaderがPostgresにデータをロードするときに、元のデータベースにちなんで名付けられた新しいスキーマを作成してターゲットにするためです。この場合は、 source_db
. これは、MySQLからPostgreSQLへの移行に対するpgLoaderのデフォルトの動作です。 ただし、ロードファイルを使用して、テーブルのスキーマを次のように変更するようにpgLoaderに指示することができます。public
データの読み込みが完了したら。 これを行う方法の例については、次のステップを参照してください。
データが実際に正しくロードされた場合、クエリの出力に次の表が表示されます。
Output employee_id | first_name | last_name | start_date | salary
-------------+------------+-------------+------------+------------
1 | Elizabeth | Cotten | 2007-11-11 | $105433.18
2 | Yanka | Dyagileva | 2017-10-30 | $107540.67
3 | Lee | Dorsey | 2013-06-04 | $118024.04
4 | Kasey | Chambers | 2010-08-18 | $116456.98
5 | Bram | Tchaikovsky | 2018-09-16 | $61989.50
(5 rows)
Postgresプロンプトを閉じるには、次のコマンドを実行します。
- \q
ネットワークを介してMySQLデータベースを移行し、それをPostgreSQLデータベースにロードする方法について説明したので、pgLoaderが役立つ他のいくつかの一般的な移行シナリオについて説明します。
ステップ6—他の移行オプションを調べる
pgLoaderは非常に柔軟なツールであり、さまざまな状況で役立ちます。 ここでは、pgLoaderを使用してMySQLデータベースをPostgreSQLに移行する他のいくつかの方法を簡単に見ていきます。
pgLoaderロードファイルを使用した移行
pgLoaderのコンテキストでは、ロードファイルまたはコマンドファイルは、pgLoaderに移行の実行方法を指示するファイルです。 このファイルには、pgLoaderの動作に影響を与えるコマンドとオプションを含めることができるため、データをPostgreSQLにロードする方法をより細かく制御でき、複雑な移行を実行できます。
pgLoaderのドキュメントには、これらのファイルを使用および拡張して多数の移行タイプをサポートする方法に関する包括的な手順が記載されているため、ここでは比較的基本的な例を紹介します。 手順5で実行したのと同じ移行を実行しますが、 ALTER SCHEMA
を変更するコマンド new_db
からのデータベースのスキーマ source_db
に public
.
まず、お好みのテキストエディタを使用してPostgresサーバーに新しいロードファイルを作成します。
- nano pgload_test.load
次に、次のコンテンツを追加します。強調表示された値を更新して、独自の構成に合わせてください。
LOAD DATABASE
FROM mysql://pgloader_my:mysql_password@mysql_server_ip/source_db?useSSL=true
INTO pgsql://pgloader_pg:postgresql_password@localhost/new_db
WITH include drop, create tables
ALTER SCHEMA 'source_db' RENAME TO 'public'
;
これらの各句の機能は次のとおりです。
LOAD DATABASE
:この行は、ファイルやデータアーカイブではなく、別のデータベースからデータをロードするようにpgLoaderに指示します。FROM
:この句は、ソースデータベースを指定します。 この場合、ステップ1で作成したMySQLデータベースの接続文字列を指します。INTO
:同様に、この行は、pgLoaderがデータをロードする必要があるPostgreSQLデータベースを指定します。WITH
:この句を使用すると、pgLoaderの特定の動作を定義できます。 あなたはの完全なリストを見つけることができますWITH
MySQL移行と互換性のあるオプションここ。 この例では、2つのオプションのみが含まれています。include drop
:このオプションを使用すると、pgLoaderは、ソースMySQLデータベースにも表示されるターゲットPostgreSQLデータベース内のすべてのテーブルを削除します。 データを既存のPostgreSQLデータベースに移行するときにこのオプションを使用する場合は、データが失われないようにデータベース全体をバックアップする必要があります。create tables
:このオプションは、MySQLデータベースに保持されているメタデータに基づいてターゲットPostgreSQLデータベースに新しいテーブルを作成するようにpgLoaderに指示します。 反対のオプションの場合、create no tables
が使用される場合、移行前にターゲットテーブルがターゲットPostgresデータベースにすでに存在している必要があります。
ALTER SCHEMA
:次のWITH
句を使用すると、このような特定のSQLコマンドを追加して、pgLoaderに追加のアクションを実行するように指示できます。 ここでは、pgLoaderに新しいPostgresデータベースのスキーマをから変更するように指示します。source_db
にpublic
、ただし、スキーマを作成した後でのみ。 このようなコマンドは、他の句内にネストすることもできることに注意してください。BEFORE LOAD DO
—移行プロセスの特定のポイントでこれらのコマンドを実行するようにpgLoaderに指示します。
これは、ロードファイルに含めてpgLoaderの動作を変更できることを示す例です。 ロードファイルに追加できる句の完全なリストとその機能は、公式のpgLoaderドキュメントにあります。
このコンテンツの追加が完了したら、ロードファイルを保存して閉じます。 これを使用するには、ファイルの名前を引数として含めます。 pgloader
指図:
- pgloader pgload_test.load
移行が成功したことをテストするには、Postgresプロンプトを開きます。
- sudo -u postgres psql
次に、データベースに接続します。
- \c new_db
そして、次のクエリを実行します。
- SELECT * FROM sample_table;
Output employee_id | first_name | last_name | start_date | salary
-------------+------------+-------------+------------+------------
1 | Elizabeth | Cotten | 2007-11-11 | $105433.18
2 | Yanka | Dyagileva | 2017-10-30 | $107540.67
3 | Lee | Dorsey | 2013-06-04 | $118024.04
4 | Kasey | Chambers | 2010-08-18 | $116456.98
5 | Bram | Tchaikovsky | 2018-09-16 | $61989.50
(5 rows)
この出力は、pgLoaderがデータを正常に移行したこと、および ALTER SCHEMA
ロードファイルに追加したコマンドは、指定する必要がなかったため、期待どおりに機能しました。 source_db
データを表示するためのクエリのスキーマ。
ロードファイルを使用して、あるデータベースに保持されているデータを別のマシンにある別のデータベースに移行する場合でも、移行を成功させるには、関連するネットワークとファイアウォールのルールを調整する必要があります。
MySQLデータベースをPostgreSQLにローカルで移行する
pgLoaderを使用して、MySQLデータベースを同じマシンに格納されているPostgreSQLデータベースに移行できます。 必要なのは、 rootMySQLユーザーにアクセスできるLinuxユーザープロファイルから移行コマンドを実行することだけです。
- pgloader mysql://root@localhost/source_db pgsql://sammy:postgresql_password@localhost/target_db
このようにローカル移行を実行すると、MySQLのデフォルトのネットワーク構成やシステムのファイアウォールルールを変更する必要がなくなります。
CSVファイルからの移行
CSVファイルのデータを含むPostgreSQLデータベースをロードすることもできます。
次の名前のデータのCSVファイルがあると仮定します load.csv
、Postgresデータベースにロードするコマンドは次のようになります。
- pgloader load.csv pgsql://sammy:password@localhost/target_db
CSV形式は完全に標準化されていないため、この方法でCSVファイルから直接データを読み込むときに問題が発生する可能性があります。 幸い、 pgLoaderのコマンドラインオプションにさまざまなオプションを含めるか、ロードファイルでそれらを指定することにより、不規則性を修正できます。 詳細については、件名のpgLoaderドキュメントを参照してください。
マネージドPostgreSQLデータベースへの移行
自己管理データベースから管理PostgreSQLデータベースへの移行を実行することも可能です。 この種の移行がどのように見えるかを説明するために、MySQLサーバーとDigitalOceanマネージドPostgreSQLデータベースを使用します。 ステップ1で作成したサンプルデータベースも使用しますが、そのステップをスキップして、移行する独自のデータベースがある場合は、代わりにそのデータベースを指定できます。
注: DigitalOcean管理対象データベースのセットアップ方法については、管理対象データベースクイックスタートガイドを参照してください。
この移行では、pgLoaderは必要ありません。 useSSL
これはリモートMySQLデータベースでのみ機能し、ローカルMySQLデータベースからこの移行を実行するためです。 ただし、 sslmode=require
DigitalOcean Managed PostgreSQLデータベースをロードして接続するときのオプション。これにより、データが保護されたままになります。
使用していないので useSSL
今回はご利用いただけます apt
pgLoaderをインストールするには postgresql-client
パッケージ。これにより、MySQLサーバーからマネージドPostgreSQLデータベースにアクセスできるようになります。
- sudo apt install pgloader postgresql-client
その後、実行することができます pgloader
データベースを移行するコマンド。 これを行うには、管理対象データベースの接続文字列が必要です。
DigitalOceanマネージドデータベースの場合、クラウドコントロールパネルから接続文字列をコピーできます。 まず、左側のサイドバーメニューでデータベースをクリックし、データの移行先のデータベースを選択します。 次に、接続の詳細セクションまで下にスクロールします。 ドロップダウンメニューをクリックし、接続文字列を選択します。 次に、コピーボタンをクリックして文字列をクリップボードにコピーし、次の移行コマンドに貼り付けます。ここに示すPostgreSQL接続文字列の例を置き換えます。 これにより、MySQLデータベースがに移行されます defaultdb
doadmin PostgreSQLロールとしてのPostgreSQLデータベース:
- pgloader mysql://root:password@localhost/source_db postgres://doadmin:password@db_host/defaultdb?sslmode=require
これに続いて、同じ接続文字列を引数として使用できます。 psql
管理対象のPostgreSQLデータベースに接続し、移行が成功したことを確認するには、次の手順に従います。
- psql postgres://doadmin:password@db_host/defaultdb?sslmode=require
次に、次のクエリを実行して、pgLoaderがデータを正しく移行したことを確認します。
- SELECT * FROM source_db.sample_table;
Output employee_id | first_name | last_name | start_date | salary
-------------+------------+-------------+------------+------------
1 | Elizabeth | Cotten | 2007-11-11 | $105433.18
2 | Yanka | Dyagileva | 2017-10-30 | $107540.67
3 | Lee | Dorsey | 2013-06-04 | $118024.04
4 | Kasey | Chambers | 2010-08-18 | $116456.98
5 | Bram | Tchaikovsky | 2018-09-16 | $61989.50
(5 rows)
これにより、pgLoaderがMySQLデータベースをマネージドPostgreSQLインスタンスに正常に移行したことが確認されます。
結論
pgLoaderは、単一のコマンドでデータベースの移行を実行できる柔軟なツールです。 いくつかの構成の調整により、安全なSSL / TLS接続を使用して、データベース全体を1つの物理マシンから別の物理マシンに移行できます。 このチュートリアルに従うことで、pgLoaderの機能と潜在的なユースケースをより明確に理解できるようになることを願っています。
データをPostgreSQLに移行した後、次のチュートリアルが役立つ場合があります。