up vote 5 down vote favorite
1
share [g+] share [fb]

Running PostgreSQL 9.0.1, with streaming replication keeping one read-only mirror instance up to date. Auto-vaccuum is on on the primary, except for a few tables which are not vacuumed by the auto-vacuum daemon, in an effort to reduce business-hour IO. These tables are "materialised views".

Each night at midnight, we run a vacuum across the database in order to clean up those tables that are excluded from the auto-vacuum. I'm wondering if that process replicates across to the mirror, or if I need to set up vacuum on the mirror as well?

link|improve this question
100% accept rate
Excellent question. The streaming replication uses the Write-Ahead Log, so it boils down to whether the changes VACUUM makes are logged or not. – DerfK Dec 13 '10 at 22:55
Interestingly, I have auto-vacuum turned ON on the read-only mirror, but looking in the table stats it doesn't appear have ever run; as all tables list 0 live/dead tuples, and there are no histories visible of vacuums or analyzes. – Scott Herbert Dec 14 '10 at 0:23
developer.postgresql.org/pgdocs/postgres/hot-standby.html - 25.5.2. Handling query conflicts - "Application of a vacuum cleanup record from WAL conflicts with standby transactions whose snapshots can still "see" any of the rows to be removed............. This seems to suggest that VACUUM is WAL logged, and therefore it's a "yes" to my question. Would love to get some more info from a PG guru though! – Scott Herbert Dec 14 '10 at 0:40
feedback

1 Answer

up vote 2 down vote accepted

Vacuum and autovacuum replicate like any other write operation. (Well, they are obviously somewhat special internally, but as far as your question is concerned, they are normal write operations.) Running vacuum or autovacuum on the slave doesn't do anything and is not necessary.

link|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.