pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedbackup WAL files,
7+ messages / 3 participants
[nested] [flat]
* backup WAL files,
@ 2008-01-15 09:44 Sebastian Reitenbach <sebastia@l00-bugdead-prods.de>
2008-01-15 15:41 ` Re: backup WAL files, Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Sebastian Reitenbach @ 2008-01-15 09:44 UTC (permalink / raw)
To: pgsql-admin
Hi,
I use a script like the example below to generate a list of the WAL files
that have to be saved by the backup job. I take the the names of the first
and last WAL files from the backup HISTORYFILE generated by
pg_start_backup() and pg_stop_backup(). The names of the WAL files between
the first and the last I calculate the following way:
HISTORYFILE=`find ${ARCHIVEPATH} -name "*.backup" -exec
grep -l "backup-${DATABASE}-${NOW}" {} \;`
FIRSTWALFILE=`grep "START WAL LOCATION" ${HISTORYFILE} |
awk '{print $6} ' | sed -e 's/)$//g'`
LASTWALFILE=`grep "STOP WAL LOCATION" ${HISTORYFILE} |
awk '{print $6} ' | sed -e 's/)$//g'`
echo FIRSTWALFILE=$FIRSTWALFILE
echo LASTWALFILE=$LASTWALFILE
FILE_PREFIX=`echo $FIRSTWALFILE | cut -c 1-15`
FIRST_SUFFIX=`echo $FIRSTWALFILE | cut -c 16-`
LAST_SUFFIX=`echo $LASTWALFILE | cut -c 16-`
CNTA=`echo "obase=10;ibase=16; $FIRST_SUFFIX" | bc`
CNTE=`echo "obase=10;ibase=16; $LAST_SUFFIX" | bc`
echo $CNTA $CNTE
while [ $CNTA -le $CNTE ];do
echo ${FILE_PREFIX}${FIRST_SUFFIX} # >> outfile
FIRST_SUFFIX=`echo "obase=16;ibase=16; ${FIRST_SUFFIX} + 1" | bc`
CNTA=$(($CNTA+1))
done
The WAL files have names like this:
00000001000000010000003C
I am wonder what the meaning of the two 1 in the filename is? Are the WAL
file names counted up to FFFFFFFFFFFFFFFFFFFFFFFFF ?
Then I'll run into problems anyways as these int number are too large to be
handled by bash.
any insight is highly appreciated.
kind regards
Sebastian
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: backup WAL files,
2008-01-15 09:44 backup WAL files, Sebastian Reitenbach <sebastia@l00-bugdead-prods.de>
@ 2008-01-15 15:41 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-01-15 22:48 ` Re: backup WAL files, Tom Davies <tgdavies@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2008-01-15 15:41 UTC (permalink / raw)
To: Sebastian Reitenbach <sebastia@l00-bugdead-prods.de>; +Cc: pgsql-admin
"Sebastian Reitenbach" <sebastia@l00-bugdead-prods.de> writes:
> The WAL files have names like this:
> 00000001000000010000003C
> I am wonder what the meaning of the two 1 in the filename is?
The first one (the first 8 hex digits actually) are the current
"timeline" number. The second one isn't very interesting, it's
an artifact of the way that WAL file locations are converted to
file names internally.
> Are the WAL
> file names counted up to FFFFFFFFFFFFFFFFFFFFFFFFF ?
> Then I'll run into problems anyways as these int number are too large to be
> handled by bash.
You definitely should not expect to convert the names to integers.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: backup WAL files,
2008-01-15 09:44 backup WAL files, Sebastian Reitenbach <sebastia@l00-bugdead-prods.de>
2008-01-15 15:41 ` Re: backup WAL files, Tom Lane <tgl@sss.pgh.pa.us>
@ 2008-01-15 22:48 ` Tom Davies <tgdavies@gmail.com>
2008-01-15 23:49 ` Re: backup WAL files, Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Davies @ 2008-01-15 22:48 UTC (permalink / raw)
To: pgsql-admin
On 16/01/2008, at 2:41 AM, Tom Lane wrote:
>
> You definitely should not expect to convert the names to integers.
Presumably you can convert them to 96 bit integers? i.e. they are
always strings of hex characters?
Tom
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: backup WAL files,
2008-01-15 09:44 backup WAL files, Sebastian Reitenbach <sebastia@l00-bugdead-prods.de>
2008-01-15 15:41 ` Re: backup WAL files, Tom Lane <tgl@sss.pgh.pa.us>
2008-01-15 22:48 ` Re: backup WAL files, Tom Davies <tgdavies@gmail.com>
@ 2008-01-15 23:49 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Tom Lane @ 2008-01-15 23:49 UTC (permalink / raw)
To: Tom Davies <tgdavies@gmail.com>; +Cc: pgsql-admin
Tom Davies <tgdavies@gmail.com> writes:
> On 16/01/2008, at 2:41 AM, Tom Lane wrote:
>> You definitely should not expect to convert the names to integers.
> Presumably you can convert them to 96 bit integers? i.e. they are
> always strings of hex characters?
You could, but in most scripting languages I can think of, it'd be
a lot easier just to treat them as strings.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: backup WAL files,
@ 2008-01-15 16:32 Sebastian Reitenbach <sebastia@l00-bugdead-prods.de>
2008-01-15 17:03 ` Re: backup WAL files, Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Sebastian Reitenbach @ 2008-01-15 16:32 UTC (permalink / raw)
To: ; +Cc: pgsql-admin
Hi,
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Sebastian Reitenbach" <sebastia@l00-bugdead-prods.de> writes:
> > The WAL files have names like this:
> > 00000001000000010000003C
>
> > I am wonder what the meaning of the two 1 in the filename is?
>
> The first one (the first 8 hex digits actually) are the current
> "timeline" number. The second one isn't very interesting, it's
> an artifact of the way that WAL file locations are converted to
> file names internally.
thanks for this information.
>
> > Are the WAL
> > file names counted up to FFFFFFFFFFFFFFFFFFFFFFFFF ?
> > Then I'll run into problems anyways as these int number are too large to
be
> > handled by bash.
>
> You definitely should not expect to convert the names to integers.
Then I do not understand why only the names of the first and the last WAL
file are stored in the backup history file. I assumed that when I count from
the first to the last I catch all WAL files needed for a complete backup.
Then I have no idea how to figure out, which of the WAL files are needed for
the backup job. Or do I have to handle this via the file modification
timestamps?
does anybody has a pointer to documentation where I can read up about how
the names of the WAL files are created/used in postgres?
thanks
Sebastian
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: backup WAL files,
2008-01-15 16:32 Re: backup WAL files, Sebastian Reitenbach <sebastia@l00-bugdead-prods.de>
@ 2008-01-15 17:03 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Tom Lane @ 2008-01-15 17:03 UTC (permalink / raw)
To: Sebastian Reitenbach <sebastia@l00-bugdead-prods.de>; +Cc: pgsql-admin
"Sebastian Reitenbach" <sebastia@l00-bugdead-prods.de> writes:
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> You definitely should not expect to convert the names to integers.
> Then I do not understand why only the names of the first and the last WAL
> file are stored in the backup history file.
You can compare the filenames as strings (at least in C locale this
works fine).
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: backup WAL files,
@ 2008-01-16 07:45 Sebastian Reitenbach <sebastia@l00-bugdead-prods.de>
0 siblings, 0 replies; 7+ messages in thread
From: Sebastian Reitenbach @ 2008-01-16 07:45 UTC (permalink / raw)
To: pgsql-admin
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Tom Davies <tgdavies@gmail.com> writes:
> > On 16/01/2008, at 2:41 AM, Tom Lane wrote:
> >> You definitely should not expect to convert the names to integers.
>
> > Presumably you can convert them to 96 bit integers? i.e. they are
> > always strings of hex characters?
>
> You could, but in most scripting languages I can think of, it'd be
> a lot easier just to treat them as strings.
yeah, I tried to convert the whole filename into an integer, and then
compare it, but the int was too large then, therefore I only took the last 9
hex digits and converted these.
Anybody knows in which source file the filename is generated, then I can
take a look by myself and get all the details how it is done?
thanks for your insights
Sebastian
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2008-01-16 07:45 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-01-15 09:44 backup WAL files, Sebastian Reitenbach <sebastia@l00-bugdead-prods.de>
2008-01-15 15:41 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-01-15 22:48 ` Tom Davies <tgdavies@gmail.com>
2008-01-15 23:49 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-01-15 16:32 Re: backup WAL files, Sebastian Reitenbach <sebastia@l00-bugdead-prods.de>
2008-01-15 17:03 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-01-16 07:45 Re: backup WAL files, Sebastian Reitenbach <sebastia@l00-bugdead-prods.de>
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