BBR restore of Director fails on director job DB restore due to PSQL error on "id" identity columns

Products

• Operations Manager

Issue/Introduction

On an Operations Manager (Ops Manager) platform that utilized Postgres 9.4 (Ops Manager 2.4 and below) and which has undergone updates to the current Postgres 10.9 (Ops Manager 2.6+), the BOSH Director database has a slightly different schema than the database schema you would see on a Fresh Ops Manager install (as required by BBR for a bbr director restore).

You can see in the errors output that it fails on the first table that it tries to edit. More specifically, on tables that feature an ID. This is due to the database schema in the upgraded ops manager using Sequences and the blank Opsman that we create to be written over using Identities (ID) in their schema. This causes the "pg restore" run by bbr to fail as it is not able to apply a "Sequence" to a field utilizing "Identities".

Reference of the error: 

pg_restore: dropping TABLE DATA blobs
pg_restore: dropping TABLE DATA agent_dns_versions
pg_restore: dropping DEFAULT vms id
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2481; 2604 16495 DEFAULT vms id postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  column "id" of relation "vms" is an identity column

HINT:  Use ALTER TABLE ... ALTER COLUMN ... DROP IDENTITY instead.
    Command was: ALTER TABLE IF EXISTS public.vms ALTER COLUMN id DROP DEFAULT;

2019/12/04 01:41:01 You may need to delete the artifact-file that was created before re-running.
exit status 1 - exit code 1

Environment

Product Version: 2.6

Resolution

The solution that allows the restore to complete is to stop services on the director, delete the database, create a new database, re-run BBR Director Restore on a jumpbox, and then start the services back up.

This would be the order:

For Operations Manager v3.0+ (PostgreSQL 15)

monit stop all
monit start postgres
/var/vcap/packages/postgres-10/bin/dropdb -h 127.0.0.1 -p 5432 -U vcap bosh
/var/vcap/packages/postgres-10/bin/createdb -h 127.0.0.1 -p 5432 -U vcap bosh
monit restart postgres
bbr director restore -a <path>
monit start all

Prior Versions

monit stop all
monit start postgres
/var/vcap/packages/postgres-10/bin/dropdb -h 127.0.0.1 -p 5432 -U vcap bosh
/var/vcap/packages/postgres-10/bin/createdb -h 127.0.0.1 -p 5432 -U vcap bosh
bbr director restore -a <path>
monit start all