Thread: upgrading postgres 7.3.4 to 9.1.9

CONFIDENTIALITY NOTICE
The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential
information of Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by anyone
other than the intended addressee is prohibited.If you are not the intended addressee please notify the writer immediately
and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries distance themselves from and accept no liability
for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes.

CONFIDENTIALITY NOTICE
The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential
information of Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by anyone
other than the intended addressee is prohibited.If you are not the intended addressee please notify the writer immediately
and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries distance themselves from and accept no liability
for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes.

Khangelani Gama <kgama@argility.com> wrote:

> this one instance is still on postgres 7.
>
> I dumped postgres 7 database using pg_dump –Fc on postgres 7
> binaries and then attempted pg_restore –Fc (using postgres9.1.9
> binaries)

The recommended approach is to perform the dump with software from
the newer version.

> which threw out the following errors.

> ERROR:  language "plpgsql" already exists
> DETAIL:  Proceeding with relation creation anyway.

I'm not sure you'll get this if you use the 9.1 pg_dump.  In any
event, I think it could be safely ignored if you do so.

> ERROR:  could not access file "$libdir/plperl": No such file or directory
> ERROR:  function public.plperl_call_handler() does not exist
> ERROR:  could not access file "$libdir/plperl": No such file or directory
> ERROR:  language "plperlu" does not exist

I would use CREATE EXTENSION for plperlu on the 9.1 database before
attempting to restore.

> Because of the above errors I tried the following
>
> /usr/local/pgsql9/bin/createlang -p59999 plperl template1
>
> createlang: language installation failed: ERROR:  could not open extension control file
"/usr/local/pgsql9/share/extension/plperl.control":No such file or directory 

I would just use CREATE EXTENSION from psql.  You might need to
install an optional package or some such, since you don't seem to
have the code....

Note that 7.3.4 is missing years of bug fixes, including fixes for
serious data-eating bugs.  I would recommend upgrading from 7.3.4
to 7.3.21 before attempting the major release upgrade if possible.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

-----Original Message-----
From: Kevin Grittner [mailto:kgrittn@ymail.com]
Sent: 04 August 2014 03:30 PM
To: Khangelani Gama; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] upgrading postgres 7.3.4 to 9.1.9

Khangelani Gama <kgama@argility.com> wrote:

> this one instance is still on postgres 7.
>
> I dumped postgres 7 database using pg_dump –Fc on postgres 7 binaries
> and then attempted pg_restore –Fc (using postgres9.1.9
> binaries)

The recommended approach is to perform the dump with software from the newer
version.

> which threw out the following errors.

> ERROR:  language "plpgsql" already exists
> DETAIL:  Proceeding with relation creation anyway.

I'm not sure you'll get this if you use the 9.1 pg_dump.  In any event, I
think it could be safely ignored if you do so.

> ERROR:  could not access file "$libdir/plperl": No such file or
> directory
> ERROR:  function public.plperl_call_handler() does not exist
> ERROR:  could not access file "$libdir/plperl": No such file or
> directory
> ERROR:  language "plperlu" does not exist

I would use CREATE EXTENSION for plperlu on the 9.1 database before
attempting to restore.

> Because of the above errors I tried the following
>
> /usr/local/pgsql9/bin/createlang -p59999 plperl template1
>
> createlang: language installation failed: ERROR:  could not open
> extension control file
> "/usr/local/pgsql9/share/extension/plperl.control": No such file or
> directory




Thanks Kevin, in psql I keep getting the following errors: I saw what it is
explained in
http://stackoverflow.com/questions/12136033/install-pl-perl-in-postgresql
But I am not sure if this installation command "yum install perl-devel" will
work without affecting other special setups.


template1=#  CREATE EXTENSION plperl;
ERROR:  could not open extension control file
"/usr/local/pgsql9/share/extension/plperl.control": No such file or
directory
template1=#  CREATE EXTENSION plperlu;
ERROR:  could not open extension control file
"/usr/local/pgsql9/share/extension/plperlu.control": No such file or
directory



http://stackoverflow.com/questions/12136033/install-pl-perl-in-postgresql


CONFIDENTIALITY NOTICE
The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential
information of Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by
anyone
other than the intended addressee is prohibited.If you are not the intended addressee please notify the writer
immediately
and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries distance themselves from and accept no
liability
for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes.

I'm not sure you'll get this if you use the 9.1 pg_dump.  In any event, I
think it could be safely ignored if you do so.

> ERROR:  could not access file "$libdir/plperl": No such file or
> directory
> ERROR:  function public.plperl_call_handler() does not exist
> ERROR:  could not access file "$libdir/plperl": No such file or
> directory
> ERROR:  language "plperlu" does not exist

I would use CREATE EXTENSION for plperlu on the 9.1 database before
attempting to restore.

> Because of the above errors I tried the following
>
> /usr/local/pgsql9/bin/createlang -p59999 plperl template1
>
> createlang: language installation failed: ERROR:  could not open
> extension control file
> "/usr/local/pgsql9/share/extension/plperl.control": No such file or
> directory


Thanks Kevin, in psql I keep getting the following errors: I saw what it is
explained in
http://stackoverflow.com/questions/12136033/install-pl-perl-in-postgresql
But I am not sure if this installation command "yum install perl-devel" will
work without affecting other special setups.


template1=#  CREATE EXTENSION plperl;
ERROR:  could not open extension control file
"/usr/local/pgsql9/share/extension/plperl.control": No such file or
directory
template1=#  CREATE EXTENSION plperlu;
ERROR:  could not open extension control file
"/usr/local/pgsql9/share/extension/plperlu.control": No such file or
directory




