Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 91B142E009B for ; Thu, 28 Feb 2008 10:36:24 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 38774-01 for ; Thu, 28 Feb 2008 10:36:12 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from ag-out-0708.google.com (ag-out-0708.google.com [72.14.246.251]) by postgresql.org (Postfix) with ESMTP id 331412E0074 for ; Thu, 28 Feb 2008 10:36:20 -0400 (AST) Received: by ag-out-0708.google.com with SMTP id 5so8959928agb.7 for ; Thu, 28 Feb 2008 06:36:19 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; bh=Us1PHtfWidog2fgBRvClZmwqRXaUw4cQ4tCjdajxcuo=; b=ldeS+unXIhj30sBBZ0gJDFmBpiGWkyMVw6rP2m6g5WmMBv5jSAy/TQmioZkheJ6FU5Uqf81fnF1ZJBd1y6wMZMpRNJf++ditRM2dQkrIQ8QAjjE9y695++JU5b4Flgxf91gUnlEYhTBJtIjj2ioRekqiSQN0STGomPT1YfSKGsQ= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=WLi3FUPMa5/qOPlSv+3rYu4fLp7UpFozaOcXmkVkArvMKWAlR1uXN6CeB969k8SqrvtZx4pOeHrO0WKGVsYWdU248o2fG5H2swgLVZBRPOiTqocMf7R1OPheAfibKpLxAEYI5pClb9n41uHub0Bgs8xqCjE3+1Jz0sZni1gjjEs= Received: by 10.142.52.9 with SMTP id z9mr6326473wfz.201.1204209376236; Thu, 28 Feb 2008 06:36:16 -0800 (PST) Received: by 10.142.213.12 with HTTP; Thu, 28 Feb 2008 06:36:16 -0800 (PST) Message-ID: <9abe84da0802280636n499f162co8d966d1ffcb7f78e@mail.gmail.com> Date: Thu, 28 Feb 2008 09:36:16 -0500 From: "libra dba" To: salman Subject: Re: Failover of the Primary database and starting the standby database in Postgresql in PITR configuraiton? Cc: pgsql-admin@postgresql.org In-Reply-To: <47C5D0AB.7070102@quietcaresystems.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_26740_26359357.1204209376217" References: <84D57263D486374587DA0820E60A2CC601B07E23@srit_mail.renaissance-it.com> <1203409158.16770.821.camel@ebony.site> <9abe84da0802190623o2a24f2a5u9a44299fd634618e@mail.gmail.com> <47BAFC47.4060902@quietcaresystems.com> <9abe84da0802200849w1f462558n270f4320a7465b6c@mail.gmail.com> <9abe84da0802271254j3a6463c8j7ccbe603bcdbf477@mail.gmail.com> <47C5D0AB.7070102@quietcaresystems.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200802/400 X-Sequence-Number: 28474 ------=_Part_26740_26359357.1204209376217 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hey Salman, Thanks a lot for all your guidance and help. It has really been a great help. I appreciate your quick replies. Which replication in postgres is better ? SLONY or WARM STANDBY (usning wal). Another thing which haunts me is that, if we have a warm standby, the recovery process on the standby server reads an archived file only if it is 16MB, if it is small, the standby server goes down as shown *quote from the logfile on standby* ~~~~~~~~~~~~~~~~~~~~~~~~~~ *FATAL: archive file "000000010000000100000032" has wrong size: 491520 instead of 16777216 LOG: startup process (PID 13177) exited with exit code 1 LOG: aborting startup due to startup process failure LOG: database system was interrupted while in recovery at log time 2008-02-28 00:47:39 EST * Usually, an archive file is written every minute. Now, if the last archive was written at 12:05:00, and the primary server goes down at 12:05:37, the archive is not generated for the last 37 seconds. The archive file which was written at 12:05:00 would be applied to the standby. But what will happen to transactions which happen in the last 37 seconds before the crash??? Do we loose that data??? Thanks! On Wed, Feb 27, 2008 at 4:05 PM, salman wrote: > > > libra dba wrote: > > continuing with the postgres replication using WARM STANDBY, what > happens to > > the wal_files which keeps on generating on the primary? > > > > Do we have to manually remove them or they are removed automatically, > once > > they have been recovered on the warm standby? > > > > Also a quick question, do we have to have a NFS for the wal_files, which > > will be mounted on both the servers, or we can have folders on each > server > > and configure DRBD between them, so that whatever happens on one is > copied > > automatically on to the other. > > > > Thanks! > > > > This is what I use -- may not be the best approach, but it has worked > quite well for me so far: > > archive_command = 'gzip %p; rsync -av %p.gz -e \"ssh -p portNum -i > /home/postgres/.ssh/id_dsa\" > postgres@rhostname:/usr/local/postgresql/archives/recovery/%f.gz && rm > -f %p.gz' > > > > > ------=_Part_26740_26359357.1204209376217 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
Hey Salman,
 
Thanks a lot for  all your guidance and help. It has really been a great help. I appreciate your quick replies.
 
Which replication in postgres is better  ?
SLONY or WARM STANDBY (usning wal).

Another thing which haunts me is that, if we have a warm standby, the recovery process on the standby server reads an archived file only if it is 16MB, if it is small, the standby server goes down as shown
 
quote from the logfile on standby
~~~~~~~~~~~~~~~~~~~~~~~~~~
FATAL:  archive file "000000010000000100000032" has wrong size: 491520 instead of 16777216
LOG:  startup process (PID 13177) exited with exit code 1
LOG:  aborting startup due to startup process failure
LOG:  database system was interrupted while in recovery at log time 2008-02-28 00:47:39 EST

Usually, an archive file is written every minute. Now, if the last archive was written at 12:05:00, and the primary server goes down at 12:05:37, the archive is not generated for the last 37 seconds. The archive file which was written at 12:05:00 would be applied to the standby. But what will happen to transactions which happen in the last 37 seconds before the crash???
 
Do we loose that data???
 
Thanks!
 
On Wed, Feb 27, 2008 at 4:05 PM, salman <salmanb@quietcaresystems.com> wrote:


libra dba wrote:
> continuing with the postgres replication using WARM STANDBY, what happens to
> the wal_files which keeps on generating on the primary?
>
> Do we have to manually remove them or they are removed automatically, once
> they have been recovered on the warm standby?
>
> Also a quick question, do we have to have a NFS for the wal_files, which
> will be mounted on both the servers, or we can have folders on each server
> and configure DRBD between them, so that whatever happens on one is copied
> automatically on to the other.
>
> Thanks!
>

This is what I use -- may not be the best approach, but it has worked
quite well for me so far:

archive_command = 'gzip %p; rsync -av %p.gz -e \"ssh -p portNum -i
/home/postgres/.ssh/id_dsa\"
postgres@rhostname:/usr/local/postgresql/archives/recovery/%f.gz && rm
-f %p.gz'





------=_Part_26740_26359357.1204209376217--