Configuring an Operations Database Replica Node

1. Prepare a database replica node following the same hardware requirements as those for the master nodes.
2. Configure the firewall.
3. Deploy a Operations database replica to the node. See below.

Deploying an Operations Database Replica

Important: Supported PostgreSQL versions are 11.x and later.

To create a Operations DB replica and set up a UI booster:

1. Create a PostgreSQL DB replica. Depending on your PostgreSQL deployment solution, you can use one of the following approaches:
   • for Azure Database for PostgreSQL
   • for an on-premise PostgreSQL DB deployment (Please see an example below)

2. Connect to the Master PostgreSQL server as the admin user with the help of a psql command. Create a read-only user that will be used by the UI booster to access the DB replica.

   CREATE ROLE <username> WITH ENCRYPTED PASSWORD '<some password>' LOGIN;

3. For the UI Booster to be able to check the DB Replica actuality state, create a function with the name func_stat_wal_receiver and the following body and grant the read-only user the capability to call the function:

   CREATE FUNCTION func_stat_wal_receiver() RETURNS SETOF pg_stat_wal_receiver as $$ select pid,
   status, receive_start_lsn, receive_start_tli, received_lsn, received_tli, last_msg_send_time,
   last_msg_receipt_time, latest_end_lsn, latest_end_time, cast('' as text) as slot_name,
   cast('' as text) as sender_host, -1 as sender_port, cast('' as text) as conninfo
   from pg_stat_wal_receiver; $$ LANGUAGE sql SECURITY DEFINER;
    
   GRANT EXECUTE ON FUNCTION func_stat_wal_receiver() to <username>;

4. Allow the read-only user to perform select queries on the following APS tables:

   GRANT SELECT ON TABLE aps_resource, aps_property_value, aps_resource_link, aps_security_link, aps_application,
   aps_property_info, aps_package, aps_relation_info, aps_relation_types, aps_type_info, aps_type_schema,
   aps_type_inheritance, aps_package_series, aps_package_service, aps_property_enum_info,
   aps_type_info_to_package, aps_operation_param, aps_operation_info TO <username>;

5. Connect the UI node to the DB Replica node.

   Note: While installing the systemdb-slave-client ppm package on a standalone UI node, or connecting to the DB Replica node from the UI cluster using the Connect to DBR Host button, specify the DB Replica host, DB name, and read-only user login and password. When using a cloud DB solution, to ensure proper authentication, add the domain part to the user name.

Example: An On-premise DB Replica Deployment

1. On the DB Master node:

   1. Create a replication user:

      CREATE ROLE replication WITH REPLICATION ENCRYPTED PASSWORD '<PASSWD>' LOGIN CONNECTION LIMIT 8;

   2. Allow the replication user to connect to the Master DB by adding the following line to ~postgres/11/data/pg_hba.conf:

      host replication replication <Replica IP>/32 md5

   3. Set the timezone: Open /var/lib/pgsql/11/data/postgresql.conf for editing and set the timezone and log_timezone parameters to the necessary timezone. For example: 'UTC', 'Europe/Moscow', and so on.

      Note: If the timezone or log_timezone parameter has the 'Host' value, the PostgeSQL service will not start.

   4. Allow replication: In /var/lib/pgsql/11/data/postgresql.conf, set the following values:

      wal_level = replica
      max_wal_senders = 16

   5. Reload the PostgreSQL service:

      systemctl reload postgresql-11

2. On the DB Replica node:

   1. To add PA Central YUM repo, create a file with the name /etc/yum.repos.d/pa-central.repo with the following content:

      [pa-central-repo]
      name=PA Central YUM repo for RHEL $releasever
      baseurl=http://download.automation.odin.com/oa/6.3/repo/RHEL/$releasever/
      gpgcheck=0

   2. Install PostgreSQL:

      yum install -y postgresql11-server postgresql11-contrib

   3. Delete the data directory:

      Note: The data directory will be recreated during the next step.

      rm -rf /var/lib/pgsql/11/data/

   4. Enable database replication:

      su - postgres -c "PGPASSWORD=<replication user passwd> /usr/pgsql-11/bin/pg_basebackup -X stream --host=<OA DB IP> --port=5432 --pgdata=/var/lib/pgsql/11/data/ --username=replication --write-recovery-conf --checkpoint=fast"

   5. Open the file /var/lib/pgsql/11/data/postgresql.conf for editing. Set the following parameter:

      hot_standby = on

   6. Restart the PostgreSQL service.