using postgres_fdw

Here I want to share to configure postgres_fdw

the postgres_fdw extension in PostgreSQL is included in the contrib package and I will write how to install contrib here.

I have pg1 - 10.10.1.9 and pg2 - 10.10.1.10

on pg1

\c testdb;

CREATE EXTENSION IF NOT EXISTS postgres_fdw;

CREATE SERVER tonewdatabase
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (
  host '10.10.1.10',
  dbname 'mydb',
  port '5432'
);

tonewdatabase = name Foreign-data wrapper
host = hostname or ip destination
dbname = database name destination
port = database port destionation

use below command to list FOREIGN DATA WRAPPER

postgres=# \des
             List of foreign servers
     Name      |  Owner   | Foreign-data wrapper
---------------+----------+----------------------
 tonewdatabase | postgres | postgres_fdw

now we create user mapping

CREATE USER MAPPING FOR andi
SERVER tonewdatabase 
OPTIONS (user 'joe', password 'joe123);

andi = username on pg1
joe = username on pg2
joe123= joe's password on pg2

login to pg1 as joe user and connect to mydb then run below query.

Select t1 on pg2 from pg1

IMPORT FOREIGN SCHEMA public
LIMIT TO (t1)
FROM SERVER tonewdatabase 
INTO public;


select * from public.t1 ;

t1 = tablename on pg1
the IMPORT FOREIGN SCHEMA (or manually creating a FOREIGN TABLE) does not copy any rows locally. It simply creates table definitions on pg1 that point at the real data on pg2. Every time you run

below how to create new table

CREATE FOREIGN TABLE public.t1(
  id INT,
  amount NUMERIC
)
SERVER tonewdatabase 
OPTIONS (schema_name 'public', table_name 't2');

this statement will create table on pg2 from pg1.

Import ALL tables from pg2's 'public' schema:

IMPORT FOREIGN SCHEMA public
FROM SERVER tonewdatabase
INTO public;

Or just a few selected tables:

IMPORT FOREIGN SCHEMA public
LIMIT TO (table1, table2, table3)
FROM SERVER tonewdatabase
INTO public;