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!
