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.
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
drop view pghero.pg_stat_activity CASCADE;
Hope this helps.
RDS Postgres major upgrade from 12 to 13 failsasked 8 days ago
Unable to upgrade RDS postgres instance from 9.5.15 to 9.6.11asked 4 years ago
RDS SQL Server upgrade downtimeAccepted Answerasked 2 years ago
PostgreSQL RDS minor version upgrade stuck with "Incompatible-parameters"asked 3 years ago
Cannot upgrade RDS PostgreSQL 9.5 to 12.5asked 7 months ago
Upgrade RDS Postgres engine from 9.6.11 to 10.6 - Migrate to AuroraAccepted Answerasked 3 years ago
RDS upgrade from PG 12.7 to 13.3 fails due to missing librariesasked a year ago
How to upgrade Lightsail Postgres instance? (minor version upgrade)asked 3 days ago
Cannot upgrade nor restore PostgreSQL 9.4 RDS snapshotasked 2 months ago
RDS Storage upgrade - failure Error: Error modifying DB Instance: InvalidParameterCombination: Invalid storage size for engine name postgres and storage type gp2: 2400Accepted Answerasked 7 months ago