I have trouble backing up a PostgreSQL database. When I run the following command:
psql -d cdb -U "$(whoami)"
The result:
cdb=# \c
You are now connected to database "cdb" as user "gub".
So no problems with that. However when I run
pg_dump -h localhost -p 5432 -U "$(whoami)" -d cdb -w -C -F p -b -f /home/"$(whoami)"/cdb.sql
I get the following error message:
pg_dump: error: connection to database "cdb" failed: fe_sendauth: no password supplied
I have the following auth settings:
cdb=# select * from pg_hba_file_rules;
line_number | type | database | user_name | address | netmask | auth_method | options | error
------------- ------- --------------- ------------ ----------- ----------------------------------------- ------------- --------- -------
89 | local | {all} | {postgres} | | | peer | |
94 | local | {all} | {all} | | | peer | |
96 | host | {all} | {all} | 127.0.0.1 | 255.255.255.255 | md5 | |
98 | host | {all} | {all} | ::1 | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | md5 | |
101 | local | {replication} | {all} | | | peer | |
102 | host | {replication} | {all} | 127.0.0.1 | 255.255.255.255 | md5 | |
103 | host | {replication} | {all} | ::1 | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | md5 | |
I am not sure why this is not working as auth_method peer
should not require a password. Can someone please help? postgresql.conf has not been changed and setup is: psql (PostgreSQL) 12.9 (Ubuntu 12.9-0ubuntu0.20.04.1)
My goal is to backup without the need of a password.
CodePudding user response:
With psql
, you are using a local connection via domain sockets, since you didn't specify -h localhost
, so the first or second pg_hba.conf
line applies, and you can connect without a password.
With pg_dump
, you are using a TCP connection, and the third or fourth line applies, and you have to supply a password.
Omit -h localhost
!