postgresql数据库创建外部表

环境：

1、postgresql版本9.6；

2、部署系统ubuntu版本20；

3、navicat premium版本15；

数据库操作步骤：

## 1、登陆数据库服务器，并切换到对应数据库
sudo -u postgres psql
\c prod_mydb
## 2、删除已存在的user mapping，server，foreign data wrapper
drop user mapping for dev_user server server_remote_rudy_01;
drop server server_remote_rudy_01;
drop foreign data wrapper if exists postgres_fdw;
## 3、重新安装postgres_fdw
CREATE EXTENSION postgres_fdw;
## 4、查询是否安装成功
## 注意pg_foreign_data_wrapper是单数或者复数
select * from pg_foreign_data_wrapper;
## 5、创建外部服务，注意每个数据库都要创建相同的
CREATE SERVER "server_remote_rudy_01" FOREIGN DATA WRAPPER "postgres_fdw" OPTIONS ( host '172.30.0.3',
 port '5432',
 dbname 'prod_remotedb');

ALTER SERVER "server_remote_rudy_01" OWNER TO "dev_user";

CREATE USER MAPPING FOR "dev_user" SERVER "server_remote_rudy_01" OPTIONS ( user 'dev_user',
 password 'mypassword');

## 6、创建外部表
CREATE FOREIGN TABLE "public"."sys_sessions" (
  "id" varchar(255) COLLATE "pg_catalog"."default" NOT NULL,
  "session_id" varchar(255) COLLATE "pg_catalog"."default",
  "account_id" varchar(255) COLLATE "pg_catalog"."default",
  "account" varchar(255) COLLATE "pg_catalog"."default",
  "login_time" timestamp(6),
  "logout_time" timestamp(6),
  "create_time" timestamp(6),
  "update_time" timestamp(6),
  "delete_flag" varchar(255) COLLATE "pg_catalog"."default"
)
SERVER "server_remote_rudy_01"
OPTIONS ("schema_name" 'public', "table_name" 'sys_sessions')
;

ALTER FOREIGN TABLE "public"."sys_sessions" 
  OWNER TO "dev_user";

## 7、不同数据库之间可以使用外部表啦！！！