PostgreSQL High Availability with SafeKit: Install the postgresql.safe Module for Failover

High Availability Quick Installation Guide for PostgreSQL

This guide explains how to set up a postgresql.safe cluster for PostgreSQL using SafeKit, ensuring automatic failover and synchronous replication without the need for shared storage.

For help, you can use SafeKit AI 🤖.

1. Overview

• Architecture: Uses a two-node system (Primary/Secondary).
• Data Protection: Implements real-time synchronous replication for zero data loss (RPO=0).

2. Installation

• Software: Install the SafeKit engine on both servers.
• Module: Download the pre-configured postgresql.safe.safe application module.

3. Configuration

• Web Console: Configure the specific folders containing the PostgreSQL files.
• Monitoring: Start monitoring and protecting the PostgreSQL application.

SafeKit High Availability Limitations

Why a replication of a few Tera-bytes?

Resynchronization time after a failure (step 3)

• 1 Gb/s network ≈ 3 Hours for 1 Tera-bytes.
• 10 Gb/s network ≈ 1 Hour for 1 Tera-bytes or less depending on disk write performances.

Alternative

• For a large volume of data, use external shared storage.
• More expensive, more complex.

Why a replication < 1,000,000 files?

• Resynchronization time performance after a failure (step 3).
• Time to check each file between both nodes.

Alternative

• Put the many files to replicate in a virtual hard disk / virtual machine.
• Only the files representing the virtual hard disk / virtual machine will be replicated and resynchronized in this case.

Why a failover ≤ 32 replicated VMs?

• Each VM runs in an independent postgresql.safe module.
• Maximum of 32 postgresql.safe modules running on the same cluster.

Alternative

• Use an external shared storage and another VM clustering solution.
• More expensive, more complex.

Why a LAN/VLAN network between remote sites?

• Automatic failover of the virtual IP address with 2 nodes in the same subnet.
• Good bandwidth for resynchronization (step 3) and good latency for synchronous replication (typically a round-trip of less than 2ms).

Alternative

• Use a load balancer for the virtual IP address if the 2 nodes are in 2 subnets (supported by SafeKit, especially in the cloud).
• Use backup solutions with asynchronous replication for high latency network.

Overview of the SafeKit / PostgreSQL solution

The solution is described here: The Simplest PostgreSQL High Availability: 2-Node Synchronous Replication& Failover.

Installation of the SafeKit / PostgreSQL solution on Windows and Linux (postgresql.safe)

Prerequisites

• You need PostgreSQL installed on 2 nodes (virtual machines or physical servers).

Package installation on Windows

• Download and install the free version of SafeKit on 2 Windows nodes.
  Note: the free version includes all SafeKit features. At the end of the trial, you can activate permanent license keys without uninstalling the package.
• Upon completing the package installation, select Set console credentials and firewall rules now and configure a password for the admin user of the SafeKit console.
  Set the same password on all nodes.
• Exclude from antivirus scans C:/safekit/ (the default installation directory) and all replicated folders that you are going to define.
  Antiviruses may face detection challenges with SafeKit due to its close integration with the OS, virtual IP mechanisms, real-time replication and restart of critical services.

Download SafeKit (Windows) >

Module installation on Windows

• Download the postgresql.safe module. The module is free. It contains the files userconfig.xml and the restart scripts.
• Put postgresql.safe under C:/safekit/Application_Modules/generic.

⬇️ Download postgresql.safe (Windows) >

Package installation on Linux

• Install the free version of SafeKit on 2 Linux nodes. Note: the free trial includes all SafeKit features. At the end of the trial, you can activate permanent license keys without uninstalling the package.
• After the download of safekit_xx.bin package, execute it to extract the rpm and the safekitinstall script and then execute the safekitinstall script
• Answer yes to firewall automatic configuration
• Set the password for the web console and the default user admin. Set the same password on all nodes.

Download SafeKit (Linux) >

Module installation on Linux

• Download the postgresql.safe module.
  The module is free. It contains the files userconfig.xml and the restart scripts.
• Put postgresql.safe under /opt/safekit/Application_Modules/generic/.

⬇️ Download postgresql.safe (Linux) >

Step by step configuration of the SafeKit / PostgreSQL solution

Prerequisites

