pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedupgrading postgres 7.3.4 to 9.1.9
8+ messages / 2 participants
[nested] [flat]
* upgrading postgres 7.3.4 to 9.1.9
@ 2014-08-04 10:34 Khangelani Gama <kgama@argility.com>
0 siblings, 1 reply; 8+ messages in thread
From: Khangelani Gama @ 2014-08-04 10:34 UTC (permalink / raw)
To: pgsql-admin
Hi
Please help me here, there was an upgrade done by someone who left the
company but there is one database which was not upgraded. The server has
several instances running on it but 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) which threw
out the following errors. Note that postgres 7 is still running on it’s own
instance. I am doing this postgres 9 restore on a different port and
different instance to get it right first before implementing the change.
ERROR: language "plpgsql" already exists
DETAIL: Proceeding with relation creation anyway.
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
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
Then I looked for where extention is located:
~> locate plperl.control
/home/ellerines/pgsql9_upgrade/postgresql-9.1.9/src/pl/plperl/plperl.control
> /home/ellerines/pgsql9_upgrade/postgresql-9.1.9/src/bin
total 44K
-rw-r--r-- 1 1107 1107 658 Apr 1 2013 Makefile
drwxrwxrwx 2 1107 1107 4.0K Apr 1 2013 pgevent
drwxrwxrwx 3 1107 1107 4.0K Aug 18 2013 initdb
drwxrwxrwx 3 1107 1107 4.0K Aug 18 2013 pg_ctl
drwxrwxrwx 3 1107 1107 4.0K Aug 18 2013 pg_dump
drwxrwxrwx 3 1107 1107 4.0K Aug 18 2013 psql
drwxrwxrwx 3 1107 1107 4.0K Aug 18 2013 scripts
drwxrwxrwx 3 1107 1107 4.0K Aug 18 2013 pg_controldata
drwxrwxrwx 3 1107 1107 4.0K Aug 18 2013 pg_config
drwxrwxrwx 3 1107 1107 4.0K Aug 18 2013 pg_resetxlog
drwxrwxrwx 3 1107 1107 4.0K Aug 18 2013 pg_basebackup
How can then do a pg_restore to prevent errors above?
Kind regards,
Khangelani
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: upgrading postgres 7.3.4 to 9.1.9
@ 2014-08-04 12:14 Khangelani Gama <kgama@argility.com>
0 siblings, 0 replies; 8+ messages in thread
From: Khangelani Gama @ 2014-08-04 12:14 UTC (permalink / raw)
To: pgsql-admin
P.S
plperl.control file is in
=>/home/ellerines/pgsql9_upgrade/postgresql-9.1.9/src/pl/plperl
=> l -ltrh
total 364K
-rw-r--r-- 1 1107 1107 4.8K Apr 1 2013 Util.xs
-rw-r--r-- 1 1107 1107 2.2K Apr 1 2013 text2macro.pl
-rw-r--r-- 1 1107 1107 3.5K Apr 1 2013 SPI.xs
-rw-r--r-- 1 1107 1107 462 Apr 1 2013 README
-rw-r--r-- 1 1107 1107 171K Apr 1 2013 ppport.h
-rw-r--r-- 1 1107 1107 376 Apr 1 2013 plperlu--unpackaged--1.0.sql
-rw-r--r-- 1 1107 1107 367 Apr 1 2013 plperl--unpackaged--1.0.sql
-rw-r--r-- 1 1107 1107 187 Apr 1 2013 plperlu.control
-rw-r--r-- 1 1107 1107 337 Apr 1 2013 plperlu--1.0.sql
-rw-r--r-- 1 1107 1107 2.0K Apr 1 2013 plperl_opmask.pl
-rw-r--r-- 1 1107 1107 2.9K Apr 1 2013 plperl_helpers.h
-rw-r--r-- 1 1107 1107 2.7K Apr 1 2013 plperl.h
-rw-r--r-- 1 1107 1107 176 Apr 1 2013 plperl.control
-rw-r--r-- 1 1107 1107 100K Apr 1 2013 plperl.c
-rw-r--r-- 1 1107 1107 323 Apr 1 2013 plperl--1.0.sql
-rw-r--r-- 1 1107 1107 922 Apr 1 2013 plc_trusted.pl
-rw-r--r-- 1 1107 1107 1.9K Apr 1 2013 plc_perlboot.pl
-rw-r--r-- 1 1107 1107 253 Apr 1 2013 nls.mk
-rw-r--r-- 1 1107 1107 3.8K Apr 1 2013 GNUmakefile
drwxrwxrwx 2 1107 1107 4.0K Apr 1 2013 sql
drwxrwxrwx 2 1107 1107 4.0K Apr 1 2013 po
drwxrwxrwx 2 1107 1107 4.0K Apr 1 2013 expected
*From:* Khangelani Gama [mailto:kgama@argility.com]
*Sent:* 04 August 2014 12:35 PM
*To:* 'pgsql-admin@postgresql.org'
*Subject:* upgrading postgres 7.3.4 to 9.1.9
Hi
Please help me here, there was an upgrade done by someone who left the
company but there is one database which was not upgraded. The server has
several instances running on it but 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) which threw
out the following errors. Note that postgres 7 is still running on it’s own
instance. I am doing this postgres 9 restore on a different port and
different instance to get it right first before implementing the change.
ERROR: language "plpgsql" already exists
DETAIL: Proceeding with relation creation anyway.
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
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
Then I looked for where extention is located:
~> locate plperl.control
/home/ellerines/pgsql9_upgrade/postgresql-9.1.9/src/pl/plperl/plperl.control
> /home/ellerines/pgsql9_upgrade/postgresql-9.1.9/src/bin
total 44K
-rw-r--r-- 1 1107 1107 658 Apr 1 2013 Makefile
drwxrwxrwx 2 1107 1107 4.0K Apr 1 2013 pgevent
drwxrwxrwx 3 1107 1107 4.0K Aug 18 2013 initdb
drwxrwxrwx 3 1107 1107 4.0K Aug 18 2013 pg_ctl
drwxrwxrwx 3 1107 1107 4.0K Aug 18 2013 pg_dump
drwxrwxrwx 3 1107 1107 4.0K Aug 18 2013 psql
drwxrwxrwx 3 1107 1107 4.0K Aug 18 2013 scripts
drwxrwxrwx 3 1107 1107 4.0K Aug 18 2013 pg_controldata
drwxrwxrwx 3 1107 1107 4.0K Aug 18 2013 pg_config
drwxrwxrwx 3 1107 1107 4.0K Aug 18 2013 pg_resetxlog
drwxrwxrwx 3 1107 1107 4.0K Aug 18 2013 pg_basebackup
How can then do a pg_restore to prevent errors above?
Kind regards,
Khangelani
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: upgrading postgres 7.3.4 to 9.1.9
@ 2014-08-04 13:30 Kevin Grittner <kgrittn@ymail.com>
parent: Khangelani Gama <kgama@argility.com>
0 siblings, 2 replies; 8+ messages in thread
From: Kevin Grittner @ 2014-08-04 13:30 UTC (permalink / raw)
To: Khangelani Gama <kgama@argility.com>; pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: upgrading postgres 7.3.4 to 9.1.9
@ 2014-08-04 16:48 Khangelani Gama <kgama@argility.com>
parent: Kevin Grittner <kgrittn@ymail.com>
1 sibling, 0 replies; 8+ messages in thread
From: Khangelani Gama @ 2014-08-04 16:48 UTC (permalink / raw)
To: Kevin Grittner <kgrittn@ymail.com>; pgsql-admin
-----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.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: upgrading postgres 7.3.4 to 9.1.9
@ 2014-08-04 17:32 Khangelani Gama <kgama@argility.com>
parent: Kevin Grittner <kgrittn@ymail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Khangelani Gama @ 2014-08-04 17:32 UTC (permalink / raw)
To: Kevin Grittner <kgrittn@ymail.com>; pgsql-admin
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.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: upgrading postgres 7.3.4 to 9.1.9
@ 2014-08-04 18:19 Kevin Grittner <kgrittn@ymail.com>
parent: Khangelani Gama <kgama@argility.com>
0 siblings, 1 reply; 8+ messages in thread
From: Kevin Grittner @ 2014-08-04 18:19 UTC (permalink / raw)
To: Khangelani Gama <kgama@argility.com>; pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: upgrading postgres 7.3.4 to 9.1.9
@ 2014-08-05 04:23 Khangelani Gama <kgama@argility.com>
parent: Kevin Grittner <kgrittn@ymail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Khangelani Gama @ 2014-08-05 04:23 UTC (permalink / raw)
To: Kevin Grittner <kgrittn@ymail.com>; pgsql-admin
> 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,
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: upgrading postgres 7.3.4 to 9.1.9
@ 2014-08-05 15:55 Kevin Grittner <kgrittn@ymail.com>
parent: Khangelani Gama <kgama@argility.com>
0 siblings, 0 replies; 8+ messages in thread
From: Kevin Grittner @ 2014-08-05 15:55 UTC (permalink / raw)
To: Khangelani Gama <kgama@argility.com>; pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2014-08-05 15:55 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-08-04 10:34 upgrading postgres 7.3.4 to 9.1.9 Khangelani Gama <kgama@argility.com>
2014-08-04 13:30 ` Kevin Grittner <kgrittn@ymail.com>
2014-08-04 16:48 ` Khangelani Gama <kgama@argility.com>
2014-08-04 17:32 ` Khangelani Gama <kgama@argility.com>
2014-08-04 18:19 ` Kevin Grittner <kgrittn@ymail.com>
2014-08-05 04:23 ` Khangelani Gama <kgama@argility.com>
2014-08-05 15:55 ` Kevin Grittner <kgrittn@ymail.com>
2014-08-04 12:14 Re: upgrading postgres 7.3.4 to 9.1.9 Khangelani Gama <kgama@argility.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox