Configuring appliance database server for replication in vCAC or vRA

Products

• VMware Aria Suite

Issue/Introduction

This article provides advice on configuring the databases internal to VMware vRealize Automation for streaming replication. 

Symptoms:

VMware vRealize Automation Center documentation recommended the utilization of an external instance of VMware vFabric Postgres when setting up a high availability (HA) environment. However, since the release of VMware vRealize Automation standalone, VMware vFabric Postgres is End Of Availability and no longer available as a standalone product. To address customers needs, VMware developed a way to utilize the database instance located in the VMware vRealize Automation appliance in a high availability (HA) mode, without having to incur additional licensing. 

Environment

VMware vRealize Automation 7.6

Resolution

Prerequisites

• The appliance is freshly deployed and no configuration is performed.
• The replication channel is not encrypted.
• A DNS Entry is created, for example: dbCluster.domain.local.
• (Optional) An IP address is allocated for the Load Balancer.
• Two VMware vRealize Automation appliances, freshly deployed and resolvable through DNS.
• Administrator access to VMware vSphere vCenter, in order to add new disks to the vRealize Automation Appliance.

Configuring the database VIP

Note: For additional options to configure the database VIP, see:

• Appendix A - DNS entry
• Appendix B - F5

To configure database VIP:

Note: Perform these steps on both vRealize Automation appliances.

1. Perform a graceful power off of the appliance using Shut down guest.
2. Add a 20 GB disk through VMware vSphere Server. For more information, see the Add a New Hard Disk to a Virtual Machine in vSphere Web Client section in the Add a New Hard Disk to a Virtual Machine.
3. Power on the appliance.
4. Verify that SSH is enabled:
   1. Access the VAMI using the URL https://appliance:5480.
   2. Click Admin.
   3. Verify if the SSH service enabled and Administrator SSH login enabled options are selected.
   4. If the options are not selected, ensure to select them and click Save Settings.
       
5. Copy the 2108923_dbCluster.zip file attached to this article to the appliance.
6. Connect to the appliance using SSH as root.
7. Extract the tar file by running this command:
   
   # tar xvf 2108923_dbCluster.tar
    
8. Locate the disk added in Step 2 by running this command::
   
   # parted -l
   ...
   Error:
   /dev/sdd: unrecognized disk label
   Sector size (logical/physical): 512B/512B
   
   Note: For a fresh VMware vRealize Automation 6.2 appliance deployment, this should be /dev/sdd. This value varies depending on the version of VMware vRealize Automation appliance deployed.
    
9. Run the configureDisk.sh script to configure the disk added in Step 2:
   
   # ./configureDisk.sh /dev/sdd
   ...
   Ownership changed successfully
   WAL Archive disk configured successfully
    
10. Run the pgClusterSetup.sh script to prepare the appliance database for clustering:
    
    ./pgClusterSetup.sh [-d] db_fqdn [-w] db_pass [-r] replication_password [-p] postgres_password
    [-d] Database load balancer fully qualified domain name
    [-w] Database password (will set password to this value)
    [-r] Replication password (Optional: will use Database password if not set)
    [-p] Postgres password (Optional: will use Database password if not set)
    
    Note: Change the password from changeMe!
    
    # ./pgClusterSetup.sh -d dbCluster.domain.local -w changeMe1! -r changeMe1! -p changeMe1!
    ...
    Updating vRealize Automation to utilize database cluster fully qualified domain name
    Finished

Configuring the database replication on appliance B

1. Connect to the appliance B using SSH as root.
2. Configure replication as user postgres:
   
   ./run_as_replica –h Primary Appliance -b -W -U replicate
   [-U] The user who will perform replication. For the purpose of this KB this user is replicate
   [-W] Prompt for the password of the user performing replication
   [-b] Take a base backup from the primary. This option destroys the current contents of the data directory
   [-h] Hostname of the primary database server. Port 5432 is assumed
   
   # su – postgres
   ./opt/vmware/vpostgres/current/share/run_as_replica -h app1.domain.local -b -W -U replicate
    
   1. Enter the replicate users password when prompted.
   2. Type yes after verifying the thumbprint of the primary machine.
   3. Enter the postgres users password.
   4. Type yes when prompted with:
      
      Type yes to enable WAL archiving on primary
       
   5. Type yes when prompted with:
      
      WARNING: the base backup operation will replace the current contents of the data directory. Please confirm by typing yes:
