Thread: Question about pg_upgrade from 9.2 to X.X
Dear SMEs
I have finally decided to move forward after great hospitality in Version 9.2.24 :-)
First i attempted to upgrade from 9.2.24 to 10.7, but its failed with following error during Check Mode.
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.
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.
Method Used : pg_upgrade
Could you please share some light here to get rid of library issue .
Thanks, in advance ,
Raju
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:
Dear SMEsI have finally decided to move forward after great hospitality in Version 9.2.24 :-)First i attempted to upgrade from 9.2.24 to 10.7, but its failed with following error during Check Mode.could not load library "$libdir/hstore": ERROR: could not access file "$libdir/hstore": No such file or directorycould not load library "$libdir/adminpack": ERROR: could not access file "$libdir/adminpack": No such file or directorycould not load library "$libdir/uuid-ossp": ERROR: could not access file "$libdir/uuid-ossp": No such file or directoryObservation : the above Libraries are present in 9.2 whereas its mising in 10.7. So i decided to go with lower version.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 directoryObservation : In this case , pg_reorg is not present on both Source and Target . But strange its failing.Method Used : pg_upgradeCould you please share some light here to get rid of library issue .Thanks, in advance ,Raju
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:
HiPlease 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/resultexport 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/dataNote: old server should be in running state and new server should not be in running state.Thanks and RegardsMahendraOn Thu, 28 Feb 2019 at 23:44, Perumal Raj <perucinci@gmail.com> wrote:Dear SMEsI have finally decided to move forward after great hospitality in Version 9.2.24 :-)First i attempted to upgrade from 9.2.24 to 10.7, but its failed with following error during Check Mode.could not load library "$libdir/hstore": ERROR: could not access file "$libdir/hstore": No such file or directorycould not load library "$libdir/adminpack": ERROR: could not access file "$libdir/adminpack": No such file or directorycould not load library "$libdir/uuid-ossp": ERROR: could not access file "$libdir/uuid-ossp": No such file or directoryObservation : the above Libraries are present in 9.2 whereas its mising in 10.7. So i decided to go with lower version.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 directoryObservation : In this case , pg_reorg is not present on both Source and Target . But strange its failing.Method Used : pg_upgradeCould you please share some light here to get rid of library issue .Thanks, in advance ,Raju
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 abandonedfor 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:
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
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 EXTENSIONstatements 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 executedplpgsql | 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) pairspg_stat_statements | 1.1 | public | track execution statistics of all SQL statements executedplpgsql | 1.0 | pg_catalog | PL/pgSQL procedural languageuuid-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
> 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
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:
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 List of installed extensions Name | Version | Schema | Description --------------------+---------+------------+-----------------------------------------------------------pg_stat_statements | 1.1 | public | track execution statistics of all SQL statements executedplpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language (2 rows)archive_old=# \c production # \dx List of installed extensions Name | Version | Schema | Description --------------------+---------+------------+-----------------------------------------------------------hstore | 1.1 | public | data type for storing sets of (key, value) pairspg_stat_statements | 1.1 | public | track execution statistics of all SQL statements executedplpgsql | 1.0 | pg_catalog | PL/pgSQL procedural languageuuid-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
> 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