فوت و فن های استفاده از foreign table در postgres

گاهی وقتا احتیاج پیدا میکنیم دیتا رو از جدول هایی که داخل یه دیتابیس یا یه سرور دیگه ساختیم، به دیتابیس خودمون منتقل کنیم.

یکی از بهترین راه حل ها برای ایجاد ارتباط بین دو تا دیتابیس یا دو تا سرور در Postgres، استفاده از دیتای خارجی(foreign data) هست.

توی این پست، حالتی رو بررسی می کنیم که پایگاه داده مبدا postgres یا sql server باشه. مفهوم دیتای خارجی به صورت خلاصه، این میشه که برای دیتایی که توی دیتابیس مبدا هست یه مسیر ورودی به دیتابیس مقصد درست میکنیم تا بتونه انتقال پیدا کنه(مراحل اول تا سوم که در ادامه توضیح میدم).

و بعد یه اسکیما شبیه اسکیمایی که توی مبدا داشته براش میسازیم(مرحله چهارم که در ادامه توضیح میدیم).

یه کاربرد جالب دیگه ایی جدول خارجی، که علاوه بر انتقال دیتا داره، اجرا کردن دستورات sql از سرور مقصد روی سرور مبدا هست. در واقع هر query رو میشه روی سرور مقصد اجرا کرد.

بریم مراحل کار رو با query ببینیم:

• قدم اول: ایجاد extension مربوط به دیتای خارجی:

۱- در حالتی که دیتابیس مبدا postgres باشه.

CREATE EXTENSION IF NOT EXISTS postgres_fdw;

۲- در حالتی که دیتابیس مبدا sql server باشه.

CREATE EXTENSION IF NOT EXISTS  tds_fdw;

• قدم دوم: معرفی سرور مبدا

۱- در حالتی که دیتابیس مبدا postgres باشه:

CREATE SERVER &quotSERVER_NAME&quot FOREIGN data wrapper postgres_fdw 
options ( dbname 'DATABASE_NAME', host 'HOST_NAME', port '5432' );

۲- در حالتی که دیتابیس مبدا sql server باشه:

CREATE SERVER &quotSERVER_NAME&quot FOREIGN DATA WRAPPER tds_fdw

OPTIONS (servername 'HOST_NAME', port '1433',database 'DATABASE_NAME' , msg_handler 'notice');

گزینه ی msg_handler که برابر با notice گذاشتیم در حالتی که دیتابیس مبدا SQL Server هست، بهمون کمک میکنه تا مراحل کار رو مشاهده کنیم و اگر مشکلی بود راحت تر متوجهش بشیم.پیشنهاد میکنم شما هم حتما ازش استفاده کنید.

• قدم سوم: ایجاد user mapping

توی این مرحله username و password کاربری که به دیتای مبدا دسترسی داره رو به دیتابیس مقصد معرفی میکنیم.

نکته مهم: کاربری که توی این مرحله به عنوان user mapping تعریف میشه، حتما باید دسترسی grant روی جدول داشته باشه.

CREATE USER MAPPING FOR USER_NAME SERVER &quotSERVER_NAME&quot 
options (USER 'USER_NAME', PASSWORD 'PASSWORD');

• قدم چهارم: یه اسکیما دقیقا مثل اسکیمای دیتا توی جدول مبدا درست میکنیم.

میخوایم وقتی دیتا از راه رسید یه جایی برای موندن داشته باشه! فرض کنید اسکیمای دیتامون توی مبدا به این صورته که یه id داره و یه name. پس جدول خارجی ما هم به این صورت میشه:

CREATE FOREIGN TABLE  my_foreign_data (
    id integer,
    name text)
SERVER &quotSERVER_NAME&quot OPTIONS (
    schema_name 'public',
    table_name 'source_table'
);

توی این مرحله اگر سرور مبدا SQL Server هست و در واقع داریم از tds_fdw استفاده میکنیم، میتونیم به جای اینکه اسم جدول رو به داخل table_name بدیم، از آپشن query استفاده کنیم و این طوری میتونیم روی دیتای اصلی عملیایی که میخوایم رو انجام بدیم و بعد منتقلش کنیم. مثلا من فقط میخوام رکوردهایی که id شون کمتر از ۱۰۰۰ هست رو توی جدول مقصد داشته باشم.

CREATE FOREIGN TABLE  my_foreign_data (
    id integer,
    name text)
SERVER &quotSERVER_NAME&quot OPTIONS (
    schema_name 'public',
    query 'select * from source_table where id < 1000; ',
    row_estimate_method 'showplan_all'
);

میتونیم خروجی جدول خارجی رو با دستور زیر توی یه جدول در سرور مقصد نگهداریم.

select * into tbl_my_foreign_data from my_foreign_data;

توی آپشن query (که فقط روی tds_fdw فعال هست.) علاوه بر select کردن میتونید هر دستور DDL دیگه ایی رو هم اجرا کنید. مثلا یه جدول جدید بسازید یا یه جدول رو آپدیت و حذف کنید و چون توی این حالت query ما رکوردی رو سمت جدول خارجی بر نمیگردونه، لازم نیست ستون براش تعریف کنیم.

مثال زیر جدول test رو روی سرور مبدا ایجاد میکنه:

CREATE FOREIGN TABLE my_foreign_data
() -- اینجا لازم نیست ستون ها رو تعریف کنیم
SERVER &quotSERVER_NAME&quot OPTIONS
OPTIONS (row_estimate_method 'showplan_all' ,query 'CREATE TABLE [test](
[Id] [bigint] NOT NULL);';

بر اساس مشکلی که در این لینک گزارش شده، حتما باید از row_estimate_method 'showplan_all' استفاده کنید. وگرنه query تون دوبار روی سرور اجرا میشه و بار دوم حتما خطا میده چون جدول وجود داره!

راستی برای اینکه دستوری که داخل query نوشتیم روی سرور اجرا بشه حتما باید جدول خارجی رو select کنیم.

select * from my_foreign_data;