pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
WAL copying includes vacuum, reindex, etc?
3+ messages / 3 participants
[nested] [flat]

* WAL copying includes vacuum, reindex, etc?
@ 2008-01-15 17:19 David Wall <d.wall@computer.org>
  2008-01-15 17:35 ` Re: WAL copying includes vacuum, reindex, etc? Simon Riggs <simon@2ndquadrant.com>
  0 siblings, 1 reply; 3+ messages in thread

From: David Wall @ 2008-01-15 17:19 UTC (permalink / raw)
  To: pgsql-admin

When running WAL backups and warm-standby restores (we're currently on 
PG 8.2), do the vacuumlo changes, vacuum, analyze and reindex type 
commands get pushed through WAL files to the backup?  Or will the backup 
database not have these maintenance items done?

Thanks,
David



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: WAL copying includes vacuum, reindex, etc?
  2008-01-15 17:19 WAL copying includes vacuum, reindex, etc? David Wall <d.wall@computer.org>
@ 2008-01-15 17:35 ` Simon Riggs <simon@2ndquadrant.com>
  2008-01-15 18:07   ` Re: WAL copying includes vacuum, reindex, etc? Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 3+ messages in thread

From: Simon Riggs @ 2008-01-15 17:35 UTC (permalink / raw)
  To: d.wall@computer.org; +Cc: pgsql-admin

On Tue, 2008-01-15 at 09:19 -0800, David Wall wrote:

> When running WAL backups and warm-standby restores (we're currently on 
> PG 8.2), do the vacuumlo changes, vacuum, analyze and reindex type 
> commands get pushed through WAL files to the backup?  Or will the backup 
> database not have these maintenance items done?

All of those commands produce WAL for critical changes to data blocks,
so those are passed through correctly.

Hint bits are not set following recovery, so a full database VACUUM may
help later performance, depends upon your access patterns. It's probably
good for peace of mind too, but it's a long way from being an essential
requirement.

-- 
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com




^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: WAL copying includes vacuum, reindex, etc?
  2008-01-15 17:19 WAL copying includes vacuum, reindex, etc? David Wall <d.wall@computer.org>
  2008-01-15 17:35 ` Re: WAL copying includes vacuum, reindex, etc? Simon Riggs <simon@2ndquadrant.com>
@ 2008-01-15 18:07   ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 3+ messages in thread

From: Tom Lane @ 2008-01-15 18:07 UTC (permalink / raw)
  To: Simon Riggs <simon@2ndquadrant.com>; +Cc: d.wall@computer.org; pgsql-admin

Simon Riggs <simon@2ndquadrant.com> writes:
> All of those commands produce WAL for critical changes to data blocks,
> so those are passed through correctly.

> Hint bits are not set following recovery, so a full database VACUUM may
> help later performance, depends upon your access patterns.

That's only partially true.  Full-page images in WAL will propagate hint
bits, so in practice you could expect that only about one checkpoint
cycle's worth of hint information is missing.  If you'd been running
with full_page_writes = off then this doesn't hold, and in that case
I'd agree that a forced vacuum might be worth the trouble.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2008-01-15 18:07 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-01-15 17:19 WAL copying includes vacuum, reindex, etc? David Wall <d.wall@computer.org>
2008-01-15 17:35 ` Simon Riggs <simon@2ndquadrant.com>
2008-01-15 18:07   ` Tom Lane <tgl@sss.pgh.pa.us>

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