Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 40068B5DBD5 for ; Tue, 28 Jun 2011 23:15:03 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 25564-02 for ; Wed, 29 Jun 2011 02:14:57 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-pv0-f174.google.com (mail-pv0-f174.google.com [74.125.83.174]) by mail.postgresql.org (Postfix) with ESMTP id 7C85FB5D803 for ; Tue, 28 Jun 2011 23:14:56 -0300 (ADT) Received: by pvg12 with SMTP id 12so473458pvg.19 for ; Tue, 28 Jun 2011 19:14:56 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; bh=BNDwGlMzvwkalJqrimEvqP232MHPTXchArEZigaf5+4=; b=m+WOBkLy99RJ+Cv2h5ZBo7nM8A5l66Sfra6encZm4xJls36++5WDQBujEicPGLXFEi QL7SWFguB1MHw5e3zpeypVyjlIlli3oZTW3KUa713aF70v8r7arFE3aay/BkYiwGBiY8 abJL5L7Nfr8KGgPXHNYuuzO6o/+egEAbavPks= MIME-Version: 1.0 Received: by 10.68.29.229 with SMTP id n5mr441389pbh.176.1309313696151; Tue, 28 Jun 2011 19:14:56 -0700 (PDT) Received: by 10.68.43.67 with HTTP; Tue, 28 Jun 2011 19:14:56 -0700 (PDT) In-Reply-To: References: <26007.1303309175@sss.pgh.pa.us> <27149.1303312506@sss.pgh.pa.us> Date: Tue, 28 Jun 2011 22:14:56 -0400 Message-ID: Subject: Re: time-delayed standbys From: Robert Haas To: Fujii Masao Cc: Tom Lane , Greg Stark , pgsql-hackers@postgresql.org Content-Type: multipart/mixed; boundary=bcaec520ede9c2381b04a6d05920 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-5 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001 X-Spam-Level: X-Archive-Number: 201106/2222 X-Sequence-Number: 190935 --bcaec520ede9c2381b04a6d05920 Content-Type: text/plain; charset=ISO-8859-1 On Wed, Jun 15, 2011 at 1:58 AM, Fujii Masao wrote: > After we run "pg_ctl promote", time-delayed replication should be disabled? > Otherwise, failover might take very long time when we set recovery_time_delay > to high value. PFA a patch that I believe will disable recovery_time_delay after promotion. The only change from the previous version is: diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog index 1dbf792..41b3ae9 100644 --- a/src/backend/access/transam/xlog.c +++ b/src/backend/access/transam/xlog.c @@ -5869,7 +5869,7 @@ pg_is_xlog_replay_paused(PG_FUNCTION_ARGS) static void recoveryDelay(void) { - while (1) + while (!CheckForStandbyTrigger()) { long secs; int microsecs; -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company --bcaec520ede9c2381b04a6d05920 Content-Type: application/octet-stream; name="time-delayed-standby-v2.patch" Content-Disposition: attachment; filename="time-delayed-standby-v2.patch" Content-Transfer-Encoding: base64 X-Attachment-Id: f_gphnjznd0 Y29tbWl0IDgyZTIyYmViYjMwYjZiMWQxODQwMzkxMzFkOTRhYjI2YTNkNGI5MmUKQXV0aG9yOiBS b2JlcnQgSGFhcyA8cmhhYXNAcG9zdGdyZXNxbC5vcmc+CkRhdGU6ICAgVHVlIEFwciAxMiAyMjo1 MDoxMyAyMDExIC0wNzAwCgogICAgU3VwcG9ydCB0aW1lLWRlbGF5ZWQgc3RhbmRieXMuCiAgICAK ICAgIFJldmlldyBieSBHcmVnIFN0YXJrLCBKYWltZSBDYXNhbm92YSwgRnVqaWkgTWFzYW8uCgpk aWZmIC0tZ2l0IGEvZG9jL3NyYy9zZ21sL3JlY292ZXJ5LWNvbmZpZy5zZ21sIGIvZG9jL3NyYy9z Z21sL3JlY292ZXJ5LWNvbmZpZy5zZ21sCmluZGV4IGRlNjA5MDUuLjM3NWY0M2IgMTAwNjQ0Ci0t LSBhL2RvYy9zcmMvc2dtbC9yZWNvdmVyeS1jb25maWcuc2dtbAorKysgYi9kb2Mvc3JjL3NnbWwv cmVjb3ZlcnktY29uZmlnLnNnbWwKQEAgLTI3OSw2ICsyNzksMjggQEAgcmVzdG9yZV9jb21tYW5k ID0gJ2NvcHkgIkM6XFxzZXJ2ZXJcXGFyY2hpdmVkaXJcXCVmIiAiJXAiJyAgIyBXaW5kb3dzCiAg ICAgICA8L2xpc3RpdGVtPgogICAgICA8L3Zhcmxpc3RlbnRyeT4KIAorICAgICA8dmFybGlzdGVu dHJ5IGlkPSJyZWNvdmVyeS10aW1lLWRlbGF5IgorICAgICAgICAgICAgICAgICAgIHhyZWZsYWJl bD0icmVjb3ZlcnlfdGltZV9kZWxheSI+CisgICAgICA8dGVybT48dmFybmFtZT5yZWNvdmVyeV90 aW1lX2RlbGF5PC92YXJuYW1lPgorICAgICAgICAoPHR5cGU+aW50ZWdlcjwvdHlwZT4pCisgICAg ICA8L3Rlcm0+CisgICAgICA8aW5kZXh0ZXJtPgorICAgICAgICA8cHJpbWFyeT48dmFybmFtZT5y ZWNvdmVyeV90aW1lX2RlbGF5PC8+IHJlY292ZXJ5IHBhcmFtZXRlcjwvcHJpbWFyeT4KKyAgICAg IDwvaW5kZXh0ZXJtPgorICAgICAgPGxpc3RpdGVtPgorICAgICAgIDxwYXJhPgorICAgICAgICBT cGVjaWZpZXMgdGhlIGFtb3VudCBvZiB0aW1lIChpbiBtaWxsaXNlY29uZHMsIGlmIG5vIHVuaXQg aXMgc3BlY2lmaWVkKQorICAgICAgICB3aGljaCByZWNvdmVyeSBvZiB0cmFuc2FjdGlvbiBjb21t aXRzIHNob3VsZCBsYWcgdGhlIG1hc3Rlci4gIFRoaXMKKyAgICAgICAgcGFyYW1ldGVyIGFsbG93 cyBjcmVhdGlvbiBvZiBhIHRpbWUtZGVsYXllZCBzdGFuZGJ5LiAgRm9yIGV4YW1wbGUsIGlmCisg ICAgICAgIHlvdSBzZXQgdGhpcyBwYXJhbWV0ZXIgdG8gPGxpdGVyYWw+NW1pbjwvbGl0ZXJhbD4s IHRoZSBzdGFuZGJ5IHdpbGwKKyAgICAgICAgcmVwbGF5IGVhY2ggdHJhbnNhY3Rpb24gY29tbWl0 IG9ubHkgd2hlbiB0aGUgc3lzdGVtIHRpbWUgb24gdGhlIHN0YW5kYnkKKyAgICAgICAgaXMgYXQg bGVhc3QgZml2ZSBtaW51dGVzIHBhc3QgdGhlIGNvbW1pdCB0aW1lIHJlcG9ydGVkIGJ5IHRoZSBt YXN0ZXIuCisgICAgICAgIE5vdGUgdGhhdCBpZiB0aGUgbWFzdGVyIGFuZCBzdGFuZGJ5IHN5c3Rl bSBjbG9ja3MgYXJlIG5vdCBzeW5jaHJvbml6ZWQsCisgICAgICAgIHRoaXMgbWlnaHQgbGVhZCB0 byB1bmV4cGVjdGVkIHJlc3VsdHMuCisgICAgICAgPC9wYXJhPgorICAgICAgPC9saXN0aXRlbT4K KyAgICAgPC92YXJsaXN0ZW50cnk+CisKICAgICAgPC92YXJpYWJsZWxpc3Q+CiAgICA8L3NlY3Qx PgogCmRpZmYgLS1naXQgYS9zcmMvYmFja2VuZC9hY2Nlc3MvdHJhbnNhbS94bG9nLmMgYi9zcmMv YmFja2VuZC9hY2Nlc3MvdHJhbnNhbS94bG9nLmMKaW5kZXggNDk1MmQyMi4uNDFiM2FlOSAxMDA2 NDQKLS0tIGEvc3JjL2JhY2tlbmQvYWNjZXNzL3RyYW5zYW0veGxvZy5jCisrKyBiL3NyYy9iYWNr ZW5kL2FjY2Vzcy90cmFuc2FtL3hsb2cuYwpAQCAtMTk1LDYgKzE5NSw4IEBAIHN0YXRpYyBib29s IHJlY292ZXJ5UGF1c2VBdFRhcmdldCA9IHRydWU7CiBzdGF0aWMgVHJhbnNhY3Rpb25JZCByZWNv dmVyeVRhcmdldFhpZDsKIHN0YXRpYyBUaW1lc3RhbXBUeiByZWNvdmVyeVRhcmdldFRpbWU7CiBz dGF0aWMgY2hhciAqcmVjb3ZlcnlUYXJnZXROYW1lOworc3RhdGljIGludCByZWNvdmVyeV90aW1l X2RlbGF5ID0gMDsKK3N0YXRpYyBUaW1lc3RhbXBUeiByZWNvdmVyeURlbGF5VW50aWxUaW1lOwog CiAvKiBvcHRpb25zIHRha2VuIGZyb20gcmVjb3ZlcnkuY29uZiBmb3IgWExPRyBzdHJlYW1pbmcg Ki8KIHN0YXRpYyBib29sIFN0YW5kYnlNb2RlID0gZmFsc2U7CkBAIC02MDYsNiArNjA4LDcgQEAg c3RhdGljIGJvb2wgcmVjb3ZlcnlTdG9wc0hlcmUoWExvZ1JlY29yZCAqcmVjb3JkLCBib29sICpp bmNsdWRlVGhpcyk7CiBzdGF0aWMgdm9pZCByZWNvdmVyeVBhdXNlc0hlcmUodm9pZCk7CiBzdGF0 aWMgYm9vbCBSZWNvdmVyeUlzUGF1c2VkKHZvaWQpOwogc3RhdGljIHZvaWQgU2V0UmVjb3ZlcnlQ YXVzZShib29sIHJlY292ZXJ5UGF1c2UpOworc3RhdGljIHZvaWQgcmVjb3ZlcnlEZWxheSh2b2lk KTsKIHN0YXRpYyB2b2lkIFNldExhdGVzdFhUaW1lKFRpbWVzdGFtcFR6IHh0aW1lKTsKIHN0YXRp YyBUaW1lc3RhbXBUeiBHZXRMYXRlc3RYVGltZSh2b2lkKTsKIHN0YXRpYyB2b2lkIENoZWNrUmVx dWlyZWRQYXJhbWV0ZXJWYWx1ZXModm9pZCk7CkBAIC01Mzk2LDYgKzUzOTksMTkgQEAgcmVhZFJl Y292ZXJ5Q29tbWFuZEZpbGUodm9pZCkKIAkJCQkJKGVycm1zZygidHJpZ2dlcl9maWxlID0gJyVz JyIsCiAJCQkJCQkJVHJpZ2dlckZpbGUpKSk7CiAJCX0KKwkJZWxzZSBpZiAoc3RyY21wKGl0ZW0t Pm5hbWUsICJyZWNvdmVyeV90aW1lX2RlbGF5IikgPT0gMCkKKwkJeworCQkJY29uc3QgY2hhciAq aGludG1zZzsKKworCQkJaWYgKCFwYXJzZV9pbnQoaXRlbS0+dmFsdWUsICZyZWNvdmVyeV90aW1l X2RlbGF5LCBHVUNfVU5JVF9NUywKKwkJCQkmaGludG1zZykpCisJCQkJZXJlcG9ydChFUlJPUiwK KwkJCQkJCShlcnJjb2RlKEVSUkNPREVfSU5WQUxJRF9QQVJBTUVURVJfVkFMVUUpLAorCQkJCQkJ IGVycm1zZygicGFyYW1ldGVyIFwiJXNcIiByZXF1aXJlcyBhIHRlbXBvcmFsIHZhbHVlIiwgInJl Y292ZXJ5X3RpbWVfZGVsYXkiKSwKKwkJCQkJCSBoaW50bXNnID8gZXJyaGludCgiJXMiLCBfKGhp bnRtc2cpKSA6IDApKTsKKwkJCWVyZXBvcnQoREVCVUcyLAorCQkJCQkoZXJybXNnKCJyZWNvdmVy eV90aW1lX2RlbGF5ID0gJyVzJyIsIGl0ZW0tPnZhbHVlKSkpOworCQl9CiAJCWVsc2UKIAkJCWVy ZXBvcnQoRkFUQUwsCiAJCQkJCShlcnJtc2coInVucmVjb2duaXplZCByZWNvdmVyeSBwYXJhbWV0 ZXIgXCIlc1wiIiwKQEAgLTU1NzksNyArNTU5NSw4IEBAIGV4aXRBcmNoaXZlUmVjb3ZlcnkoVGlt ZUxpbmVJRCBlbmRUTEksIHVpbnQzMiBlbmRMb2dJZCwgdWludDMyIGVuZExvZ1NlZykKICAqIFdl IGFsc28gdHJhY2sgdGhlIHRpbWVzdGFtcCBvZiB0aGUgbGF0ZXN0IGFwcGxpZWQgQ09NTUlUL0FC T1JUCiAgKiByZWNvcmQgaW4gWExvZ0N0bC0+cmVjb3ZlcnlMYXN0WFRpbWUsIGZvciBsb2dnaW5n IHB1cnBvc2VzLgogICogQWxzbywgc29tZSBpbmZvcm1hdGlvbiBpcyBzYXZlZCBpbiByZWNvdmVy eVN0b3BYaWQgZXQgYWwgZm9yIHVzZSBpbgotICogYW5ub3RhdGluZyB0aGUgbmV3IHRpbWVsaW5l J3MgaGlzdG9yeSBmaWxlLgorICogYW5ub3RhdGluZyB0aGUgbmV3IHRpbWVsaW5lJ3MgaGlzdG9y eSBmaWxlOyBhbmQgcmVjb3ZlcnlEZWxheVVudGlsVGltZQorICogaXMgdXBkYXRlZCwgZm9yIHRp bWUtZGVsYXllZCBzdGFuZGJ5cy4KICAqLwogc3RhdGljIGJvb2wKIHJlY292ZXJ5U3RvcHNIZXJl KFhMb2dSZWNvcmQgKnJlY29yZCwgYm9vbCAqaW5jbHVkZVRoaXMpCkBAIC01NTg5LDYgKzU2MDYs OSBAQCByZWNvdmVyeVN0b3BzSGVyZShYTG9nUmVjb3JkICpyZWNvcmQsIGJvb2wgKmluY2x1ZGVU aGlzKQogCVRpbWVzdGFtcFR6IHJlY29yZFh0aW1lOwogCWNoYXIJCXJlY29yZFJQTmFtZVtNQVhG TkFNRUxFTl07CiAKKwkvKiBDbGVhciBhbnkgcHJldmlvdXMgcmVjb3ZlcnkgZGVsYXkgdGltZSAq LworCXJlY292ZXJ5RGVsYXlVbnRpbFRpbWUgPSAwOworCiAJLyogV2Ugb25seSBjb25zaWRlciBz dG9wcGluZyBhdCBDT01NSVQsIEFCT1JUIG9yIFJFU1RPUkUgUE9JTlQgcmVjb3JkcyAqLwogCWlm IChyZWNvcmQtPnhsX3JtaWQgIT0gUk1fWEFDVF9JRCAmJiByZWNvcmQtPnhsX3JtaWQgIT0gUk1f WExPR19JRCkKIAkJcmV0dXJuIGZhbHNlOwpAQCAtNTU5OSw2ICs1NjE5LDEwIEBAIHJlY292ZXJ5 U3RvcHNIZXJlKFhMb2dSZWNvcmQgKnJlY29yZCwgYm9vbCAqaW5jbHVkZVRoaXMpCiAKIAkJcmVj b3JkWGFjdENvbW1pdERhdGEgPSAoeGxfeGFjdF9jb21taXQgKikgWExvZ1JlY0dldERhdGEocmVj b3JkKTsKIAkJcmVjb3JkWHRpbWUgPSByZWNvcmRYYWN0Q29tbWl0RGF0YS0+eGFjdF90aW1lOwor CQlpZiAocmVjb3ZlcnlfdGltZV9kZWxheSA+IDApCisJCQlyZWNvdmVyeURlbGF5VW50aWxUaW1l ID0KKwkJCQlUaW1lc3RhbXBUelBsdXNNaWxsaXNlY29uZHMocmVjb3JkWGFjdENvbW1pdERhdGEt PnhhY3RfdGltZSwKKwkJCQkJCQkJCQkJcmVjb3ZlcnlfdGltZV9kZWxheSk7CiAJfQogCWVsc2Ug aWYgKHJlY29yZC0+eGxfcm1pZCA9PSBSTV9YQUNUX0lEICYmIHJlY29yZF9pbmZvID09IFhMT0df WEFDVF9BQk9SVCkKIAl7CkBAIC01ODM5LDYgKzU4NjMsNDcgQEAgcGdfaXNfeGxvZ19yZXBsYXlf cGF1c2VkKFBHX0ZVTkNUSU9OX0FSR1MpCiB9CiAKIC8qCisgKiBXaGVuIHJlY292ZXJ5X3RpbWVf ZGVsYXkgaXMgc2V0LCB3ZSB3YWl0IGxvbmcgZW5vdWdoIHRvIG1ha2Ugc3VyZSB3ZSBhcmUKKyAq IGF0IGxlYXN0IHRoYXQgZmFyIGJlaGluZCB0aGUgbWFzdGVyLgorICovCitzdGF0aWMgdm9pZAor cmVjb3ZlcnlEZWxheSh2b2lkKQoreworCXdoaWxlICghQ2hlY2tGb3JTdGFuZGJ5VHJpZ2dlcigp KQorCXsKKwkJbG9uZwlzZWNzOworCQlpbnQJCW1pY3Jvc2VjczsKKworCQlIYW5kbGVTdGFydHVw UHJvY0ludGVycnVwdHMoKTsKKwkJVGltZXN0YW1wRGlmZmVyZW5jZShHZXRDdXJyZW50VGltZXN0 YW1wKCksIHJlY292ZXJ5RGVsYXlVbnRpbFRpbWUsCisJCQkJCQkJJnNlY3MsICZtaWNyb3NlY3Mp OworCisJCS8qCisJCSAqIElmIHRoZSB0aW1lc3RhbXAgd2UgZm91bmQgaW4gdGhlIGNvbW1pdCBy ZWNvcmQgaXMgb3V0IG9mIHdoYWNrLCB3ZQorCQkgKiByZWZ1c2UgdG8gd2FpdC4gIEluIHRoZSB3 b3JzdCBjYXNlIHRoaXMgbWlnaHQgbWVhbiBnb2luZyBpbnRvIHRoZQorCQkgKiB0YW5rIGVzc2Vu dGlhbGx5IGZvcmV2ZXIuCisJCSAqLworCQlpZiAoc2VjcyA+IChyZWNvdmVyeV90aW1lX2RlbGF5 IC8gMTAwMCkgKyAxKQorCQl7CisJCQllbG9nKExPRywgInJlY292ZXJ5IGRlbGF5IGlzICVkIHNl Y29uZHMsIHNraXBwaW5nIHdhaXQgb2YgJWxkIHNlY29uZHMiLAorCQkJCXJlY292ZXJ5X3RpbWVf ZGVsYXkgLyAxMDAwLCBzZWNzKTsKKwkJCWJyZWFrOworCQl9CisKKyAgCQkvKgorCQkgKiBTbGVl cCBpbiBpbnRlcnZhbHMgb2YgYXQgbW9zdCBvbmUgc2Vjb25kLCBzbyB0aGF0IHdlIGNhbiBiZQor CQkgKiByZXNwb25zaXZlIHRvIGludGVycnVwdHMgYW5kIGNoYW5nZXMgaW4gdGhlIHN5c3RlbSBj bG9jay4KKwkJICovCisJCWlmIChzZWNzID4gMCkKKwkJCXBnX3VzbGVlcChVU0VDU19QRVJfU0VD KTsKKwkJZWxzZSBpZiAobWljcm9zZWNzID4gMCkKKwkJCXBnX3VzbGVlcChtaWNyb3NlY3MpOwor CQllbHNlCisJCQlicmVhazsKKwl9Cit9CisKKy8qCiAgKiBTYXZlIHRpbWVzdGFtcCBvZiBsYXRl c3QgcHJvY2Vzc2VkIGNvbW1pdC9hYm9ydCByZWNvcmQuCiAgKgogICogV2Uga2VlcCB0aGlzIGlu IFhMb2dDdGwsIG5vdCBhIHNpbXBsZSBzdGF0aWMgdmFyaWFibGUsIHNvIHRoYXQgaXQgY2FuIGJl CkBAIC02NTczLDYgKzY2MzgsMTMgQEAgU3RhcnR1cFhMT0codm9pZCkKIAkJCQkJcmVjb3ZlcnlQ YXVzZXNIZXJlKCk7CiAKIAkJCQkvKgorCQkJCSAqIElmIHdlJ3ZlIGJlZW4gYXNrZWQgdG8gbGFn IHRoZSBtYXN0ZXIsIHBhdXNlIHVudGlsIGVub3VnaAorCQkJCSAqIHRpbWUgaGFzIHBhc3NlZC4K KwkJCQkgKi8KKwkJCQlpZiAocmVjb3ZlcnlfdGltZV9kZWxheSA+IDApCisJCQkJCXJlY292ZXJ5 RGVsYXkoKTsKKworCQkJCS8qCiAJCQkJICogSWYgd2UgYXJlIGF0dGVtcHRpbmcgdG8gZW50ZXIg SG90IFN0YW5kYnkgbW9kZSwgcHJvY2VzcwogCQkJCSAqIFhJRHMgd2Ugc2VlCiAJCQkJICovCg== --bcaec520ede9c2381b04a6d05920--