27.2. Setting Up a BiHA Cluster #

The BiHA cluster is set up by means of the bihactl utility. There are several scenarios of using the bihactl utility:

Before you start setting up the BiHA cluster, carefully read Prerequisites and Considerations.

27.2.1. Prerequisites and Considerations #

Before you begin to set up the BiHA cluster, read the following information and perform the required actions if needed:

  • Ensure network connectivity between all nodes of your future BiHA cluster.

    If network isolation is required, when both the control channel and WAL transmission operate in one network, while client sessions with the database operate in another network, configure the BiHA cluster as follows:

    • Use host names resolving to IP addresses of the network for the control channel and WAL.

    • Add the IP address for client connections to the listen_addresses configuration parameter.

  • BiHA creates a number of auxiliary files and configures some Postgres Pro configuration parameters to ensure proper operation. For more information, see Postgres Pro Configuration.

  • To avoid any biha-background-worker issues related to system time settings on cluster nodes, configure time synchronization on all nodes.

  • It is not recommended to execute the bihactl commands in the PGDATA directory. The bihactl utility may create the biha_init.log and biha_add.log files in the directory where it is executed. However, the target PGDATA directory must be empty for proper execution of the bihactl commands.

  • You can configure user authentication using the password file, the environment variable, or SSL. For more information, see User Authentication Configuration.

  • During operation, BiHA uses its own mechanism to modify the Postgres Pro configuration dynamically. Some Postgres Pro parameters are managed by biha and cannot be modified using ALTER SYSTEM, as they are essential for biha operation. These parameters are the following:

    For more information, see Postgres Pro Configuration.

  • When using BiHA, it is not recommended to manually modify any configuration parameters related to WAL application as this may cause unexpected cluster behavior. For example, modifying the recovery_min_apply_delay value on a follower may interrupt its WAL receipt, even though the follower will be displayed as online and in the FOLLOWER state.

  • It is not recommended to use restore_command with the BiHA cluster. For more information about recovery from a backup, see Section 27.3.11.

  • In some operating systems, user session management may be handled by systemd. In this case, if your server is started using pg_ctl and managed remotely, be aware that all background processes initiated within an SSH session will be terminated by the systemd daemon when the session ends. To avoid such behavior, you can do one of the following:

    • Use the postgrespro-ent-16 systemd unit file to start the DBMS server on the cluster node.

    • Modify the configuration of the user session management service called systemd-logind in the /etc/systemd/logind.conf file, specifically, set the KillUserProcesses parameter to no.

27.2.2. User Authentication Configuration #

The biha_replication_user role is created during cluster initialization and has privileges to perform replication in a BiHA cluster. Before you start initializing your cluster, you must choose the authentication method for the biha_replication_user role and make the required preparations based on your choice.

Important

If you do not configure authentication using any of the methods listed below, you will be unable to add followers to your BiHA cluster.

BiHA supports the following ways to configure the biha_replication_user authentication:

27.2.2.1. Configuring SSL for User Authentication #

BiHA supports SSL for the biha_replication_user authentication that you can configure when initializing your cluster using bihactl init. For more information about protecting connections using SSL, see Section 18.9.

Note

  • SSL authentication takes the highest priority. When SSL is used, the password file is ignored.

  • When SSL is used, the biha_replication_user role is created without password.

