# Restore VCSA Postgres Database ![](https://opvizor.com/hubfs/CN-Assets%20\(1\).png) 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 ```bash ## Create directory 'vcsa_recovery' apt :: web/s3/loot » ls -al ... -rw-rw-r-- 1 void void 7955684429 Nov 28 14:14 database_full_backup.tar.gz drwxrwxr-x 2 void void 4096 Nov 28 16:01 vcsa_recovery -rw-rw-r-- 1 void void 4902456 Nov 28 15:23 wal_backup_1.tar.gz -rw-rw-r-- 1 void void 5065989 Nov 28 15:24 wal_backup_2.tar.gz -rw-rw-r-- 1 void void 8063773 Nov 28 15:24 wal_backup_3.tar.gz -rw-rw-r-- 1 void void 4088343 Nov 28 15:25 wal_backup_4.tar.gz -rw-rw-r-- 1 void void 4213428 Nov 28 15:25 wal_backup_5.tar.gz -rw-rw-r-- 1 void void 5292680 Nov 28 15:25 wal_backup_6.tar.gz -rw-rw-r-- 1 void void 4715323 Nov 28 15:26 wal_backup_7.tar.gz -rw-rw-r-- 1 void void 163128 Nov 28 15:26 wal_backup_8.tar.gz -rw-rw-r-- 1 void void 192 Nov 28 15:23 wal_dir_struct.tar.gz ## Extract content, it is important to start with 'database_full_backup.tar.gz' ## as it will create the directory structure. apt :: web/s3/loot » cd vcsa_recovery apt :: s3/loot/vcsa_recovery » tar xzf ../database_full_backup.tar.gz apt :: s3/loot/vcsa_recovery » for i in {1..8}; do tar xzf ../wal_backup_$i.tar.gz; done apt :: s3/loot/vcsa_recovery » tar xzf ../wal_dir_struct.tar.gz ## wal-files are extracted to storage/archive/vpostgres, extract them and move to pg_xlog apt :: s3/loot/vcsa_recovery » cd storage/archive/vpostgres apt :: storage/archive/vpostgres » ls -al total 38724 drwxrwxr-x 2 void void 4096 Nov 29 08:25 . drwxrwxr-x 3 void void 4096 Nov 29 08:25 .. -rw------- 1 void void 5122431 Nov 28 02:35 00000001000003EA000000E6.gz -rw------- 1 void void 5328181 Nov 28 02:43 00000001000003EA000000E7.gz -rw------- 1 void void 4426101 Nov 28 02:45 00000001000003EA000000E8.gz -rw------- 1 void void 4584563 Nov 28 02:45 00000001000003EA000000E9.gz -rw------- 1 void void 4650365 Nov 28 02:45 00000001000003EA000000EA.gz -rw------- 1 void void 4728503 Nov 28 02:47 00000001000003EA000000EB.gz -rw------- 1 void void 5543681 Nov 28 02:53 00000001000003EA000000EC.gz -rw------- 1 void void 5008740 Nov 28 03:00 00000001000003EA000000ED.gz -rw------- 1 void void 236885 Nov 28 03:00 00000001000003EA000000EE.gz apt :: storage/archive/vpostgres » gunzip * apt :: storage/archive/vpostgres » mv * ../../dblog/vpostgres/pg_xlog/ ## Reconfigure postgresql.conf apt :: storage/archive/vpostgres » cd ../../../ apt :: s3/loot/vcsa_recovery » vim storage/db/vpostgres/postgresql.conf old: unix_socket_directories = '/var/run/vpostgres' new: unix_socket_directories = '/var/run/postgresql' old: #log_destination = 'stderr' new: log_destination = 'stderr' old: logging_collector = on new: #logging_collector = on old: stats_temp_directory = '/dev/shm/postgres_stats' new: #stats_temp_directory = '/dev/shm/postgres_stats' old: shared_preload_libraries = 'health_status_worker,pg_stat_statements' new: shared_preload_libraries = 'pg_stat_statements' ## Change ownership of files to match PostgreSQL docker image uid and gid apt :: s3/loot/vcsa_recovery » cd .. apt :: web/s3/loot » sudo chown -R 999:999 vcsa_recovery ``` ### Access the database container ```bash ## Start the container apt :: web/s3/loot » sudo docker run -it --rm --name=vcsa_recovery_db -e PGDATA=/storage/db/vpostgres -v ./vcsa_recovery/storage:/storage postgres:13 PostgreSQL Database directory appears to contain a database; Skipping initialization 2024-11-29 07:37:29.634 GMT [1] LOG: skipping missing configuration file "/storage/db/vpostgres/postgresql.conf.repl" 2024-11-29 07:37:29.634 GMT [1] LOG: skipping missing configuration file "/storage/db/vpostgres/standby.conf" 2024-11-29 07:37:29.634 GMT [1] LOG: skipping missing configuration file "/storage/db/vpostgres/stig.conf" 2024-11-29 07:37:29.634 GMT [1] LOG: skipping missing configuration file "/storage/db/vpostgres/standby.conf" 2024-11-29 07:37:29.634 GMT [1] LOG: skipping missing configuration file "/storage/db/vpostgres/stig.conf" 2024-11-29 07:37:29.634 GMT [1] LOG: skipping missing configuration file "/storage/db/vpostgres/standby.conf" 2024-11-29 07:37:29.634 GMT [1] LOG: skipping missing configuration file "/storage/db/vpostgres/stig.conf" 2024-11-29 07:37:29.634 GMT [1] LOG: skipping missing configuration file "/storage/db/vpostgres/standby.conf" 2024-11-29 07:37:29.634 GMT [1] LOG: skipping missing configuration file "/storage/db/vpostgres/stig.conf" 2024-11-29 07:37:29.634 GMT [1] LOG: skipping missing configuration file "/storage/db/vpostgres/standby.conf" 2024-11-29 07:37:29.634 GMT [1] LOG: skipping missing configuration file "/storage/db/vpostgres/stig.conf" 2024-11-29 07:37:29.634 GMT [1] LOG: skipping missing configuration file "/storage/db/vpostgres/standby.conf" 2024-11-29 07:37:29.634 GMT [1] LOG: skipping missing configuration file "/storage/db/vpostgres/stig.conf" 2024-11-29 07:37:29.634 GMT [1] LOG: skipping missing configuration file "/storage/db/vpostgres/standby.conf" 2024-11-29 07:37:29.634 GMT [1] LOG: skipping missing configuration file "/storage/db/vpostgres/stig.conf" 2024-11-29 07:37:29.634 GMT [1] LOG: skipping missing configuration file "/storage/db/vpostgres/standby.conf" 2024-11-29 07:37:29.634 GMT [1] LOG: skipping missing configuration file "/storage/db/vpostgres/stig.conf" 2024-11-29 07:37:29.634 GMT [1] LOG: skipping missing configuration file "/storage/db/vpostgres/standby.conf" 2024-11-29 07:37:29.634 GMT [1] LOG: skipping missing configuration file "/storage/db/vpostgres/stig.conf" 2024-11-29 07:37:29.634 GMT [1] LOG: skipping missing configuration file "/storage/db/vpostgres/postgresql.conf.repl" 2024-11-29 07:37:29.635 GMT [1] LOG: skipping missing configuration file "/storage/db/vpostgres/standby.conf" 2024-11-29 07:37:29.635 GMT [1] LOG: skipping missing configuration file "/storage/db/vpostgres/stig.conf" 2024-11-29 07:37:29.635 GMT [1] LOG: skipping missing configuration file "/storage/db/vpostgres/standby.conf" 2024-11-29 07:37:29.635 GMT [1] LOG: skipping missing configuration file "/storage/db/vpostgres/stig.conf" 2024-11-29 07:37:29.635 GMT [1] LOG: skipping missing configuration file "/storage/db/vpostgres/standby.conf" 2024-11-29 07:37:29.635 GMT [1] LOG: skipping missing configuration file "/storage/db/vpostgres/stig.conf" 2024-11-29 07:37:29.635 GMT [1] LOG: skipping missing configuration file "/storage/db/vpostgres/standby.conf" 2024-11-29 07:37:29.635 GMT [1] LOG: skipping missing configuration file "/storage/db/vpostgres/stig.conf" 2024-11-29 07:37:29.635 GMT [1] LOG: skipping missing configuration file "/storage/db/vpostgres/standby.conf" 2024-11-29 07:37:29.635 GMT [1] LOG: skipping missing configuration file "/storage/db/vpostgres/stig.conf" 2024-11-29 07:37:29.635 GMT [1] LOG: skipping missing configuration file "/storage/db/vpostgres/standby.conf" 2024-11-29 07:37:29.635 GMT [1] LOG: skipping missing configuration file "/storage/db/vpostgres/stig.conf" 2024-11-29 07:37:29.635 GMT [1] LOG: skipping missing configuration file "/storage/db/vpostgres/standby.conf" 2024-11-29 07:37:29.635 GMT [1] LOG: skipping missing configuration file "/storage/db/vpostgres/stig.conf" 2024-11-29 07:37:29.635 GMT [1] LOG: skipping missing configuration file "/storage/db/vpostgres/standby.conf" 2024-11-29 07:37:29.635 GMT [1] LOG: skipping missing configuration file "/storage/db/vpostgres/stig.conf" 2024-11-29 07:37:29.635 GMT [1] LOG: skipping missing configuration file "/storage/db/vpostgres/standby.conf" 2024-11-29 07:37:29.635 GMT [1] LOG: skipping missing configuration file "/storage/db/vpostgres/stig.conf" 2024-11-29 07:37:29.680 UTC 67496f39.1 0 1 39 LOG: starting PostgreSQL 13.18 (Debian 13.18-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit 2024-11-29 07:37:29.680 UTC 67496f39.1 0 1 40 LOG: listening on IPv4 address "0.0.0.0", port 5432 2024-11-29 07:37:29.680 UTC 67496f39.1 0 1 41 LOG: listening on IPv6 address "::", port 5432 2024-11-29 07:37:29.683 UTC 67496f39.1 0 1 42 LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432" 2024-11-29 07:37:29.688 UTC 67496f39.1a 0 26 1 LOG: database system was interrupted; last known up at 2024-11-28 01:36:36 UTC 2024-11-29 07:37:29.807 UTC 67496f39.1a 0 26 2 LOG: database system was not properly shut down; automatic recovery in progress 2024-11-29 07:37:29.811 UTC 67496f39.1a 0 26 3 LOG: redo starts at 3EA/E6000028 2024-11-29 07:37:30.488 UTC 67496f39.1a 0 26 4 LOG: redo done at 3EA/EE0776F8 2024-11-29 07:37:30.496 UTC 67496f39.1a 0 26 5 LOG: checkpoint starting: end-of-recovery immediate 2024-11-29 07:37:30.932 UTC 67496f39.1a 0 26 6 LOG: checkpoint complete: wrote 14637 buffers (8.6%); 0 WAL file(s) added, 0 removed, 9 recycled; write=0.400 s, sync=0.003 s, total=0.438 s; sync files=258, longest=0.003 s, average=0.001 s; distance=147456 kB, estimate=147456 kB 2024-11-29 07:37:30.941 UTC 67496f39.1 0 1 43 LOG: database system is ready to accept connections ``` 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`. Access the database: `sudo docker exec -it vcsa_recovery_db psql -U vc VCDB` **Useful Queries:** ```sql ## 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; ```