Before diving into data extraction, it’s essential to understand the anatomy of a typical vCSA backup:
Backups are usually stored on a network share.
The top-level folder of the network share will have a folder named 'vCenter'.
Inside the 'vCenter' folder, you will find separate folders for each vCenter instance being backed up to the network share.
Each vCenter folder contains subfolders for individual backup runs. These subfolders are named using the following convention: an initial letter ('M' for manually created or 'S' for scheduled backups), followed by the vCenter version, and ending with a timestamp.
Inside these subfolders, you’ll find various files related to different vCSA components. For our purposes, we need specific files to restore the internal PostgreSQL database.
Restore using Docker Container
Prerequisites
From the VCSA backup we need the following files to restore the Postgres database:
database_full_backup.tar.gz
wal_backup_*.tar.gz
wal_dir_struct.tar.gz
If the database is password protected you'll usually find the password in the archive file config_files.tar.gz, at etc/vmware-vpx/vcdb.properties, root/.pgpass or etc/vmware-vpx/embedded_db.cfg.
Setup container structure
Access the database container
As noted earlier login credentails can be found in config_files.tar.gz, /etc/vmware-vpx/vcdb.properties, root/.pgpass or /etc/vmware-vpx/embedded_db.cfg.
## List all VM:s and their annotations
SELECT name,annotation FROM vpxv_vms;
## Dump all event tables to file (with password)
for i in {1..92}; do sudo docker exec -it -e PGPASSWORD='s3cretp@ssw0rd' vcsa_recovery_db psql -h localhost -U vc -d VCDB -c "SELECT username FROM vpx_event_$i;" -o /storage/out/event_$i.txt; done
## Dump all event tables to file (without password)
for i in {1..92}; do sudo docker exec -it vcsa_recovery_db psql -U postgres VCDB -c "SELECT username FROM vpx_event_$i;" -o /storage/out/event_$i.txt; done
## List event data dates
SELECT * FROM vpx_event_partition_lookup;
## Account information
SELECT * FROM vpx_access;
## Unknown maybe good (?) session info
SELECT * FROM vpx_device;
