From jynolen@gmail.com Mon Dec 12 11:32:27 2011 Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id EB7571E9AFB1 for ; Mon, 12 Dec 2011 07:32:27 -0400 (AST) Received: from mail-vx0-f174.google.com ([209.85.220.174]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Ra47P-0000vf-Bf for pgsql-admin@postgresql.org; Mon, 12 Dec 2011 11:32:28 +0000 Received: by vcbfk14 with SMTP id fk14so3621851vcb.19 for ; Mon, 12 Dec 2011 03:32:14 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=from:to:subject:date:message-id:mime-version:content-type:x-mailer :thread-index:content-language; bh=VQbxM0oI8Gtv7gJ55nUIwERyPFw1z2mtX8wYhfHIT7s=; b=QbkWRXiqBE9kZokcXqK0+VHF0NkpEsIUlHOd1kOVpb1NKxNaf9iYK6Gp8bagJbRttS YsCZeUZy3KWRcPMhA7zzTa4fBaKLglAeiSwEF9E/roTSV3ez9vsyyfzRaPcK6gD+A6Ev lRs+q7BFWdEFNgv2xxQh2iyJRSG/yRRdmIs4Q= Received: by 10.52.36.109 with SMTP id p13mr9476866vdj.59.1323689534828; Mon, 12 Dec 2011 03:32:14 -0800 (PST) Received: from PCJeanYves (inet-gw.alma.fr. [212.99.87.106]) by mx.google.com with ESMTPS id s10sm13919294vdj.1.2011.12.12.03.32.12 (version=TLSv1/SSLv3 cipher=OTHER); Mon, 12 Dec 2011 03:32:13 -0800 (PST) From: "Jean-Yves NOLEN" To: Subject: Problems while restoring databases Date: Mon, 12 Dec 2011 12:32:10 +0100 Message-ID: <00ff01ccb8c1$b19f2e60$14dd8b20$@gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0100_01CCB8CA.13654410" X-Mailer: Microsoft Outlook 14.0 Thread-Index: Acy4wa9FgEcOtQwTRZmC0sMUqdfFpQ== Content-Language: fr X-Pg-Spam-Score: -2.6 (--) X-Archive-Number: 201112/63 X-Sequence-Number: 38821 This is a multipart message in MIME format. ------=_NextPart_000_0100_01CCB8CA.13654410 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Hi everyone ! I am facing a problem after a lot of research has not found a solution. I hope some of you can enlighten me I will hold my position in 3 part: what I want, what I run, which is a problem. First of all I want to: Back up a database with all information (structure, data, functions, triggers) in a "dump" Restore the same data (structure, data, trigger, etc ...) into a new database with a different name (ex: src=foo ; dst= bar) So far not rocket science. Then what I do: pg_dump -i -F c -i -h ***.***.***.*** -p #### -U #### -f c:\dump.bak mySourceBase A creation of new base like this : CREATE DATABASE "testingBackup" WITH OWNER = postgres ENCODING = 'UTF8' TABLESPACE = pg_default LC_COLLATE = 'French_France.1252' LC_CTYPE = 'French_France.1252' CONNECTION LIMIT = -1; And finally : pg_restore -F c -h ##### -p #### -U #### -d testingBackup C:\dump.bak What problems i got pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 626; 2612 16386 PROCEDURAL LANG UAGE plpgsql postgres pg_restore: [archiver (db)] could not execute query: ERREUR: language "plpgsql" already exists Command was: CREATE PROCEDURAL LANGUAGE plpgsql; WARNING: errors ignored on restore: 1 In fact there is no problem since all data is restored but in a goal of "cleanliness" I want to clean this mess and finally get no error. To this end I am open to your suggestions. Thank you in advance for your help, and I apologies for my bad English. Jean-Yves ------=_NextPart_000_0100_01CCB8CA.13654410 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Hi = everyone !

I am facing a problem after a lot of research has not found a = solution.
I hope some of you can enlighten me
I will hold my = position in 3 part: what I want, what I run, which is a = problem.

First of all I want to:

Back up a database with all information (structure, data, = functions, triggers) in a "dump"
Restore the same data = (structure, data, trigger, etc ...) into a new database with a different = name (ex: src=3Dfoo ; dst=3D bar)

So far not rocket = science.

=

Then what I do:
pg_dump -i -F c -i -h ***.***.***.*** -p #### -U #### = -f c:\dump.bak mySourceBase

A creation of new base like = this :

CREATE DATABASE = "testingBackup"

WITH OWNER =3D = postgres

= ENCODING =3D 'UTF8'

= TABLESPACE =3D pg_default

= LC_COLLATE =3D 'French_France.1252'

= LC_CTYPE =3D 'French_France.1252'

= CONNECTION LIMIT =3D = -1;

<= p class=3DMsoNormal>And finally = :

pg_restore -F c -h = ##### -p #### -U #### -d testingBackup = C:\dump.bak

What problems i got =

pg_restore: [archiver (db)] = Error while PROCESSING TOC:

pg_restore: [archiver (db)] = Error from TOC entry 626; 2612 16386 PROCEDURAL = LANG

UAGE plpgsql = postgres

pg_restore: [archiver (db)] = could not execute query: ERREUR: language "plpgsql" = already exists

Command = was:

CREATE PROCEDURAL LANGUAGE = plpgsql;

WARNING: errors ignored on = restore: 1

In fact there is no = problem since all data is = restored but in a goal of = "cleanliness" = I want to clean this mess and = finally get no error.
To this = end I = am open = to your = suggestions.

Thank you in = advance for your = help, and I apologies for my bad English.

Jean-Yves

------=_NextPart_000_0100_01CCB8CA.13654410-- From tgl@sss.pgh.pa.us Mon Dec 12 16:11:34 2011 Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id CD8567D8411 for ; Mon, 12 Dec 2011 12:11:34 -0400 (AST) Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Ra8TV-0002xl-Al for pgsql-admin@postgresql.org; Mon, 12 Dec 2011 16:11:34 +0000 Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id pBCGBJ5V019697; Mon, 12 Dec 2011 11:11:19 -0500 (EST) To: "Jean-Yves NOLEN" cc: pgsql-admin@postgresql.org Subject: Re: Problems while restoring databases In-reply-to: <00ff01ccb8c1$b19f2e60$14dd8b20$@gmail.com> References: <00ff01ccb8c1$b19f2e60$14dd8b20$@gmail.com> Comments: In-reply-to "Jean-Yves NOLEN" message dated "Mon, 12 Dec 2011 12:32:10 +0100" Date: Mon, 12 Dec 2011 11:11:19 -0500 Message-ID: <19696.1323706279@sss.pgh.pa.us> From: Tom Lane X-Pg-Spam-Score: -4.2 (----) X-Archive-Number: 201112/64 X-Sequence-Number: 38822 "Jean-Yves NOLEN" writes: > [ while restoring a pg_dump backup ] > pg_restore: [archiver (db)] Error while PROCESSING TOC: > pg_restore: [archiver (db)] Error from TOC entry 626; 2612 16386 PROCEDURAL > LANGUAGE plpgsql postgres > pg_restore: [archiver (db)] could not execute query: ERREUR: language > "plpgsql" already exists Probably the reason you get this is that you have plpgsql installed in template1, and so it's already there in your destination database. You can avoid that by cloning the new database from template0 (add "TEMPLATE template0" to your CREATE DATABASE command). regards, tom lane