# 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`.&#x20;

### 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;
```
