Re: Improving Physical Backup/Restore within the Low Level API

On Mon, 2023-10-16 at 09:26 -0700, David G. Johnston wrote:
> This email is a first pass at a user-visible design for how our backup and restore
> process, as enabled by the Low Level API, can be modified to make it more mistake-proof.
> In short, it requires pg_start_backup to further expand upon what it means for the
> system to be in the midst of a backup, pg_stop_backup to reverse those things,
> and modifying the startup process to deal with the server having crashed while the
> system is in that backup state.  Notes at the end extend the design to handle concurrent backups.
>
> The core functional changes are:
> 1) pg_backup_start modifies a newly added "in backup" state flag in pg_control to on.
> 2) pg_backup_stop modifies that flag back to off.
> 3) postmaster will refuse to start if that flag is on, unless one of:
>   a) crash.signal exists in the data directory
>   b) recovery.signal exists in the data directory
>   c) standby.signal exists in the data directory
> 4) Signal file processing causes the in-backup flag in pg_control to be set to off
>
> The newly added crash.signal file is required to handle the case where the server
> crashes after pg_backup_start and before pg_backup_stop.  It initiates a crash recovery
> of the instance just as is done today but with the added change of flipping the flag
> to off when recovery is complete just before going live.

I see a couple of problems and/or things that need clarification with that idea:

- Two backups can run concurrently.  How do you reconcile that with the "in backup"
  flag and crash.signal?
- I guess crash.signal is created during pg_start_backup().  So that file will be
  included in the backup.  How do you handle that during recovery?  Ignore it if
  another signal file is present?  And if the user forgets to create a signal file
  for recovery, how do you prevent PostgreSQL from performing crash recovery?