Обсуждение: WAL files during a backup
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
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 > > >
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 >> >> >>
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 > > >
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 >> >> >>
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 >>> >>> >>> > > >