Database

postgres_fdw

The extension enables Postgres to query tables and views on a remote Postgres server.

Enable the extension

  1. Go to the Database page in the Dashboard.
  2. Click on Extensions in the sidebar.
  3. Search for "postgres_fdw" and enable the extension.

Create a connection to another database

1

Create a foreign server

Define the remote database address


_10
create server "<foreign_server_name>"
_10
foreign data wrapper postgres_fdw
_10
options (
_10
host '<host>',
_10
port '<port>',
_10
dbname '<dbname>'
_10
);

2

Create a server mapping

Set the user credentials for the remote server


_10
create user mapping for "<dbname>"
_10
server "<foreign_server_name>"
_10
options (
_10
user '<db_user>',
_10
password '<password>'
_10
);

3

Import tables

Import tables from the foreign database

Example: Import all tables from a schema


_10
import foreign schema "<foreign_schema>"
_10
from server "<foreign_server>"
_10
into "<host_schema>";

Example: Import specific tables


_10
import foreign schema "<foreign_schema>"
_10
limit to (
_10
"<table_name1>",
_10
"<table_name2>"
_10
)
_10
from server "<foreign_server>"
_10
into "<host_schema>";

4

Query foreign table


_10
select * 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.


_10
alter server "<foreign_server_name>"
_10
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.


_10
alter server "<foreign_server_name>"
_10
options (batch_size '1000');

Extensions

Lists shared extensions. Without them, queries involving unlisted extension functions or operators may fail or omit references.


_10
alter server "<foreign_server_name>"
_10
options (extensions 'vector, postgis');

For more server options, check the extension's official documentation

Resources