Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 2FFB964FD86 for ; Sun, 19 Oct 2008 12:16:56 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 03003-10 for ; Sun, 19 Oct 2008 12:16:53 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from fg-out-1718.google.com (fg-out-1718.google.com [72.14.220.153]) by postgresql.org (Postfix) with ESMTP id 9945364FCC0 for ; Sun, 19 Oct 2008 12:16:52 -0300 (ADT) Received: by fg-out-1718.google.com with SMTP id 16so1416615fgg.23 for ; Sun, 19 Oct 2008 08:16:51 -0700 (PDT) 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:in-reply-to:mime-version:content-type :content-transfer-encoding:content-disposition:references; bh=e4I41x+CvC9i6iT6i2SNhuD0XxZdB6T330R7ngvPjQU=; b=dLtg2MSXwcee4wLAM2cJDvMaqifThEi86110Uqpuh8klhx9utVMb39byB4Nz+JL/7s z9CSFQvM/ob3sjH1Qz3Pd4JiO4+8aJYnWAs6RoDyK0shcsdsWMI7aLgrSbfUzpoIuRCt 9hkY9ozjckVkRJBkHEyC/UifqpPBawlsdadF8= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:in-reply-to:mime-version :content-type:content-transfer-encoding:content-disposition :references; b=N/vzWQO6cnaQx58hLeKHefb8v+1rGA0QVrCuqhWJPw5+ADwuXD4U02QIe4dwIGAhWL FuAv1j4j9ej9sn02KM9UjmciZ9HBYE1EVTtSHu+xxjWi6qVAlETG9x+8XIkfdf8KS8EC w7hYZfkMwzT+7X1dXEvMNuPE4qHUK5oyMikY4= Received: by 10.86.95.20 with SMTP id s20mr5788318fgb.65.1224429411382; Sun, 19 Oct 2008 08:16:51 -0700 (PDT) Received: by 10.86.78.13 with HTTP; Sun, 19 Oct 2008 08:16:51 -0700 (PDT) Message-ID: <530068a0810190816q48460223maab3d30056b37f4e@mail.gmail.com> Date: Sun, 19 Oct 2008 18:16:51 +0300 From: "Ibrahim Harrani" To: pgsql-admin@postgresql.org, jcleyva@hotmail.com Subject: Re: pg_standby and continuous recovery In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <530068a0810160118m70eb10d9q8b3c895aa1907fc2@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200810/129 X-Sequence-Number: 30585 I tried pg_standby with PostgreSQL 8.3. But the result is the same. If I create trigger file to finish recovery and restart recovering by renaming recovery.done as recover.conf I see the following line in the standby.log Command for restore : ln -s -f "/pgarsiv/00000002.history" "pg_xlog/RECOVERYHISTORY" As you can see history file refers to the 000000010000000000000008 file (starting with . 00000001) # cat /pgarsiv/00000002.history 1 000000010000000000000008 before transaction 0 at 2000-01-01 02:00:00+02 So next file should be "000000010000000000000009" (starting with 00000001) but pg_standby is looking for 000000020000000000000009 file (starting with 00000002). Trigger file : /tmp/pgsql.trigger.5442 Waiting for WAL file : 000000020000000000000009 WAL file path : /pgarsiv/000000020000000000000009 Restoring to... : pg_xlog/RECOVERYXLOG Sleep interval : 2 seconds Max wait interval : 0 forever Command for restore : ln -s -f "/pgarsiv/000000020000000000000009" "pg_xlog/RECOVERYXLOG" Keep archive history : 000000000000000000000000 and late[ OK ] As you can see below 000000020000000000000009 file will never exist in the archivedirectory. So new wal files will never be restored by pg_standby. # ls -1 /pgarsiv/ 000000010000000000000001 000000010000000000000001.00000068.backup 000000010000000000000002 000000010000000000000003 000000010000000000000004 000000010000000000000005 000000010000000000000006 000000010000000000000007 000000010000000000000008 000000010000000000000009 00000001000000000000000A I also tried to cleanup pg_xlog directory at standby server but the result is the same! Any idea? On Thu, Oct 16, 2008 at 8:25 PM, Julio Leyva wrote: > > You better move to postgresql 8.3 > PITR works better in that version > WE have that in our system and so far ok > > >> Date: Thu, 16 Oct 2008 11:18:21 +0300 >> From: ibrahim.harrani@gmail.com >> To: pgsql-admin@postgresql.org >> Subject: [ADMIN] pg_standby and continuous recovery >> >> Hello, >> >> I am using pg_standby with PostgreSQL 8.2.4. >> I configured master and standby server according to the articles at >> >> http://michsan.blogspot.com/2008/08/using-pgstandby-for-high-availability.html >> and http://www.postgresql.org/docs/8.3/static/pgstandby.html. >> >> my recovery.conf file is like following: >> # more recovery.conf >> restore_command = 'pg_standby -l -d -s 2 -k 40 -t >> /tmp/pgsql.trigger.5442 /pgarsiv %f %p %r 2>>standby.log' >> [root@mysql-ndb2 data]# >> >> >> I can successfully recover and start the standby server. I can see >> that the database is completely restored. (recovery.conf was renamed >> as recovery.done) >> But I would like to continue recovering from last recovery point >> restored by pg_standby without starting from scratch. >> Can I do this? If the answer is positve, which procedure should I apply? >> After recovering standby server. I have the following files in the >> pg_xlog directory. >> # ls -l >> total 16412 >> -rw------- 1 postgres postgres 16777216 Oct 16 12:51 >> 0000000200000000000000CA >> lrwxrwxrwx 1 postgres postgres 33 Oct 16 12:30 >> 0000000200000000000000CB -> /pgarsiv/0000000100000000000000C9 >> -rw------- 1 postgres postgres 75 Oct 16 12:30 00000002.history >> drwx------ 2 postgres postgres 4096 Oct 16 12:30 archive_status >> lrwxrwxrwx 1 postgres postgres 25 Oct 16 12:54 RECOVERYHISTORY >> -> /pgarsiv/00000002.history >> >> As you can see below, all archive file names are staring with "00000001" >> >> # ls -l /pgarsiv/ >> total 262468 >> -rw------- 1 postgres postgres 16777216 Oct 16 12:03 >> 0000000100000000000000C3 >> -rw------- 1 postgres postgres 241 Oct 16 12:03 >> 0000000100000000000000C3.00000070.backup >> -rw------- 1 postgres postgres 16777216 Oct 16 12:09 >> 0000000100000000000000C4 >> -rw------- 1 postgres postgres 16777216 Oct 16 12:13 >> 0000000100000000000000C5 >> -rw------- 1 postgres postgres 16777216 Oct 16 12:19 >> 0000000100000000000000C6 >> -rw------- 1 postgres postgres 16777216 Oct 16 12:21 >> 0000000100000000000000C7 >> -rw------- 1 postgres postgres 16777216 Oct 16 12:23 >> 0000000100000000000000C8 >> -rw------- 1 postgres postgres 16777216 Oct 16 12:25 >> 0000000100000000000000C9 >> -rw------- 1 postgres postgres 16777216 Oct 16 12:29 >> 0000000100000000000000CA >> -rw------- 1 postgres postgres 16777216 Oct 16 12:33 >> 0000000100000000000000CB >> -rw------- 1 postgres postgres 16777216 Oct 16 12:39 >> 0000000100000000000000CC >> -rw------- 1 postgres postgres 16777216 Oct 16 12:43 >> 0000000100000000000000CD >> -rw------- 1 postgres postgres 16777216 Oct 16 12:47 >> 0000000100000000000000CE >> -rw------- 1 postgres postgres 16777216 Oct 16 12:49 >> 0000000100000000000000CF >> -rw------- 1 postgres postgres 16777216 Oct 16 12:53 >> 0000000100000000000000D0 >> -rw------- 1 postgres postgres 16777216 Oct 16 12:59 >> 0000000100000000000000D1 >> -rw------- 1 postgres postgres 16777216 Oct 16 13:03 >> 0000000100000000000000D2 >> >> But pg_standby is looking for the files name starting with 00000002! >> >> Trigger file : /tmp/pgsql.trigger.5442 >> Waiting for WAL file : 00000002.history >> WAL file path : /pgarsiv/00000002.history >> Restoring to... : pg_xlog/RECOVERYHISTORY >> Sleep interval : 2 seconds >> Max wait interval : 0 forever >> Command for restore : ln -s -f "/pgarsiv/00000002.history" >> "pg_xlog/RECOVERYHISTORY" >> Keep archive history : %r and later >> running restore : OK >> Trigger file : /tmp/pgsql.trigger.5442 >> Waiting for WAL file : 0000000200000000000000CA >> WAL file path : /pgarsiv/0000000200000000000000CA >> Restoring to... : pg_xlog/RECOVERYXLOG >> Sleep interval : 2 seconds >> Max wait interval : 0 forever >> Command for restore : ln -s -f "/pgarsiv/0000000200000000000000CA" >> "pg_xlog/RECOVERYXLOG" >> Keep archive history : %r and later >> >> Any help is appreciated >> >> -- >> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-admin >