Can you connect to the postgres server from the production webserver?

Try "telnet 178.XXX.XXX.XXX 5432" - can it connect or does it timeout?

I did "telnet 192.34.XXX.XX 5432" with 5432 being the numbers for the postgres server (i assume) and it told me "telnet: Unable to connect to remote host: Connection refused"

What's the output of "nestat -plutn | grep 5432" on the postgres server? Postgres is probably listening locally.

This is the output of

(No info could be read for "-p": geteuid()=1000 but you should be root.)
tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN

Do I have to tell the postgres server to listen to the server with the Rails app? If so, can you tell me how to do that, and please let me know where the config files would be located. Thanks

Using this article http://hendrelouw73.wordpress.com/2012/11/14/how-to-install-postgresql-9-1-on-ubuntu-12-10-linux/ as guidance, I tried to enable tcp/ip connection in this file

"sudo nano /etc/postgresql/9.1/main/postgresql.conf"

by setting, as the listen address, the ip address of the server hosting the rails app

listen_addresses = '192.XXX.XXX.X'

but when I tried to restar postgres it said

2013-07-25 17:10:35 UTC LOG: could not bind IPv4 socket: Cannot assign requested address
2013-07-25 17:10:35 UTC HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
2013-07-25 17:10:35 UTC WARNING: could not create listen socket for "192.XXX.XXX.X"
2013-07-25 17:10:35 UTC FATAL: could not create any TCP/IP sockets

I'm not sure if that was the right step to take (i.e. tell the db to listen for the ip address of the server with the rails apps) but if it was I'm not sure why it gave me an error

Set it to the postgres server's IP and restart Postgres. Make sure you set up a firewall to allow only the rails app server to connect to it:

https://www.digitalocean.com/community/articles/how-to-set-up-a-firewall-using-ip-tables-on-ubuntu-12-04
https://www.digitalocean.com/community/articles/how-to-setup-a-firewall-with-ufw-on-an-ubuntu-and-debian-cloud-server
https://www.digitalocean.com/community/articles/how-to-setup-a-basic-ip-tables-configuration-on-centos-6

can you please clarify this statement: "set it to the postgres server's IP" I have a server with a postgres database that I want to store data from a server with a rails app. Shouldn't the postgresql.conf listen_address listen to the ip of the server with the rails app?

Also, is it necessary to change pg_hba.conf on the server with the postgres database. So that it lets every user from the ip address 178.XXX.XXX.X (where the rails app is hosted) connect to remotepg_production, I set it like this

host remotepg_production all 178.XXX.XXX.X ident

Note, regarding the firewall, I haven't set that up yet, but this is only a dummy test app. I will do it for an app that will exist more than a day

Set listen_addresses to the database server's IP address so that postgres listens on the local interface allowing other machines to connect to it.

ok, thanks, but when I tried to restart postgres it gave me an error originating from the changes i made to the pg_hba.conf file. I set it like this so that it listens to the ip of the rails app

host remotepg_production all 178.XXX.XXX.X ident

The error it gave me is this. It's interpreting 'ident' as the ip mask. I thought i should put 'ident' as the authentication method. Can you tell me what to chagne here or if it's even necessary? Thanks in advance

2013-07-25 17:43:15 UTC LOG: invalid IP mask "ident": Name or service not known
2013-07-25 17:43:15 UTC CONTEXT: line 100 of configuration file "/etc/postgresql/9.1/main/pg_hba.conf"
2013-07-25 17:43:15 UTC FATAL: could not load pg_hba.conf

Try commenting that line out - does it work without it?

I removed that line from pg_hba.conf. Postgres restarts now on the server with the database I want to connect to. The rails app, however, is connected to the database on its own server, even though the host in the database.yml file is set to the ip address of server that has the postgres database I want to connect to i.e. the rails app is not connecting to database on the other server.

That's weird. Did you save the file? Make sure you restart any related processes as the code/config might be cached in one of them.

Got it to work by reploying the app, which I guess restarted all the related processes. Thanks for your help.