支持Azure DB for postgres服务器中的跨数据库查询

Question

Azure DB for Postgres Server是否支持跨数据库查询？Azure SQL数据库不支持跨数据库查询，其中Azure SQL托管实例和弹性池支持跨DB查询！

Answer 1

如果你在Azure Postgres服务器中禁用了“拒绝公共网络访问”，并在“连接安全”下启用了“允许访问其他Azure资源”，你可以使用dblink扩展：

CREATE EXTENSION dblink;

SELECT dblink_connect('[connection_alias]','host=[azure_pg_hostname] port=5432
dbname=[other_db_name] user=[username]@[azure_pg_hostname] password=[password] sslmode=require');

SELECT * FROM dblink('[connection_alias]', 'select [field1], [field2] from public.[remote_table_name]') 
AS t([field1] [data type], [field2] [data type]);

或外部数据包装器扩展：

CREATE EXTENSION postgres_fdw;

CREATE SERVER [connection_alias]
FOREIGN DATA WRAPPER postgres_fdw  
OPTIONS (host '[azure_pg_hostname]', dbname '[other_db_name]', port '5432');

CREATE USER MAPPING FOR ([user_name] | CURRENT_USER)
SERVER [connection_alias]
OPTIONS (user '[user_name]@[azure_pg_hostname]', password '[password]');

CREATE FOREIGN TABLE [local_table_name] ( 
[field1] [data type], [field2] [data type]
)
SERVER [connection_alias]
OPTIONS (schema_name 'public', table_name '[remote_table_name]');

SELECT * from [local_table_name];