Обсуждение: 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