CODEWITHSUNDEEP
postgresqlpostgresql-9.2postgres-fdw
Koumarelas Ioannis
Koumarelas Ioannis

Reputation: 71

Query two different postgres databases stored in different servers

I have two POSTGRES databases stored in different servers.

The "Firstdb" is version 9.2 and it is stored in a LAN server, port 5432.

The "Seconddb" is version 10 and it is stored as localhost to my PC, port 5432.

I have access to both of them through pgAdmin4 version 2.0.

I would like to run query between those two databases to compare data.

Any ideas about how this can be done?

Thank you all for your time.

Upvotes: 2

Views: 3646

Answers (1)

Jim Jones
Jim Jones

Reputation: 19643

For running federated queries I use most of the time postgres_fdw, which creates a foreign table in the source database. Quite handy but has its caveats when dealing with joins.

An example:

CREATE SERVER my_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'target.host.com', port '5432', dbname 'targetdb');
CREATE USER MAPPING FOR postgres SERVER my_server OPTIONS (user 'postgres');

CREATE FOREIGN TABLE my_foreign_table (
  id INT,
  desc TEXT
)
SERVER my_server OPTIONS (schema_name 'public', table_name 'target_table');

EDIT based on the comments:

Keep in mind that the source database, as any other application, needs access to the target database and it has to be described at the pg_hba.conf:

host yourdb youruser 0.0.0.0 md5

Another approach is using dblink, which does not create a foreign table but enables you to fire queries directly to the target database and retrieve the result sets just as if it was local.

Upvotes: 2

Related Questions