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.

    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
    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.

Postat av: Steffen


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.


2010-09-23 @ 19:55:49
Postat av:

@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.

2010-09-23 @ 20:09:46

Kommentera inlägget här:

Kom ihåg mig?

E-postadress: (publiceras ej)



RSS 2.0