3. Peform the steps in Appendix D below to validate replication.
   
   Note: Appliance A is the primary and appliance B is the replica.

Performing a test failover (appliance A to appliance B)

1. Connect to appliance A using SSH as root.
2. Stop the vpostgres service by running this command:
   
   # service vpostgres stop
   Stopping VMware vPostgres: Last login: Mon Apr 27 19:49:26 UTC 2015 on pts/0
   ok
    
3. Connect to appliance B using SSH as root.
4. Promote the replica database to primary as the postgres user by running these commands:
   
   # su – postgres
   ./opt/vmware/vpostgres/current/share/promote_replica_to_primary
   server promoting
    
5. SSH into appliance A as root.
6. Configure database replication as user postgres by running these commands:
   
   # su – postgres
   ./opt/vmware/vpostgres/current/share/run_as_replica -h app2.domain.local -b -W -U replicate
    
   1. Enter the replicate users password when prompted.
   2. Type yes after verifying the thumbprint of the primary machine when prompted.
   3. Enter the postgres users password when prompted.
   4. Type yes when prompted with:
      
      WARNING: the base backup operation will replace the current contents of the data directory. Please confirm by typing yes:
7. Perform the steps in Appendix D below to validate replica.
   
   Note: Appliance B is the primary and appliance A is the replica.

Perform a test failback (appliance B to appliance A)

1. Connect to appliance B using SSH as root.
2. Stop the vpostgres service by running this command:
   
   # service vpostgres stop
   Stopping VMware vPostgres: Last login: Mon Apr 27 19:49:26 UTC 2015 on pts/0
   ok
    
3. Connect to appliance A using SSH as root.
4. Promote the replicate database to primary as user postgres by running these commands:
   
   # su – postgres
   ./opt/vmware/vpostgres/current/share/promote_replica_to_primary
   server promoting
    
5. Connect to appliance B using SSH as root.
6. Configure database replication as user postgres by running these commands:
   
   # su – postgres
   ./opt/vmware/vpostgres/current/share/run_as_replica -h app1.domain.local -b -W -U replicate
    
   1. Enter the replicate users password when prompted.
   2. Type yes when prompted with:
      
      WARNING: the base backup operation will replace the current contents of the data directory. Please confirm by typing yes:
7. Execute the steps in Appendix D below to validate replication.
   
   Note: Appliance A is primary and appliance B is the replica.

Appendix A - Configuring a DNS entry for the database

The IP address of the DNS entry created should point to the primary appliance.

Steps to execute during failover

1. Modify the IP address of the DNS Entry to point at the new primary appliance.
2. On all VMware vRealize Automation appliances, log in as root and run the command:
   
   service network restart

Appendix B - Configuring F5 VIP for Database

 

To configure F5 VIP for the database:

Note: Use default values unless specified otherwise.

1. Create a pool which contains the two VMware vRealize Automation appliances as nodes using these settings:
   
   Health Monitor: gateway_icmp
   Service Port for Nodes:5432
    
2. Edit the Pool.
3. Select Members.
4. Disable the second node.
5. Create a Virtual Server using these settings:
   
   Destination: database_vip
   Service Port: 5432
   Source Address Translation: Auto Map
   Default Pool: pool_name
   
   Where pool_name is the name of the pool created in Step 1.

Steps to execute during failover

1. Edit the pool.
2. Disable the old primary node.
3. Enable the new primary node.

Appendix C - Performing failover

Note: VMware vRealize Automation will be offline from step 1 until step 7 has been completed.

1. If the appliance hosting the primary database is still functional, perform steps 2 and 3.
   If the appliance hosting the primary database is no longer accessible, go to step 4.
2. Connec to appliance A, containing the primary (primary) database, using SSH as root.
3. Stop the vpostgres service by running this command:
   
   # service vpostgres stop
   Stopping VMware vPostgres: Last login: Mon Apr 27 19:49:26 UTC 2015 on pts/0
   ok
    
