From: Brian Weaver <cmdrclueless@gmail.com>
To: pgsql-admin@postgresql.org
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> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: cmdrclueless@gmail.com
Subject: Re: Problems with pg_restore (plpgsql already exists)
In-Reply-To: <EB254343-461B-4B6B-93A1-3065BA902CF3@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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