EXTENSION dblink and postgres_fdw All Versions

Examples

• 2

  Extention FDW
  2

  FDW is an implimentation of dblink it is more helpful, so to use it:

  1-Create an extention:

  CREATE EXTENSION postgres_fdw;
  

  2-Create SERVER:

  CREATE SERVER name_srv FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'hostname', 
  dbname 'bd_name', port '5432');
  

  3-Create user mapping for postgres server

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

  4-Create foreign table:

  CREATE FOREIGN TABLE table_foreign (id INTEGER, code character varying) 
  SERVER name_srv OPTIONS(schema_name 'schema', table_name 'table');
  

  5-use this foreign table like it is in your database:

  SELECT * FROM table_foreign;
  

  created Sep 22 '16 at 15:19

  
  edit share discuss request improvement

  edit move example delete example
• 2

  Foreign Data Wrapper
  2

  To access complete schema of server db instead of single table. Follow below steps:

  1. Create EXTENSION :

      CREATE EXTENSION postgres_fdw;
  

  2. Create SERVER :

      CREATE SERVER server_name FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'host_ip', 
      dbname 'db_name', port 'port_number');
  

  3. Create USER MAPPING:

      CREATE USER MAPPING FOR CURRENT_USER
      SERVER server_name
      OPTIONS (user 'user_name', password 'password');
  

  4. Create new schema to access schema of server DB:

     CREATE SCHEMA schema_name;
  

  5. Import server schema:

       IMPORT FOREIGN SCHEMA schema_name_to_import_from_remote_db
       FROM SERVER server_name
       INTO schema_name;
  

  6. Access any table of server schema:

      SELECT * FROM schema_name.table_name; 
  

  This can be used to access multiple schema of remote DB.

  edited Mar 23 at 13:39

  

  
  edit share discuss request improvement

  edit move example delete example
• 1

  Extention dblink
  1

  dblink EXTENSION is a technique to connect another database and make operation of this database so to do that you need:

  1-Create a dblink extention:

  CREATE EXTENSION dblink;
  

  2-Make your operation:

  For exemple Select some attribute from another table in another database:

  SELECT * FROM 
  dblink ('dbname = bd_distance port = 5432 host = 10.6.6.6 user = username 
  password = passw@rd', 'SELECT id, code FROM schema.table') 
  AS newTable(id INTEGER, code character varying);
  

  edited Jan 30 at 12:59

  
  edit share discuss request improvement

  edit move example delete example