I am trying to backup my PostgreSQL database called crewdb on Ubuntu 18.04 LTS from a script with the following command in it:
pg_dump -h localhost -p 5432 -U postgres -w -C -F p -b -v -f ~/Dropbox\/postgres_backup/crewdb.backup.sql crewdb
I know the script that the above command runs in, itself works. When I run the above command with -W instead of -w I am prompted for a password and the backup goes ahead nicely. I am trying to automate this command within the script and want the backup to go ahead without prompting for a password thus with the -w flag. To this end I created the following file
/home/chh1/.pgpass
When ls -la ~/.pgpass
-rw------- 1 chh1 chh1 74 Oct 15 10:00 .pgpass
Inside the file .pgpass I placed the following text:
# Server:Port:Database:Username:Password
*:*:crewdb:postgres:9Gh#$mq
However when I run the command I get the following error output and the backup fails:
pg_dump -h localhost -p 5432 -U postgres -w -C -F p -b -v -f ~/Dropbox\/postgres_backup/crewdb.backup.sql crewdb
pg_dump: [archiver (db)] connection to database "crewdb" failed: FATAL:
password authentication failed for user "postgres" password retrieved from
file "/home/chh1/.pgpass" FATAL: password authentication failed for user
"postgres" password retrieved from file "/home/chh1/.pgpass"
I basically followed the following process:
1) Create .pgpass file with content
*:*:crewdb:postgres:9Gh#$mq
2) set the permissions using command
sudo chmod 600 .pgpass
3) Set the file owner as the same user using which you logged in :
sudo chown chh1:chh1 .pgpass
4) Set PGPASSFILE environment variable :
export PGPASSFILE='/home/chh1/.pgpass'
Now when checking with
psql -h localhost -U postgres crewdb
I get a similar error:
psql: FATAL: password authentication failed for user "postgres"
password retrieved from file "/home/chh1/.pgpass"
FATAL: password authentication failed for user "postgres"
password retrieved from file "/home/chh1/.pgpass"
The following are the settings in my pg_hba.conf file:
# Database administrative login by Unix domain socket
local all postgres md5
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all peer
# IPv4 local connections:
host all all 127.0.0.1/32 md5
# IPv6 local connections:
host all all ::1/128 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all peer
host replication all 127.0.0.1/32 md5
host replication all ::1/128 md5
I do much appreciate if anyone here can put me on the right path!
hexdump -C /home/chh1/.pgpass
?hexdump -C /home/chh1/.pgpass
I get:` 00000000 23 20 68 6f 73 74 6e 61 6d 65 3a 70 6f 72 74 3a |# hostname:port:| 00000010 64 61 74 61 62 61 73 65 3a 75 73 65 72 6e 61 6d |database:usernam| 00000020 65 3a 70 61 73 73 77 6f 72 64 0a 2a 3a 2a 3a 63 |e:password.*:*:c| 00000030 72 65 77 64 62 3a 70 6f 73 74 67 72 65 73 3a 39 |rewdb:postgres:9| 00000040 47 68 23 24 6d 71 20 0a |Gh#$mq .| 00000048`