Thread: PostgreSQL v9.0.3 Database Import Error: language "plpgsql" already exists

Hi,

We're planning to upgrade our PostgreSQL database from v8.2.3 to v9.0.3.
Before upgrading our Production server, we're trying out this complete
upgrade process in a separate database server that is just exactly the
replica of the production server.

I already took a dump of our database using pg_dump command in v8.2.3:
"/usr/local/pgsql/bin/pg_dump -D -f dbdump.sql -S mydbuser mydb"

Now, after upgrading PostgreSQL to v9.0.3, I'm trying to import the database
dump using: "psql -f dbdump.sql -o dbdump.log -d mydb -U mydbuser".  I'm
getting the following errors while importing:

psql:dbdump.sql:38: ERROR:  language "plpgsql" already exists
psql:dbdump.sql:102646: ERROR:  attempt to redefine parameter
"plpgsql.variable_conflict"
psql:dbdump.sql:102649: ERROR:  function public.plpgsql_call_handler() does
not exist

I also read from 9.0 release notes that PL/pgSQL is now installed by
default.
http://www.postgresql.org/docs/current/interactive/release-9-0.html

My question is, how do I prevent/fix this error during import.  Are all
these 3 errors one and the same or are they different errors that each has
to be fixed separately?

NOTE: We've multiple onsite customer servers that has to be upgraded to
v9.0.3, hence I need to address this issue in general.

Regards,
Gnanam

Στις Wednesday 30 March 2011 16:44:33 ο/η Gnanakumar έγραψε:
> Hi,
>
> We're planning to upgrade our PostgreSQL database from v8.2.3 to v9.0.3.
> Before upgrading our Production server, we're trying out this complete
> upgrade process in a separate database server that is just exactly the
> replica of the production server.
>
> I already took a dump of our database using pg_dump command in v8.2.3:
> "/usr/local/pgsql/bin/pg_dump -D -f dbdump.sql -S mydbuser mydb"

Start with a schema dump (DDL) pg_dump -s
edit the schema to excluded plpgsql, plpgsql_call_handler,etc... related statements
and then create a data dump using pg_dump -a
then load first the schema dump (DDL) and then the data dump.
Anyways, those little error msgs should not scare you, no major harm done,
you should be able to tell *real* problems from minor decorative issues like this one.

>
> Now, after upgrading PostgreSQL to v9.0.3, I'm trying to import the database
> dump using: "psql -f dbdump.sql -o dbdump.log -d mydb -U mydbuser".  I'm
> getting the following errors while importing:
>
> psql:dbdump.sql:38: ERROR:  language "plpgsql" already exists
> psql:dbdump.sql:102646: ERROR:  attempt to redefine parameter
> "plpgsql.variable_conflict"
> psql:dbdump.sql:102649: ERROR:  function public.plpgsql_call_handler() does
> not exist
>
> I also read from 9.0 release notes that PL/pgSQL is now installed by
> default.
> http://www.postgresql.org/docs/current/interactive/release-9-0.html
>
> My question is, how do I prevent/fix this error during import.  Are all
> these 3 errors one and the same or are they different errors that each has
> to be fixed separately?
>
> NOTE: We've multiple onsite customer servers that has to be upgraded to
> v9.0.3, hence I need to address this issue in general.
>
> Regards,
> Gnanam
>
>



--
Achilleas Mantzios