CODEWITHSUNDEEP
databasepostgresql
DrStalker
DrStalker

Reputation: 9601

Copy a postgres database without LOCK permissions

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

Upvotes: 44

Views: 98055

Answers (7)

anjaneyulubatta505
anjaneyulubatta505

Reputation: 11695

This worked for me -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

Upvotes: 2

Frank Heikens
Frank Heikens

Reputation: 127396

You need SELECT permissions (read) on all database objects to make a dump, not LOCK permissions (whatever that may be). What's the complete error message when you start pg_dump to make a dump?

Upvotes: 8

user330315
user330315

Reputation:

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?

Upvotes: 31

TonyTony
TonyTony

Reputation: 1404

https://forums.aws.amazon.com/thread.jspa?threadID=151526

this link helped me a lot. It refers to another one,

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.html#Appendix.PostgreSQL.CommonDBATasks.PostGIS

I first change the ownship to rds_superuser, then paste this piece of code, 

CREATE FUNCTION exec(text) returns text language plpgsql volatile AS $f$             
BEGIN EXECUTE $1; RETURN $1; 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.

Upvotes: 4

mamesaye
mamesaye

Reputation: 2153

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: 

 sudo -u postgres /usr/local/pgsql/bin/pg_restore -U postgres -d db_name -v file_name.pgdump

Upvotes: 12

zeratool
zeratool

Reputation: 1281

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.

HTH

Upvotes: 3

araqnid
araqnid

Reputation: 133692

pg_dump doesn'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.

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...

As Frank H. suggested, post the full error message and we'll try to help decode it.

Upvotes: 10

Related Questions