pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedRecovery question
5+ messages / 4 participants
[nested] [flat]
* Recovery question
@ 2007-05-03 15:15 Lorraine Mancuso <mancusol1@Scranton.edu>
0 siblings, 1 reply; 5+ messages in thread
From: Lorraine Mancuso @ 2007-05-03 15:15 UTC (permalink / raw)
To: pgsql-admin
Hi,
I am new to Postgres and had a few questions. We are testing the
recovery of a database cluster. We have read the documentation and have
been able to recover successfully but we were wondering about the
contents of the ARCHIVE_STATUS subdirectory. It appears that it is OK
for this directory to be entry during recovery. I just wanted to verify
that this is correct.
Also, is there a way to search the archives for all notes on a
particular subject?
Is this ADMIN list the appropriate forum for backup and recovery
questions?
Thanks alot!
Lorraine
University of Scranton
(using POSTGRES for Xythos on a Linux box)
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Recovery question
@ 2007-05-14 18:30 Jim C. Nasby <decibel@decibel.org>
parent: Lorraine Mancuso <mancusol1@Scranton.edu>
0 siblings, 0 replies; 5+ messages in thread
From: Jim C. Nasby @ 2007-05-14 18:30 UTC (permalink / raw)
To: Lorraine Mancuso <mancusol1@Scranton.edu>; +Cc: pgsql-admin
On Thu, May 03, 2007 at 11:15:37AM -0400, Lorraine Mancuso wrote:
> Hi,
>
> I am new to Postgres and had a few questions. We are testing the
> recovery of a database cluster. We have read the documentation and have
> been able to recover successfully but we were wondering about the
> contents of the ARCHIVE_STATUS subdirectory. It appears that it is OK
> for this directory to be entry during recovery. I just wanted to verify
> that this is correct.
Yes, that's OK. That directory is just used as a means to tell the
archiver what logs it can archive.
> Also, is there a way to search the archives for all notes on a
> particular subject?
http://archives.postgresql.org/
> Is this ADMIN list the appropriate forum for backup and recovery
> questions?
Yes.
--
Jim Nasby decibel@decibel.org
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
^ permalink raw reply [nested|flat] 5+ messages in thread
* recovery question
@ 2009-02-25 15:16 Mark Steben <msteben@autorevenue.com>
0 siblings, 1 reply; 5+ messages in thread
From: Mark Steben @ 2009-02-25 15:16 UTC (permalink / raw)
To: pgsql-admin
Hi listers,
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.
Now I rename recovery.done to recovery.conf (Copied here for your
convenience)
'sh /usr/local/postgresql-8.2.5/bin/copy.sh %f %p 2>>/tmp/recovery.log'
(and copy.sh:)
REQ_FILE=$1
DEST=$2
LF="${REQ_FILE}.lock"
SUFFIX=${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}" != 'history' ] && [ "${SUFFIX}" != 'backup' ]; then
cat "/logs/var/backups/archlog/${REQ_FILE}" | gzip -dc >
"/logs/var/backups/unzipped/${REQ_FILE}"
if [ "$?" = "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=$(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}" != 'history' ] && [ "${SUFFIX}" != 'backup' ]; then
if [ "${SIZE}" != '16777216' ]; then
echo 'partially written log - not restored - finishing recovery' >>
/tmp/restore.mavmail.log
exit 0
fi
fi
/usr/bin/lockfile "${LF}"
################################################################
## 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}"
(END)
Now when I try to restart, hoping to begin recovery with the C2 log I get an
invalid checkpoint error:
: LOG: starting archive recovery
Feb 25 10:08:10 ar-db3 postgres[32538]: [3-1] @: LOG: restore_command = "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
I remove the recovery.conf file, successfully start the database and issue a
checkpoint. 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│Database Administrator│
@utoRevenue-R- "Join the Revenue-tion"
95 Ashley Ave. West Springfield, MA., 01089
413-243-4800 x1512 (Phone) │ 413-732-1824 (Fax)
@utoRevenue is a registered trademark and a division of Dominion Enterprises
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: recovery question
@ 2009-02-25 15:40 Lee Azzarello <lee@dropio.com>
parent: Mark Steben <msteben@autorevenue.com>
0 siblings, 1 reply; 5+ messages in thread
From: Lee Azzarello @ 2009-02-25 15:40 UTC (permalink / raw)
To: pgsql-admin
Is 0000000100000C28000000B1 the same size as the other segments?
-lee
2009/2/25 Mark Steben <msteben@autorevenue.com>:
> Hi listers,
>
>
>
> 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.
>
>
>
> Now I rename recovery.done to recovery.conf (Copied here for your
> convenience)
>
>
>
> 'sh /usr/local/postgresql-8.2.5/bin/copy.sh %f %p 2>>/tmp/recovery.log'
>
>
>
> (and copy.sh:)
>
>
>
> REQ_FILE=$1
>
> DEST=$2
>
> LF="${REQ_FILE}.lock"
>
> SUFFIX=${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}" != 'history' ] && [ "${SUFFIX}" != 'backup' ]; then
>
> cat "/logs/var/backups/archlog/${REQ_FILE}" | gzip -dc >
> "/logs/var/backups/unzipped/${REQ_FILE}"
>
> if [ "$?" = "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=$(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}" != 'history' ] && [ "${SUFFIX}" != 'backup' ]; then
>
> if [ "${SIZE}" != '16777216' ]; then
>
> echo 'partially written log - not restored - finishing recovery' >>
> /tmp/restore.mavmail.log
>
> exit 0
>
> fi
>
> fi
>
>
>
> /usr/bin/lockfile "${LF}"
>
> ################################################################
>
> ## 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}"
>
>
>
> (END)
>
>
>
> Now when I try to restart, hoping to begin recovery with the C2 log I get an
> invalid checkpoint error:
>
>
>
> : LOG: starting archive recovery
>
> Feb 25 10:08:10 ar-db3 postgres[32538]: [3-1] @: LOG: restore_command = "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
>
>
>
> I remove the recovery.conf file, successfully start the database and issue a
> checkpoint. 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│Database Administrator│
>
> @utoRevenue-(R)- "Join the Revenue-tion"
> 95 Ashley Ave. West Springfield, MA., 01089
> 413-243-4800 x1512 (Phone) │ 413-732-1824 (Fax)
>
> @utoRevenue is a registered trademark and a division of Dominion Enterprises
>
>
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: recovery question
@ 2009-02-25 19:31 Mark Steben <msteben@autorevenue.com>
parent: Lee Azzarello <lee@dropio.com>
0 siblings, 0 replies; 5+ messages in thread
From: Mark Steben @ 2009-02-25 19:31 UTC (permalink / raw)
To: 'Lee Azzarello' <lee@dropio.com>; pgsql-admin
Hi Lee, just got your reply.
Every segment comes over compressed (gzip). So every segment would be a
Different size in the compressed folder. But we decompress it into another
folder (gzip) and they always decompress into the standard 16 meg size when
We copy them back into xlog.
So 0000000100000C28000000B1 came into xlog as 16777216, just like the
others.
Thanks for the response.
Mark Steben│Database Administrator│
@utoRevenue-R- "Join the Revenue-tion"
95 Ashley Ave. West Springfield, MA., 01089
413-243-4800 x1512 (Phone) │ 413-732-1824 (Fax)
@utoRevenue is a registered trademark and a division of Dominion Enterprises
-----Original Message-----
From: Lee Azzarello [mailto:lee@dropio.com]
Sent: Wednesday, February 25, 2009 10:40 AM
To: pgsql-admin@postgresql.org
Subject: Re: recovery question
Is 0000000100000C28000000B1 the same size as the other segments?
-lee
2009/2/25 Mark Steben <msteben@autorevenue.com>:
> Hi listers,
>
>
>
> 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.
>
>
>
> Now I rename recovery.done to recovery.conf (Copied here for your
> convenience)
>
>
>
> 'sh /usr/local/postgresql-8.2.5/bin/copy.sh %f %p 2>>/tmp/recovery.log'
>
>
>
> (and copy.sh:)
>
>
>
> REQ_FILE=$1
>
> DEST=$2
>
> LF="${REQ_FILE}.lock"
>
> SUFFIX=${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}" != 'history' ] && [ "${SUFFIX}" != 'backup' ]; then
>
> cat "/logs/var/backups/archlog/${REQ_FILE}" | gzip -dc >
> "/logs/var/backups/unzipped/${REQ_FILE}"
>
> if [ "$?" = "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=$(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}" != 'history' ] && [ "${SUFFIX}" != 'backup' ]; then
>
> if [ "${SIZE}" != '16777216' ]; then
>
> echo 'partially written log - not restored - finishing recovery' >>
> /tmp/restore.mavmail.log
>
> exit 0
>
> fi
>
> fi
>
>
>
> /usr/bin/lockfile "${LF}"
>
> ################################################################
>
> ## 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}"
>
>
>
> (END)
>
>
>
> Now when I try to restart, hoping to begin recovery with the C2 log I get
an
> invalid checkpoint error:
>
>
>
> : LOG: starting archive recovery
>
> Feb 25 10:08:10 ar-db3 postgres[32538]: [3-1] @: LOG: restore_command =
"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
>
>
>
> I remove the recovery.conf file, successfully start the database and issue
a
> checkpoint. 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│Database Administrator│
>
> @utoRevenue-(R)- "Join the Revenue-tion"
> 95 Ashley Ave. West Springfield, MA., 01089
> 413-243-4800 x1512 (Phone) │ 413-732-1824 (Fax)
>
> @utoRevenue is a registered trademark and a division of Dominion
Enterprises
>
>
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2009-02-25 19:31 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-05-03 15:15 Recovery question Lorraine Mancuso <mancusol1@Scranton.edu>
2007-05-14 18:30 ` Jim C. Nasby <decibel@decibel.org>
2009-02-25 15:16 recovery question Mark Steben <msteben@autorevenue.com>
2009-02-25 15:40 ` Re: recovery question Lee Azzarello <lee@dropio.com>
2009-02-25 19:31 ` Re: recovery question Mark Steben <msteben@autorevenue.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox