Vengala Vinay

Having 9+ Years of Experience in Software Development

Home » Disaster Recovery 101: Architecting Auto-Failovers for PostgreSQL and Laravel Deployments on Google Cloud

Disaster Recovery 101: Architecting Auto-Failovers for PostgreSQL and Laravel Deployments on Google Cloud

Leveraging Google Cloud SQL for High Availability PostgreSQL

For mission-critical applications, a single PostgreSQL instance is an unacceptable single point of failure. Google Cloud SQL offers a managed PostgreSQL service with built-in High Availability (HA) configurations that are essential for disaster recovery and automated failover. The HA configuration creates a primary instance and a standby replica in a different zone within the same region. If the primary instance becomes unavailable, Cloud SQL automatically promotes the standby replica to become the new primary.

When setting up a Cloud SQL instance for HA, consider the following:

  • Region and Zone Selection: Choose a region that aligns with your user base and deploy your HA instances across different zones within that region. This ensures that a zone-level outage doesn’t bring down your database.
  • Machine Type: Select a machine type that can handle your peak load. While Cloud SQL manages the underlying hardware, choosing an appropriate CPU and memory configuration is crucial for performance during normal operations and failover.
  • Storage: Enable automatic storage increases to prevent performance degradation due to full disks. SSD storage is recommended for production workloads.
  • Backups: Configure automated daily backups and point-in-time recovery. These are vital for data recovery in scenarios beyond simple instance failures.

Here’s how you can provision an HA PostgreSQL instance using the `gcloud` CLI:

gcloud sql instances create my-postgres-ha \
  --database-version=POSTGRES_14 \
  --region=us-central1 \
  --availability-type=REGIONAL \
  --tier=db-custom-2-7680 \
  --storage-size=100GB \
  --storage-type=SSD \
  --backup-start-time=03:00

The key flag here is --availability-type=REGIONAL, which instructs Cloud SQL to set up the instance in a High Availability configuration. The --tier specifies the machine type (2 vCPUs, 7.5 GB memory in this example). The --region and --backup-start-time are self-explanatory.

Architecting Laravel for Automatic PostgreSQL Failover

Laravel applications need to be resilient to database failovers. The default database configuration in Laravel is straightforward, but it doesn’t inherently handle dynamic IP address changes or connection retries during a failover event. We need to implement a strategy to ensure the application can reconnect to the new primary instance seamlessly.

Cloud SQL instances have a stable IP address that remains the same even after a failover. This is a critical feature that simplifies application configuration. However, network latency during the failover process can still cause temporary connection issues. Therefore, implementing connection retry logic in your Laravel application is a robust approach.

We can leverage Laravel’s database configuration to define multiple read/write connections and read-only connections. While Cloud SQL’s HA ensures a single primary for writes, this structure is useful for read replicas if you were to add them later. For failover, the primary mechanism is the stable IP and retry logic.

Modify your config/database.php file to include a robust connection configuration. We’ll use environment variables for sensitive information and connection details.

<?php

return [

    'default' => env('DB_CONNECTION', 'pgsql'),

    'connections' => [
        'pgsql' => [
            'driver' => 'pgsql',
            'url' => env('DATABASE_URL'),
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '5432'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'charset' => 'utf8',
            'prefix' => '',
            'prefix_indexes' => true,
            'search_path' => 'public',
            'sslmode' => 'prefer',
            // Add retry logic for connection
            'options' => [
                PDO::ATTR_TIMEOUT => 10, // Initial connection timeout
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_PERSISTENT => true, // Consider persistent connections carefully
            ],
            // Custom retry logic can be implemented in a service provider
        ],
    ],

    // ... other configurations
];

The standard PDO options provide a basic timeout. For more sophisticated retry mechanisms, we can create a custom database service provider.

Create a new service provider, for example, app/Providers/DatabaseServiceProvider.php:

<?php

namespace App\Providers;

use Illuminate\Support\ServiceProvider;
use Illuminate\Database\Connection;
use Illuminate\Database\Events\ConnectionFailed;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use PDO;

class DatabaseServiceProvider extends ServiceProvider
{
    /**
     * Register any application services.
     *
     * @return void
     */
    public function register()
    {
        //
    }

    /**
     * Bootstrap any application services.
     *
     * @return void
     */
    public function boot()
    {
        DB::beforeResolving('db', function ($name, $connection) {
            $this->app->resolving('db', function ($db) use ($name) {
                $db->getEventDispatcher()->listen(ConnectionFailed::class, function ($event) use ($name) {
                    if ($name === 'pgsql' && $event->connectionName === 'pgsql') {
                        $this->handleConnectionFailure($event);
                    }
                });
            });
        });
    }

