selecting custom schema in postgres from same DB. Spring boot configuration.

[aadarshpanwar]

I need to select schema in postgres when using aws jdbc wrapper. By default its select public schema in postgres but I need to select different schema due to project constraint. Anyone know how to configure this.
Code sample -
protected HikariConfig hikariConfig(String serverUrl) {
HikariConfig hikariConfig = new HikariConfig();
hikariConfig.setUsername(username);
hikariConfig.setPassword(password);
hikariConfig.setMaximumPoolSize(maxPoolSize);
hikariConfig.setMinimumIdle(minimumIdle);
hikariConfig.setAutoCommit(false);
// Added schema in hikariconfig. But didnt work
hikariConfig.setSchema("test2");
hikariConfig.setDataSourceClassName(AwsWrapperDataSource.class.getName());
hikariConfig.addDataSourceProperty("jdbcProtocol", "jdbc:postgresql:");
hikariConfig.addDataSourceProperty("databasePropertyName", "databaseName");
hikariConfig.addDataSourceProperty("portPropertyName", "portNumber");
hikariConfig.addDataSourceProperty("serverPropertyName", "serverName");
hikariConfig.addDataSourceProperty("currentSchema", "currentSchema");
hikariConfig.addDataSourceProperty("targetDataSourceClassName", "org.postgresql.ds.PGSimpleDataSource");
Properties targetDataSourceProps = new Properties();
targetDataSourceProps.setProperty("serverName", serverUrl);
targetDataSourceProps.setProperty("databaseName", dbName);
targetDataSourceProps.setProperty("portNumber", port);
//failover plugin specific config
targetDataSourceProps.setProperty("wrapperPlugins", "failover");
targetDataSourceProps.setProperty("failoverTimeoutMs", "30000");
targetDataSourceProps.setProperty("failoverWriterReconnectIntervalMs", "2000");
targetDataSourceProps.setProperty("failoverReaderConnectTimeoutMs", "10000");
targetDataSourceProps.setProperty("failoverClusterTopologyRefreshRateMs", "2000");
hikariConfig.addDataSourceProperty("targetDataSourceProperties", targetDataSourceProps);
return hikariConfig;
}

[karenc-bq]

Hi @aadarshpanwar, thank you for reaching out. Your use of currentSchema is correct, but you have to set it on the targetDataSourceProps instead of the hikariConfig.

I have modified the config you provided:

      protected HikariConfig hikariConfig(String serverUrl) {
        HikariConfig hikariConfig = new HikariConfig();
        hikariConfig.setUsername(username);
        hikariConfig.setPassword(password);
        hikariConfig.setMaximumPoolSize(maxPoolSize);
        hikariConfig.setMinimumIdle(minimumIdle);
        hikariConfig.setAutoCommit(false);
        // Added schema in hikariconfig. But didnt work
        hikariConfig.setSchema("test2");
        hikariConfig.setDataSourceClassName(AwsWrapperDataSource.class.getName());
        hikariConfig.addDataSourceProperty("jdbcProtocol", "jdbc:postgresql:");
        hikariConfig.addDataSourceProperty("databasePropertyName", "databaseName");
        hikariConfig.addDataSourceProperty("portPropertyName", "portNumber");
        hikariConfig.addDataSourceProperty("serverPropertyName", "serverName");
        hikariConfig.addDataSourceProperty("targetDataSourceClassName", "org.postgresql.ds.PGSimpleDataSource");
        Properties targetDataSourceProps = new Properties();
        targetDataSourceProps.setProperty("serverName", serverUrl);
        targetDataSourceProps.setProperty("databaseName", dbName);
        targetDataSourceProps.setProperty("portNumber", port);
        targetDataSourceProps.setProperty("currentSchema", "currentSchema");
        //failover plugin specific config
        targetDataSourceProps.setProperty("wrapperPlugins", "failover");
        targetDataSourceProps.setProperty("failoverTimeoutMs", "30000");
        targetDataSourceProps.setProperty("failoverWriterReconnectIntervalMs", "2000");
        targetDataSourceProps.setProperty("failoverReaderConnectTimeoutMs", "10000");
        targetDataSourceProps.setProperty("failoverClusterTopologyRefreshRateMs", "2000");
        hikariConfig.addDataSourceProperty("targetDataSourceProperties", targetDataSourceProps);
        return hikariConfig;
      }