Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WSCuF-00078B-0F for pgsql-admin@arkaria.postgresql.org; Mon, 24 Mar 2014 21:59:43 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1WSCuE-0002hk-4X for pgsql-admin@arkaria.postgresql.org; Mon, 24 Mar 2014 21:59:42 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WSCuC-0002hb-16 for pgsql-admin@postgresql.org; Mon, 24 Mar 2014 21:59:40 +0000 Received: from mail-ob0-f181.google.com ([209.85.214.181]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WSCu7-0005Kq-Gy for pgsql-admin@postgresql.org; Mon, 24 Mar 2014 21:59:39 +0000 Received: by mail-ob0-f181.google.com with SMTP id wp4so6348698obc.26 for ; Mon, 24 Mar 2014 14:59:33 -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=IxDaARq68kjZA5DKMrHZbnR3Kolq3UUetXNT8wLMaIg=; b=BO6weWQCcbRhLkVP82x9qKiE2J1h4qvXsunCDlQ9hHrYtbnqgqTkeHaEbTrlu7tVlm Vvb9TtsT2qixArj/DvyNL8kUPnl0lLdxYmIclpIGMx/2bq1tr4fvfEjX2ANT0jQW5Uwp RaXytpE81zqpGqOUnT5O+nDw/oIfkjlBJTKBHKNrCZPnPRhrjmbiCRw7veqpsXXEEf7S lMZLDRI1lSDLxXpRxcox0cXvbRqa7wavOuzLpELirYy8gHBXzQFWuLa+GWzLtIliDfKf DsIKumQH4uAhu5I+RXxSkHnT7bLkt+3PjCLarHkvTmWVo3Lv4i1GCikvUGbpz7xMf84v TqyA== X-Gm-Message-State: ALoCoQmDkNvDxWu5hYROtVsZFtY9oA1oSwbHONUJyErWUGPrg0ndpf+WNF0n9SoQOAWht9dWdeos3zUYCZRnt+noYB20iVvl2VWs/BP8ymY5c8BwmpY9YOM= X-Received: by 10.182.247.68 with SMTP id yc4mr3228451obc.67.1395698373019; Mon, 24 Mar 2014 14:59:33 -0700 (PDT) Received: from palos ([50.76.140.65]) by mx.google.com with ESMTPSA id l5sm25345219obh.15.2014.03.24.14.59.31 for (version=TLSv1.2 cipher=ECDHE-RSA-RC4-SHA bits=128/128); Mon, 24 Mar 2014 14:59:32 -0700 (PDT) Date: Mon, 24 Mar 2014 15:59:29 -0600 From: Jason Mathis To: pgsql-admin@postgresql.org Message-ID: In-Reply-To: References: Subject: archiving wal files after PITR X-Mailer: Airmail (231) MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="5330aac1_2eb141f2_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 --5330aac1_2eb141f2_fb4f Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline 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. --5330aac1_2eb141f2_fb4f Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline =
Hi All,

T= his is a difficult question to frame since I am currently new to postgres a= nd 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 $P= GARCHIVE_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 f= iles. The restores completes, is successful and starts a new timeline. All = good! But=E2=80=A6=E2=80=A6

One strange problem ex= ists. I now start getting errors about the last restored wal files cannot b= e 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 b= e archived (seems to always be the last one). If I look in the pg_xlog/arch= ive_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&n= bsp;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&n= bsp;00000007.history.read

See it? What the heck. 0= 0000006* was from the restore AND are all marked *.done except 000000060000= 00010000002C.* which has two entries! one =E2=80=9Cdone=E2=80=9D and one = =E2=80=9Cready.=E2=80=9D 

Its perfectly possi= ble I am doing something wrong. I know could manually fix the issue by dele= ting 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.

--5330aac1_2eb141f2_fb4f--