Re: pg_start_backup and pg_stop_backup Re: Re: [COMMITTERS] pgsql: Make CheckRequiredParameterValues() depend upon correct

Robert Haas wrote:
> On Wed, Apr 28, 2010 at 6:52 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>> On Wed, 2010-04-28 at 19:40 +0900, Fujii Masao wrote:
>>> On Wed, Apr 28, 2010 at 4:43 PM, Heikki Linnakangas
>>> <heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
>>>> This doesn't contain any changes to pg_start_backup() yet, that's a
>>>> separate issue and still under discussion.
>>> I'm thinking of changing pg_start_backup and pg_stop_backup so that
>>> they just check that wal_level >= 'archive', and changing pg_stop_backup
>>> so that it doesn't wait for archiving when archive_mode is OFF.
>>>
>>> This change is very simple and enables us to take a base backup for SR
>>> even if archive_mode is OFF. Thought?
>> Makes sense.
>>
>> I'm wondering whether this could cause problems with people taking hot
>> backups that aren't aimed at SR. Perhaps we could have 2 new functions
>> whose names are more closely linked to the exact purpose:
>> pg_start_replication_copy() etc..
>> which then act exactly as you suggest.
>
> Hmm. That seems a bit complicated. Why can't we just let people use
> the existing functions the way they always have?

Well, it would be nice to allow using pg_start_backup() on the primary
when streaming replication is enabled, even if archiving isn't.
Otherwise the only way to get the base backup for the standby is to shut
down primary first, or use filesystem snapshot etc.

The straightforward way to enable that would be to allow
pg_start_backup() when wal_level >= 'archive', regardless of
archive_mode. However, I'm worried that someone might take an online
backup without archiving (and replication), not realizing that it's not
safe.

That risk is there already, though, if you restore from an online backup
and forget to create recovery.conf. It will start up in inconsistent
state. The proposed change would make it easier to make that mistake.
I'm not sure what to do about it, maybe throw a warning if you start up
a database and there's a backup_label file in the data directory.
Something like:

WARNING: database system was interrupted while backup was in progress
HINT: If you are restoring from an online backup, you must use a WAL
archive for the restore, or the database can be in inconsistent state

That would also occur if the primary database crashes while a backup is
being taken, in which case the warning can be ignored.

Or maybe we should check in pg_start_backup() that either archive_mode
or streaming replication (max_wal_senders > 0) is enabled.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com