After a year I have restarted work on a project using Postgresql. The database I created was in Postgresql 8.2.5. I updated my database server from Ubuntu 7.10 to 10.4 (lucid) and installed Postgresql 9.0.2 The Postgresql install went fine and the restore from my previous pg_dumpall went fine but I have an issue with the backup script.
I have made the pgpass file as root but I cannot get the backup script to run as root I must change to a postgresql/ubuntu user engineering and then the script runs. Previously the backup script ran from cron.daily . I need to know what I am not doing correctly for this to work. I do not have much scripting experience.
The error message is
root@claLinus:/# source /etc/cron.daily/postgresql-backup.sh
pg_dumpall: could not connect to database "template1": FATAL: Ident authentication failed for user "engineering"
I have confirmed that .pgpass exists
root@claLinus:/# locate .pgpass
/home/engineering/.pgpass
/home/mmg/.pgpass
/root/.pgpass
Below is my backup script and my instructions for creating the pgpass that came from this list several years ago
Thank you for any help.
Margaret
*** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** ***
#!/bin/bash
#file is /etc/cron.daily/postgresql-backup.sh
#store the file name into a variable
numdate=$(date +%F)-$(date +%k%M)owl.sql
#remove spaces from the file name
numdate=${numdate/ /}
#backup the database
pg_dumpall -U engineering --column-inserts --attribute-inserts > /tmp/$numdate
# record in the log it was done
echo $(date) PID $$ backup completed >> /var/log/messages
#ftp the file
ftp -inv 192.168.100.29<<ENDFTP
user backupftp password
cd ./backupLinus/
lcd /tmp
put $numdate
bye
ENDFTP
*** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** *** ***
#instructions for making the .pgpass file
touch ~/.pgpass
chmod 0600 ~/.pgpass
echo '*:*:*:username:password' > ~/.pgpass
Note that this presumes you are logged in as the user who will be dumping the database(s).