Can I try the following , I just don’t want to break other databases already
running on postgres9

Stop all instances from running

./configure  --with-perl --prefix=$PATH
Gmake
Gmake install

And then create an instance, and then pg_restore.


CONFIDENTIALITY NOTICE
The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential
information of Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by
anyone
other than the intended addressee is prohibited.If you are not the intended addressee please notify the writer
immediately
and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries distance themselves from and accept no
liability
for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes.

Khangelani Gama <kgama@argility.com> wrote:

> in psql I keep getting the following errors: I saw what it is
> explained in
> http://stackoverflow.com/questions/12136033/install-pl-perl-in-postgresql
> But I am not sure if this installation command "yum install
> perl-devel" will
> work without affecting other special setups.

Hard to say.  I'm not even sure what OS this is or what you mean by
"other special setups".

> template1=#  CREATE EXTENSION plperl;
> ERROR:  could not open extension control file
> "/usr/local/pgsql9/share/extension/plperl.control": No such file or directory
> template1=#  CREATE EXTENSION plperlu;
> ERROR:  could not open extension control file
> "/usr/local/pgsql9/share/extension/plperlu.control": No such file or directory

/usr/local/pgsql9/ does not seem likely to be a location that a
packaged build would use, since 9.0, 9.1, etc. are different major
releases and packagers would normally want to make it easy to
install different major releases on the same system.  This looks
like it was probably a build and install from source code.  It
would help to know how the build was configured.  Please run
/usr/local/pgsql9/bin/pg_config to and post the output.

Most likely your best course will be to download the source code
for 9.1.14 to get bug fixes from the last year and a quarter, and
make a new build to a new export location (specific to at least
9.1; personally I like to use a separate directory for each minor
release to make the next minor upgrade take only a few seconds of
down time, but most people don't do it that way).  Make sure when
you configure for the new build you configure it to be compatible
with the old, but make sure that --with-perl is specified.If you
are not familiar with this sort of build and install process, you
should probably find someone to help you who is.

> Can I try the following , I just don’t want to break other
> databases already running on postgres9
>
> Stop all instances from running
>
> ./configure  --with-perl --prefix=$PATH
> Gmake
> Gmake install
>
> And then create an instance, and then pg_restore.

Personally, I would get that new build in a new directory, make
sure that it works with a PITR restore (or directory tree copy
while the database is stopped) of one of the other 9.1 clusters,
and make sure you can CREATE EXTENSION plperl before trying to
convert this 7.3 database.  Then you can move all the old clusters
over to the new executable by updating your service script to point
to the new executable running stop and then running start.  (I have
found that restart does not work for a minor upgrade -- a separate
stop -w and start -w is needed.)

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

> Can I try the following , I just don’t want to break other databases
> already running on postgres9
>
> Stop all instances from running
>
> ./configure  --with-perl --prefix=$PATH Gmake Gmake install
>
> And then create an instance, and then pg_restore.

Personally, I would get that new build in a new directory, make sure that it
works with a PITR restore (or directory tree copy while the database is
stopped) of one of the other 9.1 clusters, and make sure you can CREATE
EXTENSION plperl before trying to convert this 7.3 database.  Then you can
move all the old clusters over to the new executable by updating your
service script to point to the new executable running stop and then running
start.  (I have found that restart does not work for a minor upgrade -- a
separate stop -w and start -w is needed.)


Thanks Kevin,

From the /home/ellerines/pgsql9_upgrade/postgresql-9.1.9/config.log  file it
shows that when it was configured , only the prefix was specified in the
configure command.

/home/ellerines/pgsql9_upgrade/postgresql-9.1.9/config.log   shows the
following in the beginning:

It was created by PostgreSQL configure 9.1.9, which was
generated by GNU Autoconf 2.63.  Invocation command line was

  $ ./configure --prefix=/usr/local/pgsql9


with_perl='no'
perl_archlibexp=''
perl_embed_ldflags=''
perl_privlibexp=''
perl_useshrplib=''



I think it's safe to re-build it by including --with_perl and the same
prefix option  which was specified.


CONFIDENTIALITY NOTICE
The contents of and attachments to this e-mail are intended for the addressee only, and may contain the confidential
information of Argility (Proprietary) Limited and/or its subsidiaries. Any review, use or dissemination thereof by
anyone
other than the intended addressee is prohibited.If you are not the intended addressee please notify the writer
immediately
and destroy the e-mail. Argility (Proprietary) Limited and its subsidiaries distance themselves from and accept no
liability
for unauthorised use of their e-mail facilities or e-mails sent other than strictly for business purposes.

Khangelani Gama <kgama@argility.com> wrote:

> From the /home/ellerines/pgsql9_upgrade/postgresql-9.1.9/config.log  file it
> shows that when it was configured , only the prefix was specified in the
> configure command.
>
> /home/ellerines/pgsql9_upgrade/postgresql-9.1.9/config.log  shows the
> following in the beginning:
>
> It was created by PostgreSQL configure 9.1.9, which was
> generated by GNU Autoconf 2.63.  Invocation command line was
>
>   $ ./configure --prefix=/usr/local/pgsql9
>
>
> with_perl='no'
> perl_archlibexp=''
> perl_embed_ldflags=''
> perl_privlibexp=''
> perl_useshrplib=''
>
>
>
> I think it's safe to re-build it by including --with_perl and the same
> prefix option  which was specified.

Sounds like a plan.  Of course, be sure to test carefully before
using it in production.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Is anyone on this list running postgres-xc? If so, would it be possible to
get a couple of questions answered? Or at least see an example of someone
else's configs?