Restore VCSA Postgres Database
Last updated
Last updated
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.
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.
## 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## 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 connectionsAs 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:
## 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;