Connecting JIRA to Postgresql

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

 

 

3 answers

2 accepted

1 votes

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?

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"

+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:

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

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

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

 

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 -

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.

Can you connect with that?

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

0 votes

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

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

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

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.. :) 

Suggest an answer

Log in or Join to answer

Stay in touch

Be the first to know what's trending on Atlassian Community