How Can I Query Across Different PostgreSQL Databases?

Use PostgreSQL to query across databases

PostgreSQL itself does not natively support cross-database queries, as the error message indicates, but there are some workarounds available. One way is to use the postgres_fdw extension.

postgres_fdw

postgres_fdw (foreign data wrapper) allows you to connect to a table in any PostgreSQL database, regardless of its location. To use this extension:

1. Make sure your version of Postgres supports postgres_fdw (v9.3 or higher recommended).

2. Install the postgres_fdw extension in each database where you wish to perform cross-database queries.

3. Create external server and user mapping:

   <code class="language-sql"> postgres=# CREATE SERVER foreign_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'remote_host', port '5432');
    postgres=# CREATE USER MAPPING FOR CURRENT_USER SERVER foreign_server OPTIONS (user 'remote_user', password 'remote_password');</code>

4. Create external table:

   <code class="language-sql"> postgres=# CREATE FOREIGN TABLE cross_db_table SERVER foreign_server OPTIONS (table_name 'remote_table');</code>

Alternatives before version 9.3

For Postgres versions prior to 9.3, you can use a function named dblink to perform cross-database queries. Although it is maintained and distributed together with PostgreSQL, its usage is different from postgres_fdw. See the PostgreSQL documentation for more details on dblink.

The above is the detailed content of How Can I Query Across Different PostgreSQL Databases?. For more information, please follow other related articles on the PHP Chinese website!