Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WSZbd-0005KG-5C for pgsql-admin@arkaria.postgresql.org; Tue, 25 Mar 2014 22:14:01 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1WSZbc-0005S7-I6 for pgsql-admin@arkaria.postgresql.org; Tue, 25 Mar 2014 22:14:00 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WSZbb-0005Rz-L5 for pgsql-admin@postgresql.org; Tue, 25 Mar 2014 22:13:59 +0000 Received: from mail-oa0-f41.google.com ([209.85.219.41]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WSZbX-0006aK-Jf for pgsql-admin@postgresql.org; Tue, 25 Mar 2014 22:13:58 +0000 Received: by mail-oa0-f41.google.com with SMTP id j17so1420508oag.14 for ; Tue, 25 Mar 2014 15:13:53 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:date:from:to:message-id:in-reply-to:references :subject:mime-version:content-type; bh=IKyX7qSB9B9zVvXjy8xOVHYz+LdOAL/iuA7iqBzt2UY=; b=BM8yf9Oad2mYDY4aNDV/0FHUCliKB3EEOW2wQj6NAj5GZuyYBN5LOvpDJ0kD9El4pS BAyczeePRMlvm+euikjGMyWjb/TnBBPsO5Tu1hA9FwEbMNtJyrFtkCOgVUPdBdhofm3G VvI6ujYDGboGeKY1J7R/lonSEhKJ2Of3EprCDXFHIOuE5ESGKn7k6vfRSzeMwenpYHLc Ee+47w+Xhj8wOWUZUJqu2eT+4Awo9kAEGk1qFcb+gMDbqgUGp0TsDh7FjVfLnLp2+6CT 81Zxv0Yob5eMqtSor7OMSx5Ebuc+WEVas+SWFfu5Fe+a2zsM75kTzgauKIP/OplSAwyh Jf3A== X-Gm-Message-State: ALoCoQnc96ssMplPE9tQaeqUoNJcPEPcMSKVWUq8KqGht2pjVpZm0F2SFS+/A/y+OpSHBGH8Dz3ZyqMi5J4o0n9MPZ6F6Pc5pZDWdF2IDnCUJAGlW7/lYq8= X-Received: by 10.60.133.107 with SMTP id pb11mr3810206oeb.58.1395785633180; Tue, 25 Mar 2014 15:13:53 -0700 (PDT) Received: from palos ([50.76.140.65]) by mx.google.com with ESMTPSA id zm8sm168756obc.16.2014.03.25.15.13.51 for (version=TLSv1.2 cipher=ECDHE-RSA-RC4-SHA bits=128/128); Tue, 25 Mar 2014 15:13:52 -0700 (PDT) Date: Tue, 25 Mar 2014 16:13:49 -0600 From: Jason Mathis To: pgsql-admin@postgresql.org Message-ID: In-Reply-To: References: Subject: Re: archiving wal files after PITR X-Mailer: Airmail (231) MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="5331ff9d_1190cde7_fb4f" X-Pg-Spam-Score: -2.6 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org --5331ff9d_1190cde7_fb4f Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline I was really hoping some one would chime in on whats going on here. Its not= too late let me know;) Anyways after some more testing today I came to the conclusion that if you = simply do a restart after the recovery process completes then this problem = fixes itself. Which I like slightly better than deleting the .ready file. I= suppose if you changed your hba file before the restore, you would need to= restart anyways to allow connections back in. Although I really wish this = was documented somewhere.=C2=A0 If anyone comes up with some info please contribute to this thread. thanks! On March 24, 2014 at 3:59:32 PM, Jason Mathis (jmathis@redzonesoftware.com)= wrote: Hi All, This is a difficult question to frame since I am currently new to postgres = and trying to implement a continuous archiving strategy at this new company= .=C2=A0 Postgresql 9.2.7 Centos 6.5=C2=A0 I am using pg_basebackup to create a backup. I am not backing up the xlog d= ir. I am using a script for the archiving process which is using this comma= nd: usr/bin/test ! -f $PGARCHIVE_DIR/$2 && /usr/bin/sudo cp $1 $PGARCHIVE_DIR/$= 2 Pretty straight forward at this point. I also have a restore script that wo= rks well. Gets the base backup and restores the needed wal files. The resto= res completes, is successful and starts a new timeline. All good! But=E2=80= =A6=E2=80=A6 One strange problem exists. I now start getting errors about the last resto= red wal files cannot be archived. Because its already archived which sounds= right to me after all the postgres know it got it from the archive. Now in= the case of the last restore test I did, I restored two wal files but only= the last one cannot be archived (seems to always be the last one). If I lo= ok in the pg_xlog/archive_status I will see something like: -rw------- 1 postgres postgres =C2=A0 =C2=A00=C2=A0Mar 24 20:52=C2=A0000000= 06000000010000002B.done -rw------- 1 postgres postgres =C2=A0 =C2=A00=C2=A0Mar 24 20:54=C2=A0000000= 06000000010000002C.done -rw------- 1 postgres postgres =C2=A0 =C2=A00=C2=A0Mar 24 20:54=C2=A0000000= 06000000010000002C.ready -rw------- 1 postgres postgres =C2=A0 =C2=A00=C2=A0Mar 24 20:49=C2=A0000000= 06.history.done -rw------- 1 postgres postgres =C2=A0 =C2=A00=C2=A0Mar 24 20:54=C2=A0000000= 07.history.read See it? What the heck. 00000006* was from the restore AND are all marked *.= done except 00000006000000010000002C.* which has two entries! one =E2=80=9C= done=E2=80=9D and one =E2=80=9Cready.=E2=80=9D=C2=A0 Its perfectly possible I am doing something wrong. I know could manually fi= x the issue by deleting the .ready file, but I need this to be right and to= understand what is happening. Can anyone explain this behavior?=C2=A0 Thanks! --=20 =20 This transmission contains confidential and privileged information intended= =20 solely for the party identified above. If you receive this message in=20 error, you must not use it or convey it to others. Please destroy it=20 immediately and contact the sender at (303) 386-3955 or by return e-mail to= =20 the sender. --5331ff9d_1190cde7_fb4f Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline =
I was really hoping some one would chime in on wha= ts going on here. Its not too late let me know;)

