Unable to upgrade RDS postgres instance from 9.5.15 to 9.6.11

You need to remove/drop all the materialized views which are dependent on the view pg_stat_activity.

In the newer version starting 9.6.X, there is a change in the relation (pg_stat_activity) and so any dependent materialized view which has been created needs to be re-created.

in my case it was from our monitoring tool, we made some of the customized views to monitor locks and other processes via pg_hero.

Query to list the views using pg_stat_activity relation:

 select * from pg_views where definition like '%pg_stat_activity%';

Query to get a list of dependent materialized views across schemas:

select schemaname, viewname, viewowner from pg_views where viewowner != 'rdsadmin' and definition ilike '%pg_stat_activity%' union all select schemaname, matviewname, matviewowner from pg_matviews
  where matviewowner != 'rdsadmin' and definition ilike '%pg_stat_activity%';

Then once you identify the view name, take the back up of the view definition as you might need to apply them again once the instance is upgraded to newer version (9.6.X)

Drop the views using the below query:

 drop view <<view_name>>  ;

you can also use cascade if needed to drop the dependent objects too
EX:

 drop view pghero.pg_stat_activity CASCADE;

Hope this helps.

Thanks,
Bajrang Panigrahi

Resolved via AWS technical support.

We are glad that AWS Premium Support was able to help you in upgrading the instance from 9.5.15 to 9.6.11.

Is there something to resolve this that I can do without opening a support case? I have the same issue.