PostgreSQL中实现跨库连接的两种方案

create extension if not exists dblink;

-- 简单查询示例（需提供目标数据库连接信息）
select *
from dblink(
  'dbname=target_db user=username password=password host=localhost port=5432',
  'select column1, column2 from target_table'
) as remote_table(column1 datatype, column2 datatype);

-- 带参数的查询示例
select *
from dblink(
  'dbname=target_db user=username password=password',
  format('select * from target_table where id = %l', 1)
) as t(column1 datatype, column2 datatype);

create extension if not exists postgres_fdw;

create server target_server
foreign data wrapper postgres_fdw
options (host 'localhost', port '5432', dbname 'target_db');

create user mapping for current_user
server target_server
options (user 'username', password 'password');

-- 手动创建外部表
create foreign table remote_table (
  column1 datatype,
  column2 datatype
)
server target_server
options (schema_name 'public', table_name 'target_table');

-- 或批量导入远程模式中的所有表
import foreign schema public
from server target_server
into current_schema;

select * from remote_table;