postgresql 创建外部表

-- 1. 启用 扩展
CREATE EXTENSION if not exists postgres_fdw;

foreign_server_database_name 

-- 2. 创建外部服务器
drop server if exists foreign_server_database_name cascade;
CREATE SERVER foreign_server_database_name
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host '172.16.1.1', dbname 'database_name');

-- 3. 建立用户映射
CREATE USER MAPPING if not exists FOR postgres
    SERVER foreign_server_database_name
    OPTIONS (user 'username', password 'password')
;

-- 4. 创建schema
drop schema if exists schemaName CASCADE;
create schema schemaName;

-- 5. 在Schema下批量创建 foreign table
IMPORT FOREIGN SCHEMA public
    FROM SERVER foreign_server_database_name INTO schemaName;

-- 6. 创建外部表
CREATE FOREIGN TABLE country (
    country_id int NOT NULL,
    country varchar(50) NOT NULL,
    last_update timestamp NOT NULL
)
SERVER foreign_server_database_name
OPTIONS (schema_name 'public', table_name 'country');