Re: Postgres jobs will not automatically login on Mac OSX - Mailing list pgsql-bugs

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@sss.pgh.pa.us> wrote:

> Patrick Lademan <mjfrog14@gmail.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
>