Postgres connection refused from Openshift Tomcat

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.

Answer 1

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;
}

Answer 2

I fixed this issue by ensuring that the application hosting the database is Scalable.

It is easy to miss this on reading the OpenShift documentation: https://blog.openshift.com/sharing-database-across-applications/

This site also helped me to identify and fix the issue: http://www.hfaber.com/post/135256827109/openshift-sharing-database-across-applications

A scalable application requires at least 2 gears and Scalable needs to be specified when you create the application, by using the -s option in the rhc app create command.

You can clone an existing non-scalable application using the --from-app parameter when using the create command: https://access.redhat.com/documentation/en-US/OpenShift_Online/2.0/html/User_Guide/Cloning_an_Existing_Application.html