2024.01.16 Tech

本番環境のデータ移管ももう怖くない？dblinkを使って手軽なデータ移管の方法

こんにちは、エンジニアの小宮です。今回はリモートデータベースのデータを直接SQLで操作することができるdblinkをご紹介します。dblinkを使用することで手軽にリモートデータベースのデータをローカルデータベースに反映できたりとなにかと便利です。

どういう用途でdblinkを使ったのか
dblinkを使ってみよう！
活用例
まとめ

どういう用途でdblinkを使ったのか

業務の一環としてデータベースを分割する機会がありました。新しく作ったデータベースにもともとのデータベースからデータを持ってくる必要があり、そのとき使ったpostgresqlのエクステンションがdblinkです。dblinkを活用することで、本番環境に対してもdumpファイルなどを作成せずにSQLを打つだけで手軽にデータ移管することができます。

dblinkを使ってみよう！

dblinkが使えるかのチェック!

まずはローカルデータベースでdblinkが使える状態であるのかをチェックしましょう。以下のSQLを実行します。

SELECT nspname AS schema, extname FROM pg_extension e JOIN pg_namespace n ON n.oid = e.extnamespace;

 schema | extname
--------+---------
 public | dblink

これでdblinkが結果のスキーマにあれば使える状態ですが、もしなければ次のSQLでdblinkを読み込みましょう。

CREATE extension dblink;

コネクションの確立

リモートデータベースへの接続を確立するにはdblink_connect()を使います。

SELECT dblink_connect('prd','host=ホスト port=ポート dbname=データベース user=ユーザ password=パスワード');

 dblink_connect
----------------
 OK

・第一引数にはコネクション名
・第二引数にはリモートデータベースへの接続のための情報

を渡します。ドメインの場合はhostですが、IPの場合はhostaddrを使用してください。結果としてOKが返ってきたらリモートデータベースへの永続的な接続は完了です。

リモートからのデータの取得

dblink()を使うことでリモートデータベースにSQLを投げて必要なデータを取得し、ローカルデータベース上で仮想的なテーブルとして利用することができます。第一引数ではコネクション名を、第二引数ではSQLを指定します。

SELECT 
  name, age
FROM 
  dblink('prd', 'SELECT name, age FROM users`) 
AS 
　prd(name varchar, age integer);

この例では、コネクション名prdでコネクション確立されているリモートデータベースで第二引数のSQLを実行し、その結果をローカルデータベースで取得しています。dblink()は戻り値としてレコード型を返すので、列名と型名を明示的に記述する必要があります。

コネクションの切断

利用が終わったらdblink_disconnect()でコネクションを切断しておきましょう。

SELECT dblink_disconnect('コネクション名');

dblink_disconnect
-----------------
 OK

活用例

リモートのDBと同じレコードをローカルに保存したい(INSERT文)

今回、自分がdblinkを活用した用途がこれです。dblinkを使うことでリモートからローカルに直接データをコピーすることができます！

INSERT INTO
 users 
SELECT 
 *
FROM 
 dblink('prd', 'SELECT id,name,age FROM users') 
AS 
 prd(id bigint,name varchar, age integer);

注意するべき点その1は、アスタリスクを利用したデータ移管の場合、元のテーブルと移管先のテーブルのカラムの順番を一致させなければいけない点です。もし、カラムの順番が違う場合は、セレクト文でデータの取ってくる順番を整形してあげないといけません。これはdblink()の戻り値がレコード型のためです。

例)
移管元のカラムの順番がid,name,ageなのに、
移管先のカラムの順番がid,age,nameの場合

INSERT INTO
 users 
 /*明示的にid,age,name とこちらで順番を合わせてもいいですがカラム数が増えるととても大変な作業です...*/
SELECT  
 *
FROM 
 /*下のSELECT文でカラムの順番を移管先の順番にあわせる必要あり*/
 dblink('prd', 'SELECT id,age,name FROM users') 
AS 
 /*もちろんここのカラムの順番も移管先の順番にあわせましょう*/
 prd(id bigint,age integer,name varchar);

注意するべき点その2はオートインクリメントで増えているカラムがある場合は手動で調整する必要がある点です。リモートのデータベースを使用していたときと同等の動きを新しいデータベースに切り替えた後でもさせたいときは、一意な値であるシークエンスも一致させなければなりません。dblinkでデータ移管した場合、手動でシークエンスを一致させてあげないといけないため注意が必要です。

主キーをidカラムとしたときの調整の手順をみてみましょう。

まずは、nextval()でシークエンスの値を確認します。

SELECT nextval('users_id_seq');

次に以下のSQLでテーブル中の最大のキーの値が取得できます。

SELECT MAX(id) FROM table;

dblinkでデータを移管しただけではこれらの値にズレが生じているはずです。

以下のようにsetval()を用いることで正しいシークエンスに修正することができます。

SELECT setval('users_id_seq', (SELECT MAX(id) FROM users));

ローカルのデータをリモートの内容に更新したい!(UPDATE文)

こちらはローカル環境でのテストデータ作成等に活用できます。

UPDATE 
  users set age = prd.age
FROM 
  dblink('prd', 'SELECT name,age FROM users') 
AS
  prd(name varchar, age integer)
WHERE
  users.name = prd.name;

dblinkのユースケース

今回、自分は活用例のリモートのDBと同じレコードをローカルに保存したい(INSERT文)に示したようにdblinkを用いてデータ移管を行いましたが、注意するべき点その2など事前には想定していなかった処理も必要だったため、人によってはdumpとrestoreを使えば？と考える方もいらっしゃると思います。

そこで以下にdblinkが力を発揮するユースケースを載せておきます。

・データ元とコピー先のID(プライマリーキー)が違う場合
　オートインクリメントにより、ローカルとリモートでデータは論理的には同じだが、IDが微妙に違うケース
・テーブルの一部のカラムのみコピーしたい場合
・外部キーなどの制約により単純なrestoreが面倒な場合
・データのコピーや移転ではなく、単純にDB間を跨いてデータを処理したい場合

これらの場合は、dumpとrestoreを用いるよりもdblinkの方が手軽に実行できると思うのでぜひ、そのシチュエーションがきたときにdblinkをご検討ください！