Обсуждение: please explain vacuum with WAL
Hello! I tried to ask this question in novice list. Just because there are no replies I try here. This is really novice question- I'm oracle dba :-) > I just installed 8.3 with WAL enabled. > But I can't understand why postgres generated many archive logs during > vacuum, if WAL is enabled. > Could you explain? > Thank you! > > >
On Tue, 2008-07-08 at 13:09 +0500, Dmitry Melekhov wrote: > Hello! > > I tried to ask this question in novice list. > Just because there are no replies I try here. > This is really novice question- I'm oracle dba :-) > > > I just installed 8.3 with WAL enabled. > > But I can't understand why postgres generated many archive logs during > > vacuum, if WAL is enabled. > > Could you explain? What do you mean "WAL is enabled"? That's not a term I recognize since WAL is always enabled. Best read this http://developer.postgresql.org/pgdocs/postgres/routine-vacuuming.html VACUUM needs to perform writes to clear up, which generates WAL. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support
Simon Riggs пишет: > On Tue, 2008-07-08 at 13:09 +0500, Dmitry Melekhov wrote: > >> Hello! >> >> I tried to ask this question in novice list. >> Just because there are no replies I try here. >> This is really novice question- I'm oracle dba :-) >> >> >>> I just installed 8.3 with WAL enabled. >>> But I can't understand why postgres generated many archive logs during >>> vacuum, if WAL is enabled. >>> Could you explain? >>> > > What do you mean "WAL is enabled"? That's not a term I recognize since > WAL is always enabled. > > AFAIK, it can be disabled. May be I'm wrong... > Best read this > http://developer.postgresql.org/pgdocs/postgres/routine-vacuuming.html > > VACUUM needs to perform writes to clear up, which generates WAL. > > This is what I don't understand. I think WAL can be used for point-in-time recovery. So, if I have database backup and WAL generated after this backup, I can do recovery, this mean WAL already contains all changes to database, without vacuum. Could you tell me what is wrong in my sentence?
WAL is the journal for postgres, so every event that happens goes into the WAL. Using it for backup or replication simplyuses it to replay all events on the backup / replicated database. ----- Original Message ---- > From: Dmitry Melekhov <dm@belkam.com> > To: Simon Riggs <simon@2ndquadrant.com> > Cc: pgsql-general@postgresql.org > Sent: Tuesday, 8 July, 2008 9:54:57 AM > Subject: Re: [GENERAL] please explain vacuum with WAL > > Simon Riggs пишет: > > On Tue, 2008-07-08 at 13:09 +0500, Dmitry Melekhov wrote: > > > >> Hello! > >> > >> I tried to ask this question in novice list. > >> Just because there are no replies I try here. > >> This is really novice question- I'm oracle dba :-) > >> > >> > >>> I just installed 8.3 with WAL enabled. > >>> But I can't understand why postgres generated many archive logs during > >>> vacuum, if WAL is enabled. > >>> Could you explain? > >>> > > > > What do you mean "WAL is enabled"? That's not a term I recognize since > > WAL is always enabled. > > > > > AFAIK, it can be disabled. May be I'm wrong... > > Best read this > > http://developer.postgresql.org/pgdocs/postgres/routine-vacuuming.html > > > > VACUUM needs to perform writes to clear up, which generates WAL. > > > > > This is what I don't understand. > I think WAL can be used for point-in-time recovery. > So, if I have database backup and WAL generated after this backup, I can > do recovery, this mean WAL already contains all changes to database, > without vacuum. Could you tell me what is wrong in my sentence? > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general __________________________________________________________ Not happy with your email address?. Get the one you really want - millions of new email addresses available now at Yahoo! http://uk.docs.yahoo.com/ymail/new.html
On Tue, 2008-07-08 at 13:54 +0500, Dmitry Melekhov wrote: > > What do you mean "WAL is enabled"? That's not a term I recognize > since > > WAL is always enabled. > > > > > AFAIK, it can be disabled. May be I'm wrong... Maybe you mean archiving? That *can* be disabled. > > Best read this > > > http://developer.postgresql.org/pgdocs/postgres/routine-vacuuming.html > > > > VACUUM needs to perform writes to clear up, which generates WAL. > > > > > This is what I don't understand. > I think WAL can be used for point-in-time recovery. > So, if I have database backup and WAL generated after this backup, I > can > do recovery, this mean WAL already contains all changes to database, > without vacuum. Could you tell me what is wrong in my sentence? Vacuum performs an essential function and its changes are WAL-logged. Those changes are an essential part of the structure of the database and must be included as part of recovery also. If you think PostgreSQL is somehow different to Oracle, read up on Deferred Block Cleanout and how SELECT statements can cause additional redo writes. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support
Glyn Astill пишет: > WAL is the journal for postgres, so every event that happens goes into the WAL. Using it for backup or replication simplyuses it to replay all events on the backup / replicated database. > > > > As I thought, thank you. But why vacuum generates WAL ? As I understand all database changes are already logged.... > ----- Original Message ---- > >> From: Dmitry Melekhov <dm@belkam.com> >> To: Simon Riggs <simon@2ndquadrant.com> >> Cc: pgsql-general@postgresql.org >> Sent: Tuesday, 8 July, 2008 9:54:57 AM >> Subject: Re: [GENERAL] please explain vacuum with WAL >> >> Simon Riggs пишет: >> >>> On Tue, 2008-07-08 at 13:09 +0500, Dmitry Melekhov wrote: >>> >>> >>>> Hello! >>>> >>>> I tried to ask this question in novice list. >>>> Just because there are no replies I try here. >>>> This is really novice question- I'm oracle dba :-) >>>> >>>> >>>> >>>>> I just installed 8.3 with WAL enabled. >>>>> But I can't understand why postgres generated many archive logs during >>>>> vacuum, if WAL is enabled. >>>>> Could you explain? >>>>> >>>>> >>> What do you mean "WAL is enabled"? That's not a term I recognize since >>> WAL is always enabled. >>> >>> >>> >> AFAIK, it can be disabled. May be I'm wrong... >> >>> Best read this >>> http://developer.postgresql.org/pgdocs/postgres/routine-vacuuming.html >>> >>> VACUUM needs to perform writes to clear up, which generates WAL. >>> >>> >>> >> This is what I don't understand. >> I think WAL can be used for point-in-time recovery. >> So, if I have database backup and WAL generated after this backup, I can >> do recovery, this mean WAL already contains all changes to database, >> without vacuum. Could you tell me what is wrong in my sentence? >> >> >>
Simon Riggs пишет: > Vacuum performs an essential function and its changes are WAL-logged. > Those changes are an essential part of the structure of the database and > must be included as part of recovery also. > Now I understand. Thank you!
Dmitry Melekhov wrote: > Glyn Astill пишет: >> WAL is the journal for postgres, so every event that happens goes into the WAL. Using it for backup or replication simplyuses it to replay all events on the backup / replicated database. > As I thought, thank you. > But why vacuum generates WAL ? As I understand all database changes are > already logged.... Sure, but at a very low level. It's not logging statements and replaying those statements; it's more of a block-level change log. My understanding is that for later WAL records from the master server to make sense to a slave that's replaying them, the changes made by VACUUM must also be replicated. Otherwise the archived WAL segments from the master will be asking the slave to do things that just don't make sense given the slave's current understanding of the database state. -- Craig Ringer
Dmitry Melekhov wrote: > I tried to ask this question in novice list. > Just because there are no replies I try here. > This is really novice question- I'm oracle dba :-) > > > I just installed 8.3 with WAL enabled. > > But I can't understand why postgres generated many archive logs during > > vacuum, if WAL is enabled. > > Could you explain? VACUUM changes the database, and these changes go to the transaction log. If VACUUM changes a lot, you can end up with many archived WAL files. There's no correspondence in Oracle, but think of it as an "ALTER TABLE <name> MOVE" except that there is no NOLOGGING clause. Yours, Laurenz Albe
Dmitry Melekhov wrote: > So, if I have database backup and WAL generated after this backup, I can > do recovery, this mean WAL already contains all changes to database, > without vacuum. Could you tell me what is wrong in my sentence? VACUUM *is* a change to the database. Yours, Laurenz Albe