0

I want to connect two postgreSQL databases of different systems. I am using dblink to connect two postgreSQL databases.

but iam getting a connection error saying

ERROR:  could not establish connection
DETAIL:  could not connect to server: No route to host (0x00002751/10065)
Is the server running on host "192.168.0.5" and accepting
TCP/IP connections on port 5432?


********** Error **********

ERROR: could not establish connection
SQL state: 08001
Detail: could not connect to server: No route to host (0x00002751/10065)
Is the server running on host "192.168.0.5" and accepting
TCP/IP connections on port 5432?

what i was trying is 

select * from dblink('user=postgres host=192.168.0.5 password=geethu dbname=partition','select  count(*) from part1') as qwe(tid int);

I have editted the hba.con file and also postgresql.conf file by including the ip address and settting the listen_address to *

can any one help me? Thanks in advance

gela
  • 23
  • 2
  • 5
  • Well, test it in `psql` first. Does the same connection string work in `psql` on the same host? `psql 'user=postgres host=192.168.0.5 password=geethu dbname=partition'` . If it works in `psql`, I'd be looking at SELinux or a process-specific firewall as possible culprits. – Craig Ringer May 17 '13 at 05:45
  • by doing the same tasks mentioned above i can connect to another system... But the fact is that i have established the connection around two months later.. i am in doubt whether i had fogotten any steps... there is no firewall issue..i just make sure of it. – gela May 17 '13 at 06:12
  • That isn't very clear. Did you run the `psql` command on the *exact same system* that the PostgreSQL server you're having problems connecting *from* using dblink runs on? Did you try it as the `postgres` user on that system using `sudo -u postgres psql ...` ? And **does the system you're making the connection *from* have SELinux enabled**? Does *either* have a firewall, either software (iptables etc) or a hardware firewall/router inbetween the two? – Craig Ringer May 17 '13 at 06:22
  • Also, please show the exact operating system and version of both servers the exact postgresql version. – Craig Ringer May 17 '13 at 06:24
  • actually i had not used psql sql version is 9.2 for both the systems – gela May 17 '13 at 06:30
  • Well, *test with psql* then. Does the same problem happen with psql *as the same user postgresql runs as on the problem server*? I said *exact version* - do `select version()`. And **what operating system is on both servers**. Seriously. – Craig Ringer May 17 '13 at 06:31
  • did you check the ip address and port no? Because your code work for me with small correction i am the port no inside of the dblink – Sathish Apr 23 '14 at 06:51

1 Answers1

0
  1. You need to enable postgres to listen on port 5432 from the 'different system' IP address, this is done by editing pg_hba.conf file and postgres.conf file. Check here
  2. Make sure that there is no firewall that is blocking the request.
Community
  • 1
  • 1
Ankit
  • 3,083
  • 7
  • 35
  • 59
  • there is no firewall issue. i have editted the hba.conf file and also postgresql.conf file – gela May 17 '13 at 06:16
  • This is not a `pg_hba.conf` issue, otherwise they'd get a different error. `pg_hba.conf` has nothing to do with what ports and addresses PostgreSQL listens on, only what source addresses it accepts connections from. Nor is it to do with addresses PostgreSQL is listening on; if it were not listening the user would get a `connection refused`. It could be a remote, local, or intermediate firewall though. – Craig Ringer May 17 '13 at 06:20
  • but i had disabled my wifi access and had set a local address – gela May 17 '13 at 06:23
  • @gela: it looks like a firewall issue then, have you checked iptables. Try to do a tracetcp. – Ankit May 17 '13 at 06:28
  • can help me to do that... o dont know how to do it – gela May 17 '13 at 06:31
  • let X and Y be the two systems. I can acess the database from X to Y but cannot acess from Y to X – gela May 17 '13 at 08:36
  • @gela: Does both systems X and Y has postgres installed? if yes, have you checked configuration files (change needs postgres restart) and firewall? – Ankit May 17 '13 at 08:42
  • yes both have postgres installed and i have made changes to configuration files – gela May 17 '13 at 09:02
  • @gela: Strange, by the way, why do you have such configuration, where x and y both are accessing each other for database! Also, recheck the configuration, restart postgres and try again. – Ankit May 17 '13 at 09:15