가끔 vCenter Database 내에 Record들을 조회해야 하는 경우가 있습니다.
vCenter Database는 Postgres SQL 기반이라서, psql 명령어와 pgAdmin으로 모두 접속이 가능합니다.
이에 대한 내용을 소개합니다.
1. psql 이용 방법
접속 방법
| root@vcsa01 [ ~ ]# /opt/vmware/vpostgres/current/bin/psql -U postgres -d VCDB psql.bin (13.8 (VMware Postgres 13.8.0-20449095 release)) Type "help" for help. VCDB=# |
데이터베이스 목록 조회
| VCDB=# \l+ List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description -----------+----------+----------+-------------+-------------+-----------------------+---------+------------+-------------------------------------------- VCDB | vc | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/vc +| 635 MB | pg_default | | | | | | vc=CTc/vc +| | | | | | | | vlcmuser=Tc/vc +| | | | | | | | vstatsuser=Tc/vc | | | postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | 7645 kB | pg_default | default administrative connection database template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 7433 kB | pg_default | unmodifiable empty database | | | | | postgres=CTc/postgres | | | template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| 7433 kB | pg_default | default template for new databases | | | | | postgres=CTc/postgres | | | (4 rows) |
테이블 목록 조회
| VCDB=# \dt List of relations Schema | Name | Type | Owner --------+-----------------------------------------+-------------------+------- vc | certmgmt_service_subscribable | table | vc vc | certmgmt_sync_item_metadata | table | vc vc | certmgmt_sync_replica_metadata | table | vc vc | cis_kv_keyvalue | table | vc vc | cis_kv_providers | table | vc vc | cl_checkout | table | vc vc | cl_config | table | vc <snip> |
SQL Query 구문은 일반적인 문법을 그대로 따르시면 되겠습니다.
2. pgAdmin 이용 방법
pgAdmin은 일반적인 방법 그대로 설치를 진행하시면 됩니다.
설치를 완료한 후, VCDB에 접속을 하게 되면, 다음과 같은 화면을 확인할 수 있습니다.
pgAdmin으로 접속한 후, vCenter OS에서 실제 Network Connection을 보면 pgAdmin을 통해 vCenter Database에 연결된 것을 알 수 있습니다.
| root@vcsa01 [ ~ ]# netstat -anp | grep 5432 tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN 64280/postgres tcp 0 0 192.168.1.3:5432 192.168.1.2:54595 ESTABLISHED 62649/postgres: vc ### <-- !! tcp 0 0 127.0.0.1:43762 127.0.0.1:5432 TIME_WAIT - tcp 0 0 127.0.0.1:48754 127.0.0.1:5432 TIME_WAIT - tcp 0 0 127.0.0.1:5432 127.0.0.1:48126 ESTABLISHED 35406/postgres: vum tcp 0 0 127.0.0.1:5432 127.0.0.1:59250 ESTABLISHED 3693/postgres: vc V tcp 0 0 127.0.0.1:46062 127.0.0.1:5432 ESTABLISHED 4909/vmware-vpxd-sv tcp 0 0 127.0.0.1:48116 127.0.0.1:5432 ESTABLISHED 12637/updatemgr tcp 0 0 127.0.0.1:58786 127.0.0.1:5432 ESTABLISHED 4909/vmware-vpxd-sv tcp 0 0 127.0.0.1:43794 127.0.0.1:5432 TIME_WAIT - tcp 0 0 127.0.0.1:48798 127.0.0.1:5432 TIME_WAIT - tcp 118 0 127.0.0.1:46088 127.0.0.1:5432 CLOSE_WAIT 6734/vmware-hvc.lau tcp 0 0 127.0.0.1:5432 127.0.0.1:48116 ESTABLISHED 35405/postgres: vum tcp 0 0 127.0.0.1:5432 127.0.0.1:46062 ESTABLISHED 64560/postgres: vc tcp 0 0 127.0.0.1:5432 127.0.0.1:56006 ESTABLISHED 805/postgres: vc VC tcp 0 0 127.0.0.1:48782 127.0.0.1:5432 TIME_WAIT - tcp 0 0 127.0.0.1:44394 127.0.0.1:5432 ESTABLISHED 4909/vmware-vpxd-sv tcp 0 0 127.0.0.1:59250 127.0.0.1:5432 ESTABLISHED 14756/vmware-conten tcp 118 0 127.0.0.1:46060 127.0.0.1:5432 CLOSE_WAIT 6734/vmware-hvc.lau tcp 0 0 127.0.0.1:43784 127.0.0.1:5432 TIME_WAIT - tcp 0 0 127.0.0.1:48126 127.0.0.1:5432 ESTABLISHED 12637/updatemgr tcp 0 0 127.0.0.1:49306 127.0.0.1:5432 TIME_WAIT - tcp 0 0 127.0.0.1:49292 127.0.0.1:5432 TIME_WAIT - tcp 118 0 127.0.0.1:53300 127.0.0.1:5432 CLOSE_WAIT 13817/vmware-perfch tcp 0 0 127.0.0.1:5432 127.0.0.1:58800 ESTABLISHED 23456/postgres: vc tcp 0 0 127.0.0.1:58814 127.0.0.1:5432 ESTABLISHED 4909/vmware-vpxd-sv tcp 0 0 127.0.0.1:5432 127.0.0.1:44394 ESTABLISHED 4259/postgres: vc V tcp 0 0 127.0.0.1:48750 127.0.0.1:5432 TIME_WAIT - tcp 0 0 127.0.0.1:43810 127.0.0.1:5432 TIME_WAIT - tcp 0 0 192.168.1.3:5432 192.168.1.2:54664 ESTABLISHED 63720/postgres: vc ### <-- !! tcp 0 0 127.0.0.1:49318 127.0.0.1:5432 TIME_WAIT - tcp 0 0 127.0.0.1:48768 127.0.0.1:5432 TIME_WAIT - tcp 0 0 127.0.0.1:43774 127.0.0.1:5432 TIME_WAIT - tcp 0 0 127.0.0.1:5432 127.0.0.1:44402 ESTABLISHED 4262/postgres: vc V tcp 0 0 10.10.10.2:5432 10.10.10.1:42952 ESTABLISHED 64428/8AAD7EE0 tcp 0 0 127.0.0.1:49332 127.0.0.1:5432 TIME_WAIT - tcp 0 0 127.0.0.1:5432 127.0.0.1:33428 ESTABLISHED 1455/postgres: vc V tcp 0 0 127.0.0.1:48808 127.0.0.1:5432 TIME_WAIT - tcp 118 0 127.0.0.1:58594 127.0.0.1:5432 CLOSE_WAIT 6981/vmware-certifi tcp 0 0 127.0.0.1:5432 127.0.0.1:58818 ESTABLISHED 23458/postgres: vc tcp 0 0 127.0.0.1:56006 127.0.0.1:5432 ESTABLISHED 12554/vmware-sps.la tcp 0 0 127.0.0.1:33428 127.0.0.1:5432 ESTABLISHED 14756/vmware-conten tcp 0 0 127.0.0.1:5432 127.0.0.1:58786 ESTABLISHED 23455/postgres: vc tcp 0 0 127.0.0.1:49330 127.0.0.1:5432 TIME_WAIT - tcp 0 0 127.0.0.1:49322 127.0.0.1:5432 TIME_WAIT - tcp 118 0 127.0.0.1:47132 127.0.0.1:5432 CLOSE_WAIT 4909/vmware-vpxd-sv tcp 0 0 127.0.0.1:5432 127.0.0.1:44400 ESTABLISHED 4260/postgres: vc V tcp 0 0 127.0.0.1:5432 127.0.0.1:58814 ESTABLISHED 23457/postgres: vc tcp 0 0 127.0.0.1:43792 127.0.0.1:5432 TIME_WAIT - tcp 0 0 127.0.0.1:58800 127.0.0.1:5432 ESTABLISHED 4909/vmware-vpxd-sv tcp 0 0 127.0.0.1:44402 127.0.0.1:5432 ESTABLISHED 4909/vmware-vpxd-sv tcp 0 0 127.0.0.1:58818 127.0.0.1:5432 ESTABLISHED 4909/vmware-vpxd-sv tcp 0 0 127.0.0.1:44400 127.0.0.1:5432 ESTABLISHED 4909/vmware-vpxd-sv tcp6 0 0 :::5432 :::* LISTEN 64280/postgres unix 2 [ ACC ] STREAM LISTENING 3459168 64280/postgres /var/run/vpostgres/.s.PGSQL.5432 unix 3 [ ] STREAM CONNECTED 3820803 55464/postgres: cns /var/run/vpostgres/.s.PGSQL.5432 unix 3 [ ] STREAM CONNECTED 3835831 60481/postgres: vc /var/run/vpostgres/.s.PGSQL.5432 unix 3 [ ] STREAM CONNECTED 3716200 19300/postgres: vc /var/run/vpostgres/.s.PGSQL.5432 unix 3 [ ] STREAM CONNECTED 3908783 22117/postgres: vc /var/run/vpostgres/.s.PGSQL.5432 unix 3 [ ] STREAM CONNECTED 3464926 475/postgres: wcpus /var/run/vpostgres/.s.PGSQL.5432 unix 3 [ ] STREAM CONNECTED 3459339 64345/postgres: vc /var/run/vpostgres/.s.PGSQL.5432 unix 3 [ ] STREAM CONNECTED 3458684 64303/8AAD7EE0 /var/run/vpostgres/.s.PGSQL.5432 unix 3 [ ] STREAM CONNECTED 3469757 1291/postgres: vc V /var/run/vpostgres/.s.PGSQL.5432 |
| top - 05:44:19 up 1 day, 5:00, 1 user, load average: 0.23, 0.20, 0.18 Tasks: 271 total, 1 running, 270 sleeping, 0 stopped, 0 zombie %Cpu0 : 0.0/0.0 0[ ] %Cpu1 : 5.9/5.9 12[|||||||||||| ] GiB Mem : 85.2/11.7 [ ] GiB Swap: 1.2/25.0 [ ] PID USER PR NI VIRT RES %CPU %MEM TIME+ S COMMAND 1 root 20 0 10.8m 7.4m 0.0 0.1 0:05.98 S /lib/systemd/systemd --switched-root --system --deserialize 16 <snip> 64280 vpostgr+ 20 0 437.0m 50.7m 0.0 0.4 0:02.00 S `- /opt/vmware/vpostgres/current/bin/postgres -D /storage/db/vpostg+ 64281 vpostgr+ 20 0 35.8m 5.9m 0.0 0.0 0:00.03 S `- postgres: logger 64283 vpostgr+ 20 0 437.3m 43.8m 0.0 0.4 0:00.58 S `- postgres: checkpointer 64284 vpostgr+ 20 0 437.2m 14.1m 0.0 0.1 0:01.98 S `- postgres: background writer 64285 vpostgr+ 20 0 437.0m 17.7m 0.0 0.1 0:01.40 S `- postgres: walwriter 64286 vpostgr+ 20 0 437.8m 9.0m 0.0 0.1 0:00.46 S `- postgres: autovacuum launcher 64287 vpostgr+ 20 0 36.9m 6.5m 0.0 0.1 0:03.38 S `- postgres: stats collector 64288 vpostgr+ 20 0 440.0m 20.2m 0.0 0.2 0:00.53 S `- postgres: health_status_worker 64289 vpostgr+ 20 0 437.7m 9.0m 0.0 0.1 0:00.00 S `- postgres: logical replication launcher 64303 vpostgr+ 20 0 440.3m 11.6m 0.0 0.1 0:00.30 S `- postgres: walsender archiver [local] streaming 7/8AB3BEF8 64345 vpostgr+ 20 0 443.5m 30.1m 0.0 0.3 0:00.06 S `- postgres: vc VCDB [local] idle 64428 vpostgr+ 20 0 440.4m 12.9m 0.0 0.1 0:00.82 S `- postgres: walsender replicator 10.10.10.1(42952) streaming 7+ 64560 vpostgr+ 20 0 441.0m 20.8m 0.0 0.2 0:00.05 S `- postgres: vc VCDB 127.0.0.1(46062) idle 475 vpostgr+ 20 0 440.5m 20.2m 0.0 0.2 0:00.17 S `- postgres: wcpuser VCDB [local] idle 805 vpostgr+ 20 0 441.2m 20.6m 0.0 0.2 0:00.15 S `- postgres: vc VCDB 127.0.0.1(56006) idle 1291 vpostgr+ 20 0 449.5m 90.9m 0.0 0.8 0:01.05 S `- postgres: vc VCDB [local] idle 1455 vpostgr+ 20 0 440.8m 19.2m 0.0 0.2 0:00.10 S `- postgres: vc VCDB 127.0.0.1(33428) idle 3693 vpostgr+ 20 0 441.4m 25.6m 0.0 0.2 0:00.12 S `- postgres: vc VCDB 127.0.0.1(59250) idle 4259 vpostgr+ 20 0 441.1m 20.9m 0.0 0.2 0:00.13 S `- postgres: vc VCDB 127.0.0.1(44394) idle 4260 vpostgr+ 20 0 441.0m 20.9m 0.0 0.2 0:00.08 S `- postgres: vc VCDB 127.0.0.1(44400) idle 4262 vpostgr+ 20 0 441.1m 21.1m 0.0 0.2 0:00.27 S `- postgres: vc VCDB 127.0.0.1(44402) idle 23455 vpostgr+ 20 0 440.9m 20.8m 0.0 0.2 0:00.09 S `- postgres: vc VCDB 127.0.0.1(58786) idle 23456 vpostgr+ 20 0 441.1m 20.9m 0.0 0.2 0:00.10 S `- postgres: vc VCDB 127.0.0.1(58800) idle 23457 vpostgr+ 20 0 441.1m 21.0m 0.0 0.2 0:00.21 S `- postgres: vc VCDB 127.0.0.1(58814) idle 23458 vpostgr+ 20 0 441.0m 20.9m 0.0 0.2 0:00.13 S `- postgres: vc VCDB 127.0.0.1(58818) idle 19300 vpostgr+ 20 0 441.8m 28.2m 0.0 0.2 0:00.05 S `- postgres: vc VCDB [local] idle 35405 vpostgr+ 20 0 440.3m 18.4m 0.0 0.2 0:00.00 S `- postgres: vumuser VCDB 127.0.0.1(48116) idle 35406 vpostgr+ 20 0 440.3m 18.5m 0.0 0.2 0:00.00 S `- postgres: vumuser VCDB 127.0.0.1(48126) idle 55464 vpostgr+ 20 0 440.9m 19.4m 0.0 0.2 0:00.02 S `- postgres: cns VCDB [local] idle 60481 vpostgr+ 20 0 447.5m 64.3m 0.0 0.5 0:00.69 S `- postgres: vc VCDB [local] idle 22117 vpostgr+ 20 0 447.9m 59.6m 0.0 0.5 0:00.38 S `- postgres: vc VCDB [local] idle 62649 vpostgr+ 20 0 452.1m 30.9m 0.0 0.3 0:00.09 S `- postgres: vc postgres 192.168.1.2(54595) idle ### <-- !! 63720 vpostgr+ 20 0 452.7m 34.2m 0.0 0.3 0:00.13 S `- postgres: vc VCDB 192.168.1.2(54664) idle ### <-- !! 64302 vpostgr+ 20 0 22.5m 5.9m 0.0 0.0 0:01.11 S `- /opt/vmware/vpostgres/current/bin/pg_archiver --directory /stora+ |
