Set up Your App to Consume Postgres Service with Single Instance

This topic shows how to set up your app to consume Tanzu Postgres for Tanzu Application Service with a single instance.

• The VCAP_APPLICATION and VCAP_SERVICES variables are provided in the container environment.
• These variables become available to the application when the service is bound to the application.

The following shows a sample of environment variables VCAP_SERVICES and VCAP_APPLICATION for Postgres Service with single instance:

VCAP_SERVICES: {
  "postgres": [
    {
      "binding_guid": "75e8053f-57ac-90cc-b7af-4bc40d714c2e",
      "binding_name": null,
      "credentials": {
        "db": "my-db",
        "hosts": [
            "q-s0.postgres-instance.shamrockgreen-services-subnet.service-instance-f2398a52-5430-2b17-50sd-b50f0c7c150c.bosh"
        ],
        "jdbcUrl": "jdbc:postgresql://q-s0.postgres-instance.shamrockgreen-services-subnet.service-instance-f2398a52-5430-2b17-50sd-b50f0c7c150c.bosh:5432/postgres?user=pgadmin&password=1a4E820y19XgFmH4143",
        "password": "1a4E820y19XgFmH4143",
        "port": 5432,
        "service_gateway_access_port": 0,
        "service_gateway_enabled": false,
        "user": "myuser"
      },
      "instance_guid": "22a1998f-e30f-3032-b360-f7a21de7a461",
      "instance_name": "postgres-instance",
      "label": "postgres",
      "name": "postgres-instance",
      "plan": "on-demand-postgres-db",
      "provider": null,
      "syslog_drain_url": null,
      "tags": [
        "postgres",
        "pivotal",
        "on-demand"
      ],
      "volume_mounts": []
    }
  ]
}


VCAP_APPLICATION: {
  "application_id": "9646bed4-d52d-4a6c-b781-6d589e3873f0",
  "application_name": "sample-app",
  "application_uris": [
    "my-app.example.com"
  ],
  "cf_api": "https://api.example.com",
  "limits": {
    "fds": 16384
  },
  "name": "pg-app-ci-2",
  "organization_id": "eb4d1234-0w34-2e21-912c-f4ae36501845",
  "organization_name": "my-org",
  "space_id": "a32e9046-0167-4e64-95c3-abe04d99c2bd",
  "space_name": "my-space",
  "uris": [
    "my-app.example.com"
  ],
  "users": null
}

The application developer can use the following environment variables from VCAP_SERVICES to create a Postgres connection URI:

• hosts (hosts is an array.)
• port
• user
• password
• db

To connect an application using jdbc drivers to the Postgres service, you can use the environment variable jdbcUrl. For example, the jdbcUrl looks like this:

• For a single instance Postgres Service: jdbc:postgresql://q-s0.postgres-instance.shamrockgreen-services-subnet.service-instance-f2398a52-5430-2b17-50sd-b50f0c7c150c.bosh:5432/postgres?user=pgadmin&password=1a4E820y19XgFmH4143

The following is sample Java code to form a connection string for a JDBC driver for a Postgres Service with Single Instance. This is just an example. You can use the jdbcUrl instead.

@Configuration
@Profile("cloud")
public class DataSourceConfiguration {

    @Bean
    public Cloud cloud() {
        return new CloudFactory().getCloud();
    }

    Logger logger = LoggerFactory.getLogger(DataSourceConfiguration.class);
    @Value("${VCAP_SERVICES}")
    private String vsJson;

    @Value("${SSL_MODE}")
    private String sslMode;

    private static Gson gson = new Gson();

    @Bean
    public DataSource dataSource() {
        VcapServices vcapServices = gson.fromJson(vsJson, VcapServices.class);
        DataSourceBuilder dataSourceBuilder = DataSourceBuilder.create();
        List<String> hosts = vcapServices.getPostgres().get(0).getCredentials().getHosts();
        Long port = vcapServices.getPostgres().get(0).getCredentials().getPort();
        StringBuilder jdbcUri = new StringBuilder("jdbc:postgresql://");
        Stream<String>withPort=hosts.stream().map(s-> String.format("%s:%d",s,port));
        jdbcUri.append(withPort.collect(Collectors.joining(",")));
        jdbcUri.append("/")
            .append(vcapServices.getPostgres().get(0).getCredentials().getDb());

        if(!StringUtils.isEmpty(sslMode)) {
            jdbcUri.append("&sslmode="+sslMode);
            jdbcUri.append("&sslfactory=org.postgresql.ssl.DefaultJavaSSLFactory");
        }

        logger.info("-------------POSTGRES URL-----------" + jdbcUri);

        HikariConfig hikariConfig = new HikariConfig();
        hikariConfig.setJdbcUrl(jdbcUri.toString());
        hikariConfig.setUsername(vcapServices.getPostgres().get(0).getCredentials().getUser());
        hikariConfig.setPassword(vcapServices.getPostgres().get(0).getCredentials().getPassword());
        hikariConfig.setInitializationFailTimeout(300000); //5 minutes
        return new HikariDataSource(hikariConfig);

    }
}