Anyways after some more testing t= oday I came to the conclusion that if you simply do a restart after the rec= overy process completes then this problem fixes itself. Which I like slight= ly better than deleting the .ready file. I suppose if you changed your hba = file before the restore, you would need to restart anyways to allow connect= ions back in. Although I really wish this was documented somewhere. 

If anyon= e comes up with some info please contribute to this thread.

thanks!


On March 24, 2014 = at 3:59:32 PM, Jason Mathis (jmathis@redzonesoftware.com) wrote:
Hi All,

This is a difficult question to frame since I am currently new to postgres and trying to implement a continuous archiving strategy at this new company. 

Postgresql 9.2.7
Centos 6.5 

I am using pg_basebackup to create a backup. I am not backing up the xlog dir. I am using a script for the archiving process which is using this command:

usr/bin/test ! -f $PGARCHIVE_DIR/$2 && /usr/bin/sudo cp $1 $PGARCHIVE_DIR/$2

Pretty straight forward at this point. I also have a restore script that works well. Gets the base backup and restores the needed wal files. The restores completes, is successful and starts a new timeline. All good! But=E2=80=A6=E2=80=A6

One strange problem exists. I now start getting errors about the last restored wal files cannot be archived. Because its already archived which sounds right to me after all the postgres know it got it from the archive. Now in the case of the last restore test I did, I restored two wal files but only the last one cannot be archived (seems to always be the last one). If I look in the pg_xlog/archive_status I will see something like:

-rw------- 1 postgres postgres    0 Mar 24 20:52 00000006000000010000002B.done
-rw------- 1 postgres postgres    0 Mar 24 20:54 00000006000000010000002C.done
-rw------- 1 postgres postgres    0 Mar 24 20:54 00000006000000010000002C.ready
-rw------- 1 postgres postgres    0 Mar 24 20:49 00000006.history.done
-rw------- 1 postgres postgres    0 Mar 24 20:54 00000007.history.read

See it? What the heck. 00000006* was from the restore AND are all marked *.done except 00000006000000010000002C.* which has two entries! one =E2=80=9Cdone=E2=80=9D and one =E2=80=9Cready.=E2=80=9D <= /div>

Its perfectly possible I am doing something wrong. I know could manually fix the issue by deleting the .ready file, but I need this to be right and to understand what is happening. Can anyone explain this behavior? 

Thanks!

This transmission contains confidential = and privileged information intended solely for the party identified above. If you receive = this message in error, you must not use it or convey it to others. Please destroy it immediately and contact the se= nder at (303) 386-3955 or by return e-mail to the sender.

--5331ff9d_1190cde7_fb4f--