Re: Postgres jobs will not automatically login on Mac OSX

Thanks for the tip. That got me a lot farther.

I have changed the permissions and run it as user postgres.
This time, it prompted for a password on the 2nd time through the loop.

postgres ~
$ ./pg_backup.sh
PWD: /Library/PostgreSQL/9.3
Home: /Library/PostgreSQL/9.3
BACKUP_DIR: /Users/backup/postgres/
BACKUP_DIR_DATED: /Users/backup/postgres/2013-11-21/
----- Full backups Begin -----
Backing up postgres ---> /Users/backup/postgres/2013-11-21/postgres.sql.gz
Backing up rincon ---> /Users/backup/postgres/2013-11-21/rincon.sql.gz
Password:

Here is the code:
#!/bin/bash

# Configure the configuration path
HOSTNAME=localhost
USERNAME=postgres

BACKUP_DIR=/home/backup/postgres/
if [ "$(uname)" == "Darwin" ]; then
BACKUP_DIR=/Users/backup/postgres/
fi

echo -e "PWD: "`pwd`
echo -e "Home: $HOME"
echo -e "BACKUP_DIR: $BACKUP_DIR"

# If the directory could not be created, display error and abort
BACKUP_DIR_DATED=$BACKUP_DIR"`date +\%Y-\%m-\%d`/"
echo -e "BACKUP_DIR_DATED: $BACKUP_DIR_DATED"
if ! mkdir -p $BACKUP_DIR_DATED; then
echo -e "Cannot create backup directory in $BACKUP_DIR_DATED. Go and fix
it!"
exit 1;
fi;

# Get List of Schemas
echo -e "----- Full backups Begin -----"
FULL_BACKUP_QUERY="select datname from pg_database where datname not like
'template%';"
FULL_BACKUP_QUERY_LIST=`psql -h "$HOSTNAME" -U "$USERNAME" -At -c
"$FULL_BACKUP_QUERY" postgres`

# If the list of Schemas is empty, display error and abort
if [ "$FULL_BACKUP_QUERY_LIST" = "" ]; then
echo -e "No schemas returned from database. This could be a password
issue."
exit 1
fi

# Loop through each schema
*for DATABASE in $FULL_BACKUP_QUERY_LIST*
*do*
BACKUP_FILE="$BACKUP_DIR_DATED$DATABASE.sql.gz"
echo -e "Backing up $DATABASE \t---> $BACKUP_FILE"
*if ! pg_dump -Fp -h "$HOSTNAME" -U "$USERNAME" "$DATABASE" | gzip >
$BACKUP_FILE.in_progress; then*
echo -e "ERROR: Failed to backup $DATABASE"
else
mv $BACKUP_FILE.in_progress $BACKUP_FILE
fi
done
echo -e "----- Full backups End -----"

This time, it prompted for a password on the 2nd time through the loop.
How do I stop it from prompting for a password on the 2nd time through the
loop?

On Thu, Nov 21, 2013 at 2:42 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Patrick Lademan <mjfrog14(at)gmail(dot)com> writes:
> > I forgot to mention... for testing purposes, I chmod 777 .pgpass
> > Since everyone can read the file, postgres should be able to read it too.
>
> Well, that's pretty silly. You might as well use TRUST mode for local
> connections. Anyway, postgres will not consult a .pgpass file that has
> such loose permissions. This is not a bug, it's entirely intentional.
>
> regards, tom lane
>