rembrembdocs
← supabaseoriginal source ↗

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

1create server "<foreign_server_name>"2    foreign data wrapper postgres_fdw3    options (4        host '<host>',5        port '<port>',6        dbname '<dbname>'7    );

2

Create a server mapping

Set the user credentials for the remote server

1create user mapping for "<dbname>"2server "<foreign_server_name>"3options (4    user '<db_user>',5    password '<password>'6);

3

Import tables

Import tables from the foreign database

Example: Import all tables from a schema

1import foreign schema "<foreign_schema>"2from server "<foreign_server>"3into "<host_schema>";

Example: Import specific tables

1import foreign schema "<foreign_schema>"2limit to (3    "<table_name1>",4    "<table_name2>"5)6from server "<foreign_server>"7into "<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.

1alter server "<foreign_server_name>"2options (fetch_size '10000');

Batch_size#

Maximum rows inserted per cycle. For example, inserting 200 rows with batch_size set to 100 requires 2 requests.

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

Extensions#

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

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

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

Resources#