Search the VMware Knowledge Base (KB)
View by Article ID

Configuring appliance database server for replication in vCAC or vRA (2108923)

  • 3 Ratings
Language Editions

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.

Purpose

This article provides advice on configuring the databases internal to VMware vRealize Automation for streaming replication. These steps are applicable to VMware vRealize Automation 6.X versions, starting from 6.0.1.

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:
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 vSphere Virtual Machine Administration Guide.
  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 master. This option destroys the current contents of the data directory
    [-h] Hostname of the master 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 to validate replication.

    Note: Appliance A is the primary (master) 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 master 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 to validate replica.

    Note: Appliance B is the primary (master) 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 master 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 to validate replication.

    Note: Appliance A is primary (master) 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 master database is still functional, perform steps 2 and 3.
    If the appliance hosting the master database is no longer accessible, go to step 4.
  2. Connec to appliance A, containing the primary (master) 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 master database.
  5. Connect to appliance B, containing the replica database, using SSH as root.
  6. Promote the replica database to master 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, as relevant to your setup.

    Note: The DNS Entry or Load Balancer should forward traffic to the current master, 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 to validate replication.

    Note: Appliance B is the primary (master) and appliance A is the replica.


Appendix D - Validate replication

  1. Connect to the appliance with the primary (master) 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 master 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 master 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

See Also

Update History

05/15/2015 - Fresh content provided

Attachments

Request a Product Feature

To request a new product feature or to provide feedback on a VMware product, please visit the Request a Product Feature page.

Feedback

  • 3 Ratings

Did this article help you?
This article resolved my issue.
This article did not resolve my issue.
This article helped but additional information was required to resolve my issue.

What can we do to improve this information? (4000 or fewer characters)




Please enter the Captcha code before clicking Submit.
  • 3 Ratings
Actions
KB: