From: | Patrick Lademan <mjfrog14(at)gmail(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Postgres jobs will not automatically login on Mac OSX |
Date: | 2013-11-21 16:49:38 |
Message-ID: | CADMm_6Ay7M4=QHcvsYG-iYbSwPsCP-DmaXjZBivp4Unhe=mVPQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
Hi All,
I have been trying to setup a simple backup job to backup up the individual
schemas but I cannot get it postgres to utilize the .pgpass password file.
Mac:
System Version: OS X 10.9 (13A603)
Kernel Version: Darwin 13.0.0
Postgres:
PostgreSQL 9.3.1 on x86_64-apple-darwin, compiled by
i686-apple-darwin11-llvm-gcc-4.2 (GCC) 4.2.1 (Based on Apple Inc. build
5658) (LLVM build 2336.9.00), 64-bit
I have copied the .pgpass to many different directories (eg. my home,
postgres home, etc) but that had no affect. After trying many different
permutations with environment variables etc, I have put the script and
.pgpass files in the /Users/postgres/script directory. The script contains
PGPASSFILE=/Users/postgres/script/.pgpass
Here is a sample of the .pgpass file (password deleted):
localhost:5432:postgres:postgres:XXXXXX
When I run the pg_backup.sh as root, postgres prompts me for a password on
every transaction and creates the backups as expected. When I run the
script as any other user, postgres fails to authenticate.
Here is the pg_backup.sh file that I harvested from an online post:
#!/bin/bash
# Configure the configuration path
PGPASSFILE=/Users/postgres/script/.pgpass
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 "$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 -----"
Here are the results of the script:
plademan(at)Patricks-MacBook-Pro /Users/postgres/script
$ ./pg_backup.sh
PWD: /Users/postgres/script
Home: /Users/plademan
BACKUP_DIR: /Users/backup/postgres/
BACKUP_DIR_DATED: /Users/backup/postgres/2013-11-21/
----- Full backups Begin -----
psql: FATAL: password authentication failed for user "postgres"
No schemas returned from database. This could be a password issue.
plademan(at)Patricks-MacBook-Pro /Users/postgres/script
$
How do I configure postgres to utilize the .pgpass file?
Thank you,
Pat
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2013-11-21 17:33:43 | Re: Postgres jobs will not automatically login on Mac OSX |
Previous Message | Oliver Munyao | 2013-11-21 15:29:08 | pgdump not dumping my database |