Re: Postgres jobs will not automatically login on Mac OSX

Thanks for your help. I added printing the Hostname and Username to the
beginning of the script. Everything is the same.

I tried the -x option but since pgagent only logs errors, no additional
information was returned.

Here is the run from the command line:
whoami: postgres
PWD: /Library/PostgreSQL/9.3
HOME: /Library/PostgreSQL/9.3
BACKUP_DIR: /Users/backup/postgres/
HOSTNAME: localhost
USERNAME: postgres
BACKUP_DIR_DATED: /Users/backup/postgres/2013-11-22/
----- Full backups Begin -----
Backing up postgres ---> /Users/backup/postgres/2013-11-22/postgres.sql.gz
Backing up rincon ---> /Users/backup/postgres/2013-11-22/rincon.sql.gz
----- Full backups End -----

Here is the run from pgagent initiated by run now in pgadmin:
665 Failed 1 Fri Nov 22 12:55:44 2013 Fri Nov 22 12:55:44 2013
00:00:00.017515
whoami: postgres
PWD: /Library/PostgreSQL/9.3
HOME: /Library/PostgreSQL/9.3
BACKUP_DIR: /Users/backup/postgres/
HOSTNAME: localhost
USERNAME: postgres
BACKUP_DIR_DATED: /Users/backup/postgres/2013-11-22/
----- Full backups Begin -----
No schemas returned from database. This could be a password issue.

What am I missing to get this backup job to run correctly from pgagent?

Thanks,

Pat

On Fri, Nov 22, 2013 at 12:35 PM, David Kerr <dmk(at)mr-paradox(dot)net> wrote:

> On Thu, Nov 21, 2013 at 07:11:46PM -0500, Patrick Lademan wrote:
> - That worked!!! Thanks!!!
> -
> - I have one last problem.
> -
> - It works correctly at the command line:
> - postgres ~
> - $ ./pg_backup.sh
> -
> - User: postgres
> - 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
> - ----- Full backups End -----
> -
> - When I run it as a job in postgres, it fails. Here are the results:
> - 662 Failed 1 Thu Nov 21 19:02:38 2013 Thu Nov 21 19:02:38 2013
> - 00:00:00.016344
> - User: postgres
> - 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 -----
> - No schemas returned from database. This could be a password issue.
> -
> - It is behaving like it could not login to get the schema list.
> - Since prompting for a password is not an error message, it is not
> displayed
> - in the error log.
> - How can I debug this job?
>
>
> As a sanity check you might want to add the following to the if statement.
> echo "Hostname: ${HOSTNAME}"
> echo "Username: ${USERNAME}"
>
> Those may be getting lost when the job is run via cron.
>
> For example:
> # If the list of Schemas is empty, display error and abort
> if [ "$FULL_BACKUP_QUERY_LIST" = "" ]; then
> echo "Hostname: ${HOSTNAME}" # <-------Add
> echo "Username: ${USERNAME}" # <-------Add
> echo -e "No schemas returned from database. This could be a password
> issue."
> exit 1
> fi
>
> Alternativly, add -x to the top line to enable debugging
> #!/bin/bash -x
>
> and then in your cron job output stdout and stderr to a file
> 0 0 0 0 0 0 backupjob.sh >/tmp/backup.out 2>&1
>