By using AWS re:Post, you agree to the Terms of Use

Unable to upgrade RDS postgres instance from 9.5.15 to 9.6.11


We are testing a RDS posgres upgrade from 9.5.15 to 9.6.11 and are running in the following error message when performing the upgrade:

Database instance is in a state that cannot be upgraded: PreUpgrade checks failed: The instance could not be upgraded because one or more databases have views or materialized views which depend on 'pg_stat_activity'. Please drop them and try again.

The upgrade is being performed with the following AWS cli command:

aws rds modify-db-instance \
--apply-immediately \
--allow-major-version-upgrade \
--db-parameter-group-name ... \
--engine-version ... \
--db-instance-identifier ...

The problems appears to be that there are some views that are owned by rdsadmin that we cannot drop:

scp=> drop view pg_stat_activity;
ERROR: must be owner of relation pg_stat_activity

Anyone run into this before or has some tips howto resolve it?

asked 4 years ago93 views
4 Answers

Resolved via AWS technical support.

answered 4 years ago

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

answered 3 years ago

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

answered 3 years ago

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.

Bajrang Panigrahi

answered 3 years ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions