Thread: Re: Question about pg_upgrade from 9.2 to X.X

Moving to -general list (-hackers is for development topics like proposed
patches and patch reviews and beta testing and crash reports).

On Thu, Feb 28, 2019 at 10:13:58AM -0800, Perumal Raj wrote:
> could not load library "$libdir/pg_reorg":
> ERROR:  could not access file "$libdir/pg_reorg": No such file or directory

As Sergei said, you can run pg_dump -s and look for references to reorg, and
drop them.

Or, you could try this:
CREATE EXTENSION pg_reorg FROM unpackaged;

Or maybe this:
CREATE EXTENSION pg_repack FROM unpackaged;

If that works, you can DROP EXTENSION pg_repack;

Otherwise, I think you can maybe do something like:
DROP SCHEMA pg_repack CASCADE; -- or,
DROP SCHEMA pg_reorg CASCADE;

Please send output of: \dn

Thanks.Will decently try that option and keep you posted.

Thanks again for redirecting to right group.

Perumal Raju

On Sun, Mar 3, 2019, 6:51 AM Justin Pryzby <pryzby@telsasoft.com> wrote:

Moving to -general list (-hackers is for development topics like proposed
patches and patch reviews and beta testing and crash reports).

On Thu, Feb 28, 2019 at 10:13:58AM -0800, Perumal Raj wrote:
> could not load library "$libdir/pg_reorg":
> ERROR:  could not access file "$libdir/pg_reorg": No such file or directory

As Sergei said, you can run pg_dump -s and look for references to reorg, and
drop them.

Or, you could try this:
CREATE EXTENSION pg_reorg FROM unpackaged;

Or maybe this:
CREATE EXTENSION pg_repack FROM unpackaged;

If that works, you can DROP EXTENSION pg_repack;

Otherwise, I think you can maybe do something like:
DROP SCHEMA pg_repack CASCADE; -- or,
DROP SCHEMA pg_reorg CASCADE;

Please send output of: \dn

On Mon, Mar 04, 2019 at 01:37:30PM -0800, Perumal Raj wrote:
> I could see bunch of functions under reorg schema.

Those functions are the ones preventing you from upgrading.
You should drop schema pg_reorg cascade.
You can run it in a transaction first to see what it will drop.
But after the upgrade, you can CREATE EXTENSION pg_repack, which is a fork of
pg_reorg, which is itself no longer maintained.

Justin

Hi

seems this is unpackaged extension, usually installed prior 9.1 release. Maybe reorg even does not support "create extension" syntax. That was long ago and project homepage is unavailable now. pg_repack documentation mention "support for PostgreSQL 9.2 and EXTENSION packaging" as improvements.

> Are these functions seeded ( default) one ?

No its not default.

regards, Sergei

On Tue, Mar 05, 2019 at 08:09:12AM -0800, Perumal Raj wrote:
> Thanks Sergei/Justin for the continues update.
>
> So reorg Schema might be created as part of some scripts prior to 9.2
> Version ?

I'm guessing they were probably created in 9.2.

> These are the functions in DB not the Extension. However these functions
> will not run as the associated libraries are not exists in System now (9.2)
> and I hope no impact to system.

I guess someone installed pgreorg, ran its scripts to install its functions
into the DB, and then removed pgreorg without removing its scripts.

> One Question need your address,
>
> Prior to 9.2 to 9.6 upgrade , I have tried 9.2 10.7 upgrade and failed
> similar error(you can refer beginning o the post ).
>
> > could not load library "$libdir/hstore": ERROR:  could not access file "$libdir/hstore": No such file or directory
> > could not load library "$libdir/adminpack": ERROR:  could not access file "$libdir/adminpack": No such file or directory
> > could not load library "$libdir/uuid-ossp": ERROR:  could not access file "$libdir/uuid-ossp": No such file or directory
>
> These Extension seems to be standard. What is the use of these function and
> do we have any alternative in Higher version or Enhanced object if i drop
> it in 9.2 and continue upgrade to 10.7 Version.

See Sergei's response:
https://www.postgresql.org/message-id/7164691551378448%40myt3-1179f584969c.qloud-c.yandex.net

You probably want to install this package for the new version (9.6 or 10 or
11).

[pryzbyj@TS-DB ~]$ rpm -ql postgresql11-contrib |grep -E '(uuid-ossp|adminpack|hstore)\.control'
/usr/pgsql-11/share/extension/adminpack.control
/usr/pgsql-11/share/extension/hstore.control
/usr/pgsql-11/share/extension/uuid-ossp.control

Justin

On Wed, Mar 06, 2019 at 09:44:16PM -0800, Perumal Raj wrote:
> Any pointers for pg_repack schema creation ?

With recent postgres, you should use just: "CREATE EXTENSION pg_repack", which
does all that for you.

> Will there be any impact in the future , Since i used --link option ?

You probably have an old DB directory laying around which is (at least
partially) hardlinks.  You should remove it .. but be careful to remove the
correct dir.  My scripts always rename the old dir before running pg_upgrade,
so it's less scary to rm -fr it later.

Justin