关于 PLPGSQL 的 PostgreSQL 9.1 pg_restore 错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10169203/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL 9.1 pg_restore error regarding PLPGSQL
提问by fury-s12
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.
我正在将 Postgres 用于 django 项目，并且我目前正在实施一个数据库备份/恢复系统，该系统尽可能简单地在用户单击备份时执行 pg_dump，然后在单击恢复备份时执行 pg_restore。
All seems fine and dandy until it actually tries to perform the pg_restore at which time it gives this error:
一切看起来都很好，直到它实际尝试执行 pg_restore 时它会出现此错误：
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';
pg_restore: [archiver (db)] 目录条目 3206 中的错误；0 0 COMMENT EXTENSION plpgsql pg_restore: [archiver (db)] 无法执行查询：错误：必须是扩展 plpgsql 命令的所有者：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
我已经研究了 plpgsql 是什么等，我明白这一点，关于错误，我尝试手动将“扩展的所有者”设置为执行脚本并拥有数据库本身的用户，但这没有任何改变，这真的很烦人，因为它在试图设置所有事物的评论时出错
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.
这都是由 pg_dump 自动创建的，因此无法删除注释行，并且没有禁用注释的标志（我知道），所以我真的不知道如何解决这个问题。
回答by Roman Akinfold
It seems like pg_restore tries to restore some extra data which you don't own. Try to add -n publicoption 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 试图恢复一些您不拥有的额外数据。尝试向-n publicpg_restore 命令行添加选项。它会告诉 pg_restore 只恢复公共模式的内容。你的命令行应该看起来像
pg_restore -U username -c -n public -d database_name
回答by rantler
I found the following workaround on this page:
我在此页面上找到了以下解决方法：
http://archives.postgresql.org/pgsql-general/2011-10/msg00826.php
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.
想法是使用 pg_restore -l 列出存档的内容，grep 出用户无权恢复的扩展名，并在恢复时使用 pg_restore -L 使用这个省略的列表。
For example:
例如：
pg_restore -l ~/database.dump | grep -v "EXTENSION - plpgsql" > ~/restore_elements
pg_restore -L ~/restore_elements ~/database.dump
回答by Peter Gerber
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.)
如果您不想为每个新创建的数据库执行此操作，请从名为 template1 的数据库中删除注释（CREATE DATABASE…复制此数据库。）
Dumps created after that should restore with no error.
之后创建的转储应该恢复没有错误。
回答by jabclab
If you're running Postgres 11's (or higher) version of pg_dumpthen you can take advantage of the new --no-commentsflag.
如果您运行的是Postgres 11（或更高）版本，pg_dump那么您可以利用新--no-comments标志。
回答by Deepak Mahakale
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
回答by Edmund
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.
您是否正在加载到由不同用户创建的数据库中？如果可能，请尝试使用创建数据库及其现有对象的同一用户进行恢复。
