Hi everybody,
I’m trying to restore a database dump from our production server to our development server.
But when running pg_restore I’m getting this error:
pg_restore: creating PROCEDURAL LANGUAGE plpgsql
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 512; 2612 16386 PROCEDURAL LANGUAGE plpgsql postgres
pg_restore: [archiver (db)] could not execute query: ERROR: language “plpgsql” already exists
Command was:
CREATE PROCEDURAL LANGUAGE plpgsql;
I’ve tried to search on internet for an answer and people say that it could be because my dump is for another database. But I have dropped/deleted the one of my development server.
Any idea?
Hi Foobrother,
Are you connecting to the database under a specific user (with pg_restore)? Does this user have rights on that database ?
Yes using the database owner user: postgres
I have found a “solution” on a forum. The guy says that…
The language plpsql already exists in PostgreSQL. It can happen if it already exists in the database ‘template1’ at the creation point, because this database is used as a model.
A solution is to connect to the PostgreSQL before restoring and run: DROP LANGUAGE plpgsql cascade;
Another solution is to remove the option ‘-e’ of pg_restore. It will not stop on an error which in that case is not annoying.
I have done the first solution. I don’t get any error now, but it seems to hang or take a very long time to restore the data from a table (more than 5min now). My dump file is 16MB and this table is one of the biggest. But I don’t know if it’s a normal restore time?!
It worked!
I’ve let it all night and this morning it’s done ![:D]( "Very Happy")
Don’t know how long it took but it’s ok now.
Cheers.
Hi Foobrother,
you can safely ignore any ‘x does already exist’ type of errors.
Your dump file should contain all the necessary instructions to restore your PostgreSQL database, but if your server already has some items like pgsql installed in template1, you’ll get these messages.