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.
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.
pg_hba.conf file in the target database and add the missing credentials.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 postgresql.org/docs/9.1/static/auth-pg-hba-conf.htmlpg_hba.conf