Virtualmin postgresql backup creates language

Submitted by iann@fuelforce.com on Mon, 11/28/2011 - 10:54

Is there anyway to configure the backup script to skip dropping and creating a language for a backed up database? If we don't manually edit the postgresql backup file we end up with this error:

pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 520; 2612 61755 PROCEDURAL LANGUAGE plpgsql pgsql pg_restore: [archiver (db)] could not execute query: ERROR: language "plpgsql" already exists Command was: CREATE PROCEDURAL LANGUAGE plpgsql;

Status: 
Active

Comments

Submitted by JamieCameron on Mon, 11/28/2011 - 14:47

Are you creating this backup using Virtualmin's domain-level backup, or Webmin's PostgreSQL module?

Submitted by JamieCameron on Mon, 11/28/2011 - 14:47

Also, which PostgreSQL version is your system running?

Submitted by iann@fuelforce.com on Mon, 01/23/2012 - 15:06

We're using Virtualmin's domain-level backup, PostgreSQL 9.1.2

Submitted by JamieCameron on Mon, 01/23/2012 - 16:23

I did some research on this, and it seems that if your template1 database already has that language, it can cause this error - see https://www.servoy.com/forum/viewtopic.php?f=4&t=12451

The fix may be to delete the plpgsql language from the template1 database. I am guessing it doesn't exist by default..

I could fix Virtualmin to ignore these errors during a restore, but only at the cost of having it ignore all errors.

Submitted by iann@fuelforce.com on Fri, 01/27/2012 - 15:47

What we did was drop the plpgsql language from the template db. In the future is there anyway you can expose the -e (ignore errors) pg_restore option in the restore script?

Submitted by JamieCameron on Fri, 01/27/2012 - 15:59

Cool, that sounds like the best solution.

The trouble with the -e flag is that it ignores all errors, even those that you might really need to know about, like errors writing to the database.