Re: [HACKERS] detecting binary backup in progress
On Sat, Jun 1, 2013 at 4:55 AM, Alvaro Herrera alvhe...@2ndquadrant.comwrote: Heikki Linnakangas wrote: The manual says: pg_is_in_backup()boolTrue if an on-line exclusive backup is still in progress. So clearly that is intentional. That could use some rephrasing, though; a layman won't know what an exclusive backup is. Heck, I don't understand what it is either. Same here. Does it mean taking a backup not with pg_basebackup but by executing yourself external operations between pg_start/stop_backup calls? -- Michael
Re: [HACKERS] detecting binary backup in progress
On 2013-06-01 17:05:57 +0900, Michael Paquier wrote: On Sat, Jun 1, 2013 at 4:55 AM, Alvaro Herrera alvhe...@2ndquadrant.comwrote: Heikki Linnakangas wrote: The manual says: pg_is_in_backup()boolTrue if an on-line exclusive backup is still in progress. So clearly that is intentional. That could use some rephrasing, though; a layman won't know what an exclusive backup is. Heck, I don't understand what it is either. Same here. Does it mean taking a backup not with pg_basebackup but by executing yourself external operations between pg_start/stop_backup calls? Basically yes. pg_start/stop_backup places the backup label into the data directory itself so there can only be one of them at a time since it has to have a fixed name. With the streaming protocol the backup label is only added to the streamed data, so there can be multiple ones at the same time. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] detecting binary backup in progress
## Joe Conway (m...@joeconway.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. Regards, Christoph -- Spare Space -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] detecting binary backup in progress
On 31 May 2013 21:06, Andres Freund and...@2ndquadrant.com wrote: On 2013-05-31 22:53:14 +0300, Heikki Linnakangas wrote: On 31.05.2013 22:36, Andres Freund wrote: On 2013-05-31 22:29:45 +0300, Heikki Linnakangas wrote: Note that pg_is_in_backup() just checks for presence of $PGDATA/backup_label. Also note that pg_basebackup doesn't create backup_label in the server. It's included in the backup that's sent to the client, but it's never written to disk in the server. So checking for backup_label manually or with pg_is_in_backup() will return false even if pg_basebackup is running. Whoa. You are right, but I'd call that a bug. I don't understand why we aren't just checking XLogCtl-Insert.(nonExclusiveBackups||exlusiveBackup)? Well, depends on what you imagine the function is used for. If you think of it as will pg_start_backup() throw an error if I call it now, or do I need to call pg_stop_backup(), then the current behavior is correct. The manual says: pg_is_in_backup()boolTrue if an on-line exclusive backup is still in progress. So clearly that is intentional. Well, just because it's intentional, doesn't mean its a good idea ;). There very well are reasons to check for in progress non-exclusive backups as well. You e.g. wouldn't want to restart the database while the weekly base backup of your 1TB database is in progress, just because it's done via the replication protocol. If we weren't in beta 1 already I'd vote for making it into: pg_backup_in_progress(OUT bool exclusive, OUT int non_exclusive) or similar. Perhaps we should do that anyway? That could use some rephrasing, though; a layman won't know what an exclusive backup is. True. Although I have to admit I can't come up with a succinct name for it it right now. I see that this exact discussion has happened once before, after the initial commit. AFAICS nobody likes the fact that pg_is_in_backup() only covers exclusive backups. The problem seems to be that we can't find a better term. But the problem remains that having a function called pg_is_in_backup() that *clearly* does *not* do what it says, is a problem. Yes, few people will understand what an exclusive backup is, but that is a very good reason to not split hairs in the definition. The way to resolve this is to have two functions: pg_is_in_backup() - which covers both/all kinds of backup pg_is_in_exclusive_backup() - which covers just the exclusive backup mode and some clear documentation that explains why the two functions are necessary. Any objections to me committing those changes? -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] detecting binary backup in progress
Simon Riggs si...@2ndquadrant.com writes: The way to resolve this is to have two functions: pg_is_in_backup() - which covers both/all kinds of backup pg_is_in_exclusive_backup() - which covers just the exclusive backup mode What will you do with pg_backup_start_time()? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] detecting binary backup in progress
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 06/01/2013 02:43 AM, Christoph Moench-Tegeder wrote: ## Joe Conway (m...@joeconway.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. Or better (at least my solution), is you check pg_stat_activity for the presence of a running SELECT pg_start_backup(...) command prior to checking for backup_label. I imagine there is still a small race condition there but at least much smaller. 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/ iQIcBAEBAgAGBQJRqg8eAAoJEDfy90M199hlm2wP/ihMBBOvhT+fbXHcZj6A4wW9 /d3N2KZ6tRZE6Sb6W6ouy8jdMjgQsVfgrfHh7Ts0NS1cu6k2mTgbjxfi2JygjFzh PLpj4b9cRMYRLaeEj5Ik69HM/IMg6gLW5WM2hRrRiXopbxXIK8qR37rQmjWM2aYU OJ+qlKKx1K5d/VS6bmxveprAMzplR/U9OhEOteFo+A2ODl7Vsd9wL2NIa6DyiVvq BsM0QEJjkFZSkKvrZyWctO6v6j/ccNgo2xUJMyPAOVFxabOTw1CMUjdVDM9Im61/ c+AfchWCVHaLOGMY7KlGUmNhHuWcIY23u2sucn5JMpNbOmJRyexwsXCYIkBZXZdP OpaMq1w37aY2HwtrSpAgzUditQqoMjbq0PVgwoTu8P+pYbwToNclXW/TGq2zeDA3 mKWUCGGbSKfjoQks0yMNho05YIJkCkZeTDRMTuXN6k2Gf3WgqRyNwDfjnT0+YFZn Su93JZ5gE/vDugf7o47OeyrLTXcqVt3WgTCae7A70Vi2nenq6jWVCCKqTz9E7Ct6 I3Vhjal0dxpd6pi0sfI6msRAnPKoxfu9vjXdDuRf+NbzxpG8Gwb+HDaZzE/ffqz8 /473B/ZgNqCIXd9/loCTVdnewSaUDNuGqNxmmCMtFpmEC1SZ0zZZhImeLQFkA17k mwSGNqxchm8J/4ExM/n9 =x1tN -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] detecting binary backup in progress
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 (m...@joeconway.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? Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 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 (m...@joeconway.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- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] detecting binary backup in progress
On 2013-06-01 08:27:42 -0700, Joe Conway wrote: 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. Backups over which you don't have control sound a bit scary ;). I think at that point you have a race condition no matter what since the backup could be started between your check and when you call pg_start_backup anyay. So just calling pg_start_backup and handling the error properly sounds like the way to go in that case. Andres -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] detecting binary backup in progress
On 1 June 2013 15:45, Tom Lane t...@sss.pgh.pa.us wrote: Simon Riggs si...@2ndquadrant.com writes: The way to resolve this is to have two functions: pg_is_in_backup() - which covers both/all kinds of backup pg_is_in_exclusive_backup() - which covers just the exclusive backup mode What will you do with pg_backup_start_time()? Hmm, at least all of those functions use the backup name consistently. I guess I wasn't suggesting we rename pg_start_backup() to pg_start_exclusive_backup(), so maybe it makes sense. pg_start_backup() talks about an online backup, while pg_is_in_backup() talks about an exclusive backup. Minimum change here would be to make pg_start_backup talk about an exclusive backup also. What we need is a function that says whether it is possible to shutdown because of a backup, or not. pg_basebackup is an active task, whereas an exclusive backup never is. So we need a function to tell us that although nothing else is running, but we are running an exclusive backup. So changing pg_is_in_backup() to refer to all kinds of backup still allows it to be used for its primary purpose - to tell whether its OK to shutdown or not, but it also makes it clearer. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] detecting binary backup in progress
On 2013-05-31 10:38:56 -0700, Joe Conway wrote: I can check for the presence of $PGDATA/backup_label in order to detect a backup in progress (i.e. pg_start_backup() has been run and pg_stop_backup() has not yet been run). 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? 9.3 has pg_is_in_backup() for that. I don't think there's a way to detect it safely without C code before that. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] detecting binary backup in progress
On 31 May 2013 18:38, Joe Conway m...@joeconway.com wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I can check for the presence of $PGDATA/backup_label in order to detect a backup in progress (i.e. pg_start_backup() has been run and pg_stop_backup() has not yet been run). 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? pg_is_in_backup() -- Thom -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] detecting binary backup in progress
On 31.05.2013 20:38, Joe Conway wrote: I can check for the presence of $PGDATA/backup_label in order to detect a backup in progress (i.e. pg_start_backup() has been run and pg_stop_backup() has not yet been run). 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? What are you trying to accomplish? Even if you eliminate that window, it's always possible that a backup starts just after you've checked that there is no backup in progress. - Heikki -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] detecting binary backup in progress
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 05/31/2013 10:42 AM, Andres Freund wrote: On 2013-05-31 10:38:56 -0700, Joe Conway wrote: I can check for the presence of $PGDATA/backup_label in order to detect a backup in progress (i.e. pg_start_backup() has been run and pg_stop_backup() has not yet been run). 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? 9.3 has pg_is_in_backup() for that. I don't think there's a way to detect it safely without C code before that. Good to know, although it won't help me much in my current scenario ;-) Thanks, 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/ iQIcBAEBAgAGBQJRqO12AAoJEDfy90M199hlkxgP/jrrnjRKhC+bIHQc/gCmaGXe CoL490f3ClST2V8e2GffybrqHrJ5xTnhVs04X+kIZOF3vSh67mWmHtSANElxjbYB t95kkLbkxPhPFnr0ubnalbMRplxlNhLERmi8BWPuknJCJGeEEZ7qlIZROV9GXH51 ZArMewbBlNGUvZQU+DHVYuA3WYVcDSCR0F9AL/O1lLsZ6RVbh/M4WH+q+GzDWsJT 80PDusnSSzHZzq1xMj96kRH3n7s8rpx0kDa5xuM/TBne0Fif1AZuh079cAUtUmnO w50pgOuTc3lhbH5HmTTuefR94820YBHnol1lsGyLQaKPdXN/6JU+Ssz+q4xx/pWb Fy1VscYRGb2yka7DpmcGlR4k8kqLhjRwZMKM+GLNGVeERHlHVV30Q++DwVZXQSSF hfgvpphAv1klCh1R0vwounD/DFb48ZZnVp541GULXSZr1X6cQl+vecrzisDlHjVq SAJHEvwsiCmR3TzaXgno3GNCmReLfQ9Wg6BJHszSuOieeT2ha+ffJQi7tZZEzsKs quEYdrgkyXheRwrO+bzi3h59wJf5MdJU3i1U9VPL0JWo9F4QcoGAwrQVYdM3RpUb DZf2T45V9nNEYYe10FUe3GfpiaoriflOU0RUSlpWoiq2IsCZA+S2jU65mqtdAtOk JLCDZUJGkoRjXtwWxMqj =l+5T -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] detecting binary backup in progress
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 05/31/2013 10:46 AM, Heikki Linnakangas wrote: On 31.05.2013 20:38, Joe Conway wrote: I can check for the presence of $PGDATA/backup_label in order to detect a backup in progress (i.e. pg_start_backup() has been run and pg_stop_backup() has not yet been run). 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? What are you trying to accomplish? Even if you eliminate that window, it's always possible that a backup starts just after you've checked that there is no backup in progress. We can lay down a lockfile before checking for backup in progress, and the script that starts the backup can check for the lockfile before doing anything. There is still a race-window, but I want to minimize it. pg_is_in_backup() mentioned nearby on this thread will help close the gap. 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/ iQIcBAEBAgAGBQJRqO7WAAoJEDfy90M199hlSPkP/AtbQjRImH0TYp0nGV+XG8zq zyeaQXtU1kB+eon7egUjObN/khnM2ePlqmKBG5TuZW8HQWK+AnISG68rB/FU10Vo rgiN2BSw/GZ4oCsJ1tXnbvyE70vRhq5pMbEbkfCCqjt/LZOGg50/Z/ROmr7RwmSx 2dSVsvhUryYS3eo/OxgkyBlCtnlV3fkkdKLc6S5VCaNWfHvu731Q9JPgN6/S7NhB kcpjdD8/OA6m7MH7VkZkYcz1clVJKDCTRP39MteM4RT4IHZL6+rztnqrgICEccSh b+gP8GUipY4o/V3QBTH09OXkSjyomgobtRFUqtB5DslY3JCdATyZdDKePUyHqZdo /jVeUddde22BkRIoC98QwB1MiNUNXXrHBZqUV9ITlj1BrFC0HpWXJ4d/TP6Sah7W 4+n6eW+2mbRd3w+TzY95Q4i3gT3U4DS6qJKL36DBlKJQhwlmvJjZS1AMEXoaAiMj BBl78u9Bo6meKxKacrwXq9vfgkKwup/kc8vHwEHvJz+nM/j2/Gve0iwjhIuuXoHp XETld9x2LWiEN8xYzI5halycP1eR7eD/IBDm8X6FzUCL+gV3/ORvYxwAR587aprf eS143/GS1JPH9nt+3ILhCGrwaBz8a/HPWE3eYnJqYlH8hVmoxjbZToDjU3FHdCNg zsdcwv1Lg7XtX6wCMiEm =eG9/ -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] detecting binary backup in progress
On 31.05.2013 21:41, Joe Conway wrote: On 05/31/2013 10:46 AM, Heikki Linnakangas wrote: On 31.05.2013 20:38, Joe Conway wrote: I can check for the presence of $PGDATA/backup_label in order to detect a backup in progress (i.e. pg_start_backup() has been run and pg_stop_backup() has not yet been run). 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? What are you trying to accomplish? Even if you eliminate that window, it's always possible that a backup starts just after you've checked that there is no backup in progress. We can lay down a lockfile before checking for backup in progress, and the script that starts the backup can check for the lockfile before doing anything. There is still a race-window, but I want to minimize it. pg_is_in_backup() mentioned nearby on this thread will help close the gap. Note that pg_is_in_backup() just checks for presence of $PGDATA/backup_label. Also note that pg_basebackup doesn't create backup_label in the server. It's included in the backup that's sent to the client, but it's never written to disk in the server. So checking for backup_label manually or with pg_is_in_backup() will return false even if pg_basebackup is running. - Heikki -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] detecting binary backup in progress
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 05/31/2013 12:29 PM, Heikki Linnakangas wrote: Note that pg_is_in_backup() just checks for presence of $PGDATA/backup_label. Also note that pg_basebackup doesn't create backup_label in the server. It's included in the backup that's sent to the client, but it's never written to disk in the server. So checking for backup_label manually or with pg_is_in_backup() will return false even if pg_basebackup is running. Oh, that's really good to know. So essentially there is no reliable way to know that pg_start_backup() has been executed and pg_stop_backup() has not? 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/ iQIcBAEBAgAGBQJRqPuGAAoJEDfy90M199hlAbIP/RkeOMMEc2aw0RjYbQKcGLRX GJFU2MxymtnsbR6kqeijxgeug48NVu1Yx0yyw0rf2aS8pAN6v6Xd5aRIS9li+j/A K6A16+2LxwvtTlyKFkdU/8NP4Uo/xAtG3qOBbfpYNT0YRwuKr8nA9UhWFOEG4+hQ Kz27O5mwjTCzW6WSrre2HZxM4B3J1eWSKQrZ+WDUAmh6GEXAIkIXdDJJnjZqiZcJ rNldh95lEEAPmqZXTq2tANYpIE7UHmRl7Vm0foNHbqsyy+fWGZtqWA4tK8mEVSrQ QdPEa8CjzOAL9gL/bLN3Ti2UimLexKcG6jn9BaUiCNLj5G50tRSiUgH+ur1vSgyv vQYmvi2E3W+L0FABzAuNcsEY1lb2V+JEBQua64m82tOyDvXPpULKw3Bi7PKcxkci M8Si1knYUnWdaeUV3c1ZVbVmtTGtanSF1TiLJH/S7tLb49eoTN3pF3EpIGSXNqcc 4H2Ixemdtu/PZ0FGT9+sKGcIWo+Bp37pdnXXWiGc/kZMML2I+5BK+/Pd6Tm3nJFH MDVI7NrPUqCtSHbrDZ/Uv9fKLulaq0kHznyNg9tW0XBwHNpM9RJcbw9Vq88UgDDW mlaTCvR6G7GM72R1+czM/ek3Oy7v/sPZQmeJrVk0OdPqCe31myyBYP2XRWMW2cW2 2S7SSN2LnQD1YF9VrrYt =c9gP -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] detecting binary backup in progress
On 2013-05-31 22:29:45 +0300, Heikki Linnakangas wrote: On 31.05.2013 21:41, Joe Conway wrote: On 05/31/2013 10:46 AM, Heikki Linnakangas wrote: On 31.05.2013 20:38, Joe Conway wrote: I can check for the presence of $PGDATA/backup_label in order to detect a backup in progress (i.e. pg_start_backup() has been run and pg_stop_backup() has not yet been run). 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? What are you trying to accomplish? Even if you eliminate that window, it's always possible that a backup starts just after you've checked that there is no backup in progress. We can lay down a lockfile before checking for backup in progress, and the script that starts the backup can check for the lockfile before doing anything. There is still a race-window, but I want to minimize it. pg_is_in_backup() mentioned nearby on this thread will help close the gap. Note that pg_is_in_backup() just checks for presence of $PGDATA/backup_label. Also note that pg_basebackup doesn't create backup_label in the server. It's included in the backup that's sent to the client, but it's never written to disk in the server. So checking for backup_label manually or with pg_is_in_backup() will return false even if pg_basebackup is running. Whoa. You are right, but I'd call that a bug. I don't understand why we aren't just checking XLogCtl-Insert.(nonExclusiveBackups||exlusiveBackup)? I vote for changing this before we release pg_is_in_backup(). Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] detecting binary backup in progress
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 05/31/2013 12:36 PM, Andres Freund wrote: On 2013-05-31 22:29:45 +0300, Heikki Linnakangas wrote: Note that pg_is_in_backup() just checks for presence of $PGDATA/backup_label. Also note that pg_basebackup doesn't create backup_label in the server. It's included in the backup that's sent to the client, but it's never written to disk in the server. So checking for backup_label manually or with pg_is_in_backup() will return false even if pg_basebackup is running. Whoa. You are right, but I'd call that a bug. I don't understand why we aren't just checking XLogCtl-Insert.(nonExclusiveBackups||exlusiveBackup)? I vote for changing this before we release pg_is_in_backup(). +1 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/ iQIcBAEBAgAGBQJRqP4QAAoJEDfy90M199hl7tEQAJd4afwW3AM5CpZSjG0gcrrD P795WAE7UH+lyTCjM1XUunMD8q9cqgnDkl2RXxjow/QD7POEOB85+6T5pHQwsqkN gSjLKUha3Q58tQHQqFOkclCWkiIRCAltvwmALYYl2W9Enlbef0vQo0Za1Rfoe4FB H5xGPAogsbaHQQGjorNcWHylxyGd/epDsTMbTfzS5LZwpK3H+KYqegs+ULHImvTv 1LS7thRe4LFC8xQ4lImZCI3ZdxhcLSL37f3b/nxv5bjM20ZRJynT4ZTMwFfALEK+ ol76zOcLLKUy7xBq+S/MFHXG5Oq+nUA3ZTokwigJPLmLI+nDC/9gFU0NfkUNoeao 5D5hCqulaqBdtbp8I+pQtY1pv/6ZdKtLbIdE7LKpvAAkh+0kCABZxyWv1f2w1DPQ VMQmmb56kKCAdk2/300pa2q0b/KuC6rjYjHgtvyz5bmhCs0wQ7FUuQeO4tlhzrGI +rW1eoi5rI79lbKp8y626ZG6XhvqKrZ4sUEjcE52lePhEcaE0F7uS/yFhzS5uv6Y wlrKWxglaFcdf4mPFM3ysxHP1i50kxZ+YcXY1F2Cn5WIWiRb0a9qjeYMP2NU6gCB 4/2+TrDWpO8JWuMrxAMlHB9QrbcW5iPyenwklVo0dh5pP0XuYt2oqUNqFrRJYHBQ SgQ7sGyuuxta/2qapWiS =e17o -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] detecting binary backup in progress
On 31.05.2013 22:36, Andres Freund wrote: On 2013-05-31 22:29:45 +0300, Heikki Linnakangas wrote: Note that pg_is_in_backup() just checks for presence of $PGDATA/backup_label. Also note that pg_basebackup doesn't create backup_label in the server. It's included in the backup that's sent to the client, but it's never written to disk in the server. So checking for backup_label manually or with pg_is_in_backup() will return false even if pg_basebackup is running. Whoa. You are right, but I'd call that a bug. I don't understand why we aren't just checking XLogCtl-Insert.(nonExclusiveBackups||exlusiveBackup)? Well, depends on what you imagine the function is used for. If you think of it as will pg_start_backup() throw an error if I call it now, or do I need to call pg_stop_backup(), then the current behavior is correct. The manual says: pg_is_in_backup() boolTrue if an on-line exclusive backup is still in progress. So clearly that is intentional. That could use some rephrasing, though; a layman won't know what an exclusive backup is. - Heikki -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] detecting binary backup in progress
Heikki Linnakangas wrote: The manual says: pg_is_in_backup()boolTrue if an on-line exclusive backup is still in progress. So clearly that is intentional. That could use some rephrasing, though; a layman won't know what an exclusive backup is. Heck, I don't understand what it is either. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] detecting binary backup in progress
On 2013-05-31 22:53:14 +0300, Heikki Linnakangas wrote: On 31.05.2013 22:36, Andres Freund wrote: On 2013-05-31 22:29:45 +0300, Heikki Linnakangas wrote: Note that pg_is_in_backup() just checks for presence of $PGDATA/backup_label. Also note that pg_basebackup doesn't create backup_label in the server. It's included in the backup that's sent to the client, but it's never written to disk in the server. So checking for backup_label manually or with pg_is_in_backup() will return false even if pg_basebackup is running. Whoa. You are right, but I'd call that a bug. I don't understand why we aren't just checking XLogCtl-Insert.(nonExclusiveBackups||exlusiveBackup)? Well, depends on what you imagine the function is used for. If you think of it as will pg_start_backup() throw an error if I call it now, or do I need to call pg_stop_backup(), then the current behavior is correct. The manual says: pg_is_in_backup()boolTrue if an on-line exclusive backup is still in progress. So clearly that is intentional. Well, just because it's intentional, doesn't mean its a good idea ;). There very well are reasons to check for in progress non-exclusive backups as well. You e.g. wouldn't want to restart the database while the weekly base backup of your 1TB database is in progress, just because it's done via the replication protocol. If we weren't in beta 1 already I'd vote for making it into: pg_backup_in_progress(OUT bool exclusive, OUT int non_exclusive) or similar. Perhaps we should do that anyway? That could use some rephrasing, though; a layman won't know what an exclusive backup is. True. Although I have to admit I can't come up with a succinct name for it it right now. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
