Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 4EF0563424C for ; Wed, 25 Feb 2009 11:16:55 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 48791-03 for ; Wed, 25 Feb 2009 11:16:43 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail.autorevenue.com (mail.autorevenue.com [208.178.167.106]) by mail.postgresql.org (Postfix) with ESMTP id 63B7D63307A for ; Wed, 25 Feb 2009 11:16:37 -0400 (AST) Received: (qmail 23893 invoked from network); 25 Feb 2009 10:20:49 -0500 Received: from unknown (HELO dei26g028534) (msteben@10.10.6.175) by mail.autorevenue.com with SMTP; 25 Feb 2009 10:20:49 -0500 From: "Mark Steben" To: Subject: recovery question Date: Wed, 25 Feb 2009 10:16:02 -0500 Message-ID: <88E7EEC199DB4809971DA47B6F801D30@dei26g028534> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0019_01C99732.0FE848D0" X-Mailer: Microsoft Office Outlook 11 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.4325 Thread-Index: AcmXW/iHUFfy5/R5SCqaStJ1pGpvfA== 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: 200902/211 X-Sequence-Number: 31432 This is a multi-part message in MIME format. ------=_NextPart_000_0019_01C99732.0FE848D0 Content-Type: text/plain; charset="koi8-r" Content-Transfer-Encoding: quoted-printable Hi listers, =20 Here is my problem. I am running PITR restore on a machine remote from = my production machine. I'm shipping logs over there, compressed, then uncompressing them and copying them to pg_xlog. Everything works fine until a network outage creates a gap in my logs. The recovery terminates at log "0000000100000C28000000B1" and brings = the database up Because it can't find "0000000100000C28000000B2". Log "0000000100000C28000000B3" is copied over but I wish to restart = recovery at B2. So I scp B2 over from my primary machine from a folder that I created = for just such an occasion. =20 Now I rename recovery.done to recovery.conf (Copied here for your convenience) =20 'sh /usr/local/postgresql-8.2.5/bin/copy.sh %f %p 2>>/tmp/recovery.log' =20 (and copy.sh:) =20 REQ_FILE=3D$1 DEST=3D$2 LF=3D"${REQ_FILE}.lock" SUFFIX=3D${REQ_FILE##*.} ############################################################### ## check if file is transaction log or informational file ## if transaction log, cat from archlog and uncompress into unzipped = folder ## if informational simply copy into unzipped folder (it came over uncompressed) #########################################################################= ### ######### if [ "${SUFFIX}" !=3D 'history' ] && [ "${SUFFIX}" !=3D 'backup' ]; then cat "/logs/var/backups/archlog/${REQ_FILE}" | gzip -dc > "/logs/var/backups/unzipped/${REQ_FILE}" if [ "$?" =3D "0" ] ; then echo 'successful uncompress of ' "/logs/var/backups/unzipped/${REQ_FILE}" >> /tmp/restore.mavmail.log else echo 'unsuccessful uncompress of ' "/logs/var/backups/unzipped/${REQ_FILE}" >> /tmp/restore.mavmail.log echo 'the return code is ' "$?" >> /tmp/restore.mavmail.log fi else cp "/logs/var/backups/archlog/${REQ_FILE}" "/logs/var/backups/unzipped/${REQ_FILE}" fi #########################################################################= ### ########### ## check for size. If not a full size (16777216) trans log, the copy = from ## cobra is still in progress. Don't copy this file. Stop recovery = here. #########################################################################= ### ########### SIZE=3D$(ls -gG1 "/logs/var/backups/unzipped/${REQ_FILE}" | awk '{ print = $3}' ) echo "The size of the log to be restored is " "${SIZE}" >> /tmp/restore.mavmail.log=20 if [ "${SUFFIX}" !=3D 'history' ] && [ "${SUFFIX}" !=3D 'backup' ]; then if [ "${SIZE}" !=3D '16777216' ]; then echo 'partially written log - not restored - finishing recovery' >> /tmp/restore.mavmail.log exit 0 fi fi =20 /usr/bin/lockfile "${LF}" =20 ################################################################ ## copy either full sized trans log or informational file ## into pg_xlog data cluster. ################################################################ cp "/logs/var/backups/unzipped/${REQ_FILE}" "${DEST}" rm -f "${LF}" rm "/logs/var/backups/unzipped/${REQ_FILE}" =20 (END) =20 Now when I try to restart, hoping to begin recovery with the C2 log I = get an invalid checkpoint error: =20 : LOG: starting archive recovery Feb 25 10:08:10 ar-db3 postgres[32538]: [3-1] @: LOG: restore_command = =3D "sh /usr/local/postgresql-8.2.5/bin/copy.sh %f %p 2>>/tmp/recovery.log" Feb 25 10:08:11 ar-db3 postgres[32538]: [4-1] @: LOG: restored log file "0000000100000C28000000B1" from archive Feb 25 10:08:11 ar-db3 postgres[32538]: [5-1] @: LOG: invalid record = length at C28/B1FFECA4 Feb 25 10:08:11 ar-db3 postgres[32538]: [6-1] @: LOG: invalid primary checkpoint record Feb 25 10:08:12 ar-db3 postgres[32538]: [7-1] @: LOG: restored log file "0000000100000C28000000B1" from archive Feb 25 10:08:12 ar-db3 postgres[32538]: [8-1] @: LOG: invalid record = length at C28/B1FFEC5C Feb 25 10:08:12 ar-db3 postgres[32538]: [9-1] @: LOG: invalid secondary checkpoint record Feb 25 10:08:12 ar-db3 postgres[32538]: [10-1] @: PANIC: could not = locate a valid checkpoint record Feb 25 10:08:12 ar-db3 postgres[32537]: [1-1] @: LOG: startup process = (PID 32538) was terminated by signal 6 Feb 25 10:08:12 ar-db3 postgres[32537]: [2-1] @: LOG: aborting startup = due to startup process failure =20 I remove the recovery.conf file, successfully start the database and = issue a checkpoint. I try the restore again and get the same error. =20 So, is there a way that I can force the recovery to begin at B2 or am I = dead in the water and need to bring in another full file copy and Start from scratch: =20 Thanks for your time. =20 Mark Steben=81Database Administrator=81=20 @utoRevenue-R- "Join the Revenue-tion" 95 Ashley Ave. West Springfield, MA., 01089=20 413-243-4800 x1512 (Phone) =81 413-732-1824 (Fax) @utoRevenue is a registered trademark and a division of Dominion = Enterprises =20 =20 ------=_NextPart_000_0019_01C99732.0FE848D0 Content-Type: text/html; charset="koi8-r" Content-Transfer-Encoding: quoted-printable

