connecting to database on a different server

  • Posted July 25, 2013

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


Submit an answer
You can type!ref in this text area to quickly search our full set of tutorials, documentation & marketplace offerings and insert the link!

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

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

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.

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. <br> <br>

Try commenting that line out - does it work without 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 <br> <br>host remotepg_production all 178.XXX.XXX.X ident <br> <br>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 <br> <br>2013-07-25 17:43:15 UTC LOG: invalid IP mask “ident”: Name or service not known <br>2013-07-25 17:43:15 UTC CONTEXT: line 100 of configuration file “/etc/postgresql/9.1/main/pg_hba.conf” <br>2013-07-25 17:43:15 UTC FATAL: could not load pg_hba.conf <br> <br>

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.

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? <br> <br>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 <br> <br>host remotepg_production all 178.XXX.XXX.X ident <br> <br>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

Using this article as guidance, I tried to enable tcp/ip connection in this file <br> <br>“sudo nano /etc/postgresql/9.1/main/postgresql.conf” <br> <br>by setting, as the listen address, the ip address of the server hosting the rails app <br> <br>listen_addresses = ‘192.XXX.XXX.X’ <br> <br>but when I tried to restar postgres it said <br> <br>2013-07-25 17:10:35 UTC LOG: could not bind IPv4 socket: Cannot assign requested address <br>2013-07-25 17:10:35 UTC HINT: Is another postmaster already running on port 5432? If not, wait a few seconds and retry. <br>2013-07-25 17:10:35 UTC WARNING: could not create listen socket for “192.XXX.XXX.X” <br>2013-07-25 17:10:35 UTC FATAL: could not create any TCP/IP sockets <br> <br>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 <br>

This is the output of <br> <br>(No info could be read for “-p”: geteuid()=1000 but you should be root.) <br>tcp 0 0* LISTEN
<br> <br>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