Cross database query in Postgres

We might face a scenario where in you want to create a query across databases or want to refer to the data in other database. Now postgres does not directly allow the users to do this. We would require to do workaround for the same using dblink. 

Given below are the steps we need to follow - 

Step 1 -
install dblink using extension in the database as shown in the image below - 

once you add it, you should be able to see the extension as shown in the image below: 

Step 2 -
then have the user who has or does not have password, but make sure he has access to the foreign db.

Step 3
then use the below query. 

select dblink_connect('<servername of your choice>',
                      'host=<hostname or ip> 
                       port=<port number> 
                       dbname=<database for which the user needs to connect> 
                       user=<username> password=<password>'

select * from dblink('<servername used above>', 
                     <query>) as <alias>(return columns along with datatype eg - id integer, name text)

example -

SELECT dblink_connect('test2server', 'host=127.0.0.1 port=5432 dbname=test2 user=testuser'); 
select *
from dblink('test2server',
           'select * from users')
           AS users_test2(id integer,name text)

you can have a user with or without password depending on your usecase