2

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.

Jim Jones
  • 18,404
  • 3
  • 35
  • 44
Koumarelas Ioannis
  • 71
  • 1
  • 2
  • 9

1 Answers1

2

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.

Jim Jones
  • 18,404
  • 3
  • 35
  • 44
  • I submited your code successfully. The foreign table created to my "Seconddb". But, if I query select * from foreign_table I get this result: ERROR: could not connect to server "my_server" DETAIL: FATAL: no pg_hba.conf entry for host "xxx.xxx.xxx.xxx", user "postgres", database "nsw", SSL off SQL state: 08001 – Koumarelas Ioannis Mar 29 '18 at 11:31
  • 1
    That's a whole other problem you have now. You user has no access to the target database. You have to edit the `pg_hba.conf` file in the target database and add the missing credentials. – Jim Jones Mar 29 '18 at 11:35
  • 1
    At the `pg_hba.conf` in the target server add something like: `host nsw postgres xxx.xxxx.xxxx.xxxx trust`. Keep in mind that there are other options for `trust`: see https://www.postgresql.org/docs/9.1/static/auth-pg-hba-conf.html – Jim Jones Mar 29 '18 at 11:40
  • I edited my answer with the last bit regarding the `pg_hba.conf` – Jim Jones Mar 29 '18 at 11:49