Saturday, November 30, 2013

How to configure db_link on Postgres database to pull data from other database?

I have 2 databases that running on the same database server, but, I have requirement to create a query to join data from the 2 databases. To achieve that, I have to configure db_link in my database server.

Assume the 2 databases are being named as dbA and dbB and the connection are from dbA
dbB User ID: [USER_ID]
dbB Password: [PASSWORD]

To enable db_link, run the command below to enable db_link function
psql -U postgres
postgres=# CREATE EXTENSION dblink
To list the installed extension, type in the command as described below
postgres=# \dx
Below are the sample command that to select data from dbB
SELECT events.* FROM dblink('dbname=dbB user=[USER_ID] password=[PASSWORD]','SELECT title FROM events') AS events(title character varying(255));

No comments: