EXTENSION dblink and postgres fdw:
*Foreign Data Wrapper
Foreign Data Wrapper
suggest changeTo 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.
Found a mistake? Have a question or improvement idea?
Let me know.