database 复制没有 LOCK 权限的 postgres 数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4363697/
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
Copy a postgres database without LOCK permissions
提问by DrStalker
I need to copy a postgres DB from one server to another, but the credentials I have do not have permission to lock the database so a pg_dump fails. I have full read/update/insert rights to the DB in question.
我需要将 postgres 数据库从一台服务器复制到另一台服务器，但我拥有的凭据无权锁定数据库，因此 pg_dump 失败。我对相关数据库拥有完整的读取/更新/插入权限。
How can I make a copy of this database? I'm not worried about inconsistencies (it is a small database on a dev server, so minimal risks of inconsistencies during the extract)
如何制作此数据库的副本？我不担心不一致（它是开发服务器上的一个小型数据库，因此在提取过程中出现不一致的风险最小）
[edit] Full error:
[编辑] 完整错误：
$ pg_dump --username=bob mydatabase > /tmp/dump.sql
pg_dump: SQL command failed
pg_dump: Error message from server: ERROR: permission denied for relation sl_node
pg_dump: The command was: LOCK TABLE _replication.sl_node IN ACCESS SHARE MODE
采纳答案by a_horse_with_no_name
ERROR: permission denied for relation sl_node
错误：关系 sl_node 的权限被拒绝
This is your real problem.
这是你真正的问题。
Make sure the user bob has SELECTprivilege for _replication.sl_node. Is that by any chance a Slony system table or something?
确保用户bob拥有SELECT的特权_replication.sl_node。有没有可能是 Slony 系统表之类的？
回答by mamesaye
This worked for me
这对我有用
sudo -u postgres pg_dump -Fc -c db_name > file_name.pgdump
Then create a DB and run pg_restore it:
然后创建一个数据库并运行 pg_restore 它：
sudo -u postgres /usr/local/pgsql/bin/pg_restore -U postgres -d db_name -v file_name.pgdump
回答by Frank Heikens
You need SELECTpermissions (read) on all database objects to make a dump, not LOCKpermissions (whatever that may be). What's the complete error message when you start pg_dumpto make a dump?
您需要SELECT对所有数据库对象的权限（读取）才能进行转储，而不是LOCK权限（无论是什么）。开始pg_dump转储时的完整错误消息是什么？
回答by araqnid
pg_dumpdoesn't lock the entire database, it does get an explicit lock on all the tables it is going to dump, though. This lock is taken in "access share mode", which is the same lock level required by a SELECT statement: it's intended just to guard against one of the tables being dropped between it deciding which tables to dump and then getting the data.
pg_dump不会锁定整个数据库，但它会在要转储的所有表上获得显式锁定。此锁以“访问共享模式”获取，这与 SELECT 语句所需的锁级别相同：它旨在防止其中一个表在它决定转储哪些表然后获取数据之间被删除。
So it sounds like your problem might actually be that it is trying to dump a table you don't have permission for? PostgreSQL doesn't have database-level read/update/insert rights, so maybe you're just missing the select privilege from a single table somewhere...
所以听起来您的问题实际上可能是它试图转储您没有权限的表？PostgreSQL 没有数据库级别的读取/更新/插入权限，所以也许您只是在某处缺少从单个表中选择权限...
As Frank H. suggested, post the full error message and we'll try to help decode it.
正如 Frank H. 建议的那样，发布完整的错误消息，我们将尝试帮助解码。
回答by TonyTony
https://forums.aws.amazon.com/thread.jspa?threadID=151526
https://forums.aws.amazon.com/thread.jspa?threadID=151526
this link helped me a lot. It refers to another one,
这个链接对我帮助很大。它指的是另一个，
I first change the ownship to rds_superuser, then paste this piece of code,
我先把所有权改成rds_superuser，然后贴上这段代码，
CREATE FUNCTION exec(text) returns text language plpgsql volatile AS $f$
BEGIN EXECUTE ; RETURN ; END; $f$;
SELECT exec('ALTER TABLE ' || quote_ident(s.nspname) || '.' || quote_ident(s.relname) || ' OWNER TO rds_superuser')
FROM (
SELECT nspname, relname
FROM pg_class c JOIN pg_namespace n ON (c.relnamespace = n.oid)
WHERE nspname in ('tiger','topology') AND
relkind IN ('r','S','v') ORDER BY relkind = 'S')
s;
thereafter, I am able to dump my whole database.
此后，我可以转储我的整个数据库。
回答by zeratool
Did you run 'pg_dump' with the correct -U (user who owns that db) ? If yes, then just like other poster said, check the permissions.
您是否使用正确的 -U（拥有该数据库的用户）运行了“pg_dump”？如果是，那么就像其他海报说的那样，检查权限。
HTH
HTH
回答by anjaneyulubatta505
This worked for me -d dbname -n schemaname
这对我有用 -d dbname -n schemaname
pg_dump -v -Fc -h <host> -U <username> -p -d <db_name> -n <schema_name> > file_name.pgdump
default schema is public
默认架构是 public
