Task:
Query the Aria (vRA/vRO) Postgres database using either command line or the PGAdmin GUI
Warning:
Broadcom (VMWare) does not support changing the PostgreSQL database w/o Support performing it or directing it.
_____________________
Query the Postgres DB using Command Line:
1. Login:
$ ssh root@vraappliance
2. Enter Postgres client:
root@vraappliance [ ~ ] vracli dev psql <db-name>
Enter "yes" at the prompt to continue.
Notes:
- Common DBs:
vracli dev psql catalog-db
vracli dev psql vco-db
- The dev psql command creates a spinoff of the actual database. It will not display newer records than the command/create datetime.
1. Alternate login methods using kubectl:
a. Locate the primary Postgres pod:
$ kubectl get -all-namespaces pods
<returns list of the all the pods>
$ kubectl get pods -l postgres-instance=<name-of-instance>,type=data,role=read-write
2. Login to the Postgres:
a. Login directly to Postgres prompt:
$ kubectl exec -it postgrespodname-0 -- psql
- or -
b. Login to the BASH shell and then run the normal vracli command afterwards:
$ kubectl exec -it postgres-pod-name-0 -- bash
<login>
root@vraappliance [ ~ ] su postgres
postgres $ psql
> \c catalog-db
3. With either method above, we can now explore the database tables:
a. Show tables in db:
catalog-db=# \dt
Deployments: dep_deployment
Request: dep_request
Resource (VMs, Disks, etc.): dep_resource
b. Run the query:
catalog-db=# SELECT * from cat_catalogitem;
or
catalog-db=# SELECT FROM dep_resource WHERE name = 'VMName';
or
catalog-db=# SELECT FROM public.dep_resource WHERE type='Cloud.vSphere.Machine' AND origin = 'DEPLOYED' AND name LIKE 'VMName%';
Notes:
- Include the trailing ; or the command will not submit.
- Use \x before performing query to show expanded output.
- The deployments and VMs are in the dep_resource table.
- For a GUI, install the pgAdmin client (Linux, MacOS, container, and MS Windows)
4. Logout of the Postgres client:
catalog-db=# \q
_____________________
Using the pgAdmin 4 Client:
If you do not yet have the client, if you are using MacOS or MS Windows, download it from the pgadmin.org web site.
If you are using an Ubuntu Desktop use the Debian installation download instructions below.
If you are using a RedHat/RockyOS linux desktop, use the Rocky OS, Redhat OS, or legacy CentOS instructions below.
For Aria/vRA, we can either directly connect to the PostgreSQL database, or we can do a full dump (like we do for a support case) and use the dump to create a local snapshot/copy in time for analyzation.
In this example, we save the password as we have the pgAdmin Master Password and it encrypts the password for convenience. We only have to remember the Master Password.
1. Start pgAdmin 4 --> Login
2. Set-up connection to Aria/vRA:
a. To bring up the Create-Server connection dialog to an external Aria/vRA external server either:
- right-click Servers icon in the left tree and choose Create --> Server,
- or -
- Object (top menu) --> Create --> Server
b. Click the Create-Server dialog, complete the connection info:
- General (tab):
Name: vRADev
Server group: Servers
- Connection (tab):
Host: vradevappliance.mindwatering.net
Port: 5432
Maintenance database: postgres
Username: postgres
Password: myreallygoodpassword
Save Password: checked
If you are using certificates, set them up on the SSL tab.
Click Save.
___
3. Perform the Quey(s) Desired:
a. Navigate to the desired db table:
Servers --> <name of server> --> Databases --> catalog-db --> Schemas --> public --> Tables --> dep_resource
b. Typically, we want to view data:
Right-click dep_resource, click View/Edit Data --> First 100 Rows
On the right, will be presented the SQL query to view the first 100 rows.
c. We then change the SQL to the query we need to run:
SELECT FROM public.dep_resource WHERE type='Cloud.vSphere.Machine' AND origin = 'DEPLOYED' AND name LIKE 'VMName%';
_____________________
Rocky OS, Redhat OS, or legacy CentOS terminal installation from repository:
a. Uninstall any existing package:
$ sudo rpm -e pgadmin4-redhat-repo
a. Download the RPM:
$ sudo rpm -i https://ftp.postgresql.org/pub/pgadmin/pgadmin4/yum/pgadmin4-redhat-repo-2-1.noarch.rpm
b. Install pgAdmin.
To install for both desktop use and the default browser, use the following command:
$ sudo yum install pgadmin4
$ sudo /usr/pgadmin4/bin/setup-web.sh
- or -
To install for desktop mode only:
$ sudo yum install pgadmin4-desktop
- or -
To install for web mode only:
$ sudo yum install pgadmin4-web
$ sudo /usr/pgadmin4/bin/setup-web.sh
Ubuntu instructions for terminal installation from repository:
a. Install the public key and add the pgAdmin repository to the OS:
$ sudo curl -fsS https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo gpg --dearmor -o /usr/share/keyrings/packages-pgadmin-org.gpg
$ sudo sh -c 'echo "deb [signed-by=/usr/share/keyrings/packages-pgadmin-org.gpg] https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list && apt update'
b. Install pgAdmin.
To install for both desktop use and the default browser, use the following command:
$ sudo apt install pgadmin4
- or -
To install for desktop mode only:
$ sudo apt install pgadmin4-desktop
- or -
To install for web mode only:
$ sudo apt install pgadmin4-web
$ sudo /usr/pgadmin4/bin/setup-web.sh
previous page
|