Postgres Cross-Server/Cross-Database Querying

To demonstrate cross database quering we will create two databases "PlaygroundLive" & "PlaygroundStaging".Both these database can be on same server or different postgresql server.In current case they are on same server.

On both databases create below table

create table TestTable

(

  id int primary key,

  name varchar(500)

)

on staging add three rows

insert into TestTable(id,name) values(1,'sagar'),(2,'sangram'),(3,'sachin');

on live add two rows

insert into TestTable(id,name) values(1,'sagar'),(2,'sangram');

Now create extensions called dblink & foreign data wrapper

CREATE EXTENSION postgres_fdw;

CREATE EXTENSION dblink;

Check if extension got created or not by checking newly added procedures/functions related to new extension db_link.

SELECT pg_namespace.nspname, pg_proc.proname FROM pg_proc, pg_namespace WHERE pg_proc.pronamespace=pg_namespace.oid AND pg_proc.proname LIKE '%dblink%';

On our staging Database connect to live database,check if connectivity succeed or not,query below give OK on success

SELECT dblink_connect('host=localhost user=postgres password=sangram dbname=PlaygroundLive');

CREATE FOREIGN DATA WRAPPER dbrnd VALIDATOR postgresql_fdw_validator;

Creating server connection:

Here we have both database on same postgres sql server yet we have to follow same process we need to provide server details & server IP & database to connect for.

CREATE SERVER demodbrnd FOREIGN DATA WRAPPER postgres_fdw OPTIONS (hostaddr '127.0.0.1', dbname 'PlaygroundLive');

you can check if server creation succeeded by running below command

SELECT * FROM pg_foreign_server; 

CREATE USER MAPPING FOR postgres SERVER demodbrnd OPTIONS (user 'postgres',
password 'sangram');

here postgres user on local server is mapped to postgres user on remote server

Connect to Server:

SELECT dblink_connect('demodbrnd');

GRANT USAGE ON FOREIGN SERVER demodbrnd TO postgres;

Running Cross Database Queries:

now from staging database we can run queries to live database

1) Select:

SELECT * FROM public.dblink('demodbrnd','select id,name from public.TestTable') AS DATA(id INTEGER,name CHARACTER VARYING);

it will have two records in output.

2) INSERT:

SELECT dblink_connect('demodbrnd');

select * from dblink_exec('INSERT INTO public.TestTable values(3,''sachin'')')

One more record will get added to TestTable on Live database.Now TestTable on both database are identical.

3) SELECT INTO:

SELECT * INTO temp_TestTable

FROM public.dblink('demodbrnd','select id,name from public.TestTable') 

AS DATA(id INTEGER,name CHARACTER VARYING);

Here temp_TestTable is created on Staging database will get all three 

records from TestTable in Live database.

4) Parametrized SELECT:

--using parametrized query with prepare

PREPARE migrate_data (integer) AS

INSERT INTO temp_TestTable

SELECT id, name

FROM dblink('demodbrnd', 'select id,name from public.TestTable')

AS t(id integer, name varchar)

WHERE id > $1;

EXECUTE migrate_data(2);

execution of prepare statement will add one more record to temp_

TestTable so there will 4 records now.

--check one more record got inserted

select * from temp_TestTable

TO delete Foreign data wrapper follow following sequence of drop 

queries as object stacked one above other.

Create Schema called “ForeignSchema” in staging database

Now import all tables from remote database public schema into staging 

atabase “ForeignSchema” as follows.

IMPORT FOREIGN SCHEMA public
FROM SERVER demodbrnd INTO "ForeignSchema";

Now remove 1st record from staging database table so that we are sure that 

both table differ as follows

delete from public.TestTable where id=1;

you can check that he our “TestTable” from live server has been imported 

into local database in schmema “ ForeignSchema” by running command 

select * from "ForeignSchema".TestTable;

Now we can run insert into statement also:
insert into "ForeignSchema".TestTable
select id+3,name from public.TestTable;

DROPPING Foreign Data WRAPPER: 

drop USER MAPPING FOR postgres SERVER demodbrnd ;

drop SERVER demodbrnd;

drop FOREIGN DATA WRAPPER dbrnd;