pg.ddx.io pgsql-admin@postgresql.org mailing list archivehelp / color / mirror / Atom feed
WAL files during a backup 6+ messages / 2 participants [nested] [flat]
* WAL files during a backup @ 2008-08-27 14:07 windsurferdrew-pg@yahoo.com 0 siblings, 1 reply; 6+ messages in thread From: windsurferdrew-pg@yahoo.com @ 2008-08-27 14:07 UTC (permalink / raw) To: pgsql-admin 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 ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: WAL files during a backup @ 2008-08-27 20:36 steve@outtalimits.com.au parent: windsurferdrew-pg@yahoo.com 0 siblings, 0 replies; 6+ messages in thread From: steve@outtalimits.com.au @ 2008-08-27 20:36 UTC (permalink / raw) To: windsurferdrew-pg@yahoo.com; +Cc: pgsql-admin 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 > > > ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: WAL files during a backup @ 2008-08-27 20:49 windsurferdrew-pg@yahoo.com 0 siblings, 1 reply; 6+ messages in thread From: windsurferdrew-pg@yahoo.com @ 2008-08-27 20:49 UTC (permalink / raw) To: steve@outtalimits.com.au; +Cc: pgsql-admin 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 > > > ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: WAL files during a backup @ 2008-08-27 21:31 steve@outtalimits.com.au parent: windsurferdrew-pg@yahoo.com 0 siblings, 0 replies; 6+ messages in thread From: steve@outtalimits.com.au @ 2008-08-27 21:31 UTC (permalink / raw) To: windsurferdrew-pg@yahoo.com; +Cc: pgsql-admin 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 >> >> >> ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: WAL files during a backup @ 2008-08-28 12:24 windsurferdrew-pg@yahoo.com 0 siblings, 1 reply; 6+ messages in thread From: windsurferdrew-pg@yahoo.com @ 2008-08-28 12:24 UTC (permalink / raw) To: steve@outtalimits.com.au; +Cc: pgsql-admin 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 >> >> >> ^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: WAL files during a backup @ 2008-08-28 23:10 steve@outtalimits.com.au parent: windsurferdrew-pg@yahoo.com 0 siblings, 0 replies; 6+ messages in thread From: steve@outtalimits.com.au @ 2008-08-28 23:10 UTC (permalink / raw) To: windsurferdrew-pg@yahoo.com; +Cc: pgsql-admin 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 >>> >>> >>> > > > ^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2008-08-28 23:10 UTC | newest] Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2008-08-27 14:07 WAL files during a backup windsurferdrew-pg@yahoo.com 2008-08-27 20:36 ` steve@outtalimits.com.au 2008-08-27 20:49 Re: WAL files during a backup windsurferdrew-pg@yahoo.com 2008-08-27 21:31 ` steve@outtalimits.com.au 2008-08-28 12:24 Re: WAL files during a backup windsurferdrew-pg@yahoo.com 2008-08-28 23:10 ` steve@outtalimits.com.au
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