connecting to database on a different server

July 25, 2013 13.4k views
I deployed a sample Rails app to a remote server with postgres hosted on the same server. The database.yml file was like this. production: adapter: postgresql encoding: unicode database: remotepg_production pool: 5 host: localhost username: mrmann password: secret Everything worked fine. I then went into the database.yml file and replaced host: localhost with the ip address of a postgres database on another server host: 178.XXX.XXX like this production: adapter: postgresql encoding: unicode database: remotepg_production pool: 5 host: 178.XXX.XXX.XXX #ip address of server with other postgres database username: mrmann password: secret When I restarted postgres on the server with the Rails app, the sample application now gave me the 'something went wrong' page for Rails. The username and the password for the username are the same on both dbs. Can you suggest what the problem might be? Thanks
13 Answers
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
by Shaun Lewis
Learn how to setup a firewall with UFW on an Ubuntu / Debian cloud server.
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.
Have another answer? Share your knowledge.