postgresql EXTENSION dblink and postgres_fdw Foreign Data Wrapper
Example
To access complete schema of server db instead of single table. Follow below steps:
- Create EXTENSION :
CREATE EXTENSION postgres_fdw;
- Create SERVER :
CREATE SERVER server_name FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'host_ip',
dbname 'db_name', port 'port_number');
- Create USER MAPPING:
CREATE USER MAPPING FOR CURRENT_USER
SERVER server_name
OPTIONS (user 'user_name', password 'password');
- Create new schema to access schema of server DB:
CREATE SCHEMA schema_name;
- Import server schema:
IMPORT FOREIGN SCHEMA schema_name_to_import_from_remote_db
FROM SERVER server_name
INTO schema_name;
- Access any table of server schema:
SELECT * FROM schema_name.table_name;
This can be used to access multiple schema of remote DB.
