pg_upgrade and shared libraries
I ran pg_upgrade on a dev cluster of mine and it halted on some shared libraries I had forgot to install for the new cluster.
Most of them were plain contrib that I'll continue to use so no problem there but there were some testing code I had been playing around with. I didn't know in which of my test databases I had used it so I had to find out somehow.
This is what I came up with, a query to list what shared libraries are used in a database. With that information I could drop the functions depending on shared libraries I no longer used.
select p.proname as "name", pg_catalog.pg_get_function_arguments(p.oid) as "args", n.nspname as "namespace", p.probin::text as "lib", u.usename as "owner" from pg_proc p join pg_user u on u.usesysid=p.proowner join pg_language l on l.oid=p.prolang join pg_namespace n on n.oid=p.pronamespace where nspname not in ('pg_catalog', 'information_schema') and lanname='c'
To avoid running it manually on all databases I wrote some python code to do that for me. If it's of use to anyone I'd be more than happy to share that too.
wouldn't that be a useful feature
for pg_upgrade ? To test (beforehand ?)
whether the source and target environments
are the same ? It could also check the /etc/postgres/.../environment etc.
@steffen: pg_upgrade does check for this. That's why I needed to get rid of the unused functions since it couldn't upgrade while the old and new environments differed.