pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedProblems with pg_restore (plpgsql already exists)
8+ messages / 4 participants
[nested] [flat]
* Problems with pg_restore (plpgsql already exists)
@ 2012-02-24 22:30 Brian Weaver <cmdrclueless@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Brian Weaver @ 2012-02-24 22:30 UTC (permalink / raw)
To: pgsql-admin
I'm running into a problem where I can't restore a previous database using pg_restore because the plpgsql language already exists. All the searching I've done implies that the fix is to created the database from template0, but that doesn't seem to be working in my case for some reason. I'm running 9.1.2 and here's the basic procedure that I'm using to try to restore if anyone has any ideas why it's not working.
1) Create a new data directory
2) Run initdb to initialize the database directories
3) Run 'pg_restore --create postgresql.pgr --dbname=template1'
I use the '--dbname=template1' option to allow the process to connect to the server and create the database for me. It's my understanding from reading the man page that's the correct way to connect. Since it's a new database there's no other database to connect to since template0 refuses connections (or at least it has when I've tried recently). Also, this is a new problem I didn't run into when I was using PostgreSQL 8.4 and it's only happened since I've upgraded to 9.1.2.
If I just run 'pg_restore --create postgresql.pgr' I see the following relevant lines:
CREATE DATABASE foo WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';
ALTER DATABASE foo OWNER TO foo;
\connect foo
SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;
--
-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: postgres
--
CREATE PROCEDURAL LANGUAGE plpgsql;
ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO postgres;
SET search_path = public, pg_catalog;
------ END SNIPPET
As you can see the database is cloned from template0 so I don't know why plpgsql would already exists.
-- Brian
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Problems with pg_restore (plpgsql already exists)
@ 2012-02-24 23:44 Dinesh Bhandary <dbhandary@iii.com>
parent: Brian Weaver <cmdrclueless@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Dinesh Bhandary @ 2012-02-24 23:44 UTC (permalink / raw)
To: pgsql-admin
one way to get over this issue is to do pg_dump of postgres 8.4 db
using postgres 9.1.2 binaries.
Here is a note from postgres 9.1.2 documentation
"In a default PostgreSQL installation, the handler for the PL/pgSQL
language is built and installed into the "library" directory;
furthermore, the PL/pgSQL language itself is installed in all databases.
If Tcl support is configured in, the handlers for PL/Tcl and PL/TclU are
built and installed in the library directory, but the language itself is
not installed in any database by default. Likewise, the PL/Perl and
PL/PerlU handlers are built and installed if Perl support is configured,
and the PL/PythonU handler is installed if Python support is configured,
but these languages are not installed by default."
hope this helps.
Thanks.
Dinesh
On 2/24/2012 2:30 PM, Brian Weaver wrote:
> CREATE DATABASE foo WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Problems with pg_restore (plpgsql already exists)
@ 2012-02-25 03:12 Brian Weaver <cmdrclueless@gmail.com>
parent: Dinesh Bhandary <dbhandary@iii.com>
0 siblings, 1 reply; 8+ messages in thread
From: Brian Weaver @ 2012-02-25 03:12 UTC (permalink / raw)
To: ; +Cc: pgsql-admin
So when did the installation of PL/PgSQL into all databases become standard
operating procedure? It wasn't standard (or at least it didn't choke) on
the installation of versions 8.3 and 8.4 that I have used on CentOS 5.
Seems like a fairly substantial change. Did I miss it in the release notes?
-- Brian
On Friday, February 24, 2012, Dinesh Bhandary wrote:
> one way to get over this issue is to do pg_dump of postgres 8.4 db using
> postgres 9.1.2 binaries.
> Here is a note from postgres 9.1.2 documentation
>
> "In a default PostgreSQL installation, the handler for the PL/pgSQL
> language is built and installed into the "library" directory; furthermore,
> the PL/pgSQL language itself is installed in all databases. If Tcl support
> is configured in, the handlers for PL/Tcl and PL/TclU are built and
> installed in the library directory, but the language itself is not
> installed in any database by default. Likewise, the PL/Perl and PL/PerlU
> handlers are built and installed if Perl support is configured, and the
> PL/PythonU handler is installed if Python support is configured, but these
> languages are not installed by default."
>
> hope this helps.
>
> Thanks.
> Dinesh
>
> On 2/24/2012 2:30 PM, Brian Weaver wrote:
>
>> CREATE DATABASE foo WITH TEMPLATE = template0 ENCODING = 'UTF8'
>> LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';
>>
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-admin<http://www.postgresql.org/mailpref/pgsql-admin;
>
--
/* insert witty comment here */
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Problems with pg_restore (plpgsql already exists)
@ 2012-02-25 15:31 Guillaume Lelarge <guillaume@lelarge.info>
parent: Brian Weaver <cmdrclueless@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Guillaume Lelarge @ 2012-02-25 15:31 UTC (permalink / raw)
To: Brian Weaver <cmdrclueless@gmail.com>; +Cc: pgsql-admin
On Fri, 2012-02-24 at 22:12 -0500, Brian Weaver wrote:
> So when did the installation of PL/PgSQL into all databases become standard
> operating procedure? It wasn't standard (or at least it didn't choke) on
> the installation of versions 8.3 and 8.4 that I have used on CentOS 5.
>
> Seems like a fairly substantial change. Did I miss it in the release notes?
>
Since 9.0, it's the third item in the overview list of the release notes
http://www.postgresql.org/docs/9.0/static/release-9-0.html.
--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Problems with pg_restore (plpgsql already exists)
@ 2012-02-25 16:18 Brian Weaver <cmdrclueless@gmail.com>
parent: Guillaume Lelarge <guillaume@lelarge.info>
0 siblings, 1 reply; 8+ messages in thread
From: Brian Weaver @ 2012-02-25 16:18 UTC (permalink / raw)
To: Guillaume Lelarge <guillaume@lelarge.info>; +Cc: pgsql-admin
Guillaume,
Thanks for the pointer. Is it just me that finds it the behavior of pg_restore odd? If the default installation since 9.0 has PL/PgSQL installed then why does pg_restore still emit statements to create the language? As a developer by trade it smells like a bug.
-- Brian
Sent from my iPhone
On Feb 25, 2012, at 10:31, Guillaume Lelarge <guillaume@lelarge.info> wrote:
> On Fri, 2012-02-24 at 22:12 -0500, Brian Weaver wrote:
>> So when did the installation of PL/PgSQL into all databases become standard
>> operating procedure? It wasn't standard (or at least it didn't choke) on
>> the installation of versions 8.3 and 8.4 that I have used on CentOS 5.
>>
>> Seems like a fairly substantial change. Did I miss it in the release notes?
>>
>
> Since 9.0, it's the third item in the overview list of the release notes
> http://www.postgresql.org/docs/9.0/static/release-9-0.html.
>
>
> --
> Guillaume
> http://blog.guillaume.lelarge.info
> http://www.dalibo.com
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Problems with pg_restore (plpgsql already exists)
@ 2012-02-25 16:23 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Brian Weaver <cmdrclueless@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Scott Ribe @ 2012-02-25 16:23 UTC (permalink / raw)
To: Brian Weaver <cmdrclueless@gmail.com>; +Cc: Guillaume Lelarge <guillaume@lelarge.info>; pgsql-admin
On Feb 25, 2012, at 9:18 AM, Brian Weaver wrote:
> Thanks for the pointer. Is it just me that finds it the behavior of pg_restore odd? If the default installation since 9.0 has PL/PgSQL installed then why does pg_restore still emit statements to create the language? As a developer by trade it smells like a bug.
It's pg_dump that's emitting the command to create the language. If you ran pg_dump from 9.0+, it would not do so. This is an example of why the standard advice for upgrading is to use the newer pg_dump against the older database--you can often get away without doing that, and I am one of those people who often ignores that advice and does things the easier way until it breaks, but it is safer.
--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Problems with pg_restore (plpgsql already exists)
@ 2012-02-25 18:10 Guillaume Lelarge <guillaume@lelarge.info>
parent: Scott Ribe <scott_ribe@elevated-dev.com>
0 siblings, 1 reply; 8+ messages in thread
From: Guillaume Lelarge @ 2012-02-25 18:10 UTC (permalink / raw)
To: Scott Ribe <scott_ribe@elevated-dev.com>; +Cc: Brian Weaver <cmdrclueless@gmail.com>; pgsql-admin
On Sat, 2012-02-25 at 09:23 -0700, Scott Ribe wrote:
> On Feb 25, 2012, at 9:18 AM, Brian Weaver wrote:
>
> > Thanks for the pointer. Is it just me that finds it the behavior of pg_restore odd? If the default installation since 9.0 has PL/PgSQL installed then why does pg_restore still emit statements to create the language? As a developer by trade it smells like a bug.
>
> It's pg_dump that's emitting the command to create the language. If you ran pg_dump from 9.0+, it would not do so.
Not quite true. pg_dump from 9.0 does save the language definition, but
it uses the new CREATE OR REPLACE statement for languages, so that, when
you restore it in a 9.0+ database that already has the same language, it
won't complain with an error message.
BTW, it isn't odd that pg_dump 9.0 save the language definition. Having
by default the plpgsql language when you create a database doesn't mean
you can't drop it.
> This is an example of why the standard advice for upgrading is to use the newer pg_dump against the older database
Exactly.
--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Problems with pg_restore (plpgsql already exists)
@ 2012-02-25 19:51 Brian Weaver <cmdrclueless@gmail.com>
parent: Guillaume Lelarge <guillaume@lelarge.info>
0 siblings, 0 replies; 8+ messages in thread
From: Brian Weaver @ 2012-02-25 19:51 UTC (permalink / raw)
To: Guillaume Lelarge <guillaume@lelarge.info>; +Cc: Scott Ribe <scott_ribe@elevated-dev.com>; pgsql-admin
Thank you all for the explanation. I'll work around the issue. It's nice to understand the thought process even though I might disagree with it.
-- Brian
Sent from my iPhone
On Feb 25, 2012, at 13:10, Guillaume Lelarge <guillaume@lelarge.info> wrote:
> On Sat, 2012-02-25 at 09:23 -0700, Scott Ribe wrote:
>> On Feb 25, 2012, at 9:18 AM, Brian Weaver wrote:
>>
>>> Thanks for the pointer. Is it just me that finds it the behavior of pg_restore odd? If the default installation since 9.0 has PL/PgSQL installed then why does pg_restore still emit statements to create the language? As a developer by trade it smells like a bug.
>>
>> It's pg_dump that's emitting the command to create the language. If you ran pg_dump from 9.0+, it would not do so.
>
> Not quite true. pg_dump from 9.0 does save the language definition, but
> it uses the new CREATE OR REPLACE statement for languages, so that, when
> you restore it in a 9.0+ database that already has the same language, it
> won't complain with an error message.
>
> BTW, it isn't odd that pg_dump 9.0 save the language definition. Having
> by default the plpgsql language when you create a database doesn't mean
> you can't drop it.
>
>> This is an example of why the standard advice for upgrading is to use the newer pg_dump against the older database
>
> Exactly.
>
>
> --
> Guillaume
> http://blog.guillaume.lelarge.info
> http://www.dalibo.com
>
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2012-02-25 19:51 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-02-24 22:30 Problems with pg_restore (plpgsql already exists) Brian Weaver <cmdrclueless@gmail.com>
2012-02-24 23:44 ` Dinesh Bhandary <dbhandary@iii.com>
2012-02-25 03:12 ` Brian Weaver <cmdrclueless@gmail.com>
2012-02-25 15:31 ` Guillaume Lelarge <guillaume@lelarge.info>
2012-02-25 16:18 ` Brian Weaver <cmdrclueless@gmail.com>
2012-02-25 16:23 ` Scott Ribe <scott_ribe@elevated-dev.com>
2012-02-25 18:10 ` Guillaume Lelarge <guillaume@lelarge.info>
2012-02-25 19:51 ` Brian Weaver <cmdrclueless@gmail.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