dotConnect for PostgreSQL Documentation
In This Topic
    Load Balancing and Failover
    In This Topic

    dotConnect for PostgreSQL allows to specify several hosts, running in Hot Standby mode, with their ports in the Host parameter of the connection string, separated with commas, like "Host=localhost:5433,db:5440,db:5441;User=postgres;Pwd=postgres;Database=test_multihosts;Target Session=Any". This can be used for load balancing and failover purposes.

    If the Port is also specified as the separate connection string parameter, this port value will be used as default for all hosts in the Host parameter with no port specified.

    Primary and Standby Servers

    The Hot Standby mode assumes one PostgreSQL server running as a primary server, and other running as standby servers. The primary server accepts commands both reading and writing data, and standby servers accept only read-only commands. In case the primary server goes down, one of the standby servers becomes primary.

    dotConnect for PostgreSQL can automatically detect the primary server. However, we recommend placing the supposed primary server first in the list in order to reduce the number of checks and increase performance.

    Selecting Server to Connect and Load Balancing

    You can control how dotConnect for PostgreSQL selects a host to connect either on a connection level, with the Target Session connection string parameter, or on the command level, with the TargetSession property of the PgSqlCommand class. The connection parameter value is applied for all the commands using this connection by default, but you can override the connection behavior for specific commands by explicitly assigning them a different TargetSession value. Here is the list of possible values and their meanings:

    Value Behavior
    SuppressLoadBalancing This is the default value. dotConnect for PostgreSQL connects only to the first host in the list. It throws an exception and does not attempt to connect to other hosts if the first host is unavailable.
    Any When opening a connection, dotConnect for PostgreSQL connects to any working host from the list and returns an error only if it could not connect to all of the hosts. After connecting, hosts are switched for read-only commands using round-robin load balancing. This option is the best for cases when load balancing is not configured on the server side, but there are several servers, running replicas of the same database and not divided into standby and primary, and the user wants their order to determine their function.
    PreferStandby When opening a connection, dotConnect for PostgreSQL connects to any working standby host from the list. If there are no valid standby hosts in the list, an error occurs. After connecting, standby hosts are switched for read-only commands using round-robin load balancing. If a connection fails when executing a command and no valid standby hosts are found when reexecuting it via failover, any valid host from the list is selected, and a primary host can be selected too.
    PreferPrimary When opening a connection, dotConnect for PostgreSQL looks for a primary host in the list and throws an error if no valid primary host is found. If a connection fails when executing a command and no valid primary host is found when reexecuting it via failover, any valid host from the list is selected, and a standby host can be selected too. After a standby host is selected, standby hosts are switched for read-only commands using round-robin load balancing.
    Standby When opening a connection, dotConnect for PostgreSQL connects to any working standby host from the list. If there are no valid standby hosts in the list, an error occurs. After connecting, standby hosts are switched for any commands using round-robin load balancing.
    Primary When opening a connection, dotConnect for PostgreSQL looks for a primary host in the list. If there are no such host in the list, an error occurs. All commands are executed against the primary server.
    If a command text is not a read-only query, and TargetSession is set to Any, PreferStandby, or PreferPrimary explicitly or implicitly, dotConnect for PostgreSQL executes the command against the primary server. However, the check for whether the command is read-only is rather simple, and it does not guarantee the correct result. It only checks if the command starts with "SELECT", "COPY TO", "DECLARE", "FETCH", "CLOSE", "SHOW", "SET", or "RESET", and that it doesn't start with "SET TRANSACTION", end with "FOR UPDATE" or "FOR SHARE", and does not contain "NEXTVAL(" or "SETVAL(". Command text is not fully parsed in order to provide better performance.

    Failover

    In case of using LocalFailover if a connection lost, dotConnect for PostgreSQL tries to implicitly reconnect and reexecute the operation. If multiple hosts are specified, and TargetSession is set to other values than Primary or SuppressLoadBalancing, dotConnect for PostgreSQL tries to reconnect to the next suitable server in the list, not to the same one. Here is an example on how you can configure failover:

     connection.LocalFailover = true;
 connection.ConnectionLost += Connection_ConnectionLost;

private static void Connection_ConnectionLost(object sender, Devart.Common.ConnectionLostEventArgs e)
{
      if (e.AttemptNumber < 3)
        e.RetryMode = Devart.Common.RetryMode.Reexecute;
}

    
conn.LocalFailover = True
AddHandler conn.ConnectionLost, AddressOf Connection_ConnectionLost

Private Shared Sub Connection_ConnectionLost(ByVal sender As Object, ByVal e As Devart.Common.ConnectionLostEventArgs)
    If e.AttemptNumber < 3 Then e.RetryMode = Devart.Common.RetryMode.Reexecute
End Sub

    Load Balancing and Pooling

    If connection pooling is enabled when using load balancing, the pooling is used in usual way. When a connection to a specific host is closed, it is placed in the pool instead of closing, and when it is opened, if there is available connection in the pool with the same connection settings, it is taken from the pool instead of opening a new one.

    We recommend not to disable pooling when using load balancing

    See Also

    LocalFailover  | Logging Onto The Server  | PostgreSQL Specific Features

     

     

    © 2003 - 2024 Devart. All Rights Reserved.

    [email protected]