Hi listers,

 

Here is my problem.=9A I am running PITR restore on a machine = remote from my production machine.

I’m shipping logs over there, compressed, then = uncompressing them and copying them to pg_xlog.

Everything works fine until a network outage creates a gap in my = logs.

The recovery terminates at log = =9A"0000000100000C28000000B1" and brings the database up

Because it can’t find = "0000000100000C28000000B2”.

Log "0000000100000C28000000B3” is copied over but I = wish to restart recovery at B2.

So I scp B2 over from my primary machine from a folder that I = created for just such an occasion.

 

Now I rename recovery.done to recovery.conf=9A (Copied here for = your convenience)

=9A

'sh = /usr/local/postgresql-8.2.5/bin/copy.sh %f %p 2>>/tmp/recovery.log'

=9A

(and = copy.sh:)

 

REQ_FILE=3D$1

DEST=3D$2

LF=3D"${REQ_FILE}.lock"

SUFFIX=3D${REQ_FILE##*.}<= /span>

####################################= ###########################

## check if file is transaction log = or informational file

## if transaction log, cat from = archlog and uncompress into unzipped folder

## if informational simply copy = into unzipped folder (it came over uncompressed)

####################################= #################################################

if [ "${SUFFIX}" !=3D = 'history' ] && [ "${SUFFIX}" !=3D 'backup' ]; = then

=9A cat = "/logs/var/backups/archlog/${REQ_FILE}" | gzip -dc=9A > = "/logs/var/backups/unzipped/${REQ_FILE}"

=9A if [ "$?" =3D = "0" ] ;

=9A = then

=9A=9A=9A=9A echo 'successful = uncompress of=9A ' "/logs/var/backups/unzipped/${REQ_FILE}" >> /tmp/restore.mavmail.log

=9A = else

=9A=9A=9A=9A echo 'unsuccessful = uncompress of=9A ' "/logs/var/backups/unzipped/${REQ_FILE}" >> /tmp/restore.mavmail.log

=9A=9A=9A=9A echo 'the return code = is ' "$?" >> = /tmp/restore.mavmail.log

=9A fi

else

=9A cp "/logs/var/backups/archlog/${REQ_FILE}"=9A "/logs/var/backups/unzipped/${REQ_FILE}"

fi

####################################= ###################################################

##=9A check for size.=9A If not a = full size (16777216) trans log, the copy from

##=9A=9A cobra is still in = progress. Don't copy this file. Stop recovery here.

####################################= ###################################################

SIZE=3D$(ls -gG1 "/logs/var/backups/unzipped/${REQ_FILE}" | awk '{ print $3}' = )

echo "The size of the log to = be restored is " "${SIZE}" >> /tmp/restore.mavmail.log =

if [ "${SUFFIX}" !=3D = 'history' ] && [ "${SUFFIX}" !=3D 'backup' ]; = then

=9A if [ "${SIZE}" !=3D = '16777216' ]; then

=9A=9A=9A echo 'partially written = log - not restored - finishing recovery' >> = /tmp/restore.mavmail.log

=9A=9A=9A exit = 0

=9A fi

fi

=9A=9A=9A=9A=9A =

/usr/bin/lockfile = "${LF}"=9A

####################################= ############################

## copy either full sized trans log = or informational file

## into pg_xlog data = cluster.

####################################= ############################

=9Acp "/logs/var/backups/unzipped/${REQ_FILE}"=9A = "${DEST}"

rm -f = "${LF}"

rm "/logs/var/backups/unzipped/${REQ_FILE}"

 

(END)

 

Now when I try to restart, hoping = to begin recovery with the C2 log I get an invalid checkpoint = error:

 

: LOG:=9A starting archive = recovery

Feb 25 10:08:10 ar-db3 = postgres[32538]: [3-1] @: LOG:=9A restore_command =3D "sh /usr/local/postgresql-8.2.5/bin/copy.sh %f %p = 2>>/tmp/recovery.log"

Feb 25 10:08:11 ar-db3 = postgres[32538]: [4-1] @: LOG:=9A restored log file "0000000100000C28000000B1" = from archive

Feb 25 10:08:11 ar-db3 = postgres[32538]: [5-1] @: LOG:=9A invalid record length at = C28/B1FFECA4

Feb 25 10:08:11 ar-db3 = postgres[32538]: [6-1] @: LOG:=9A invalid primary checkpoint = record

Feb 25 10:08:12 ar-db3 = postgres[32538]: [7-1] @: LOG:=9A restored log file "0000000100000C28000000B1" = from archive

Feb 25 10:08:12 ar-db3 = postgres[32538]: [8-1] @: LOG:=9A invalid record length at = C28/B1FFEC5C

Feb 25 10:08:12 ar-db3 = postgres[32538]: [9-1] @: LOG:=9A invalid secondary checkpoint = record

Feb 25 10:08:12 ar-db3 = postgres[32538]: [10-1] @: PANIC:=9A could not locate a valid checkpoint = record

Feb 25 10:08:12 ar-db3 = postgres[32537]: [1-1] @: LOG:=9A startup process (PID 32538) was terminated by signal = 6

Feb 25 10:08:12 ar-db3 = postgres[32537]: [2-1] @: LOG:=9A aborting startup due to startup process = failure

 

I remove the recovery.conf file, successfully start the database and issue a checkpoint.=9A I try the = restore again and get the same error.

 

So, is there a way that I can = force the recovery to begin at B2 or am I dead in the water and need to bring in = another full file copy and

Start from = scratch:

 

Thanks for your = time.

 

Mark Steben=81Database = Administrator=81

@utoRevenue­®­ "Join the = Revenue-tion"
95 Ashley Ave. West Springfield, MA., 01089 
413-243-4800 x1512 (Phone) =81 413-732-1824 (Fax)

@utoRevenue is a registered trademark and a division of Dominion = Enterprises

 

 

------=_NextPart_000_0019_01C99732.0FE848D0--