Connecting JIRA to Postgresql

[David Ashwell]

error:

Connection to BRN-USRV02:5432 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.

I have turned off the firewall completely and postgres is listening on port 5432.

Please contact me back @ Bmaunder@blueridgenetworks.com

[Andy Heinzer · Accepted solution]

Hi Danille,

Since Postgres is on the same box as Jira, you don't have to refer to the hostname to connect to the postgres instance.  So instead of using BRN-USRV02:5432  you can instead use localhost:5432

This tends to be preferred in this setup because the traffic never has to leave the loopback interface.   Whereas when you connect to that hostname, the server has to due a DNS lookup to see what IP Address resolves to that hostname.   In those cases, that probably resolves to a different IP address for that machine's hostname.

Your config looks good to me.  That line of :

host all all 127.0.0.1/32 trust

is enough to let you connect to this postgres on localhost.  localhost =  127.0.0.1

You can either change this value in the setup wizard for Jira, or in the $JIRAHOME/dbconfig.xml file that defines the connection properties as to how Jira connects to your database.

Regards,
Andy

[David Ashwell]

i tried the LOCALHOST, but no go.. check out the reply that i sent to NIC.. 

[Andy Heinzer]

Took another look at your config, I think I see a problem here:

# - Connection Settings -

#listen_addresses = 'localhost' # what IP address(es) to listen on;
127.0.0.1,10.100.1.109 # comma-separated list of addresses;
* # defaults to 'localhost'; use '*' for all
# (change requires restart)
port = 5432 # (change requires restart)

You will need to uncomment a 'listen_addresses' entry or provide some other 'listen_addresses' entry in this config in order to make sure that postgres is actually listening on that address.  I think this can look like this:

# - Connection Settings -

listen_addresses = 'localhost'
port = 5432

You will need to save that file change and restart postgres to make sure this takes effect.  Try that and see if you can connect with either the name 'localhost' (no quotes) or the IP address 127.0.0.1

[Sean Herring [Kaiser Permanente]]

I was having this problem during an upgrade to 9.6.12. This fixed it for me. 

[Nic Brough -Adaptavist- · Accepted solution]

Can you connect to the database with a database tool, using the connection settings you've given to Jira?  Locally from the database server, and remotely from the Jira server?

[David Ashwell]

i will try that...

[Nic Brough -Adaptavist-]

For what it's worth, with PostGres, the thing that usually bites me is the pg_hba file - it tells the database server about where to accept connections from and how to authenticate users, and I almost always get it wrong, locking the database down to "only the super user on this machine when he's logged into the OS as postgres" instead of "let in people from my local network who give me the right password in an md5 form"

[Andy Heinzer]

+1 for Nic's reply.  The updated  Connecting JIRA applications to PostgreSQL documentation explains the need to make sure that postgres is listening not just on the correct port, but also the correct network interface:

Accept remote TCP connections (remote PostgreSQL server only)

If you are connecting JIRA to a remote PostgreSQL server (i.e. if your PostgreSQL server is not installed locally on your JIRA server host system), you will need to configure your data/postgresql.conf and data/pg_hba.conf files to accept remote TCP connections from your JIRA server's IP address.

The following PostgreSQL documentation contains information on the appropriate listen_addresses value in the postgresql.conf file as well as the pg_hba.conf file:

• PostgreSQL 9.6 documentation — Connections and Authentication
• PostgreSQL 9.5 documentation — Connections and Authentication
• PostgreSQL 9.4 documentation — Connections and Authentication
• PostgreSQL 9.3 documentation — Connections and Authentication

After you modify the data/postgresql.conf and data/pg_hba.conf files, restart PostgreSQL for the changes to take effect.

[David Ashwell]

Andrew, i saw those instructions, but the JIRA is on the same server as POSTGRESQL. 

[Nic Brough -Adaptavist-]

You can use the "local" settings in the pg_hba.conf if you're on the same server.

[David Ashwell]

Does this look correct? 

#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------

# - Connection Settings -

