Thread: WAL files during a backup

WAL files during a backup

From
windsurferdrew-pg@yahoo.com
Date:
I have a question regarding the WAL files that are moved during a backup to the "archive directory".

I have setup the postgresql.conf file to have the following archive command:

archive_command = 'cp -i %p /var/lib/pgsql/backups/%f </dev/null'

Environment:
PG Version 8.1.4
OS: Linux 2.6.18-8.el5

After I have run the pg_hotbackup script, the backups directory contains only 2 files:
1. The gzipped tar file pg_hotbackup_<timestamp>.tar.gz, and
2. a WAL file ".backup" file, (for example 00000001000000000000223387.0089ED8C.backup)

The actual WAL file did not get copied to the backups directory. From the example above, I would have expected at least
1 WAL file named 00000001000000000000223387 to be in the backups directory. 

My questions are:
1. Is this normal behavior? (perhaps no activity in the DB to cause a WAL file write during the backup?)
2. If I try to restore the DB without this WAL file, will the restore fail?

Thanks in advance

Drew


Re: WAL files during a backup

From
Date:
00000001000000000000223387  would usually be the next WAL to be written.
How often are you WALs written out?

On Wed, 27 Aug 2008 07:07:17 -0700 (PDT), windsurferdrew-pg@yahoo.com
wrote:
> I have a question regarding the WAL files that are moved during a backup
to
> the "archive directory".
>
> I have setup the postgresql.conf file to have the following archive
> command:
>
> archive_command = 'cp -i %p /var/lib/pgsql/backups/%f </dev/null'
>
> Environment:
> PG Version 8.1.4
> OS: Linux 2.6.18-8.el5
>
> After I have run the pg_hotbackup script, the backups directory contains
> only 2 files:
> 1. The gzipped tar file pg_hotbackup_<timestamp>.tar.gz, and
> 2. a WAL file ".backup" file, (for example
> 00000001000000000000223387.0089ED8C.backup)
>
> The actual WAL file did not get copied to the backups directory. From the
> example above, I would have expected at least 1  WAL file named
> 00000001000000000000223387 to be in the backups directory.
>
> My questions are:
> 1. Is this normal behavior? (perhaps no activity in the DB to cause a WAL
> file write during the backup?)
> 2. If I try to restore the DB without this WAL file, will the restore
> fail?
>
> Thanks in advance
>
> Drew
>
>
>

Re: WAL files during a backup

From
Date:
This WAL wont get copied to the archive directory until it reaches 16MB
worth of transactional data. I will actually be testing today for work, if
I can grab the latest xlog and this can be used in the restore aswell as
archived WAL's

I will let you know the results, unless some1 else already knows.?

On Wed, 27 Aug 2008 13:49:13 -0700 (PDT), windsurferdrew-pg@yahoo.com
wrote:
> Steve, thanks for your response and your question.
>
> Ok, here is some clarification on the WAL file name used in the example
> below.
>
> The WAL file name I used in the example is actually
> 000000010000000000000088, and the backup file name is
> 000000010000000000000088.0089ED8C.backup.
>
> The WAL file 000000010000000000000088 exists in the data/pg_xlog
directory
> after the backup run is completed. The problem for me is that it wasn't
> copied to the archive directory.
>
>
> Since this is a test machine and not much db activity occurring on this
> server, there have been 6 additional WAL files added to the pg_xlog
> directory in the last 6 hours since the last backup was performed.
>
> Drew
>
>
> ----- Original Message ----
> From: "steve@outtalimits.com.au" <steve@outtalimits.com.au>
> To: windsurferdrew-pg@yahoo.com
> Cc: pgsql-admin@postgresql.org
> Sent: Wednesday, August 27, 2008 4:36:24 PM
> Subject: Re: [ADMIN] WAL files during a backup
>
> 00000001000000000000223387  would usually be the next WAL to be written.
> How often are you WALs written out?
>
> On Wed, 27 Aug 2008 07:07:17 -0700 (PDT), windsurferdrew-pg@yahoo.com
> wrote:
>> I have a question regarding the WAL files that are moved during a backup
> to
>> the "archive directory".
>>
>> I have setup the postgresql.conf file to have the following archive
>> command:
>>
>> archive_command = 'cp -i %p /var/lib/pgsql/backups/%f </dev/null'
>>
>> Environment:
>> PG Version 8.1.4
>> OS: Linux 2.6.18-8.el5
>>
>> After I have run the pg_hotbackup script, the backups directory contains
>> only 2 files:
>> 1. The gzipped tar file pg_hotbackup_<timestamp>.tar.gz, and
>> 2. a WAL file ".backup" file, (for example
>> 00000001000000000000223387.0089ED8C.backup)
>>
>> The actual WAL file did not get copied to the backups directory. From
> the
>> example above, I would have expected at least 1  WAL file named
>> 00000001000000000000223387 to be in the backups directory.
>>
>> My questions are:
>> 1. Is this normal behavior? (perhaps no activity in the DB to cause a
> WAL
>> file write during the backup?)
>> 2. If I try to restore the DB without this WAL file, will the restore
>> fail?
>>
>> Thanks in advance
>>
>> Drew
>>
>>
>>