To configure SSL authentication:

  1. Prepare the following certificates in the PEM format:

    • root CA certificate

    • server certificate and key for user validation

    • certificate and key for biha_replication_user authentication

    • certificate and key for the superuser authentication (if required)

      Note

      • The bihactl utility does not create any authentication rules in pg_hba*.conf.

      • If specified, the certificate and key for the superuser are only used when you convert your existing database server using the bihactl init command with the --convert option. Since the bihactl utility calls psql to connect as the superuser over a local Unix socket, it is recommended, if possible, to avoid specifying SSL options for the superuser authentication. In that case SSL is only used for the biha_replication_user authentication.

  2. Place the files mentioned above on all nodes of your BiHA cluster.

    It is recommended that the certificate files are located in the same directory on all cluster nodes.

  3. Execute the bihactl init command with the required options. For example:

    bihactl init \
        --biha-node-id=1 \
        --host=node_1 \
        --port=node_port_number \
        --biha-port=biha_port_number \
        --nquorum=number_of_nodes \
        --server-cert=/path/to/server.crt \
        --server-key=/path/to/server.key \
        --user-biha-cert=/path/to/client.crt \
        --user-biha-key=/path/to/client.key \
        --root-cert=/path/to/ca.crt \
        --biha-ssl-mode=verify-full \
        --postgres-ssl-mode=verify-full \
        --pgdata=leader_PGDATA_directory
    
  4. When initialization is finished, check the pg_hba.biha.conf file to ensure that the cert authentication method is used.

If required, you can later modify some SSL-related values using special configuration parameters.

27.2.2.2. Configuring Authentication via Environment Variables #

To avoid storing the biha_replication_user password in the pgpass password file, you can use PGPASSWORD and BIHA_REPLICATION_PASSWORD environment variables. The password must be the same in both environment variables and on all nodes of your BiHA cluster.

When you initialize the cluster using the bihactl init command, the bihactl utility takes the password from BIHA_REPLICATION_PASSWORD.

When you add nodes to the cluster using the bihactl add command, the bihactl utility takes the password from PGPASSWORD.

27.2.2.3. Configuring Authentication via the Password File #

To use authentication via the pgpass password file, you must specify the password for the biha_replication_user role in password files of all nodes of your BiHA cluster. When you initialize the cluster using the bihactl init command and then add nodes using the add command, the bihactl utility takes the password from the password file.

Important

To ensure connection between the leader and followers, the password of the biha_replication_user role must be the same on all nodes of the BiHA cluster.

You can specify the password using one of the following approaches:

  • The secure and recommended approach is to add a separate line for each node:

    echo 'hostname:port:biha_db:biha_replication_user:password' >> ~/.pgpass
    
    echo 'hostname:port:replication:biha_replication_user:password' >> ~/.pgpass
    
  • The simple approach is to add a single line for all nodes:

    echo '*:*:*:biha_replication_user:password' >> ~/.pgpass
    

27.2.3. Setting Up a BiHA Cluster from Scratch #

To set up a BiHA cluster from scratch, perform the following procedures.

Prerequisites

  1. On all nodes of your future cluster, install the postgrespro-ent-16-contrib package. Do not create a database instance.

  2. Ensure that you execute the bihactl command as the same user that starts the Postgres Pro Enterprise server.

    For example, if you start the server as user postgres, the bihactl command must also be run by user postgres.

  3. If you plan to use pg_probackup with biha, install the pg-probackup-ent-16 package.

  4. Choose the authentication method for the biha_replication_user role and make required preparations. For more information, see User Authentication Configuration.

Initializing the cluster

Use the bihactl init command to initialize the cluster and create the leader node.

  1. Execute the bihactl init command with the necessary options:

    bihactl init \
        --biha-node-id=1 \
        --host=node_1 \
        --port=node_port_number \
        --biha-port=biha_port_number \
        --nquorum=number_of_nodes \
        --pgdata=leader_PGDATA_directory
    

    At this stage, you can also enable SSL for cluster service connections, configure quorum-based synchronous replication, and configure SSL authentication for the biha_replication_user role.

  2. (Optional) Specify the password for the biha_replication_user role and re-enter the password for verification.

    Note

    bihactl prompts you to specify the password for the biha_replication_user role only if you have not configured any supported authentication methods. If so, the leader is created, however, adding followers is not available. To create a full-featured BiHA cluster, configure user authentication.

    The initdb utility is accessed, postgresql.conf and pg_hba.conf files are modified.

    When initializing the BiHA cluster, the magic string is generated. For more information on how to use the magic string, see Section 27.2.8.

  3. Start the DBMS using pg_ctl:

    pg_ctl start -D leader_PGDATA_directory -l leader_log_file
    
  4. Check the node status in the biha.status_v view:

    SELECT * FROM biha.status_v;
    

