BBR restore of BOSH director fails due to PSQL errors

Products

• Operations Manager

Issue/Introduction

When `bbr director restore` to restore BOSH director with internal database, it may fail due to some PSQL errors, for example: 

[bbr] 2025/01/03 11:14:15 INFO - Restoring director on bosh/0...
[bbr] 2025/01/03 11:14:15 DEBUG - Trying to execute 'sudo BBR_ARTIFACT_DIRECTORY=/var/vcap/store/bbr-backup/director/ ARTIFACT_DIRECTORY=/var/vcap/store/bbr-backup/director/ /var/vcap/jobs/director/bin/bbr/restore' on remote
[bbr] 2025/01/03 11:14:15 DEBUG - stdout:  PostgreSQL 15.10 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit

...

pg_restore: error: could not execute query: ERROR:  ...
HINT:  ...
2025/01/03 11:14:15 You may need to delete the artifact-file that was created before re-running.
exit status 1 - exit code 1

Cause

This is usually caused by schema conflicts between the existing database and backup. 

Resolution

If you do not need any existing data, you can simply drop the database, and restart `bbr director restore`. 

At first please check the exact Postgres version in BOSH director, you can run `ps -ef` to check the version. For example: 

bosh/0:/var/vcap/jobs/database-backup-restorer/bin# ps -ef | grep postgres
vcap       12695       1  0 May27 ?        00:00:17 /var/vcap/packages/bpm/bin/tini -w -s -- /var/vcap/jobs/postgres/bin/postgres
vcap       12701   12695  0 May27 ?        00:00:42 /var/vcap/packages/postgres-15/bin/postgres -h 127.0.0.1 -p 5432 -D /var/vcap/store/postgres-15

Then continue the restore with below steps (please use postgres-13 directory below if above process shows postgres-13): 

bosh/0:~# monit stop all 
bosh/0:~# monit start postgres 
bosh/0:~# /var/vcap/packages/postgres-15/bin/dropdb -h 127.0.0.1 -p 5432 -U vcap bosh 
bosh/0:~# /var/vcap/packages/postgres-15/bin/createdb -h 127.0.0.1 -p 5432 -U vcap bosh 
bosh/0:~# monit restart postgres

// on your BBR jumpbox 
$ bbr director restore -a <path> 

bosh/0:~# monit start all