PostgreSQL 9.1 pg_restore error regarding PLPGSQL

postgresql plpgsql database-restore
fury-s12 picture fury-s12 · Apr 16, 2012 · Viewed 50.5k times · Source

I am using Postgres for a django project and I am currently implementing a database backup/restore system that as simple as possible performs a pg_dump when the user clicks backup and then pg_restore when they click restore backup.

All seems fine and dandy until it actually tries to perform the pg_restore at which time it gives this error:

pg_restore: [archiver (db)] Error from TOC entry 3206; 0 0 COMMENT EXTENSION plpgsql pg_restore: [archiver (db)] could not execute query: ERROR: must be owner of extension plpgsql Command was: COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';

I've looked into what plpgsql is etc and I understand that, and regarding the error I tried manually setting the "owner of the extension" to the user who executes the script and owns the database itself but that changed nothing, its really annoying since its erroring on an attempt to set a comment of all things

This is all created automatically by pg_dump so the comment line cant be removed and there are no flags to disable comments (that I'm aware off), so I'm truly stuck as to how to solve this issue.

Answer

Roman Akinfold picture Roman Akinfold · Aug 2, 2012

It seems like pg_restore tries to restore some extra data which you don't own. Try to add -n public option to your pg_restore command line. It will tell pg_restore restore only contents of public schema. Your command line should looks like

pg_restore -U username -c -n public -d database_name

Related questions

Store query result in a variable using in PL/pgSQL

How to assign the result of a query to a variable in PL/pgSQL, the procedural language of PostgreSQL? I have a function: CREATE OR REPLACE FUNCTION test(x numeric) RETURNS character varying AS $BODY$ DECLARE name character varying(255); begin …

Read More
PostgreSQL IF statement

How can I do such query in Postgres? IF (select count(*) from orders) > 0 THEN DELETE from orders ELSE INSERT INTO orders values (1,2,3);

Read More
Postgres FOR LOOP

I am trying to get 25 random samples of 15,000 IDs from a table. Instead of manually pressing run every time, I'm trying to do a loop. Which I fully understand is not the optimum use of Postgres, but it is the …

Read More