Connecting to a vPostgres instance:
For most VMware products you can connect to the vPostgres instance using the command below
/opt/vmware/vpostgres/current/bin/psql -U postgres -d VCDB
Sometimes, the default user for vPostgres is not “postgres”. The above command will fail in that case. One such application is Site Recovery Manager. For these applications, you can find the vPostgres user details by reviewing the out of ps -aux command.
Look at the line 3, the location following “-D” is where you should find pg_hba.conf. Look at the line 11 and 22, the information following “postgres:” is “user” followed by “database”
ps -aux | grep -i postgres
vpostgr+ 1405 0.0 0.0 1599532 19904 ? Ss 00:41 0:00 postgres: vumuser VCDB 127.0.0.1(54316) idle
vpostgr+ 2847 0.0 0.2 1597388 94352 ? S Nov11 4:20 /opt/vmware/vpostgres/current/bin/postgres -D /storage/db/vpostgres
vpostgr+ 2903 0.0 0.0 68964 4592 ? Ss Nov11 0:13 postgres: logger process
vpostgr+ 2919 0.0 0.6 1598588 227664 ? Ss Nov11 16:02 postgres: checkpointer process
vpostgr+ 2920 0.0 0.1 1598284 35040 ? Ss Nov11 1:50 postgres: writer process
vpostgr+ 2921 0.0 0.0 1598084 21660 ? Ss Nov11 7:15 postgres: wal writer process
vpostgr+ 2922 0.0 0.0 1598920 8024 ? Ss Nov11 3:52 postgres: autovacuum launcher process
vpostgr+ 2923 0.0 0.0 72128 5640 ? Ss Nov11 34:03 postgres: stats collector process
vpostgr+ 2924 0.0 0.0 1599396 13204 ? Ss Nov11 1:18 postgres: bgworker: health_status_worker
vpostgr+ 3892 0.0 0.0 1600516 29148 ? Ss Nov11 2:52 postgres: vc VCDB 127.0.0.1(38434) idle
vpostgr+ 3895 0.0 0.0 1599640 29764 ? Ss Nov11 5:37 postgres: vc VCDB 127.0.0.1(38438) idle
vpostgr+ 4859 0.0 0.1 1602556 33748 ? Ss Nov11 0:33 postgres: vc VCDB 127.0.0.1(38844) idle
vpostgr+ 7565 0.0 0.0 1599620 18984 ? Ss Nov11 0:00 postgres: vc VCDB 127.0.0.1(40526) idle
vpostgr+ 7586 0.0 0.0 1599380 11736 ? Ss Nov11 0:00 postgres: vc VCDB 127.0.0.1(40536) idle
vpostgr+ 7682 0.0 0.0 1599636 19840 ? Ss Nov11 0:33 postgres: vc VCDB 127.0.0.1(40642) idle
vpostgr+ 7872 0.0 0.0 1599380 11608 ? Ss Nov11 0:47 postgres: vc VCDB 127.0.0.1(40796) idle
vpostgr+ 7881 0.0 0.0 1599380 11672 ? Ss Nov11 0:51 postgres: vc VCDB 127.0.0.1(40806) idle
vpostgr+ 7886 0.0 0.0 1601904 25184 ? Ss Nov11 1:03 postgres: vc VCDB 127.0.0.1(40816) idle
vpostgr+ 7890 0.0 0.0 1603116 25700 ? Ss Nov11 1:32 postgres: vc VCDB 127.0.0.1(40820) idle
vpostgr+ 7892 0.0 0.0 1601532 21300 ? Ss Nov11 1:27 postgres: vc VCDB 127.0.0.1(40822) idle
vpostgr+ 22560 0.0 0.0 1599496 18948 ? Ss 01:41 0:00 postgres: vumuser VCDB 127.0.0.1(36602) idle
vpostgr+ 28125 0.0 0.5 1648268 181964 ? Ss 01:59 0:05 postgres: vc VCDB 127.0.0.1(39982) idle
vpostgr+ 28221 0.0 0.1 1604472 64348 ? Ss 02:00 0:00 postgres: vc VCDB 127.0.0.1(40014) idle
vpostgr+ 36879 0.0 0.1 1604432 41728 ? Ss 02:29 0:00 postgres: vc VCDB 127.0.0.1(45128) idle
To connect to a vPostgres instance using an application specific user like “vc” or “vumuser” you can use following commands. The command requires “vc” or “vumuser” password when prompted.
/opt/vmware/vpostgres/current/bin/psql -U vc -d VCDB
Password for user vc:
/opt/vmware/vpostgres/current/bin/psql -U vumuser -d VCDB
Password for user vumuser:
The password for “vc” and “vumuser” can be found in their respective database configuration file. These files can also be used to find application specific users.
cat /etc/vmware-vpx/vcdb.properties
driver = org.postgresql.Driver
dbtype = PostgreSQL
url = jdbc:postgresql://localhost:5432/VCDB
username = vc
password = fJ{>5cD^h9*WuIUq
password.encrypted = false
cat /lib/vmware-updatemgr/bin/configvalues.txt
db_dsn=VUMDSN
db_type=PostgreSQL
db_password=Z#zau(whQ8t*lTW!
db_user=vumuser
download_after_install=true
patch_store=/storage/updatemgr/patch-store
patch_store_temp=/storage/updatemgr/patch-store-temp
install_path=/usr/lib/vmware-updatemgr/bin
soap_port=8084
vpxd_location=vc.vmzoneblog.com
vpxd_port=80
web_port=9084
Listing 10 largest tables (vc):
After connection to vPostgres instance on vCenter server execute the following code. Just copy and past
SELECT schema_name, relname, pg_size_pretty(table_size) AS size, table_size FROM ( SELECT pg_catalog.pg_namespace.nspname AS schema_name, relname, pg_relation_size(pg_catalog.pg_class.oid) AS table_size FROM pg_catalog.pg_class JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid) t WHERE schema_name NOT LIKE 'pg_%' and schema_name like 'vc' ORDER BY table_size DESC limit 10;
schema_name | relname | size | table_size
-------------+------------------------+---------+------------
vc | vpxi_task_username | 2600 kB | 2662400
vc | vpxi_task_start_time | 1544 kB | 1581056
vc | pk_vpx_topn_past_week | 1472 kB | 1507328
vc | pk_vpx_topn_past_month | 1416 kB | 1449984
vc | vpxi_root_task_id | 1320 kB | 1351680
vc | vpxi_parent_task_id | 1288 kB | 1318912
vc | vpxi_task_vm_id | 1256 kB | 1286144
vc | vpx_text_array | 1256 kB | 1286144
vc | vpxi_change_tag_id | 1216 kB | 1245184
vc | pk_vpx_topn_past_day | 1208 kB | 1236992
(10 rows)
Listing 10 largest tables (vum):
After connection to vPostgres instance on vCenter server execute the following code. Just copy and past
SELECT schema_name, relname, pg_size_pretty(table_size) AS size, table_size FROM ( SELECT pg_catalog.pg_namespace.nspname AS schema_name, relname, pg_relation_size(pg_catalog.pg_class.oid) AS table_size FROM pg_catalog.pg_class JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid) t WHERE schema_name NOT LIKE 'pg_%' and schema_name like 'vumdbc' ORDER BY table_size DESC limit 10;
schema_name | relname | size | table_size
-------------+----------------------+---------+------------
vumdbc | vci_va_files | 7744 kB | 7929856
vumdbc | uk_vci_va_files | 2896 kB | 2965504
vumdbc | vci_task_stats | 1464 kB | 1499136
vumdbc | vci_va_upgrade_files | 736 kB | 753664
vumdbc | vci_component_spec | 720 kB | 737280
vumdbc | pk_vci_va_pkg | 480 kB | 491520
vumdbc | pk_va_upg_pkg | 472 kB | 483328
vumdbc | vci_packages | 432 kB | 442368
vumdbc | vci_updates | 240 kB | 245760
vumdbc | pk_vci_task_stats | 216 kB | 221184
(10 rows)
Feel free to let me know your thoughts and feedback about the post by commenting.
Like this:
Like Loading...
Related
Posted by