Thread: streaming replication vacuum

streaming replication vacuum

From
dhaval jaiswal
Date:

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
-------------+-----------------+--------------+------------------
             |                 |              |

Re: streaming replication vacuum

From
Magnus Hagander
Date:
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/

Re: streaming replication vacuum

From
Scott Marlowe
Date:
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?

Re: streaming replication vacuum

From
Simon Riggs
Date:
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