Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id A043BB5DBF5 for ; Wed, 29 Jun 2011 09:24:38 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 67318-04 for ; Wed, 29 Jun 2011 12:24:32 +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 15FE8B5DBDA for ; Wed, 29 Jun 2011 09:24:31 -0300 (ADT) Received: by pvg12 with SMTP id 12so725416pvg.19 for ; Wed, 29 Jun 2011 05:24:31 -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:content-transfer-encoding; bh=7VXj7bAjT3xOKMc2SsldDEtTQFGv2cz3X1kgPK3T338=; b=ham34w3O1AHu/qfdType16YeGSrGrbLPxuzgjIMPEGAgoTF9rsFm++GIk7AhTKYC7F 9/6s2ITLc+epgob+/3v05hvAA6vHXUcxehMULgYPOBCyMta12kKlLf/R1zzatqIfkVpQ n3RgRhI1MLsVv+C2BpHgYsBaNNBFfUrSpOaDA= MIME-Version: 1.0 Received: by 10.68.1.195 with SMTP id 3mr1012197pbo.42.1309350271805; Wed, 29 Jun 2011 05:24:31 -0700 (PDT) Received: by 10.68.43.67 with HTTP; Wed, 29 Jun 2011 05:24:31 -0700 (PDT) In-Reply-To: References: <26007.1303309175@sss.pgh.pa.us> <27149.1303312506@sss.pgh.pa.us> Date: Wed, 29 Jun 2011 08:24:31 -0400 Message-ID: Subject: Re: time-delayed standbys From: Robert Haas To: Simon Riggs Cc: Fujii Masao , Tom Lane , Greg Stark , pgsql-hackers Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable 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/2231 X-Sequence-Number: 190944 On Wed, Jun 29, 2011 at 4:00 AM, Simon Riggs wrote: > On Thu, Jun 16, 2011 at 7:29 PM, Robert Haas wrot= e: >> On Wed, Jun 15, 2011 at 1:58 AM, Fujii Masao wro= te: >>> When the replication connection is terminated, the standby tries to rea= d >>> WAL files from the archive. In this case, there is no walreceiver proce= ss, >>> so how does the standby calculate the clock difference? >> >> Good question. =A0Also, just because we have streaming replication >> available doesn't mean that we should force people to use it. =A0It's >> still perfectly legit to set up a standby that only use >> archive_command and restore_command, and it would be nice if this >> feature could still work in such an environment. =A0I anticipate that >> most people want to use streaming replication, but a time-delayed >> standby is a good example of a case where you might decide you don't >> need it. =A0It could be useful to have all the WAL present (but not yet >> applied) if you're thinking you might want to promote that standby - >> but my guess is that in many cases, the time-delayed standby will be >> *in addition* to one or more regular standbys that would be the >> primary promotion candidates. =A0So I can see someone deciding that >> they'd rather not have the load of another walsender on the master, >> and just let the time-delayed standby read from the archive. >> >> Even if that were not an issue, I'm still more or less of the opinion >> that trying to solve the time synchronization problem is a rathole >> anyway. =A0To really solve this problem well, you're going to need the >> standby to send a message containing a timestamp, get a reply back >> from the master that contains that timestamp and a master timestamp, >> and then compute based on those two timestamps plus the reply >> timestamp the maximum and minimum possible lag between the two >> machines. =A0Then you're going to need to guess, based on several cycles >> of this activity, what the actual lag is, and adjust it over time (but >> not too quckly, unless of course a large manual step has occurred) as >> the clocks potentially drift apart from each other. =A0This is basically >> what ntpd does, except that it can be virtually guaranteed that our >> implementation will suck by comparison. =A0Time synchronization is >> neither easy nor our core competency, and I think trying to include it >> in this feature is going to result in a net loss of reliability. > > > This begs the question of why we need this feature at all, in the way pro= posed. > > Streaming replication is designed for immediate transfer of WAL. File > based is more about storing them for some later use. > > It seems strange to pollute the *immediate* transfer route with a > delay, when that is easily possible with a small patch to pg_standby > that can wait until the filetime delay is > X before returning. > > The main practical problem with this is that most people's WAL > partitions aren't big enough to store the delayed WAL files, which is > why we provide the file archiving route anyway. So in practical terms > this will be unusable, or at least dangerous to use. > > +1 for the feature concept, but -1 for adding this to streaming replicati= on. As implemented, the feature will work with either streaming replication or with file-based replication. I don't see any value in restricting to work ONLY with file-based replication. Also, if we were to do it by making pg_standby wait, then the whole thing would be much less accurate, and the delay would become much harder to predict, because you'd be operating on the level of entire WAL segments, rather than individual commit records. --=20 Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company