关于postgresql:postgresql-fdw-安装使用

本地编译装置fdw插件

cd contrib/postgres_fdwUSE_PGX=1  make install

本地装置extension

postgres=# create extension if not exists postgres_fdw;CREATE EXTENSIONpostgres=# \dx                               List of installed extensions     Name     | Version |   Schema   |                    Description--------------+---------+------------+---------------------------------------------------- plpgsql      | 1.0     | pg_catalog | PL/pgSQL procedural language postgres_fdw | 1.0     | public     | foreign-data wrapper for remote PostgreSQL servers(2 rows)

本地配置server

postgres=# create server postgres_fdwtest  FOREIGN data wrapper postgres_fdw  OPTIONS(host '127.0.0.1', port '5432', dbname 'postgres');CREATE SERVERpostgres=# create user mapping for postgres server postgres_fdwtest options(user 'postgres',password 'postgres');CREATE USER MAPPING

本地建表面

postgres=# create foreign table pg_fdw_test(id int,name text) server postgres_fdwtest options (table_name 'pg_fdw_test');CREATE FOREIGN TABLE

远端建表

postgres=# create  table pg_fdw_test(id int,name text);CREATE TABLE

本地更新，查问表面

postgres=# insert into pg_fdw_test values(1,'test');INSERT 0 1postgres=# select * from pg_fdw_test; id | name----+------  1 | test(1 rows)

远端查问

postgres=# select * from pg_fdw_test; id | name----+------  1 | test(1 row)

postgres=# drop foreign table pg_fdw_test;DROP FOREIGN TABLEdrop server postgres_fdwtest CASCADE;