"public.build_event_id_seq_xxxxxxxx does not exist" error after BOSH Backup and Restore on Concourse deployment fails intermittently

Products

• Concourse for VMware Tanzu

Issue/Introduction

As of the latest version of Concourse available (Concourse 6.3.x), this release does not include a lock on the Postgres DB while the BOSH Backup and Restore (BBR) is running. As a result, there are schema changes in reaction to pipeline create/delete events, which may result in a racing condition between changes to the database and the running backup (pg_dump).

As an operator, you see the following error in the BBR log output:

pg_dump: saving database definition
pg_dump: [archiver (db)] query failed: ERROR:  relation "public.build_event_id_seq_3136860" does not exist
LINE 1: SELECT last_value, is_called FROM public.build_event_id_seq_...
                                          ^
pg_dump: [archiver (db)] query was: SELECT last_value, is_called FROM public.build_event_id_seq_3136860
2020/08/13 13:59:32 You may need to delete the artifact-file that was created before re-running

Note: The message below appears in the BBR logs, however it's not accurate since the Concourse release does not have lock scripts.

[bbr] 2020/08/07 06:01:21 INFO - Running pre-backup-lock scripts...
[bbr] 2020/08/07 06:01:21 INFO - Finished running pre-backup-lock scripts.

Environment

Product Version: 6.3

Resolution

Workaround

There is not a permanent fix for this issue. To work around this issue, re-try the backup and ensure that pipelines are not being created or deleted. 

Concourse R&D included a retry to the BBR within the code that should work the same way as a manual retry. This feature is available on Concourse 7.5.x

Concourse GitHub Resources

• https://github.com/concourse/concourse-bosh-release/issues/145
• https://github.com/concourse/concourse-bosh-release/issues/154 
• https://github.com/concourse/concourse-bosh-release/pull/157