Database Optimizer failover configuration

As the Cast AI Database Optimizer (DBO) sits in line with your database connection, implementing additional failover mechanisms can enhance system reliability. The DBO uses a Kubernetes ClusterIP service, which provides stable IP routing to healthy pods. This means that Kubernetes automatically handles routing to available DBO instances at the IP level, providing connection stability without requiring client-side configuration.

While this built-in routing ensures basic connection reliability, you may want to implement additional failover mechanisms for comprehensive high availability. This guide covers configuration options based on your current database setup.

Systems that already support failover

If you're already using some form of master/slave or pooled database server setup, implementing DBO failover is straightforward. You'll need to make the installed DBO hostname the primary server for your system - your existing failover configuration will handle the rest.

Working with PGPool

For those using PGPool, you'll be glad to know it already supports multiple back-ends, making DBO integration rather simple. You likely have at least one back-end configuration present already, something like:

backend_hostname = 'database.hostname'
backend_port = 5432

To implement DBO support, update your configuration to include both endpoints:

backend_hostname0 = '<DBO-HOSTNAME>'
backend_port0 = 5432
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_hostname1 = 'database.hostname'
backend_port1 = 5432
backend_flag1 = 'ALLOW_TO_FAILOVER'

Direct database connections

Most customers have applications that talk directly to a single database. If this describes your setup, then implementing failover requires only minor changes to your software. The key change is updating the database URL to contain comma-separated hostnames.

Any service using a JDBC connection can provide multiple hostnames in the jdbc:postgresql connection string. Most other languages support multiple hosts in a PostgreSQL URL as it's a standard feature of the PostgreSQL library, though some may require alternative approaches for failover.

Below are examples for common languages and frameworks. You'll need to replace the placeholder hostnames (<cast-dbo-hostname> and <database-hostname>) with your actual values.

C++ applications

For C++ applications using the pqxx library, failover configuration is handled through the connection string:

#include <stdlib.h>
#include <iostream>

#include <pqxx/pqxx> 

// Old database connection URL
const auto db_url = "postgresql://user:password@<database-hostname>//database?sslmode=require";

// Cast AI database connection URL, with failover to database
const auto db_url = "postgresql://user:password@<cast-dbo-hostname>,<database-hostname>/database?sslmode=require";

int main() {
    try {
        pqxx::connection connection(db_url);
        if (connection.is_open()) {
            std::cout << "Opened database successfully: " << connection.dbname() << std::endl;
        } else {
            std::cout << "Can't open database" << std::endl;
            return EXIT_FAILURE;
        }

        pqxx::nontransaction transaction(connection);
        pqxx::result result(transaction.exec("SELECT 1"));

        std::cout << "Query executed successfully" << std::endl;
        for (auto row : result) {
            std::cout << row[0].as<int>() << std::endl;
        }

        connection.disconnect();
    } catch (const std::exception &e) {
        std::cerr << e.what() << std::endl;
        return EXIT_FAILURE;
    }

    return EXIT_SUCCESS;
}

Go applications

Go applications using the pgx library can implement failover through the connection string:

package main

import (
	"context"
	"fmt"
	"log"

	"github.com/jackc/pgx/v4"
)

// Old database connection URL
// dbUrl = "postgresql://user:password@<database-hostname>//database?sslmode=require"

// Cast AI database connection URL, with failover to database
const dbUrl = "postgresql://user:password@<cast-dbo-hostname>,<database-hostname>/database?sslmode=require"

func main() {
	conn, err := pgx.Connect(context.Background(), dbUrl)
	if err != nil {
		log.Fatalf("Unable to connect to database: %v\n", err)
	}
	defer conn.Close(context.Background())

	var result int
	err = conn.QueryRow(context.Background(), "SELECT 1").Scan(&result)
	if err != nil {
		log.Fatalf("QueryRow failed: %v\n", err)
	}

	fmt.Printf("Query result: %d\n", result)
}

PHP applications

PHP applications can take advantage of PostgreSQL's native failover support:

# Old database connection URL
# $dbUrl = 'zpostgresql://user:password@<database-hostname>//database?sslmode=require';

# Cast AI database connection URL, with failover to database
$dbUrl = 'postgresql://user:password@<cast-dbo-hostname>,<database-hostname>/database?sslmode=require';

try {
    // Connect to the database using pg_connect
    $conn = pg_connect($dbUrl);

    if (!$conn) {
        throw new Exception('Connection failed.');
    }
    
    // Execute a simple query
    $result = pg_query($conn, 'SELECT 1');

    if (!$result) {
        throw new Exception('Query failed.');
    }
    
    // Fetch the result
    $row = pg_fetch_assoc($result);
    
    // Output the result
    if ($row) {
        echo "Database connection successful. Query result: " . $row['?column?'];
    } else {
        echo "Query failed.";
    }
  
} catch (Exception $e) {
    echo 'An error occurred: ' . $e->getMessage();
}

// Close the connection
pg_close($conn);

Python applications

Python applications using psycopg2 can implement failover with a connection string update:

import psycopg2
from psycopg2 import sql

# Old database connection URL
# db_url = "postgresql://user:password@<database-hostname>//database?sslmode=require"

# Cast AI database connection URL, with failover to database
db_url = "postgresql://user:password@<cast-dbo-hostname>,<database-hostname>/database?sslmode=require"

# Establish a connection to the database
try:
    conn = psycopg2.connect(db_url)
    print("Connection to the database established successfully.")

    # Execute a simple query
    try:
        with conn.cursor() as cursor:
            cursor.execute("SELECT 1;")
            result = cursor.fetchone()
            print(f"Query result: {result}")
    except Exception as e:
        print(f"An error occurred while executing the query: {e}")
except Exception as e:
    print(f"An error occurred while connecting to the database: {e}")

# Close the connection
finally:
    if conn:
        conn.close()
        print("Database connection closed.")

Java applications

Java applications using JDBC can implement failover through the connection URL:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Cast {
    public static void main(String[] args) {
        String url = "jdbc:postgresql://<cast-dbo-hostname>,<cdatabase-hostname>/database?sslmode=require";
        String user = "username";
        String password = "password";

        try (Connection conn = DriverManager.getConnection(url, user, password);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT 1")) {

            while (rs.next()) {
                System.out.println(rs.getInt(1));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

The multi-host connection string approach is widely supported across PostgreSQL clients, making it a reliable choice for implementing failover in your applications.