Re: WAL files during a backup

From
windsurferdrew-pg@yahoo.com
Date:
Steve, thanks for your response and your question.

Ok, here is some clarification on the WAL file name used in the example below.

The WAL file name I used in the example is actually 000000010000000000000088, and the backup file name is
000000010000000000000088.0089ED8C.backup.

The WAL file 000000010000000000000088 exists in the data/pg_xlog directory after the backup run is completed. The
problemfor me is that it wasn't copied to the archive directory. 


Since this is a test machine and not much db activity occurring on this server, there have been 6 additional WAL files
addedto the pg_xlog directory in the last 6 hours since the last backup was performed. 

Drew


----- Original Message ----
From: "steve@outtalimits.com.au" <steve@outtalimits.com.au>
To: windsurferdrew-pg@yahoo.com
Cc: pgsql-admin@postgresql.org
Sent: Wednesday, August 27, 2008 4:36:24 PM
Subject: Re: [ADMIN] WAL files during a backup

00000001000000000000223387  would usually be the next WAL to be written.
How often are you WALs written out?

On Wed, 27 Aug 2008 07:07:17 -0700 (PDT), windsurferdrew-pg@yahoo.com
wrote:
> I have a question regarding the WAL files that are moved during a backup
to
> the "archive directory".
>
> I have setup the postgresql.conf file to have the following archive
> command:
>
> archive_command = 'cp -i %p /var/lib/pgsql/backups/%f </dev/null'
>
> Environment:
> PG Version 8.1.4
> OS: Linux 2.6.18-8.el5
>
> After I have run the pg_hotbackup script, the backups directory contains
> only 2 files:
> 1. The gzipped tar file pg_hotbackup_<timestamp>.tar.gz, and
> 2. a WAL file ".backup" file, (for example
> 00000001000000000000223387.0089ED8C.backup)
>
> The actual WAL file did not get copied to the backups directory. From the
> example above, I would have expected at least 1  WAL file named
> 00000001000000000000223387 to be in the backups directory.
>
> My questions are:
> 1. Is this normal behavior? (perhaps no activity in the DB to cause a WAL
> file write during the backup?)
> 2. If I try to restore the DB without this WAL file, will the restore
> fail?
>
> Thanks in advance
>
> Drew
>
>
>


Re: WAL files during a backup

From
windsurferdrew-pg@yahoo.com
Date:
Thanks Steve.

If I understand your comment regarding the 16MB limit, it means I should not worry about the WAL file not being copied
tothe archive directory. 


I hope this also means a subsequent attempt to restore the db will successfully work since the WAL file is still in the
pg_xlogdirectory. 

Drew


----- Original Message ----
From: "steve@outtalimits.com.au" <steve@outtalimits.com.au>
To: windsurferdrew-pg@yahoo.com
Cc: pgsql-admin@postgresql.org
Sent: Wednesday, August 27, 2008 5:31:30 PM
Subject: Re: [ADMIN] WAL files during a backup

This WAL wont get copied to the archive directory until it reaches 16MB
worth of transactional data. I will actually be testing today for work, if
I can grab the latest xlog and this can be used in the restore aswell as
archived WAL's

I will let you know the results, unless some1 else already knows.?

On Wed, 27 Aug 2008 13:49:13 -0700 (PDT), windsurferdrew-pg@yahoo.com
wrote:
> Steve, thanks for your response and your question.
>
> Ok, here is some clarification on the WAL file name used in the example
> below.
>
> The WAL file name I used in the example is actually
> 000000010000000000000088, and the backup file name is
> 000000010000000000000088.0089ED8C.backup.
>
> The WAL file 000000010000000000000088 exists in the data/pg_xlog
directory
> after the backup run is completed. The problem for me is that it wasn't
> copied to the archive directory.
>
>
> Since this is a test machine and not much db activity occurring on this
> server, there have been 6 additional WAL files added to the pg_xlog
> directory in the last 6 hours since the last backup was performed.
>
> Drew
>
>
> ----- Original Message ----
> From: "steve@outtalimits.com.au" <steve@outtalimits.com.au>
> To: windsurferdrew-pg@yahoo.com
> Cc: pgsql-admin@postgresql.org
> Sent: Wednesday, August 27, 2008 4:36:24 PM
> Subject: Re: [ADMIN] WAL files during a backup
>
> 00000001000000000000223387  would usually be the next WAL to be written.
> How often are you WALs written out?
>
> On Wed, 27 Aug 2008 07:07:17 -0700 (PDT), windsurferdrew-pg@yahoo.com
> wrote:
>> I have a question regarding the WAL files that are moved during a backup
> to
>> the "archive directory".
>>
>> I have setup the postgresql.conf file to have the following archive
>> command:
>>
>> archive_command = 'cp -i %p /var/lib/pgsql/backups/%f </dev/null'
>>
>> Environment:
>> PG Version 8.1.4
>> OS: Linux 2.6.18-8.el5
>>
>> After I have run the pg_hotbackup script, the backups directory contains
>> only 2 files:
>> 1. The gzipped tar file pg_hotbackup_<timestamp>.tar.gz, and
>> 2. a WAL file ".backup" file, (for example
>> 00000001000000000000223387.0089ED8C.backup)
>>
>> The actual WAL file did not get copied to the backups directory. From
> the
>> example above, I would have expected at least 1  WAL file named
>> 00000001000000000000223387 to be in the backups directory.
>>
>> My questions are:
>> 1. Is this normal behavior? (perhaps no activity in the DB to cause a
> WAL
>> file write during the backup?)
>> 2. If I try to restore the DB without this WAL file, will the restore
>> fail?
>>
>> Thanks in advance
>>
>> Drew
>>
>>
>>