• PostgreSQL application installed on 2 nodes.
• Total replicated data limited to a few terabytes — beyond this, resynchronization time becomes significant.
• Minimum 1Gb/s interconnect between nodes (10Gb/s recommended for faster resynchronization).
• IP failover requires both nodes on the same LAN or extended LAN (L2) — routed L3 networks not supported (except with cloud load balancers).

1. Launch the SafeKit console

• Launch the web console in a browser on one cluster node by connecting to http://localhost:9010.
• Enter admin as user name and the password defined during installation.

You can also run the console in a browser on a workstation external to the cluster.

The configuration of SafeKit is done on both nodes from a single browser.

To secure the web console, see 11. Securing the SafeKit web service in the User's Guide.

2. Configure node addresses

• Enter the node IP addresses, press the Tab key to check connectivity and fill node names. If either node1 or node2 has a red color, check connectivity of the browser to both nodes and check firewall on both nodes for troubleshooting.
• Then, click on Save and apply to save the configuration.
• Check the Success ✅ message on both nodes.
• If the configuration Failure ❌ occurs on one node, open the ▼ accordion for that node and review the messages. Note that you can use the SafeKit AI 🤖 for assistance.

If you want, you can add a new `LAN and nodes` ( first ➕) to create a second heartbeat and a dedicated replication network.

If you click on `Advanced configuration`, the `cluster.xml` file is displayed. This file is automatically populated by the console and deployed on the nodes.

3. Select a module

• In New module, click on the postgresql.safe module.

In the blue banner at the top, 🛜 `node1` represents the console connection node. This node relays requests to `node2` when required.

The console looks for `xxx.safe` in the `Application_Modules/generic/` directory on the connection node (`node1`) if you placed a module there during installation.

4. Configure the module

• In Module startup at boot, choose an automatic start of the module at boot without delay.
• In Macros / SERVICES, enter the service names of your application, in the startup order, separated by commas. See this screenshot for a visual example of Milestone XProtect services 🖼️.
• In Heartbeat networks, you should have a single heartbeat network on which the replication is made. If you have added a private LAN at step 2, then you can configure two heartbeats with the replication flow on the private LAN.
• In Virtual IP addresses, enter a virtual IP address. A virtual IP address is a standard IP address in the same IP network (same subnet) as the IP addresses of both nodes.
  Application clients must be configured with the virtual IP address (or the DNS name associated with the virtual IP address).
  The virtual IP address is automatically switched in the event of a failure.
• In Replicated directories, set the paths of directories to replicate. Check that they exist on both nodes and contain the application data.
  Data and log replication are essential for a database.
  You can create additional replicated directories as required.
  See this screenshot for a visual example of Milestone XProtect SQL replication 🖼️.
• In Checkers, you will be able to configure checkers if needed, such as process monitoring, custom checkers, TCP, ping, or split-brain checkers.
  For example, if a process name is displayed in Monitored processes/services, it will be monitored with a restart action in case of failure. Configuring a wrong process name will cause the module to stop right after its start.

If you click on `Advanced configuration`, the `userconfig.xml` file is displayed. This file is automatically populated by the console and deployed on the nodes with the restart scripts.

In a cloud deployment, you do not need to configure a virtual IP address in SafeKit. The virtual IP is managed at the cloud load balancer level.

When you apply the SafeKit configuration, the `startup type` of services is automatically set to `Manual` in Windows services. This ensures that services do not start automatically when the system boots, but are instead started only when the module itself is started (the same applies on PostgreSQL).

5. Edit scripts (optional)

• This step is optional and can be skipped in most cases, as the restart scripts are already pre-configured to restart services defined in the previous step.
• So, click directly on Next step.
• start_prim.ps1 starts all services in the order specified in the SERVICES list, while stop_prim.ps1 stops all services in the reverse order.
• Additionally, start_prim.ps1 checks the startup of each service and stops the module if any service fails to start correctly.

6. Communication encryption (optional)

• Keep encryption of communication between nodes.

7. Save and apply

• Save and apply the configuration and scripts on both nodes.

8. Verify successful configuration

• Check the Success ✅ message on both nodes and click on Monitor modules.
• If the configuration Failure ❌ occurs on one node, open the ▼ accordion for that node and review the messages. Note that you can use the SafeKit AI 🤖 for assistance.

