Cross database queries in pgAdmin–III

pgAdmin do not provide direct support for cross database queries, but we can achieve results similar to such queries by using two extensions provided with pgAdmin (9.3.*)

   1. Dblink
   2. Foreign data wrapper (FDW)

I found FDW easy to use as compared to Dblink, so we will be discussing FDW extension here, consider following

Example:
Suppose that we have table employee with schema employee ( eid, name, location, salary,dob, dept) Say for security reasons salary information for employees needs to be maintained at Company Headquarter Server located in Mumbai.
Now to solve the above problem statement we need to write cross database queries on server located at Mumbai and after that once we found main table employee to be accessible on the Mumbai’s server then we can create materialized view, which will be containing only salary attribute of main employee table.Following are the queries and steps to solve above problem

1. Creating FDW extension with
CREATE EXTENSION postgres_fdw;
2. Creating remote server in pgAdminIII at Mumbai’s Server
CREATE SERVER main_employee_access FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host  ‘localhost’, dbname ‘postgres’, port ‘5433’);
3. Creating foreign table in pgAdminIII at Mumbai’s Server
 CREATE FOREIGN TABLE employee ( eid integer NOT NULL, name character(30), location character(30), salary integer,dob date,dept character(30))SERVER main_employee_access;
4. Creating user mapping for remote server at Mumbai’s server
CREATE USER MAPPING FOR postgres SERVER main_employee_access OPTIONS (user ‘postgres’, password ‘pgadmin’);
5. Creating materialized view at Mumbai’s server
CREATE MATERIALIZED VIEW salary AS SELECT salary FROM employee;
Thus the above problem can be solved
 
[AMAZONPRODUCTS asin=”1449326331″]