#listen_addresses = 'localhost' # what IP address(es) to listen on;
127.0.0.1,10.100.1.109 # comma-separated list of addresses;
* # defaults to 'localhost'; use '*' for all
# (change requires restart)
port = 5432 # (change requires restart)
max_connections = 100 # (change requires restart)
#superuser_reserved_connections = 3 # (change requires restart)
unix_socket_directories = '/var/run/postgresql' # comma-separated list of directories
# (change requires restart)
#unix_socket_group = '' # (change requires restart)
#unix_socket_permissions = 0777 # begin with 0 to use octal notation
# (change requires restart)
#bonjour = off # advertise server via Bonjour
# (change requires restart)
#bonjour_name = '' # defaults to the computer name
# (change requires restart)

# - Security and Authentication -

[David Ashwell]

nic, I have updated the file in the pg_hba.conf. to look at the local settings.. 

# local DATABASE USER METHOD [OPTIONS]
# host DATABASE USER ADDRESS METHOD [OPTIONS]
# hostssl DATABASE USER ADDRESS METHOD [OPTIONS]
# hostnossl DATABASE USER ADDRESS METHOD [OPTIONS]
host all all 127.0.0.1/32 trust
host all all 0.0.0.0/0 trust
#
#
# (The uppercase items must be replaced by actual values.)
#
# The first field is the connection type: "local" is a Unix-domain
# socket, "host" is either a plain or SSL-encrypted TCP/IP socket,
# "hostssl" is an SSL-encrypted TCP/IP socket, and "hostnossl" is a
# plain TCP/IP socket.

[Nic Brough -Adaptavist-]

Can you connect with that?

[David Ashwell]

nope. same error! 

So let me paint a picture.. I ONLY have a SSH connection to my UBUNTU server. I am trying to do the setup from the web browser on my local computer. Should this matter? 

i have edited some more settings as tests.. 

# hostnossl DATABASE USER ADDRESS METHOD [OPTIONS]
local all all trust
host all all 127.0.0.1/32 trust
host all all 0.0.0.0/0 trust
hostnossl all all 0.0.0.0/0 trust

[David Ashwell]

SOoo.... i finally got it Connected.. 

what i did was this ... 

I edited the pg_hba.conf file to point the server to its local address.. 

# hostnossl DATABASE USER ADDRESS METHOD [OPTIONS]
local all all trust
host all all 127.0.0.1/32 trust
host all all 0.0.0.0/0 trust
hostnossl all all 0.0.0.0/0 trust

I had edited the postgresql.conf file... but once i deleted everything back to normal, and restarted the postgres service, i was able to connect to the database!

** NOTE** once you edit either one of these files, you must restart the service.. Even though i was doing that after each change it still did not work.. So as a test i went into sudo -i -u postgres, got the postgres@<servername>:~$ prompt... then did a psql.. came up with an error..

"psql: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?"

thats when I deleted everything that i did in the postgresql.conf file and restarted the service again.. 

then again one more time for good measure.. and boom Connected.. **

Thanks NIC, and ANDY for the help! 2 thumbs up.. :) 

[Nani]

Hi Danille Wilson, 

      I am Stuck with same error can you explain me in-detail what to do please..

Error: Connection to Server refused. Check that the host-name and port are correct and that the postmaster is accepting TCP/IP connections.

I am using PostgreSQL-9.6 with port 5432 and try to connect with Jira in a different server.

:::::::::::::::::Urgency Level : High:::::::::::::::::::::: 

[Sean Herring [Kaiser Permanente]]

Postgres uses a file called pg_hba.conf (hba stands for host based authentication). If this isn't properly configured, postgres will reject incoming connections. 

What you'll probably want to do is something like this: 

host all all <jira-ip>/32 trust

This will allow connections from that server without authentication. If you want it to use a password do this:
host all all <jira-ip>/32 md5

This is saying it wants authentication with an encrypted password. 
Once this is in place, restart postgres, restart jira and you should be ok. 

[Nani]

Okay, Thank You for your information @[deleted]