Thread: streaming replication vacuum
Might be discussed but not clear for the below query.
In async. streaming replication do I need to run VACUUM & ANALYZE on both master & slave.
Bec, while querying on master getting below result, but slave's system table is not updated.
select last_vacuum, last_autovacuum, last_analyze, last_autoanalyze from pg_stat_user_tables where relname like 'test';
last_vacuum | last_autovacuum | last_analyze | last_autoanalyze
----------------------------------+-----------------+----------------------------------+------------------
2011-12-30 11:14:02.802973+05:30 | | 2011-12-30 11:14:20.943012+05:30 |
Slave:
select last_vacuum, last_autovacuum, last_analyze, last_autoanalyze from pg_stat_user_tables where relname like 'test';
last_vacuum | last_autovacuum | last_analyze | last_autoanalyze
-------------+-----------------+--------------+------------------
| | |
On Fri, Dec 30, 2011 at 07:35, dhaval jaiswal <dhavallj@hotmail.com> wrote: > > Might be discussed but not clear for the below query. > > In async. streaming replication do I need to run VACUUM & ANALYZE on both > master & slave. > Bec, while querying on master getting below result, but slave's system table > is not updated. You run VACUUM and ANALYZE on the master only, and the results are automatically replicated to the slave. However, the *statistics views* on the slave are not updated. That's why it looks like it's not been run, even though it has. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/
On Fri, Dec 30, 2011 at 5:03 AM, Magnus Hagander <magnus@hagander.net> wrote: > On Fri, Dec 30, 2011 at 07:35, dhaval jaiswal <dhavallj@hotmail.com> wrote: >> >> Might be discussed but not clear for the below query. >> >> In async. streaming replication do I need to run VACUUM & ANALYZE on both >> master & slave. >> Bec, while querying on master getting below result, but slave's system table >> is not updated. > > You run VACUUM and ANALYZE on the master only, and the results are > automatically replicated to the slave. > > However, the *statistics views* on the slave are not updated. That's > why it looks like it's not been run, even though it has. Given the use of hot spares in this setup, wouldn't that kind of be a bug that they're not updated?
On Sat, Dec 31, 2011 at 12:06 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote: > On Fri, Dec 30, 2011 at 5:03 AM, Magnus Hagander <magnus@hagander.net> wrote: >> On Fri, Dec 30, 2011 at 07:35, dhaval jaiswal <dhavallj@hotmail.com> wrote: >>> >>> Might be discussed but not clear for the below query. >>> >>> In async. streaming replication do I need to run VACUUM & ANALYZE on both >>> master & slave. >>> Bec, while querying on master getting below result, but slave's system table >>> is not updated. >> >> You run VACUUM and ANALYZE on the master only, and the results are >> automatically replicated to the slave. >> >> However, the *statistics views* on the slave are not updated. That's >> why it looks like it's not been run, even though it has. > > Given the use of hot spares in this setup, wouldn't that kind of be a > bug that they're not updated? It's intentional. You don't need to, nor can you run VACUUM or ANALYZE, so there is no need to look at those fields. The stats tables show activity on the standby separately from the master, which is useful. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services