4. Promote the replica database to primary database.
5. Connect to appliance B, containing the replica database, using SSH as root.
6. Promote the replica database to primary as user postgres by running these commands:
   
   # su – postgres
   ./opt/vmware/vpostgres/current/share/promote_replica_to_primary
   server promoting
    
7. Perform the Steps to execute during failover in Appendix A or Appendix B above, as relevant to your setup.
   
   Note: The DNS Entry or Load Balancer should forward traffic to the current primary, appliance B, at this point.
    
8. Rebuild the replica database on appliance A.
9. Connect to appliance A using SSH as root.
10. Configure database replication as user postgres using these commands:
    
    # su – postgres
    ./opt/vmware/vpostgres/current/share/run_as_replica -h app1.domain.local -b -W -U replicate
     
    1. Enter the replicate users password when prompted.
    2. Type yes when prompted with:
       
       WARNING: the base backup operation will replace the current contents of the data directory. Please confirm by typing yes:
11. Execute the steps in Appendix D below to validate replication.
    
    Note: Appliance B is the primary and appliance A is the replica.

Appendix D - Validate replication

1. Connect to the appliance with the primary database using SSH.
2. Validate if the WAL process is running. You should see the WAL process by running this command:
   
   # ps -ef |grep wal
   postgres 4784 4779 0 21:42 ? 00:00:00 postgres: wal writer process
   postgres 20901 4779 0 22:49 ? 00:00:00 postgres: wal sender process replicate 10.26.36.64(55887) streaming 0/70000B8
    
3. Validate if the primary is ready for read-write connections by running these commands:
   
   #su - postgres
   # ./opt/vmware/vpostgres/current/bin/psql vcac
   SELECT pg_is_in_recovery();
   
   You see output similar to:
   
   vcac=# SELECT pg_is_in_recovery();
   pg_is_in_recovery
   
   -------------------
   f
   
   (1 row)
    
4. Quit psql by running this command:
   
   \q to quit psql
    
5. Connect to the appliance with the replica database using SSH.
6. Validate if the replica is read only using these commands:
   
   #su - postgres
   # ./opt/vmware/vpostgres/current/bin/psql vcac
   SELECT pg_is_in_recovery();
   
   The command should return:
   
   vcac=# SELECT pg_is_in_recovery();
   pg_is_in_recovery
   
   -------------------
   
   t
   
   (1 row)
    
7. Quit psql by running this command:
   
   \q to quit psql

Appendix E - Script information

• configureDisk.sh
  
  Configures the disk which will store the WAL archives. Execute as root.

./configureDisk [-d] disk_name [-f]
[-d] Disk to be utilized for Write Ahead Logs (example /dev/sdd)
[-f] (Optional) Bypass confirmation prompt for disk formatting

Example: ./configureDisk.sh /dev/sdd

• pgClusterSetup.sh
  
  Performs common setup, required for all VMware vRealize Automation appliances in the cluster. Execute as root.

  ./pgClusterSetup.sh [-d] db_fqdn [-w] db_pass [-r] replication_password [-p] postgres_password
  [-d] Database load balancer fully qualified domain name
  [-D] Database virtual IP address (Optional: will create /etc/hosts entry)
  [-w] Database password (will set password to this value)
  [-r] Replication password (Optional: will use Database password if not set)
  [-p] Postgres password (Optional: will use Database password if not set)

  Example: ./pgClusterSetup.sh -d dbCluster.domain.local -w changeMe1! -r changeMe1! -pchangeMe1!

• run_as_replica
  
  Run the database as a replica with the specified primary. Execute as the postgres user.

  ./run_as_replica –h Primary_Appliance -b -W -U replicate
  [-U] The user who will perform replication. For the purpose of this KB this user is replicate
  [-W] Prompt for the password of the user performing replication
  [-b] Take a fresh base backup from node whose connection IP is defined by -h host
  [-h] Hostname of the primary database server. Port 5432 is assumed

  Example: ./opt/vmware/vpostgres/current/share/run_as_replica -h app1.domain.local -b -W -U replicate

• promote_replica_to_primary
  
  Promotes the local database to primary database. Execute as the postgres user.

• show_replication_Status
  
  Displays the replication status of the local database. Execute as the postgres user.

Appendix F - Files packaged in 2108923_dbCluster.tar

• pgClusterSetup.sh
• configureDisk.sh