On PostgreSQL, you may get an error at this step if the replicated directories are mount points. See SK-0030 to solve the problem.

9. Start the node with up-to-date data

• If node 1 has the up-to-date replicated directories, select it and ⋯ Force start ▶ As primary.

When node 2 will be started, all data will be copied from node 1 to node 2.

If you make the wrong choice, you run the risk of synchronizing outdated data on both nodes.

It is also assumed that the PostgreSQL application is stopped on node 1 so that SafeKit installs the replication mechanisms and then starts the application in the `start_prim` script.

Use `Start` for subsequent starts: SafeKit retains the most up-to-date server. Starting `As primary` is a special start-up the first time or during exceptional operations.

10. Wait for the transition to ALONE (green)

• Node 1 should reach the ALONE (green) state, which means that the virtual IP is set and that the start_prim script has been executed on node 1.

If ALONE (green) is not reached or if the application is not started, analyze why with the module log 🖼️ of node 1.

• Click the 🔍 log icon of node1 to open the module log and look for error messages such as a checker detecting an error and stopping the module.
• Click on start_prim in the log: output messages of the script are displayed on the right and errors can be detected such as a service incorrectly started.
• Use the SafeKit AI 🤖 for assistance with log messages.

If the cluster is in `WAIT (red) not uptodate, STOP (red) not uptodate` state, stop the WAIT node and force its start as primary 🖼️.

11. Start node 2

• ⋯ Start node 2 with its contextual menu.
• Wait for the SECOND (green) state.

Node 2 stays in the SECOND (orange) state while resynchronizing the replicated directories (copy from node 1 to node 2).

This may take a while depending on the size of files to resynchronize in replicated directories and the network bandwidth.

To see the progress of the copy, see the module log 🖼️ and the replication resources 🖼️ of node 2. Use the SafeKit AI 🤖 for assistance with log messages.

12. Verify that the cluster is operational

• Check that the cluster is green/green with PostgreSQL services running on the PRIM node and not running on the SECOND node.

Only changes inside files are replicated in real time in this state.

Components that are clients of PostgreSQL services must be configured with the virtual IP address. The configuration can be done with a DNS name (if a DNS name has been created and associated with the virtual IP address).

13. Testing

• Stop the PRIM node by scrolling down its contextual menu and clicking Stop.
• Verify that there is a failover on the SECOND node which should become ALONE (green).
• And with Microsoft Management Console (MMC) on Windows or with command lines on PostgreSQL, check the failover of PostgreSQL services (stopped on node 1 in the stop_prim script and started on node 2 in the start_prim script).

If ALONE (green) is not reached on node2 or if the application is not started, analyze why with the module log 🖼️ of node 2.

• Click the 🔍 log icon of node2 to open the module log and look for error messages such as a checker detecting an error and stopping the module.
• Click on start_prim in the log: output messages of the script are displayed on the right and errors can be detected such as a service incorrectly started.
• Use the SafeKit AI 🤖 for assistance with log messages.

If everything is okay, initiate a start on node1, which will resynchronize the replicated directories from node2.

If things go wrong, stop node2 and force the start as primary 🖼️ of node1, which will restart with its locally healthy data at the time of the stop.

Find more details, along with videos, in the SafeKit Online Training.

14. Support

• For getting support, take 2 SafeKit Snapshots (2 .zip files), one for each node.

More information on the support here.

Demonstration of the SafeKit mirror solution

SafeKit Video: Application-Level Clustering (8:47)

In this video, discover how SafeKit implements a mirror HA cluster without the complexity of a SAN. While this demonstration uses Microsoft SQL Server, the solution works identically for other databases and applications.

Chapters

1. 2 nodes with SQL Server (0:32)
2. Configure the cluster and the mirror.safe module (3:58)
3. Start and test SQL replication, migration, failover on crash (4:17)

Step-by-Step Implementation

• Read the guide on setting up a mirrored cluster on Windows
• Read the guide on setting up a mirrored cluster on Linux

&amp;amp;amp;amp;amp;lt;br /&amp;amp;amp;amp;amp;gt;

🔍 SafeKit High Availability Navigation Hub

Explore SafeKit: Features, technical videos, documentation, and free trial