Est-il possible d'effectuer des requêtes sur plusieurs bases de données avec PostgreSQL ?

Question

Je suppose que la réponse est "non" d'après le message d'erreur ci-dessous (et ce résultat Google ), mais existe-t-il un moyen d'effectuer une requête entre bases de données en utilisant PostgreSQL ?

databaseA=# select * from databaseB.public.someTableName;
ERROR:  cross-database references are not implemented:
 "databaseB.public.someTableName"

Je travaille avec des données qui sont réparties dans deux bases de données bien que les données soient réellement partagées entre les deux (les colonnes userid dans une base de données proviennent de la base de données users dans l'autre base de données). Je n'ai aucune idée de la raison pour laquelle il s'agit de deux bases de données distinctes au lieu d'un schéma, mais c'est la vie...

Answer 1

J'ai vérifié et essayé de créer une relation de clé étrangère entre deux tables dans deux bases de données différentes en utilisant les deux méthodes suivantes dblink y postgres_fdw mais sans résultat.

Après avoir lu les commentaires d'autres personnes à ce sujet, par exemple aquí y aquí et dans d'autres sources, il semble qu'il n'y ait pas de moyen de le faire actuellement :

En dblink y postgres_fdw En effet, ils permettent de se connecter à des tables dans d'autres bases de données et de les interroger, ce qui n'est pas possible avec Postgres standard, mais ils ne permettent pas d'établir des relations de clé étrangère entre les tables de différentes bases de données.

Answer 2

Si les performances sont importantes et que la plupart des requêtes sont en lecture seule, je suggérerais de répliquer les données dans une autre base de données. Bien que cela semble être une duplication inutile des données, cela peut être utile si des index sont nécessaires.

Cela peut se faire avec de simples déclencheurs d'insertion qui appellent à leur tour dblink pour mettre à jour une autre copie. Il existe également des options de réplication à part entière (comme Slony), mais c'est hors sujet.

Answer 3

Voir https://www.cybertec-postgresql.com/en/joining-data-from-multiple-postgres-databases/ [publié en 2017]

Aujourd'hui, vous avez également la possibilité d'utiliser https://prestodb.io/

Vous pouvez exécuter SQL sur ce nœud PrestoDB et il distribuera la requête SQL selon les besoins. Il peut se connecter au même nœud deux fois pour des bases de données différentes, ou il peut se connecter à différents nœuds sur différents hôtes.

Il ne prend pas en charge :

DELETE
ALTER TABLE
CREATE TABLE (CREATE TABLE AS is supported)
GRANT
REVOKE
SHOW GRANTS
SHOW ROLES
SHOW ROLE GRANTS

Vous ne devez donc l'utiliser que pour les besoins de SELECT et de JOIN. Connectez-vous directement à chaque base de données pour les besoins ci-dessus. (Il semble que vous puissiez également INSERT ou UPDATE, ce qui est intéressant).

Les applications clientes se connectent à PrestoDB principalement en utilisant JDBC mais d'autres types de connexion sont possibles, y compris une connexion de type API web compatible avec Tableu

Il s'agit d'un outil open source régi par la Fondation Linux et la Fondation Presto.

Les membres fondateurs de la Fondation Presto sont : Facebook, Uber, Twitter et Alibaba.

Les membres actuels sont Facebook, Uber, Twitter, Alibaba, Alluxio, Ahana, Upsolver et Intel.

Answer 4

Au cas où quelqu'un aurait besoin d'un exemple plus concret sur la manière d'effectuer des requêtes entre bases de données, voici un exemple qui nettoie la base de données databasechangeloglock dans toutes les bases de données qui en disposent :

CREATE EXTENSION IF NOT EXISTS dblink;

DO 
$$
DECLARE database_name TEXT;
DECLARE conn_template TEXT;
DECLARE conn_string TEXT;
DECLARE table_exists Boolean;
BEGIN
    conn_template = 'user=myuser password=mypass dbname=';

    FOR database_name IN
        SELECT datname FROM pg_database
        WHERE datistemplate = false
    LOOP
        conn_string = conn_template || database_name;

        table_exists = (select table_exists_ from dblink(conn_string, '(select Count(*) > 0 from information_schema.tables where table_name = ''databasechangeloglock'')') as (table_exists_ Boolean));
        IF table_exists THEN
            perform dblink_exec(conn_string, 'delete from databasechangeloglock');
        END IF;     
    END LOOP;

END
$$