cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

PostgreSQL Load balancing - connection to Master/Slave based on SQL query type

Level 9

PostgreSQL Load balancing - connection to Master/Slave based on SQL query type

Hi,

I have came across with some interesting idea for scaling. 

With JDBC driver v9.4-1200 onward is possible to set up a Tomcat server in a way it uses multiple jdbc URLs. Each of which is possible to mark as "any", "master", "slave", "secondary", "preferSlave" and "preferSecondary". Driver then makes a distinction which queries are write and read and sends them to appropriate DB (i.e INSERTS goes to DB on which the user has permissions to write, SELECTS goes to read only DB etc..). That would effectively divide whole load between several DB. Of coarse this only make sense on HA configuration, there needs to be a Master/Slave and replication needs to be set up properly. 

This also only applies to TomCat config only. Since Tomcat is used for different Persistence Providers, config.xml is almost in default. and Persistence configurations are made in platformSettings.json. 

Question:

Would be possible to set up such an environment with platformSettings only? 

 

JDBC klient

 

Version 9.4-1200 (2015-01-02)

Enhance connection fail-over with master/slave restriction and loadbalancing PR #209 Based on work by chenhj@cn.fujitsu.com

 

targetServerType

Allows opening connections to only servers with required state, the allowed values areany, master, slave and preferSlave. The master/slave distinction is currently done by observing if the server allows writes. The value preferSlave tries to connect to slaves if any are available, otherwise allows falls back to connecting also to master.

 

Version 42.2.0 (2018-01-17)

slave and preferSlave values for the targetServerType connection property have been deprecated in favour of secondary and preferSecondary respectively.

 

targetServerType = String

Allows opening connections to only servers with required state, the allowed values areany, master, slave, secondary, preferSlave and preferSecondary. The master/slave distinction is currently done by observing if the server allows writes. The value preferSecondary tries to connect to secondary if any are available, otherwise allows falls back to connecting also to master.

 

Něco takto :?

 

Připojení pomocí JDBC (Tomcat)

 

Soubor conf/server.xml

 

<Resource name="jdbc/postgres" scope="Shareable" type="javax.sql.DataSource"/>

<ResourceParams name="jdbc/postgres">

    <parameter>

        <name>driverClassName</name>

        <value>org.postgresql.Driver</value>

    </parameter>

    <parameter>

        <name>url</name>

        <value>jdbc:postgresql://smbpg1a.dmz.skoda.vwg,smbpg1b.dmz.skoda.vwg/thingworx</value>

    </parameter>

    <parameter>

        <name>targetServerType</name>

        <value>slave</value>

    </parameter>

    <parameter>

        <name>username</name>

        <value>fiotro</value>

    </parameter>

    ...

</ResourceParams> 

 

1 REPLY 1
Highlighted

Re: PostgreSQL Load balancing - connection to Master/Slave based on SQL query type

While this is not a suported or even tested configuration, you may add it to our ThingWorx Ideas board as a suggestion!