82

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.

fury-s12
  • 1,240
  • 3
  • 12
  • 18
  • If you connect using psql and type `\l`, what do you see in the "Owner" column for that database? Since plpgsql is an untrusted language, it can only be modified (and I would guess that applies even to the comment on it) by the database owner or a database superuser. – kgrittn Apr 16 '12 at 09:22
  • i can confirm that the owner is of the database is correct and matches the user specified by the -U option of the pg_restor command (and the pg_dump too) – fury-s12 Apr 16 '12 at 23:28
  • It's not that simple, unfortunately. I have pg_dump output that expects to be able to create languages and functions using those languages. If I hand-create the language as the DB superuser, the function creation fails due to permission errors. If I don't, the procedural language installation fails due to permission errors. In either case, triggers further down that rely on those functions existing also can't be created, because the functions don't exist. – Quixadhal Dec 03 '12 at 14:31

7 Answers7

134

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
Alexis Wilke
  • 19,179
  • 10
  • 84
  • 156
Roman Akinfold
  • 1,457
  • 1
  • 10
  • 11
  • 9
    Does this option involve information loss after restore? That is, will it lose any important data? – C2H5OH Jun 23 '14 at 16:42
  • 8
    @C2H5OH it depends. When your database backup contains more than one schema the above command will import schema "public" only (=information loss). If you dump contains schema "public" only, then you do not have an issue (refer to: pg_dump --exclude-schema). To check what schemas are in your dump file run this: pg_restore -l -F t dumpfile.tar | grep SCHEMA | awk -F " " '{print $(NF-1)}' - – DanielaWaranie Nov 12 '14 at 11:09
  • 2
    This fixed my issue with `pg_restore` trying to load in additional extensions, which in our scenario it wasn't permitted to. – nibbex Mar 12 '15 at 13:32
  • 2
    You also need to be careful if you use large objects, they aren't restored – Peter Gerber Mar 12 '17 at 17:33
20

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
  • 2
    This worked for me. A warning though, the second command will print everything to `STDOUT` so you will probably want to redirect it to a file instead. – shrx Oct 25 '14 at 09:31
  • Is there a way to do it in just one command? I have an automated process and this won't work. – Diego Jun 18 '18 at 17:10
10

If you're running Postgres 11's (or higher) version of pg_dump then you can take advantage of the new --no-comments flag.

jabclab
  • 14,786
  • 5
  • 54
  • 51
  • if you ae using tableplus and you are on postgres:9.6 then you can write this on the options sidebar. – aRtoo Mar 15 '21 at 19:16
8

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.

Peter Gerber
  • 1,023
  • 10
  • 13
3

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.

Edmund
  • 10,533
  • 3
  • 39
  • 57
3

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
Deepak Mahakale
  • 22,834
  • 10
  • 68
  • 88
0

I found what worked for me was giving the database user the correct permissions. I was having this same error, and it was because the postgres user I was using was not a superuser. Now I know there are many caveats to giving superuser privileges, but if this is your only database on the cluster it shouldn't be a problem.

you can do this by logging into the postgres shell using sudo -u postgres psql

then setting permissions using ALTER USER your_db_username WITH SUPERUSER

and then restarting the service sudo systemctl restart postgresql.service.

Now run the command again to restore/backup and it should work. Another note, is that I believe it is important to do the backup as well as the restore with these new privileges.

MattG
  • 1,682
  • 5
  • 25
  • 45