Possible to perform cross-database queries with PostgreSQL?

sql

cross-database

dblink

performance-optimization

byAlex Kataev·Aug 12, 2024

Cross-database queries in PostgreSQL are realized using postgres_fdw. This allows you to connect two databases and query data seamlessly. Here's a quick query on configuring a four-step FDW setup:

--Hallelujah, let there be an extension!
CREATE EXTENSION postgres_fdw;

-- Knock knock. Who's there? It's a server called foreign_server!
CREATE SERVER foreign_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'remote_host', dbname 'remote_db');

-- Hi-Po-Ne-Mo, time for user mapping, let the magic happen 
CREATE USER MAPPING FOR CURRENT_USER SERVER foreign_server OPTIONS (user 'remote_user', password 'remote_pass');

Now, let's tie a knot between the remote and local tables:

-- Who's the fairest of them all? It's the local_foreign_table!
CREATE FOREIGN TABLE local_foreign_table (
  -- Mirror mirror on the wall, map the columns for us all!
) SERVER foreign_server OPTIONS (table_name 'remote_table');

Voila! You can now query data from a remote table as if it’s a local table.

-- Abracadabra! Show us the magic data!
SELECT * FROM local_foreign_table;

Congratulations, you have successfully enabled FDW for cross-database querying in PostgreSQL.

Step-by-step guide for alternate scenarios

1. Logical data partitioning using schemas

Schemas offer an appealing alternative for separating data logically within the same database, making cross-database access much simpler and can be a faster alternative to FDWs when data is on the same server:

 -- This schema ain't no ordinary schema. It's a shiny new_schema!
CREATE SCHEMA new_schema; 
SET search_path TO new_schema, public;

2. The not-so-ancient art of dblink

For PostgreSQL versions prior to 9.3, dblink is your go-to choice. However, you may need the postgresql-contrib package. But fret not, just do as instructed below:

-- Let's break the ice with a little extension...
CREATE EXTENSION dblink;

-- Roll up your sleeves and pull the rabbit out of the hat!
SELECT * FROM dblink('dbname=remote_db', 'SELECT id, name FROM users') AS t(id integer, name text);

And yes, you can perform LEFT JOINs using dblink:

-- Let's join hands, er... tables!
SELECT local.id, local.value, remote.name
FROM local_table local
LEFT JOIN dblink('remote_conn', 'SELECT id, name FROM remote_table') AS remote(id integer, name text)
ON local.id = remote.id;

3. Is your PostgreSQL version up to scratch?

Ensure your PostgreSQL version supports the methods discussed. Just for reference, version 9.3 and above supports postgres_fdw.

Master guide to complex scenarios & solutions

1. Playing nice with others: Linking to other data sources

You aren't restricted to PostgreSQL. Using foreign data wrappers (FDWs), you can also connect to non-PostgreSQL databases. For instance, to access a MS SQL Server, you'd want to look into TDS_FDW.

2. Keeping it smooth: Performance optimization

In a world of data, every millisecond saved is a victory. Optimize your queries to keep your data transfer swift and use indices effectively. Got a lot of dblink overhead? Consider persistent connections.

3. Staying in the clear: Error handling

The database world is akin to a treasure cave guarded by permissions and role privileges. Ensuring a secure FDW setup and a well-configured firewall keeps your treasure — the data — safe and sound.

4. The wisdom of client-side merging

Sometimes it's strategic to query separately and merge results on the client-side. This could grant you better performance for complex operations or help minimize loads on your database servers.

explain-codes / Sql / Possible to perform cross-database queries with PostgreSQL?

Linked

How to select data of a table from another database in SQL Server?



How to list table foreign keys



How to use (install) dblink in PostgreSQL?



Add Foreign Key relationship between two Databases



Querying data by joining two tables in two databases on different servers

