1

I have a Django app where the app and database reside in two separate VMs. I use Azure. In trying to connect to a postgresql backend hosted in a separate Azure VM, I'm writing: conn = psycopg2.connect("dbname='dbname' host='dnsname'"), where value of host was gotten off the Azure portal from here:

enter image description here

I have also tried the Virtual IP address above as the host. Both approaches fail and I get a connection error: could not connect to server: Connection refused. Is the server running on host "myapp.cloudapp.net" (23.132.341.192) and accepting TCP/IP connections on port 5432? Please note that I changed the IP address when pasting the error here.

The Azure VM hosting the postgresql database has port 5432 added to its Endpoints. Moreover, my app to which this postgresql backend belongs is a Django/Python app. My VMs have Ubuntu 14.04. In setting up postgresql, I ran the following installation commands on both app and database VMs separately:

sudo apt-get update

sudo apt-get install PostgreSQL postgresql-contrib

sudo apt-get install postgresql-server-dev-all

sudo apt-get install libpq-dev

And even though both app and database VMs have all of the above installed in them, the database itself was only created in the latter VM. I didn't create a separate user; I instead did: sudo su postgres

psql -d postgres -U postgres

And then

CREATE DATABASE dbname;

How do I fix the psycopg2.connect error? Ask for more information if needed.

Hassan Baig
  • 15,055
  • 27
  • 102
  • 205
  • Just curious, have you tried to access to this Postgres instance manually? That means from the django machine, can you manually connect to Postgres? – Simone Zandara Dec 06 '15 at 17:05
  • As in? I logged into the Django machine, and wrote `psycopg2.connect("dbname='dbname' host='dnsname'")` on the shell, in the bid to connect to the database in the other machine. If that's what you meant, yea I tried it, doesn't work. – Hassan Baig Dec 06 '15 at 18:59
  • no I mean opening a Postgres client and remotely connecting to the machine, Also try to ping and see if you can reach the Postgres machine from the Django machne. – Simone Zandara Dec 06 '15 at 19:28

1 Answers1

2

As to separate PostgreSQL into another Azure VM, we need to configure several settings both on Azure VM side and your PostgreSQL service.

1, modify the listen_addresses="*" at /etc/postgresql/9.4/main/postgresql.conf ,to allow other client beside local to request your PostgreSQL service.

2, Add the following line as the first line of /etc/postgresql/9.4/main/pg_hba.conf. It allows access to all databases for all users with an encrypted password:

# TYPE DATABASE USER CIDR-ADDRESS METHOD host all all 0.0.0.0/0 md5

3, run command sudo service postgresql restart to restart the service

4, login Azure manage portal, add a request rule in Endpoints page of your VM, configure public port 5432 to private port 5432: enter image description here

Gary Liu
  • 13,758
  • 1
  • 17
  • 32
  • No, you can only run postgresql server on the VM as the database tier – Gary Liu Dec 07 '15 at 09:09
  • Hmm, anything else I need to do to ensure the two classic VMs can 'see' eachother, other than them being in the same resource group? – Hassan Baig Dec 07 '15 at 10:15
  • Azure will deploy VMs in some nearby data centers if they are in a same resource group, which will optimize network. I think you can first make sure that you can connect to your PostgreSQL on VM from local. if it works fine, then try the connection on another VM – Gary Liu Dec 07 '15 at 10:21
  • Might the problem be that I'm not supplying a **password**? I tried to connect on `localhost`; ended up getting `fe_sendauth: no password supplied`. I then tried to connect on `localhost` without providing host (which forces psycopg2 to connect using a Unix socket in the same manner as `psql`, hence no password required), I successfully made the connection. Perhaps you could add this to your answer as well; meanwhile I'll test it further and accept the answer if it indeed turns out to untangle the tangle. – Hassan Baig Dec 07 '15 at 11:03
  • 1
    you can try to set the password to default user postgres, refer to http://stackoverflow.com/questions/7695962/postgresql-password-authentication-failed-for-user-postgres. Also you can get more information at https://help.ubuntu.com/community/PostgreSQL – Gary Liu Dec 08 '15 at 01:03