Set up Your App for Postgres HA Service with Multiple Instances

This topic shows how to set up your app to consume Tanzu Postgres for Tanzu Application Service high availability (HA) service with multiple instances.

• 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 HA service:

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?targetServerType=primary&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 HA service, you can simply use the environment variable jdbcUrl. For example, the jdbcUrl would look like this:

• For a high availability Postgres service: jdbc:postgresql://q-s0.postgres-instance.shamrockgreen-services-subnet.service-instance-f2398a52-5430-2b17-50sd-b50f0c7c150c.bosh:5432/postgres?targetServerType=primary&user=pgadmin&password=75YEk68fX291b0j3zdT4

You can use targetServerType=primary to specify that the JDBC driver only connects to the primary node in the HA cluster.

The following is sample Java code to form a connection string for a JDBC driver for a Postgres HA service. This is just an example. You can simply 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())
            .append("?targetServerType=primary");
        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);

    }
}