postgres_fdw的使用步骤

一、源码编译安装postgres_fdw扩展

由于安装postgres时没有编译安装扩展，所以要编译安装

点击查看代码

[root@cmc ~]# cd postgresql-12.10/contrib/
[root@cmc contrib]# make all && make install

[root@cmc extension]# cd /pgbase/share/postgresql/extension/
[root@cmc extension]# ll postgres_fdw*
-rw-r--r-- 1 root root 507 Jun 12 09:41 postgres_fdw--1.0.sql
-rw-r--r-- 1 root root 172 Jun 12 09:41 postgres_fdw.control

二、创建扩展

点击查看代码

postgres=# create extension postgres_fdw;
ERROR:  no schema has been selected to create in

postgres=# select current_user;
 current_user
--------------
 postgres
(1 row)

postgres=# create schema postgres authorization postgres;
CREATE SCHEMA
postgres=#
postgres=#
postgres=# create extension postgres_fdw;
CREATE EXTENSION

三、授权

点击查看代码

postgres=# grant usage on foreign data wrapper postgres_fdw to postgres;
GRANT

四、创建扩展服务

点击查看代码

postgres=# create server pg_server foreign data wrapper postgres_fdw options(host '10.0.0.11',port '5434',dbname 'testdb');
CREATE SERVER
postgres=#
postgres=# create user mapping for postgres server pg_server options(user 'postgres',password 'postgres');
CREATE USER MAPPING
postgres=#
postgres=# create foreign table t1(id int) server pg_server options(schema_name 'postgres',table_name 'fdw1');
CREATE FOREIGN TABLE

五、使用

点击查看代码

postgres=# select * from t1;
ERROR:  could not connect to server "pg_server"
DETAIL:  FATAL:  no pg_hba.conf entry for host "10.0.0.10", user "postgres", database "postgres", SSL off


在pg_hba.conf添加以下条目
# IPv4 local connections:
host    all             all             10.0.0.10/24            md5


postgres=# select * from t1;
 id
----
  1
  2
(2 rows)

五、使用完删除扩展服务

点击查看代码

postgres=# drop server pg_server cascade;
NOTICE:  drop cascades to 2 other objects
DETAIL:  drop cascades to user mapping for postgres on server pg_server
drop cascades to foreign table t1


postgres=# drop user mapping if exists for postgres server pg_server;
DROP USER MAPPING


postgres=# drop foreign table t1;
DROP FOREIGN TABLE