postgres stats on the new primary

On 2020/07/28 9:08, Ayub M wrote:

I have a RDS PostgreSQL v11.6 with primary and standby. On the primary, vacuum/analyze
were running and pg_stat_all_tables's last_vacuum/analyze/autovacuum/autoanalyze
were having values when vacuum/analyze were run.

1. Switchover to Standby happened, now when I see pg_stat_all_tables (on the active
primary which was standby earlier), the last vacuum/analyze columns are all nulls.
Would primary-standby replication not replicate system tables? Is this how it behaves?

The information shown by the various pg_stat_* views is specific to the local
server, and is *not* replicated. In most cases that wouldn't even make
sense. The information is collated by the statistics collector [1]https://www.postgresql.org/docs/current/monitoring-stats.html and is stored
in memory and/or files in the data directory, not as a system table.

[1]: https://www.postgresql.org/docs/current/monitoring-stats.html

2. On the new primary, would the table have stats on them? (I do see pg_stats
entries for the tables on new standby), are they reliable or do I need to gather
stats and run vacuum after failover/switchover to standby?

The statistics about the *contents* of the database, as shown by pg_stats, are of
course replicated and will be reliable. More precisely they'll be as up-to-date
as the last vacuum/analyze on the former primary, just you won't see the
information about when that was.

Regards

Ian Barwick

--
Ian Barwick https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services