PostgreSQL FDW installation and use

Time:2021-9-15

Compile and install FDW plug-ins locally

cd contrib/postgres_fdw
USE_PGX=1  make install

Install extension locally

postgres=# create extension if not exists postgres_fdw;
CREATE EXTENSION
postgres=# \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)

Configure server locally


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

Local building appearance

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

Remote table building

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

Local update, query appearance

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

Remote query

postgres=# select * from pg_fdw_test;
 id | name
----+------
  1 | test
(1 row)
postgres=# drop foreign table pg_fdw_test;
DROP FOREIGN TABLE
drop server postgres_fdwtest CASCADE;