pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
backup 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