Search code examples
postgresqldatabase-restorepostgresql-8.4pg-restore

How to resolve Postgresql 8.4 error 'language “plpgsql” already exists'?

After creating a database, I am restoring a database in PostgreSQL 8.4 on this blank/new database using a .backup file through a script (with proper error handling). If any error occurs, then script aborts the whole process and marks the process as failed. During database restore following error is generated:

pg_restore: connecting to database for restore
pg_restore: creating SCHEMA public
pg_restore: creating COMMENT SCHEMA public
pg_restore: creating PROCEDURAL LANGUAGE plpgsql
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 302; 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;
pg_restore: setting owner and privileges for SCHEMA public
pg_restore: setting owner and privileges for COMMENT SCHEMA public
pg_restore: setting owner and privileges for ACL public
pg_restore: setting owner and privileges for PROCEDURAL LANGUAGE plpgsql
WARNING: errors ignored on restore: 1
Exit code : 1

I know, that this error should be ignored in PostgreSQL 8.x, but since I am executing this through a script, this issue is required to be resolved i.e. PostgreSQL's exit code should be 0, otherwise, the whole process won't be completed.

Any idea how to do this?

Solution

  • After working on this issue for several hours, I found the following solution to be basic and simple. The databases created using command 'CREATE DATABASE' uses standard system database named 'template1' by default. Instead, use 'template0'. As stated in the document:

    By instructing CREATE DATABASE to copy template0 instead of template1, you can create a "virgin" user database that contains none of the site-local additions in template1. This is particularly handy when restoring a pg_dump dump: the dump script should be restored in a virgin database to ensure that one recreates the correct contents of the dumped database, without any conflicts with objects that might have been added to template1 later on.