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 not 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.

Apr 25, 2025 - 04:04

Here I want to share to configure postgres_fdw

the postgres_fdw extension in PostgreSQL is included in the contrib package and I will not 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

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.