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 :-)

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 simply uses 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

Glyn Astill пишет:

WAL is the journal for postgres, so every event that happens goes into the WAL. Using it for backup or replication simply uses 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....

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

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 simply uses 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