CODEWITHSUNDEEP
sqldatabasepostgresqlplpgsql
smn_onrocks
smn_onrocks

Reputation: 1342

connecting a remote server database from a database funcion in postgresql9.1

Friends i just want to know is it possible to connect another server database which is in same lan network from a store function of another database in postgresql 9.1

example server a have a database function which will connect server b database table for querying data.

if some one have some code sample or idea please share that.

Upvotes: 0

Views: 103

Answers (2)

smn_onrocks
smn_onrocks

Reputation: 1342

after a long research i am able to do that if any one require that please consider this and it will work fine. my environment is ubuntu 12.04 64bit and postgres 9.1

first run

sudo apt-get install postgresql-contrib-9.1

then in postgres prompt

postgres=# CREATE EXTENSION dblink;

now run the query like SELECT * FROM

 postgres=# dblink('host=123.456.78.9 port=5432 dbname=my_db user=sn password=adm123',
        'SELECT col1,col2 
         FROM schema.tbl_table')
         AS t (col1 numeric,col2 varchar);

just make sure that in the sharing server pg_hba.conf file there is an entry of 1st server.

Upvotes: 0

Houari
Houari

Reputation: 5641

I understand that you want to query a server from another server, if so, look at dblinkfunction:

dblink documentation

Upvotes: 1

Related Questions