[ADMIN] Problems while restoring databases

[Jean-Yves NOLEN]

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

[Tom Lane]

[ while restoring a pg_dump backup ]
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