Re: WAL files during a backup

From
Date:
Hey Drew,

Correct. This will all depend on what sort of backup system you are after.

If you want to recover up to its existing state, you will need a script
that will nab the current log, located in the x_log dir, and backup this up
every x minutes. Along with this you will be backing up your WAL's. You can
add the current X log to the WAL and recover your DB to its existing state.


On Thu, 28 Aug 2008 05:24:56 -0700 (PDT), windsurferdrew-pg@yahoo.com
wrote:
> Thanks Steve.
>
> If I understand your comment regarding the 16MB limit, it means I should
> not worry about the WAL file not being copied to the archive directory.
>
>
> I hope this also means a subsequent attempt to restore the db will
> successfully work since the WAL file is still in the pg_xlog directory.
>
> Drew
>
>
> ----- Original Message ----
> From: "steve@outtalimits.com.au" <steve@outtalimits.com.au>
> To: windsurferdrew-pg@yahoo.com
> Cc: pgsql-admin@postgresql.org
> Sent: Wednesday, August 27, 2008 5:31:30 PM
> Subject: Re: [ADMIN] WAL files during a backup
>
> This WAL wont get copied to the archive directory until it reaches 16MB
> worth of transactional data. I will actually be testing today for work,
if
> I can grab the latest xlog and this can be used in the restore aswell as
> archived WAL's
>
> I will let you know the results, unless some1 else already knows.?
>
> On Wed, 27 Aug 2008 13:49:13 -0700 (PDT), windsurferdrew-pg@yahoo.com
> wrote:
>> Steve, thanks for your response and your question.
>>
>> Ok, here is some clarification on the WAL file name used in the example
>> below.
>>
>> The WAL file name I used in the example is actually
>> 000000010000000000000088, and the backup file name is
>> 000000010000000000000088.0089ED8C.backup.
>>
>> The WAL file 000000010000000000000088 exists in the data/pg_xlog
> directory
>> after the backup run is completed. The problem for me is that it wasn't
>> copied to the archive directory.
>>
>>
>> Since this is a test machine and not much db activity occurring on this
>> server, there have been 6 additional WAL files added to the pg_xlog
>> directory in the last 6 hours since the last backup was performed.
>>
>> Drew
>>
>>
>> ----- Original Message ----
>> From: "steve@outtalimits.com.au" <steve@outtalimits.com.au>
>> To: windsurferdrew-pg@yahoo.com
>> Cc: pgsql-admin@postgresql.org
>> Sent: Wednesday, August 27, 2008 4:36:24 PM
>> Subject: Re: [ADMIN] WAL files during a backup
>>
>> 00000001000000000000223387  would usually be the next WAL to be written.
>> How often are you WALs written out?
>>
>> On Wed, 27 Aug 2008 07:07:17 -0700 (PDT), windsurferdrew-pg@yahoo.com
>> wrote:
>>> I have a question regarding the WAL files that are moved during a
> backup
>> to
>>> the "archive directory".
>>>
>>> I have setup the postgresql.conf file to have the following archive
>>> command:
>>>
>>> archive_command = 'cp -i %p /var/lib/pgsql/backups/%f </dev/null'
>>>
>>> Environment:
>>> PG Version 8.1.4
>>> OS: Linux 2.6.18-8.el5
>>>
>>> After I have run the pg_hotbackup script, the backups directory
> contains
>>> only 2 files:
>>> 1. The gzipped tar file pg_hotbackup_<timestamp>.tar.gz, and
>>> 2. a WAL file ".backup" file, (for example
>>> 00000001000000000000223387.0089ED8C.backup)
>>>
>>> The actual WAL file did not get copied to the backups directory. From
>> the
>>> example above, I would have expected at least 1  WAL file named
>>> 00000001000000000000223387 to be in the backups directory.
>>>
>>> My questions are:
>>> 1. Is this normal behavior? (perhaps no activity in the DB to cause a
>> WAL
>>> file write during the backup?)
>>> 2. If I try to restore the DB without this WAL file, will the restore
>>> fail?
>>>
>>> Thanks in advance
>>>
>>> Drew
>>>
>>>
>>>
>
>
>