Postgres connection refused from Openshift Tomcat 7 cartridge

Question

I'm trying to deploy an app on an Openshift Tomcat 7 (JBoss EWS 2.0) cartridge, but I'm getting:

org.postgresql.util.PSQLException: Connection refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.

Here's my datasource config:

public class OpenshiftDataConfig {
    @Inject private Environment environment;
    @Bean
    public DataSource dataSource() {
        HikariDataSource dataSource = new HikariDataSource();
        dataSource.setDataSourceClassName("org.postgresql.ds.PGSimpleDataSource");
        dataSource.setJdbcUrl(String.format("jdbc:%s/%s",
                environment.getProperty("OPENSHIFT_POSTGRESQL_DB_URL"), 
                "dev"));
        dataSource.setUsername(environment.getProperty("OPENSHIFT_POSTGRESQL_DB_USERNAME"));
        dataSource.setPassword(environment.getProperty("OPENSHIFT_POSTGRESQL_DB_PASSWORD"));
        return dataSource;
    }
}

I'm logging the JDBC URL during testing, and I've tried it with username/password embedded:

jdbc:postgresql://<value of OPENSHIFT_POSTGRESQL_DB_USERNAME>:<value of OPENSHIFT_POSTGRESQL_DB_PASSWORD>@<value of OPENSHIFT_POSTGRESQL_DB_HOST>:<value of OPENSHIFT_POSTGRESQL_DB_PORT>/dev

and without (built with slightly different code):

jdbc:postgresql://<value of OPENSHIFT_POSTGRESQL_DB_HOST>:<value of OPENSHIFT_POSTGRESQL_DB_PORT>/dev

I've tested connectivity using psql while logged into the app:

psql -h $OPENSHIFT_POSTGRESQL_DB_HOST -p $OPENSHIFT_POSTGRESQL_DB_PORT -U $OPENSHIFT_POSTGRESQL_DB_USERNAME

And I can see my data is there. I've also verified that the admin user is a login role. The pg_hba.conf file should allow the connection:

# Allow all users to connect over the network with valid credentials
host    all         all           0.0.0.0/0             md5
host    all         all           ::/0                  md5

This seems like an issue with Openshift's firewall configuration between the two cartridges, so I'm contacting their official support as well.

Have you checked that you're connect to the correct port? Have you checked that the database service is running? Have you checked that the database is configured to allow connections from other addresses other than localhost and that the user is allowed to make remote or local connections? — MadProgrammer
I'm using the host and port provided by the Openshift environment, and I tested connectivity with those values using psql from a login session. The pg_hba.conf also allows anyone access with valid credentials. — Mike Partridge
Between the stock Tomcat and Postgres gears? I guess that's possible. — Mike Partridge

Mike Partridge Mike Partridge · Accepted Answer · 2015-01-12T12:10:33

Most of the answer was in the Openshift knowledge base: a datasource is provided to Java cartridges like their Tomcat 7 (JBoss EWS 2.0). It's not specified in the article, but it appears to already be pooled using Apache DBCP, so setup is simple:

@Bean
public DataSource dataSource() throws NamingException {
    DataSource datasource = (DataSource) new InitialContext().lookup("java:comp/env/jdbc/PostgreSQLDS");
    return datasource;
}

Postgres connection refused from Openshift Tomcat 7 cartridge

2 Answers