Re: detecting binary backup in progress

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 06/01/2013 08:14 AM, Andres Freund wrote:
> On 2013-06-01 08:11:26 -0700, Joe Conway wrote:
>> -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1
>>
>> On 06/01/2013 02:43 AM, Christoph Moench-Tegeder wrote:
>>> ## Joe Conway (mail(at)joeconway(dot)com):
>>>
>>>> However there is a period of time after pg_start_backup() is
>>>> first executed to when it completes, during which
>>>> backup_label file does not exist yet, but the backup has
>>>> essentially been started. Is there any way to detect this
>>>> state?
>>>
>>> When I did some research on the very same question just a few
>>> weeks ago, I settled for external locks (lockfile,
>>> pg_advisory_lock(), ..., depending on your exact situation)
>>> around the backup-taking code.
>>
>> Right, and an external lockfile is good except there is a race
>> condition.
>>
>> Proc1, t0) SELECT pg_start_backup(...) -- command starts to
>> execute
>>
>> Proc2, t1) lay down a lockfile (or other mechanism)
>>
>> Proc2, t2) check for running backup by looking for backup_label
>>
>> Proc1, t3) SELECT pg_start_backup(...) -- command finishes, --
>> backup_label created
>>
>> So you are forced to sleep for some arbitrary time just in case
>> pg_start_backup() has started but not completed at the point when
>> you try to lock out the backup.
>
> Uh. Why would you do the lock(file) thingy *after* calling
> pg_start_backup? You should do lock before calling start backup
> and remove the lock after calling stop backup. In that case I don't
> see where the race condition is?

No, the point is I have no control over Proc1. I am trying to prevent
Proc2 from running concurrently with a binary backup (Proc1). So I
need to lock out Proc1, or detect it is running and wait for it to finish.

The problem is that in between t0 and t3 there is no good way to
determine that Proc1 has started its backup because backup_label does
not yet exist, and there is no other evidence besides pg_stat_activity
(which as I said is better than nothing but probably not perfect).

Joe

- --
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQIcBAEBAgAGBQJRqhLuAAoJEDfy90M199hlfY4P/jiuiSODmJs9xphXAcs1Xo2k
hVFTDWTYysFYr9hhnTyWlv9B60h9f/nYu/EeXXyOlynSs/DOUpWNFDJcF/t/SopI
D4es+F+LFoSnIeeyNPu4lYzBeQ4tHFh7KqHYGd640eCaTDW2O2uqu89R8sMbhMQs
HXSGAa8N8Vmy/+js1xSfMHA/8qK2QGkEAxU/IJmYPKn+QYmHh3iyQ+9rDTVG7ghM
+3FC7EPtv9jsXPHczyT3qwcAy76DNjKbET9z3GsDc9qDIZezFRlh5mALXHPf6Puj
X1Bk8okZQj45bMx3DkxhGawae045O6nbUPYIwVTujAWB+1rqbKJFW2qSmp8DRL9u
w+k0kbRB++wu3QNxNx5GOKuHJwsVVU5CBhMLqPOPrBwYCZtAJPY7llCPUQclJ91A
QSe1lcEXSlQ0vlva4oC1ksEWfSCedudAwFkbiexKUu48FHQsSQrlbE46zbAKSL40
XzYCPN1eT/MsxAeXiV0ehtvEeiiqQU3aQifrOcIZiBquZPQP6I/kQ1WbdMJ9zChD
Wff8+J6nMbFFetfxIfkMdJIaKuMYetnZ0NkHiHORzESGqIc5Rm5t4gzA4bHNiVg9
jDNDSmnsR8FujfkhySrQcZTmV/a+y7p1mLJlpyKNF/OX55LdRA97dwIbpcutXScW
DmpFiM+molu2bLPd17H5
=jjJq
-----END PGP SIGNATURE-----