PostgreSQL® Database Cluster

Redundancy - Database Cluster

The following images shows the PostgreSQL® redundancy structure.

Figure 1. PostgreSQL® Backend redundant Configuration

The configuration consists of the following elements:

  • A redundant pair of WinCC OA project servers: both WinCC OA servers host a PostgreSQL® Backend instance.
  • A redundant pair of database servers: Each database server hosts a PostgreSQL® application server instance and a pgClusterMonitor Python script. pgClusterMonitor is a service that controls a cluster of PostgreSQL® database with WAL (Write-Ahead-Logging) streaming replication.

Server Details:

  • One of the PostgreSQL® application servers works in master mode. The master database node accepts requests for reading/writing data and publishes replication for the standby PostgreSQL® application server. Master and Standby database nodes synchronize information.
  • All requests from PostgreSQL® backends are only routed to a PostgreSQL® application server with the role of master. Therefore, each instance of the PostgreSQL® backend has a configuration with two connection strings that refer the both PostgreSQL® application servers. PostgreSQL® backend uses the first connection string that successfully establishes a connection to the PostgreSQL® application server with the role of master.
  • PostgreSQL® backend monitors connection errors and tries to switch to another connection string to establish a connection.

Set up a Database Cluster

The PostgreSQL® Backend can work with the PostgreSQL® cluster. PostgreSQL® cluster for use with WinCC OA consists of two nodes that provide database redundancy. In the case of a PostgreSQL® cluster do not use the same nodes where WinCC OA is running but separate hosts for each database node of the cluster.

To set up the cluster, follow the instruction below. For the following steps, it is assumed that the master database has the IP address "192.168.0.101" and the standby database has the IP address "192.168.0.102".

On a Master Database Node

  • Run SQL script (for more information about these settings,see https://www.postgresql.org/docs/13/config-setting.html): 
    ALTER SYSTEM SET listen_addresses TO '*';
ALTER SYSTEM SET synchronous_commit TO 'remote_apply';
SELECT * FROM pg_create_physical_replication_slot('__slot');
ALTER SYSTEM SET synchronous_standby_names TO '*';
ALTER SYSTEM SET wal_level TO 'replica';
ALTER SYSTEM SET wal_log_hints TO 'on';
ALTER SYSTEM SET max_wal_senders TO '10';
ALTER SYSTEM SET wal_keep_size  TO '16MB';
ALTER SYSTEM SET hot_standby TO 'on';
  • Open the pg_hba.conf C:\Program Files\PostgreSQL\13\data\pg_hba.conf and add your server addresses to the file:
    Figure 2. Add the Server Addresses
  • Add authentication settings to pg_hba.conf (see https://www.postgresql.org/docs/13/auth-pg-hba-conf.html) to allow client applications to connect to the database and work with the replication.
  • Restart PostgreSQL® service:
     Windows task manager/services/postgresql-x64-13.

On a Standby Database Node

  • Stop the PostgreSQL® server.
  • Clear the PG_DATA directory.
  • Create the directories db, events, alerts, backups for tablespaces. The directories must be created using the same path as on the primary server. See chapter NGA - PostgreSQL® Server Installation.
  • Run the following command: 
    pg_basebackup -D "PG_DATA" -h ip_master -p port_master -X stream -c fast -U username -W -R

The different parts of the command are described in the following:

    • PG_DATA - PostgreSQL® data directory.
    • ip_master - IP address or hostname where the master DB is located.
    • port_master - Port of master DB.
    • username - Username with the replication role. This user must be able to connect to the master DB.
Note: The pg_basebackup must be run by the same user who is executing the PG service (to have all required permissions for the data).
  • Start the PostgreSQL® server in the Windows Taskmanager/services/postgresql-x64-13
Note: If you see the error message "Could not connect to server" or "No route to host" in the log viewer, check your firewall settings and the pg_hba.conf file.

PostgreSQL® Cluster with two Network Adapters

If you are using two network adapters on each of the clusters, proceed as follows:

  • Add the IP address of the second network adapter to pg_hba.conf. Both network adapters must be specified in the config.ini file - see the steps below: 
    Windows: C:/Program Files/PostgreSQL/13/data/pg_hda.conf
Linux: /var/lib/pgsql/data/pg_hda.conf
  • On the secondary server, run the "pg_basebackup" utility listing the available IP addresses, separated by commas. The "pg_basebackup" file is located in the PostgreSQL® installation: PostgreSQL/<version>/bin: 
    pg_basebackup -D "PG_DATA" -h ip_master1,ipmaster2 -p port_master -X stream -c fast -U username

  • In the configuration file of the monitoring utility ( wincc_oa/data/NGA/PostgreSQL/sql/Cluster Monitor Tool/config.ini ) list the required IP addresses in the [cluster] section, separated by commas, e.g.:

    [cluster] p1 = host=host1,host2 port=1111 dbname=winccoa user=postgres password=postgres sslmode=prefer sslcompression=1 krbsrvname=postgres target_session_attrs=any
Note: If the first host of the database connection (for database connection see chapter Database-specific Settings) becomes unavailable, it will cause a timeout during operations with the cluster node.