PostgreSQL 9.1 pg_restore error regarding PLPGSQL
PostgresqlPlpgsqlDatabase RestorePostgresql Problem Overview
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.
Postgresql Solutions
Solution 1 - Postgresql
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
Solution 2 - Postgresql
I found the following workaround on this page:
http://archives.postgresql.org/pgsql-general/2011-10/msg00826.php
The idea is to use pg_restore -l to list the contents of the archive, grep out the extension that the user doesn't have permission to restore, and use pg_restore -L to use this elided list when restoring.
For example:
pg_restore -l ~/database.dump | grep -v "EXTENSION - plpgsql" > ~/restore_elements
pg_restore -L ~/restore_elements ~/database.dump
Solution 3 - Postgresql
If you're running Postgres 11's (or higher) version of pg_dump then you can take advantage of the new --no-comments flag.
Solution 4 - Postgresql
If possible, I recommend you remove the comment which fails to restore before creating any dumps.
You can do so by:
COMMENT ON EXTENSION plpgsql IS null;
If you don't want to do this for every newly created database, remove the comment from the DB called template1 (CREATE DATABASE… copies this database.)
Dumps created after that should restore with no error.
Solution 5 - Postgresql
Are you loading into a DB that was created by a different user? If possible try restoring using the same user that created the DB and its existing objects.
Solution 6 - Postgresql
Works for me after this command -
Deepak@deepak:~$ sudo -i -u postgres
postgres@deepak:~$ psql
psql (9.3.5)
Type "help" for help.
postgres=# GRANT ALL PRIVILEGES ON DATABASE database_name TO user;
postgres=# GRANT