In PostgreSQL 9.0.1 with streaming replication, VACUUM operations on the primary server don't automatically replicate to standby servers. This is because VACUUM is a maintenance operation rather than a transactional command. The standby only receives WAL (Write-Ahead Log) records for actual data modifications, not for maintenance operations.

Materialized views often require manual VACUUMing because they're typically excluded from autovacuum due to their large size or infrequent updates. Here's a common scenario:

-- Example of a materialized view that needs manual VACUUM
CREATE MATERIALIZED VIEW sales_summary AS
SELECT region, SUM(amount) as total_sales
FROM sales
GROUP BY region;

Your current approach of running VACUUM at midnight is good practice. However, you need to implement a similar process on the standby if you want to maintain the same storage efficiency. Here's how you might modify your maintenance script:

#!/bin/bash
# Primary server script
psql -U postgres -c "VACUUM ANALYZE materialized_view1;"
psql -U postgres -c "VACUUM ANALYZE materialized_view2;"

# Standby server script (run via SSH or separate cron)
ssh standby_server "psql -U postgres -c \"VACUUM ANALYZE materialized_view1;\""

To verify if VACUUM is needed on your standby, use these queries:

-- Check dead tuples on primary or standby
SELECT schemaname, relname, n_dead_tup, last_vacuum, last_autovacuum
FROM pg_stat_user_tables
WHERE relname LIKE 'materialized_view%';

-- Compare primary and standby
-- On primary:
SELECT pg_current_xlog_location();
-- On standby:
SELECT pg_last_xlog_receive_location(), pg_last_xlog_replay_location();

For PostgreSQL 9.0.1, consider these alternatives:

1. Promote standby temporarily for maintenance
2. Use pg_repack extension (available in later versions)
3. Schedule regular failover windows for maintenance

While this behavior changed in later PostgreSQL versions, for 9.0.1 you should:

• Document your VACUUM strategy clearly
• Monitor bloat on both primary and standby
• Consider upgrading to a newer version with better replication features

When working with PostgreSQL 9.0.1 streaming replication (physical replication), it's crucial to understand how maintenance operations like VACUUM behave in this architecture. The primary question is whether VACUUM commands executed on the primary server propagate to the standby replica.

In PostgreSQL's streaming replication:

-- This VACUUM won't replicate to standby
VACUUM (VERBOSE, ANALYZE) materialized_view_sales;

Key points about VACUUM behavior:

• VACUUM operations are not WAL-logged (with exceptions for special cases)
• Streaming replication works by shipping WAL records
• Standby servers replay these WAL records to maintain sync

For your specific case with materialized views excluded from autovacuum:

-- Primary server maintenance script example
#!/bin/bash
PGHOST=primary-server PGPORT=5432 \
psql -U postgres -d your_db -c \
"VACUUM (VERBOSE) materialized_view_customers; \
VACUUM (VERBOSE) materialized_view_products;"

Since VACUUM doesn't replicate, you need alternative approaches:

1. Promote standby temporarily for maintenance
2. Run VACUUM directly on standby (if configured as hot standby)
3. Consider pg_repack for minimal-lock maintenance

Enable VACUUM on standby by setting in postgresql.conf:

hot_standby = on
hot_standby_feedback = on

Then run vacuum during low-usage periods:

-- On standby server
SET statement_timeout = '10min';
VACUUM (VERBOSE, SKIP_LOCKED) materialized_view_orders;

Track table bloat on both servers:

SELECT
  schemaname,
  relname,
  n_dead_tup,
  last_autovacuum
FROM pg_stat_user_tables
WHERE schemaname NOT LIKE 'pg_%';

Remember that PostgreSQL 9.0 has several replication limitations compared to newer versions:

• No cascading replication
• Limited synchronous replication options
• No replication slots (risk of WAL gaps)