Received-SPF: pass (google.com: domain of cmdrclueless@gmail.com designates
	10.220.3.10 as permitted sender) client-ip=10.220.3.10;
From: Brian Weaver <cmdrclueless@gmail.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: quoted-printable
Subject: Problems with pg_restore (plpgsql already exists)
Date: Fri, 24 Feb 2012 17:30:58 -0500
Message-Id: <EB254343-461B-4B6B-93A1-3065BA902CF3@gmail.com>
To: pgsql-admin@postgresql.org
Mime-Version: 1.0 (Apple Message framework v1257)

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=3Dtemplate1'

I use the '--dbname=3Dtemplate1' 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 =3D template0 ENCODING =3D 'UTF8' =
LC_COLLATE =3D 'en_US.UTF-8' LC_CTYPE =3D 'en_US.UTF-8';


ALTER DATABASE foo OWNER TO foo;

\connect foo

SET statement_timeout =3D 0;
SET client_encoding =3D 'UTF8';
SET standard_conforming_strings =3D off;
SET check_function_bodies =3D false;
SET client_min_messages =3D warning;
SET escape_string_warning =3D off;

--
-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: postgres
--

CREATE PROCEDURAL LANGUAGE plpgsql;


ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO postgres;

SET search_path =3D 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=