Adding a follower node

Note

You must add nodes one by one. Do not add a new node if creation of a previously added node has not been completed yet and the node is in the CSTATE_FORMING state. Otherwise, you may encounter the following error:

          WARNING:  aborting backup due to backend exiting before pg_backup_stop was
          called
      
  1. Ensure that the leader node is in the LEADER_RO or LEADER_RW state.

  2. Ensure that you configure the biha_replication_user role authentication using one of the supported methods.

  3. Execute the bihactl add command with the necessary options:

    bihactl add \
        --biha-node-id=2 \
        --host=node_2 \
        --port=node_port_number \
        --biha-port=biha_port_number \
        --use-leader "host=leader_host port=leader_port biha-port=leader_biha_port" \
        --pgdata=follower_PGDATA_directory
    

    A backup of the leader node is created by means of pg_basebackup or pg_probackup depending on the value set in the --backup-method option. Besides, postgresql.conf and pg_hba.conf files are modified.

    Note

    During this process, all files are copied from the leader to the new node. The larger the database size, the longer it takes to add the follower.

    You can also add the leader node connection data using the magic string. For more information on how to use the magic string, see Section 27.2.8.

  4. Start the DBMS using pg_ctl:

    pg_ctl start -D follower_PGDATA_directory -l follower_log_file
    
  5. Check the node status in the biha.status_v view:

    SELECT * FROM biha.status_v;
    

27.2.4. Setting Up a BiHA Cluster from the Existing Cluster with Streaming Replication #

Convert your existing Postgres Pro Enterprise 16 cluster with streaming replication and a configured database instance into a BiHA cluster. After conversion, the primary node of the existing cluster becomes the leader node, and standby nodes become follower nodes.

Prerequisites

  1. Ensure that you execute the bihactl command as the same user that starts the Postgres Pro Enterprise server.

    For example, if you start the server as user postgres, the bihactl command must also be run by user postgres.

  2. If you plan to use pg_probackup with biha, install the pg-probackup-ent-16 package.

  3. Choose the authentication method for the biha_replication_user role and make required preparations. For more information, see User Authentication Configuration.

Converting the existing primary node into the leader node

  1. Stop the existing primary node using pg_ctl:

    pg_ctl stop -D primary_PGDATA_directory
    
  2. Execute the bihactl init command with the --convert option:

    bihactl init --convert \
        --biha-node-id=1 \
        --host=node_1 \
        --port=PostgresPro_port \
        --biha-port=biha_port_number \
        --nquorum=number_of_nodes \
        --pgdata=leader_PGDATA_directory
    

    At this stage, you can also enable SSL for cluster service connections, configure quorum-based synchronous replication, and configure SSL authentication for the biha_replication_user role.

    When converting the cluster, the magic string is generated. For more information on how to use the magic string, see Section 27.2.8.

  3. (Optional) Specify the password for the biha_replication_user role and re-enter the password for verification.

    Note

    bihactl prompts you to specify the password for the biha_replication_user role only if you have not configured any supported authentication methods. If so, the leader is created, however, adding followers is not available. To create a full-featured BiHA cluster, configure user authentication.

  4. Start the DBMS using pg_ctl:

    pg_ctl start -D leader_PGDATA_directory -l leader_log_file
    
  5. Check the node status in the biha.status_v view:

    SELECT * FROM biha.status_v;
    

