Menu
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.
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 
270
pg_dump doesn't lock the entire database, it does get an explicit lock on all the tables it is going to dump, though.
Grant privileges to a new user We resolve this permission denied error using the command. GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO new_user; The new_user was then able to read data from the table. Similarly, we can also resolve the permission denied error by setting DEFAULT privileges to the user.
ERROR: permission denied for relation sl_node
This is your real problem.
Make sure the user bob has SELECT privilege for _replication.sl_node. Is that by any chance a Slony system table or something?
67
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
