search

powa-team / powa

PostgreSQL Workload Analyzer
http://powa.readthedocs.io/
PostgreSQL License
768 stars 57 forks source link

Can't upgrade powa database #75

Closed trourance closed 7 years ago

trourance commented 8 years ago

Hi,

I'm migrating my PostgreSQL version from 9.4 to 9.5. I have tried to use the pg_upgrade script, but I get an error because of a column of the view pg_qualstats_pretty in powa database:

Checking for reg* system OID user data types fatal

Your installation contains one of the reg* data types in user tables. These data types reference system OIDs that are not preserved by pg_upgrade, so this cluster cannot currently be upgraded. You can remove the problem tables and restart the upgrade. A list of the problem columns is in the file: tables_using_reg.txt Failure, exiting

And in the tables_using_reg.txt file:

Database: powa public.pg_qualstats_pretty.operator

rjuju commented 8 years ago

Hello,

I wasn't aware of this restriction, but it does make sense. FTR, this is problematic for pg_qualstats_pretty, pg_qualstats_indexes and pg_qualstats_indexes_ddl which are all views.

We'll fix this problem. In the meantime, you can safely drop the pg_qualstats extension before upgrading, and recreate the extension after.

rjuju commented 7 years ago

Sorry for the very late answer. I just pushed a fix for that (https://github.com/dalibo/pg_qualstats/commit/3c85a657bc06d6d539c729ad6432fbec5d4c7e41). A new version should be released soon.

FTR, after looking at https://github.com/postgres/postgres/blob/master/src/bin/pg_upgrade/check.c#L914 only pg_qualstats_pretty view was problematic.

Thanks again for reporting the issue !

trourance commented 7 years ago

Great, thanks for the fix. It's going to be useful for further migrations.