Converting the existing standby node into the follower node

  1. Ensure that you configure the biha_replication_user role authentication using one of the supported methods.

  2. Stop the existing standby node using pg_ctl:

    pg_ctl stop -D standby_PGDATA_directory
    
  3. Execute the bihactl add command with the --convert-standby option:

    bihactl add --convert-standby \
      --biha-node-id=2 \
      --host=node_2 \
      --port=PostgresPro_port \
      --biha-port=5435 \
      --use-leader "host=leader_host port=leader_port biha-port=leader_biha_port" \
      --pgdata=follower_PGDATA_directory
    

    When converting an existing standby node into the follower node, biha creates the follower_PGDATA_directory/pg_biha/biha.conf and follower_PGDATA_directory/pg_biha/biha.state files required for the node to be connected to the cluster and modifies postgresql.conf and pg_hba.conf.

    You can also add the leader node connection data using the magic string. For more information on how to use the magic string, see Section 27.2.8.

  4. Start the DBMS using pg_ctl:

    pg_ctl start -D follower_PGDATA_directory -l follower_log_file
    
  5. Check the node status in the biha.status_v view:

    SELECT * FROM biha.status_v;
    

27.2.5. Setting Up a BiHA Cluster from the Existing Database Server #

If your existing Postgres Pro Enterprise 16 server with a configured database has only one node, you can convert it into the leader and then add more nodes to your BiHA cluster using the bihactl add command.

Prerequisites

  1. Ensure that you execute the bihactl command as the same user that starts the Postgres Pro Enterprise server.

    For example, if you start the server as user postgres, the bihactl command must also be run by user postgres.

  2. If you plan to use pg_probackup with biha, install the pg-probackup-ent-16 package.

  3. Choose the authentication method for the biha_replication_user role and make required preparations. For more information, see User Authentication Configuration.

Converting the existing node into the leader node

  1. Stop the existing node using pg_ctl:

    pg_ctl stop -D server_PGDATA_directory
    
  2. Execute the bihactl init command with the --convert option:

    bihactl init --convert \
        --biha-node-id=1 \
        --host=node_1 \
        --port=PostgresPro_port \
        --biha-port=biha_port_number \
        --nquorum=number_of_nodes \
        --pgdata=leader_PGDATA_directory
    

    At this stage, you can also enable SSL for cluster service connections, configure quorum-based synchronous replication, and configure SSL authentication for the biha_replication_user role.

    The postgresql.conf and pg_hba.conf files are modified.

    When converting the node, the magic string is generated. For more information on how to use the magic string, see Section 27.2.8.

  3. (Optional) Specify the password for the biha_replication_user role and re-enter the password for verification.

    Note

    bihactl prompts you to specify the password for the biha_replication_user role only if you have not configured any supported authentication methods. If so, the leader is created, however, adding followers is not available. To create a full-featured BiHA cluster, configure user authentication.

  4. Start the DBMS using pg_ctl:

    pg_ctl start -D leader_PGDATA_directory -l leader_log_file
    
  5. Check the node status in the biha.status_v view:

    SELECT * FROM biha.status_v;
    

Adding the follower node

  1. Ensure that the leader node is in the LEADER_RO or LEADER_RW state.

  2. Ensure that you configure the biha_replication_user role authentication using one of the supported methods.

  3. Execute the bihactl add command with the necessary options:

     bihactl add \
         --biha-node-id=2 \
         --host=node_2 \
         --port=node_port_number \
         --biha-port=biha_port_number \
         --use-leader "host=leader_host port=leader_port biha-port=leader_biha_port" \
         --pgdata=follower_PGDATA_directory
     

    A backup of the leader node is created by means of pg_basebackup or pg_probackup depending on the value set in the --backup-method option. Besides, postgresql.conf and pg_hba.conf files are modified.

    You can also add the leader node connection data using the magic string. For more information on how to use the magic string, see Section 27.2.8.

  4. Start the DBMS using pg_ctl:

    pg_ctl start -D follower_PGDATA_directory -l follower_log_file
    
  5. Check the node status in the biha.status_v view:

    SELECT * FROM biha.status_v;
    

27.2.6. Setting Up the Referee Node in the BiHA Cluster #

The referee node participates in elections and helps to manage split-brain issues.

Note

  • You can use only pg_basebackup when adding the referee node to your cluster.

  • By default, only the biha_db database and system tables are copied to the referee node. The postgres database and user data are not copied. If you need the postgres database on your referee node, specify the --referee-with-postgres-db option.

To set up a referee node:

  1. Execute the bihactl add command with the relevant value of the --mode option:

    bihactl add \
        --biha-node-id=3 \
        --host=node_3 \
        --port=node_port_number \
        --biha-port=biha_port_number \
        --use-leader "host=leader_host port=leader_port biha-port=leader_biha_port" \
        --pgdata=referee_PGDATA_directory \
        --mode=referee
    

    or

    bihactl add \
        --biha-node-id=3 \
        --host=node_3 \
        --port=node_port_number \
        --biha-port=biha_port_number \
        --use-leader "host=leader_host port=leader_port biha-port=leader_biha_port" \
        --pgdata=referee_PGDATA_directory \
        --mode=referee_with_wal
    

  2. Check the shared_buffers configuration parameter of the referee.

    If it is too high, set the value to the default 128 MB.

  3. Using pg_ctl, start the Postgres Pro instance where you have set up the referee:

    pg_ctl start -D referee_PGDATA_directory
    
  4. Check the node status in the biha.status_v view:

    SELECT * FROM biha.status_v;
    

27.2.7. Configuring SSL for Service Connections (Optional) #

When initializing your BiHA cluster, you can enable SSL for the cluster service connections by means of the --use-ssl option. To enable or disable SSL in the initialized BiHA cluster, use the procedure described in Managing SSL for Service Connections.

Preparing a Certificate and Key Pair

  • Using the OpenSSL utility, generate a certificate and a private key and save them in the /PGDATA/pg_biha directory on each cluster node:

    openssl req -x509 -newkey rsa:4096 -keyout path_to_key -out path_to_certificate -sha256 -days period_of_validity -nodes -subj "/CN=certificate_domain"
    

    For example:

    openssl req -x509 -newkey rsa:4096 -keyout /PGDATA/pg_biha/biha_priv_key.pem -out /PGDATA/pg_biha/biha_pub_cert.pem -sha256 -days 365 -nodes -subj "/CN=localhost"
    

    The following files are generated:

    • biha_priv_key.pem is a private key with read and write user access (0600)

    • biha_pub_cert.pem is a self-signed certificate issued for the specified time period and domain

    Important

    Ensure that you use the above mentioned names for your certificate and private key files as BiHA searches for the files by these names.

Enabling SSL

  1. When you initialize a BiHA cluster with bihactl init, specify the --use-ssl option:

    bihactl init \
        --biha-node-id=1 \
        --host=node_1 \
        --port=node_port_number \
        --biha-port=biha_port_number \
        --nquorum=number_of_nodes \
        --pgdata=leader_PGDATA_directory
        --use-ssl
    
  2. To ensure that SSL is enabled, when your BiHA cluster is set up, check that the biha.use_ssl parameter is set to true using the SHOW command:

    SHOW biha.use_ssl;
    

27.2.8. Using the Magic String (Optional) #

The magic string is a special string generated automatically when you initiate a BiHA cluster. The magic string is used in the BiHA cluster setup scripts. It contains the data needed to connect follower nodes to the leader node.

You can use magic string to avoid entering the leader node connection data manually when adding follower nodes.

Here is an example of how to use the magic string:

  1. When initializing the cluster, redirect the bihactl output to a file:

    bihactl init \
       --biha-node-id=1 \
       --host=node_1 \
       --port=node_port_number \
       --biha-port=biha_port_number \
       --nquorum=number_of_nodes \
       --pgdata=leader_PGDATA_directory > /tmp/magic-file
    
  2. When adding a follower node, do the following:

    1. Set up an environment variable:

      export MAGIC_STRING="$(cat /tmp/magic-file)"
      
    2. Add --magic-string as a bihactl add option:

      bihactl add \
       --biha-node-id=2 \
       --host=node_2 \
       --port=node_port_number \
       --biha-port=biha_port_number \
       --magic-string=$MAGIC_STRING \
       --pgdata=follower_PGDATA_directory
      

    The follower node will now use the encoded data from the magic string to connect to the leader node.