postgres_fdw
The extension enables Postgres to query tables and views on a remote Postgres server.
Enable the extension
- Go to the Database page in the Dashboard.
- Click on Extensions in the sidebar.
- Search for "postgres_fdw" and enable the extension.
Create a connection to another database
1
Create a foreign server
Define the remote database address
1234567create server "<foreign_server_name>" foreign data wrapper postgres_fdw options ( host '<host>', port '<port>', dbname '<dbname>' );
2
Create a server mapping
Set the user credentials for the remote server
123456create user mapping for "<dbname>"server "<foreign_server_name>"options ( user '<db_user>', password '<password>');
3
Import tables
Import tables from the foreign database
Example: Import all tables from a schema
123import foreign schema "<foreign_schema>"from server "<foreign_server>"into "<host_schema>";
Example: Import specific tables
1234567import foreign schema "<foreign_schema>"limit to ( "<table_name1>", "<table_name2>")from server "<foreign_server>"into "<host_schema>";
4
Query foreign table
1select * from "<foreign_table>"
Configuring execution options
Fetch_size
Maximum rows fetched per operation. For example, fetching 200 rows with fetch_size
set to 100 requires 2 requests.
12alter server "<foreign_server_name>"options (fetch_size '10000');
Batch_size
Maximum rows inserted per cycle. For example, inserting 200 rows with batch_size
set to 100 requires 2 requests.
12alter server "<foreign_server_name>"options (batch_size '1000');
Extensions
Lists shared extensions. Without them, queries involving unlisted extension functions or operators may fail or omit references.
12alter server "<foreign_server_name>"options (extensions 'vector, postgis');
For more server options, check the extension's official documentation
Resources
- Official
postgres_fdw
documentation