    /**
     * Handle database connection failures with retries.
     *
     * @param  \Illuminate\Database\Events\ConnectionFailed  $event
     * @return void
     */
    protected function handleConnectionFailure(ConnectionFailed $event)
    {
        $connection = $event->connection;
        $config = $connection->getConfig();
        $maxRetries = (int) env('DB_RETRY_ATTEMPTS', 5);
        $retryDelay = (int) env('DB_RETRY_DELAY_MS', 1000); // Delay in milliseconds

        Log::warning("Database connection failed for {$config['database']} on host {$config['host']}. Attempting to reconnect...", [
            'connection' => $config['database'],
            'host' => $config['host'],
            'attempts' => $maxRetries,
            'delay_ms' => $retryDelay,
        ]);

        for ($attempt = 1; $attempt <= $maxRetries; $attempt++) {
            usleep($retryDelay * 1000); // usleep expects microseconds

            try {
                // Re-establish the connection
                $connection->reconnect();
                Log::info("Successfully reconnected to database after {$attempt} attempts.");
                return; // Success
            } catch (\Exception $e) {
                Log::error("Retry attempt {$attempt} failed: " . $e->getMessage());
            }
        }

        // If all retries fail, re-throw the exception to halt the application or trigger further alerts.
        throw new \RuntimeException("Failed to reconnect to the database after multiple retries.");
    }
}

Register this service provider in your config/app.php file:

'providers' => [
    // ... other providers
    App\Providers\DatabaseServiceProvider::class,
],

And define the retry parameters in your .env file:

DB_HOST=YOUR_CLOUD_SQL_INSTANCE_IP
DB_PORT=5432
DB_DATABASE=your_database
DB_USERNAME=your_user
DB_PASSWORD=your_password

DB_RETRY_ATTEMPTS=10
DB_RETRY_DELAY_MS=2000

This setup ensures that if a connection is dropped during a Cloud SQL failover, Laravel will attempt to reconnect to the database multiple times with a configurable delay. The stable IP address of the Cloud SQL instance is key here; the application is always trying to connect to the same endpoint, and the underlying Google Cloud infrastructure handles the IP resolution to the new primary after a failover.

Monitoring and Alerting for Failover Events

Automated failover is only part of a robust disaster recovery strategy. You need to know when it happens and if it’s successful. Google Cloud provides several tools for monitoring and alerting.

Cloud Monitoring (formerly Stackdriver):

  • Metrics: Monitor key PostgreSQL metrics like CPU utilization, memory usage, disk I/O, and network traffic for your Cloud SQL instance. Look for spikes or drops that might indicate an issue or a failover event.
  • Logs: Cloud SQL logs detailed information about instance operations, including failover events. You can set up log-based metrics and alerts.
  • Alerting Policies: Create alerting policies based on specific metrics or log entries. For instance, you can set an alert if the instance status changes to “FAILED” or if specific error messages related to failover appear in the logs.

To set up an alert for failover events:

  1. Navigate to the Cloud Console > Monitoring > Alerting.
  2. Click “Create Policy”.
  3. Under “Select a metric”, search for “Cloud SQL Database” and choose a metric like “Instance Status” or filter logs for “failover”.
  4. Configure the condition (e.g., “Instance Status” is “FAILED” or “REPLICA_DOWN”).
  5. Set the notification channel (e.g., email, Slack, PagerDuty).
  6. Give your alert a descriptive name, like “PostgreSQL Cloud SQL Failover Detected”.

Application-Level Monitoring:

Your custom Laravel service provider already logs connection failures and retries. Ensure these logs are being ingested by a centralized logging system (like Cloud Logging) and that you have alerts configured for critical errors or prolonged connection issues. You can also implement a “heartbeat” check in your Laravel application that periodically pings a specific database query and reports success or failure to your monitoring system.

Testing Your Failover Strategy

A disaster recovery plan is useless if it hasn’t been tested. Regularly simulate failover events to ensure your automated processes work as expected and your application remains available.

Manual Failover Simulation:

  • Cloud SQL Console: You can manually initiate a failover from the Cloud SQL instances page in the Google Cloud Console. Select your HA instance, and under the “Overview” tab, click “Failover”. This will promote the standby replica to primary.
  • `gcloud` CLI: Use the following command to trigger a manual failover:
gcloud sql instances failover my-postgres-ha --region=us-central1

During the failover, observe your application’s behavior. Check logs for connection errors and successful reconnections. Verify that users experience minimal disruption. The duration of the failover typically ranges from a few seconds to a couple of minutes, depending on the instance size and load.

Application Resilience Testing:

Beyond just triggering the failover, test your application’s ability to handle the transient unavailability. This might involve:

  • Injecting artificial latency into your application’s network requests to the database.
  • Simulating network partitions between your application servers and the database.
  • Performing load tests during a simulated failover to ensure performance remains acceptable.

By combining Google Cloud’s managed HA capabilities for PostgreSQL with thoughtful application-level resilience in Laravel, you can architect a robust system that automatically handles database failovers, minimizing downtime and ensuring business continuity.