Server Configuration Considerations

PostgreSQL Server Configuration Considerations

Before you install and configure your PostgreSQL database clusters, it is important to understand how to configure them. It is also important to understand the concepts of Active/Standby and Active/Active configurations, and how they can be set up in a PostgreSQL configuration.

PostgreSQL Database Cluster Configuration Considerations

The SIOS Protection Suite for PostgreSQL uses Windows services for administration of the PostgreSQL database cluster. If a Windows Service does not already exist for the PostgreSQL database cluster, then one will be setup when the PostgreSQL hierarchy is created.

For SIOS Protection Suite to protect a PostgreSQL database cluster the following conditions must exist:

• The PostgreSQL Server must be running

• The PostgreSQL postmaster process must be running with the port option: -p port

• The PostgreSQL database cluster data directory must reside on a protected volume

• The PostgreSQL database cluster data directory, sub-directories, and all files must be accessible by the Windows Service account on all servers

The following configuration limitations currently exist in the kit:

• Does not automatically include IP resource instances as part of the hierarchy. If a user connects remotely, then an IP resource will need to be created and added as a child resource in the PostgreSQL hierarchy.

• Only the location of the database cluster data directory is taken into consideration when determining which volume resources need to be included as part of the resource hierarchy. If any database table space is not located on the same protected volume as the data directory, then the volume containing the table space will need to be protected and added as a child resource in the PostgreSQL hierarchy.

PostgreSQL Active/Standby Configuration

A configuration is Active/Standby when there is only one PostgreSQL database cluster, located on a shared or replicated volume. The PostgreSQL database cluster services run on only one system at a time. The servers are assigned priorities within SIOS Protection Suite which determine the order of failover for a particular hierarchy.

The figure below depicts a single PostgreSQL instance installed on a pair of servers. The instance contains one database cluster, PGSQL1 residing on a single volume.

When you create the PostgreSQL hierarchy within SIOS Protection Suite, you are asked to specify the PostgreSQL data directory (database cluster location). If remote connections to the database cluster will be made then a protected IP resource will need to be configured and added to the resource hierarchy. SIOS Protection Suite then reads the configuration data for that instance and pulls the associated volumes into the hierarchy.

Once the hierarchy is created, it will appear as follows in the LifeKeeper GUI.

Active/Standby Failover

In the event of failure, SIOS Protection Suite brings the PostgreSQL Server hierarchy In Service on the backup Server. PostgreSQL Server is started on the backup server and it takes over protection of the database cluster as depicted in the figure below.

PostgreSQL Active/Active Configuration

Multiple PostgreSQL Server database clusters can be configured on any of the servers using initdb. SIOS Protection Suite can protect the multiple PostgreSQL database clusters in what is called an Active/Active configuration. SIOS Protection Suite identifies each instance by the port used for connections.

Each database cluster is protected in a single SIOS Protection Suite hierarchy.

The figure below depicts two PostgreSQL database clusters: PGSQL1 and PGSQL2.

Notes:

• In this configuration Server1 is the primary server for the PGSQL1 database cluster and Server2 is the primary server for the PGSQL2 database cluster.

• Each server can be the primary and backup server for multiple instances.

• It would be possible for Server1 or Server2 to be the primary server for both database clusters.