Обсуждение: backup WAL files,

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

"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

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

"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

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

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

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