Question about pg_upgrade from 9.2 to X.X

Hi
Please try with below commands.

Let we want to upgrade v6 to v11.
Note: I installed my binary inside result folder.

export OLDCLUSTER=./6_EDBAS/EDBAS/result
export NEWCLUSTER=./11_EDBAS/EDBAS/result
./11_EDBAS/EDBAS/result/bin/pg_upgrade --old-bindir=$OLDCLUSTER/bin
--new-bindir=$NEWCLUSTER/bin --old-datadir=$OLDCLUSTER/bin/data
--new-datadir=$NEWCLUSTER/bin/data

Note: old server should be in running state and new server should not be in
running state.

Thanks and Regards
Mahendra

On Thu, 28 Feb 2019 at 23:44, Perumal Raj <perucinci@gmail.com> wrote:

Thanks Mahendra for quick response.

I have followed same way, only difference i didn't bringup Source ( 9.2),
But not sure how that will resolve libraries issue.
All i tried with --check mode only

Thanks,

On Thu, Feb 28, 2019 at 10:23 AM Mahendra Singh <mahi6run@gmail.com> wrote:

Hello

pgsql-hackers seems wrong list for such question.

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

Observation : the above Libraries are present in 9.2 whereas its mising in 10.7. So i decided to go with lower version.

This is contrib modules. They can be shipped in separate package, postgresql10-contrib.x86_64 for example (in centos repo)

Second  i tried to attempt to upgrade from 9.2.24 to 9.6.12,9.4,9.3 but its failed with following error during Check Mode.

could not load library "$libdir/pg_reorg":
ERROR:  could not access file "$libdir/pg_reorg": No such file or directory

Observation : In this case , pg_reorg is not present on both Source and Target . But strange its failing.

This is 3rd-party extension. Best way would be drop this extension on old cluster and perform upgrade. pg_reorg is abandoned for years, pg_repack is live fork if you need such tool.

regards, Sergei

Thank you very much Sergei,

Yes, i want to get rid of old extension, Could you please share the query
to find extension which is using pg_reorg.

Regards,

On Thu, Feb 28, 2019 at 10:27 AM Sergei Kornilov <sk@zsrv.org> wrote:

Hi

Yes, i want to get rid of old extension, Could you please share the query to find extension which is using pg_reorg.

pg_reorg is name for both tool and extension.
Check every database in cluster with, for example, psql command "\dx" or read pg_dumpall -s output for some CREATE EXTENSION statements to find all installed extensions.

regards, Sergei

here is the data,

postgres=# \c template1
You are now connected to database "template1" as user "postgres".
template1=# \dx
List of installed extensions
Name | Version | Schema | Description
---------+---------+------------+------------------------------
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(1 row)

template1=# \c postgres
You are now connected to database "postgres" as user "postgres".
postgres=# \dx
List of installed extensions
Name | Version | Schema | Description
---------+---------+------------+------------------------------
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(1 row)

postgres=# \c nagdb
You are now connected to database "nagdb" as user "postgres".
nagdb=# \dx
List of installed extensions
Name | Version | Schema | Description
---------+---------+------------+------------------------------
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(1 row)

nagdb=# \c archive_old
You are now connected to database "books_old" as user "postgres".
books_old=# \dx
List of installed extensions
Name | Version | Schema |
Description
--------------------+---------+------------+-----------------------------------------------------------
pg_stat_statements | 1.1 | public | track execution
statistics of all SQL statements executed
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)

archive_old=# \c production
You are now connected to database "blurb_production" as user "postgres".
production=# \dx
List of installed extensions
Name | Version | Schema |
Description
--------------------+---------+------------+-----------------------------------------------------------
hstore | 1.1 | public | data type for storing
sets of (key, value) pairs
pg_stat_statements | 1.1 | public | track execution
statistics of all SQL statements executed
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
uuid-ossp | 1.0 | public | generate universally
unique identifiers (UUIDs)
(4 rows)

Thanks,

On Thu, Feb 28, 2019 at 11:04 AM Sergei Kornilov <sk@zsrv.org> wrote:

Hi Sergei and Team

Could you share your observation further.

Perumal Raju

On Thu, Feb 28, 2019, 11:21 AM Perumal Raj <perucinci@gmail.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

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:

Hi Justin

I could see bunch of functions under reorg schema.

AS '$libdir/pg_reorg', 'reorg_disable_autovacuum';
AS '$libdir/pg_reorg', 'reorg_get_index_keys';
AS '$libdir/pg_reorg', 'reorg_apply';
AS '$libdir/pg_reorg', 'reorg_drop';
AS '$libdir/pg_reorg', 'reorg_indexdef';
AS '$libdir/pg_reorg', 'reorg_swap';
AS '$libdir/pg_reorg', 'reorg_trigger';
AS '$libdir/pg_reorg', 'reorg_version';

I am not sure about the impact of these functions if i drop .

Are these functions seeded ( default) one ?

Regards,
Raj

On Sun, Mar 3, 2019 at 7:38 PM Perumal Raj <perucinci@gmail.com> wrote:

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 Justin

Does it mean that these functions are default and came with 9.2 ?
I am wondering how these functions are created in the DB as the
library($libdir/pg_reorg) is not exists in system

Note:
My schema name is reorg not pg_reorg

On Mon, Mar 4, 2019 at 1:45 PM Justin Pryzby <pryzby@telsasoft.com> wrote:

On Mon, Mar 04, 2019 at 02:21:11PM -0800, Perumal Raj wrote:

Does it mean that these functions are default and came with 9.2 ?
I am wondering how these functions are created in the DB as the
library($libdir/pg_reorg) is not exists in system

I don't think it's default.
But was probably installed by running some SQL script.

It tentatively sounds safe to me to drop, but you should take a backup and
inspect and double check your pg_dump output and output of "begin; drop schema
pgreorg cascade".

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

Thanks Sergei/Justin for the continues update.

So reorg Schema might be created as part of some scripts prior to 9.2
Version ?
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.

AS '$libdir/pg_reorg', 'reorg_disable_autovacuum';
AS '$libdir/pg_reorg', 'reorg_get_index_keys';
AS '$libdir/pg_reorg', 'reorg_apply';
AS '$libdir/pg_reorg', 'reorg_drop';
AS '$libdir/pg_reorg', 'reorg_indexdef';
AS '$libdir/pg_reorg', 'reorg_swap';
AS '$libdir/pg_reorg', 'reorg_trigger';
AS '$libdir/pg_reorg', 'reorg_version';

Will continue 9.6 upgrade after dropping reorg schema.

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.

Thanks and Regards,

On Mon, Mar 4, 2019 at 11:42 PM Sergei Kornilov <sk@zsrv.org> wrote:

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:
/messages/by-id/7164691551378448@myt3-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

Awesome, thanks Sergei and Justin,

Finally, I am able to upgrade the DB from 9.2 to 9.6 successfully after
dropping Schema (reorg) without library issue.
Also , I have installed -Contrib. package for Version:10 and upgraded to
version 10.7 too.

On both the cases , I have used --link option and it took just fraction of
seconds ( I feel 'Zero' Downtime effect )

Any pointers for pg_repack schema creation ?
Will there be any impact in the future , Since i used --link option ?

Regards,
Raju

On Tue, Mar 5, 2019 at 8:21 AM Justin Pryzby <pryzby@telsasoft.com> wrote:

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

Thanks again.

Perumal Raju

On Thu, Mar 7, 2019, 2:32 AM Justin Pryzby <pryzby@telsasoft.com> wrote: