Error must be owner of extension plpgsql

For people using Google Cloud Platform, any error will stop the import process.
Personally I encountered two different errors depending on the pg_dump command I issued :

1- The input is a PostgreSQL custom-format dump. Use the pg_restore command-line client to restore this dump to a database.

Occurs when you’ve tried to dump your DB in a non plain text format. I.e when the command lacks the -Fp or —format=plain parameter. However, if you add it to your command, you may then encounter the following error :

2- SET SET SET SET SET SET CREATE EXTENSION ERROR: must be owner of extension plpgsql

This is a permission issue I have been unable to fix using the command provided in the GCP docs, the tips from this current thread, or following advice from Google Postgres team here. Which recommended to issue the following command :

pg_dump -Fp --no-acl --no-owner -U myusername myDBName > mydump.sql

The only thing that did the trick in my case was manually editing the dump file and commenting out all commands relating to plpgsql.

I hope this helps GCP-reliant souls.

Update :

It’s easier to dump the file commenting out extensions, especially since some dumps can be huge :
pg_dump ... | grep -v -E '(CREATE EXTENSION|COMMENT ON)' > mydump.sql

Which can be narrowed down to plpgsql :
pg_dump ... | grep -v -E '(CREATE EXTENSION IF NOT EXISTS plpgsql|COMMENT ON EXTENSION plpgsql)' > mydump.sql

Seeing error «Must be owner of extension plpgsql» during a Postgres database restore.

I see this when I push my database to Heroku.

Locally, I have OS X 10.11.2 and:

$ psql --version
psql (PostgreSQL) 9.4.4

The pg:push:

$ heroku pg:push -a myapp mydb DATABASE_URL

...

pg_restore: connecting to database for restore
pg_restore: creating SCHEMA public
pg_restore: creating COMMENT SCHEMA public
pg_restore: creating EXTENSION plpgsql
pg_restore: creating COMMENT EXTENSION plpgsql
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2509; 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';



pg_restore: creating TABLE mytable

...

WARNING: errors ignored on restore: 1

$ heroku pg:info -a myapp
=== DATABASE_URL
Plan:        Hobby-basic
Status:      Available
Connections: 2/20
PG Version:  9.4.5
Created:     2015-12-15 18:14 UTC
Data Size:   116.6 MB
Tables:      0
Rows:        0/10000000 (In compliance) - refreshing
Fork/Follow: Unsupported
Rollback:    Unsupported
Region:      Europe
Add-on:      postgresql-parallel-5079

Locally I have:

$ heroku update
Updating plugins... no plugins to update.

$ heroku version
heroku-toolbelt/3.42.25 (x86_64-darwin14.5.0) ruby/1.9.3
heroku-cli/4.27.9-cce0260 (amd64-darwin) go1.5.2
=== Installed Plugins
heroku-accounts
heroku-apps@1.0.0
heroku-cli-addons@0.1.1
heroku-fork@4.0.0
heroku-git@2.4.4
heroku-local@4.1.5
heroku-redis@1.1.1
heroku-run@2.9.2
heroku-status@1.2.4

Says «The error is harmless» over at http://dba.stackexchange.com/questions/90258/pg-restore-archiver-db-could-not-execute-query-error-schema-public-alre, but I thought I make a note here anyway. I haven’t noticed any problems.

ERROR:  must be owner of extension plpgsql

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 7309; 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';

pg_restore: [archiver (db)] Error from TOC entry 2; 3079 32579416 EXTENSION uuid-ossp 
pg_restore: [archiver (db)] could not execute query: ERROR:  permission denied to create extension "uuid-ossp"
HINT:  Must be superuser to create this extension.
    Command was: CREATE EXTENSION IF NOT EXISTS "uuid-ossp" WITH SCHEMA public;

pg_restore: [archiver (db)] Error from TOC entry 7310; 0 0 COMMENT EXTENSION "uuid-ossp" 
pg_restore: [archiver (db)] could not execute query: ERROR:  extension "uuid-ossp" does not exist
    Command was: COMMENT ON EXTENSION "uuid-ossp" IS 'generate universally unique identifiers (UUIDs)';

This solution is part of Red Hat’s fast-track publication program, providing a huge library of solutions that Red Hat engineers have created while supporting our customers. To give you the knowledge you need the instant it becomes available, these articles may be presented in a raw and unedited form.

Для пользователей, использующих Виртуальную платформу Google, любая ошибка остановит процесс импорта.
Лично я столкнулся с двумя разными ошибками в зависимости от команды pg_dump, которую я выпустил:

1- The input is a PostgreSQL custom-format dump. Use the pg_restore command-line client to restore this dump to a database.

Возникает, когда вы пытались сбрасывать свою БД в текстовом формате. I. когда команда не имеет параметра -Fp или -формат = простой. Однако, если вы добавите его в свою команду, вы можете столкнуться со следующей ошибкой:

2- SET SET SET SET SET SET CREATE EXTENSION ERROR: must be owner of extension plpgsql

Это проблема с разрешением, которую я не смог исправить, используя команду, представленную в GCP docs, советы от этого текущего потока, или после консультации с командой Google Postgres здесь. Который рекомендовал выпустить следующую команду:

pg_dump -Fp --no-acl --no-owner -U myusername myDBName > mydump.sql

Единственное, что делало трюк в моем случае, это вручную редактировать файл дампа и комментировать все команды, относящиеся к plpgsql.

Я надеюсь, что это поможет душам, поддерживающим GCP.

Обновление:

Легче сбрасывать файлы с комментариями расширений, тем более, что некоторые дампы могут быть огромными:
pg_dump ... | grep -v -E '(CREATE EXTENSION|COMMENT ON)' > mydump.sql

Которое можно сузить до plpgsql:
pg_dump ... | grep -v -E '(CREATE EXTENSION IF NOT EXISTS plpgsql|COMMENT ON EXTENSION plpgsql)' > mydump.sql