I'm trying to dump a db using pg_dump but when I do it says I don't have access to the db. I have a specific user for the db, but I'm not sure how to dump it using that user. The is no system user for that db user.

The command I am using is:

pg_dump lhhsweb > lhhs.sql

The error I am getting is:

pg_dump: [archiver (db)] query failed: ERROR:  permission denied for table django_migrations
pg_dump: [archiver (db)] query was: LOCK TABLE public.django_migrations IN ACCESS SHARE MODE

CodePudding user response：

This:

pg_dump lhhsweb > lhhs.sql

should be specified as at minimum as:

pg_dump -U <db_owner or superuser> -d lhhsweb -f lhhs.sql

Where <db_owner or superuser> is the Postgres user that owns the database or is a superuser. In any case a user(role) that has sufficient privileges to dump the entire database.

The reason you are getting the error is that in the absence of -U(user) the user name used is that of the system user the command is being run as, per Parameter Key words.:

user

PostgreSQL user name to connect as. Defaults to be the same as the operating system name of the user running the application.

I suggest spending some time at the above link to see what the other key words mean also. It will make things clearer and prevent issues down the road.

In conclusion, in modern versions of Postgres the term user is an alias for role, with the convention that a role that can login can also be referred to as a user. Postgres has its own set of users internal to the database that are independent of the OS system users. When you are extracting information from the database you need to be using those users. In particular a user that has the necessary privileges.

CodePudding user response：

At any time, you can use:

pg_dump --host=<host> -U <username> -n <schema> -F t -d <database> -p <port> -v > lhhs.sql.tar

Here: the database looks like to be: lhhsweb the schema to be : public you need specify the connected username.