0

What I'm trying to accomplish is having a pg_dump script run as a cron job. Right now I'm just trying to get the script to run by invoking it myself at the command line. I've searched the postgresql mailing lists and followed the postgresql documentation with no success on a solution. I've looked on StackOverflow as well, but after reading a similar post on StackOverflow it seems like this sort of problem should be on this site.

System Specs:

RHEL5

Postgresql 9.0.3

This is what I do:

script is located here ~/database_backup/pg_dump_script

I run this from the directory

./pg_dump_script.sh

The script looks like this:

/home/myusername/lappstack-1.2-5/postgresql/bin/pg_dump --host=localhost 
--port 5433 --username "myusername" --no-password  --format plain --inserts 
--column-inserts --verbose
--file "/home/username/database_backup/$(%m.%d.%y_%1.%M%P).sql"
--table "schema.table_name" --table "schema.table_name1" "db_name"

This script is of course modified, and not the actual one, but gives you a sense of what I'm doing. I've tried other suggested host names: 127.0.0.1, 10.32.123.22 (not the real IP address of course).The port is correct. 5433 is what is in the pg_hba.conf

Error I receive:

./pg_dump_script.sh
" failed: fe_sendauth: no password supplieddatabase "
pg_dump.bin: *** aborted because of error
./pg_dump_script.sh: line 2: fg: no job control
./pg_dump_script.sh: line 2: --file: command not found
./pg_dump_script.sh: line 3: --table: command not found
./pg_dump_script.sh: line 4: --table: command not found
./pg_dump_script.sh: line 5: --table: command not found
./pg_dump_script.sh: line 6: --table: command not found
./pg_dump_script.sh: line 7: --table: command not found
./pg_dump_script.sh: line 8: --table: command not found
./pg_dump_script.sh: line 9: --table: command not found
./pg_dump_script.sh: line 10: --table: command not found
./pg_dump_script.sh: line 11: --table: command not found
./pg_dump_script.sh: line 12: --table: command not found
./pg_dump_script.sh: line 13: --table: command not found
./pg_dump_script.sh: line 14: --table: command not found
./pg_dump_script.sh: line 15: --table: command not found
./pg_dump_script.sh: line 16: --table: command not found
./pg_dump_script.sh: line 17: --table: command not found
./pg_dump_script.sh: line 18: --table: command not found
./pg_dump_script.sh: line 19: --table: command not found
./pg_dump_script.sh: line 20: --table: command not found
./pg_dump_script.sh: line 21: --table: command not found
./pg_dump_script.sh: line 22: --table: command not found
./pg_dump_script.sh: line 23: --table: command not found
./pg_dump_script.sh: line 24: --table: command not found
./pg_dump_script.sh: line 25: --table: command not found
./pg_dump_script.sh: line 26: --table: command not found
./pg_dump_script.sh: line 27: --table: command not found
./pg_dump_script.sh: line 28: --table: command not found
./pg_dump_script.sh: line 29: --table: command not found
./pg_dump_script.sh: line 30: --table: command not found
./pg_dump_script.sh: line 31: --table: command not found
./pg_dump_script.sh: line 32: --table: command not found
./pg_dump_script.sh: line 33: --table: command not found
./pg_dump_script.sh: line 34: --table: command not found
./pg_dump_script.sh: line 35: --table: command not found
./pg_dump_script.sh: line 36: --table: command not found
./pg_dump_script.sh: line 37: --table: command not found
./pg_dump_script.sh: line 38: --table: command not found
./pg_dump_script.sh: line 39: --table: command not found
./pg_dump_script.sh: line 40: --table: command not found
./pg_dump_script.sh: line 41: --table: command not found
./pg_dump_script.sh: line 42: --table: command not found
./pg_dump_script.sh: line 43: --table: command not found
./pg_dump_script.sh: line 44: --table: command not found

Other files that were needed to be configured

I have a .pgpass in my ~ directory. Following this convention:

http://www.postgresql.org/docs/9.0/static/libpq-pgpass.html

I also made sure I did chmod 0600 ~/.pgpass

I am able to run pg_dump using PgAdmin3 on my windows machine. So I don't think it's a connection issue. I am also able to run psql without supplying a password, so this should mean the pgpass is working correctly?

Let me know if anymore information is needed!

Updates

I've been using everyone's suggestions and I've gotten to the point where I'm debugging one parameter at a time.

This is what I have: 

/home/username/lappstack-1.2-5/postgresql/bin/pg_dump --host=127.0.0.1 --port=5433 --username=myusername mydb_name

This will give the error:

$ ./pg_dump_script.sh
Password:
" does not exist  database "my_dbnamenection to database "mydb_name

^ It doesn't output the error correctly. That's not a spelling mistake. I just replaced the actual db name with "mydb_name".

I have it prompting for the password, which it does and I then enter my password. I appreciate the comments. They are definitely helping with the troubleshooting process and will save me time in the future!

Ladadadada
  • 26,337
  • 7
  • 59
  • 90
Tim Sanders
  • 119
  • 1
  • 4
  • 1
    Have you tried manually supplying the password? – voretaq7 May 29 '13 at 20:08
  • Yes, and now I'm receiving a different error. I've cut down the script code to be just a few parameters. Please see the update code. I've also tried it with the parameter --no-password I will then get the same error. – Tim Sanders May 30 '13 at 20:10
  • I can also copy and past the code directly from the script and run it from the command line and everything works fine. There is something about running it from a script that gives an error. – Tim Sanders May 31 '13 at 12:25

2 Answers2

2

According to your error messages, you should try using backslashes \:


/home/myusername/lappstack-1.2-5/postgresql/bin/pg_dump --host=localhost \
--port 5433 --username "myusername" --no-password  --format plain --inserts \
--column-inserts --verbose \
--file "/home/username/database_backup/$(%m.%d.%y_%1.%M%P).sql" \
--table "schema.table_name" --table "schema.table_name1" "db_name"
  • I'm on Red Hat Linux. They should be forward slashes no? I believe the error message is "failed: fe_sendauth: no password supplieddatabase". – Tim Sanders May 30 '13 at 18:34
  • backslashes should be added before newlines. http://www.gnu.org/software/bash/manual/html_node/Escape-Character.html –  May 30 '13 at 19:44
  • Yes! That's right. I copied and pasted this from when I do the backup from pgadmin3. It spits out a script. Didn't think about the new line. I took another approach to troubleshooting this by adding one parameter at a time. I've updated the issue. – Tim Sanders May 30 '13 at 19:55
1

The problem was that there were DOS carriage returns. I was using putty and apparently DOS encoding was making it's way into the script. What I did was run dos2unix on the script. I had posted this question on stackoverflow after seeing that it was more of a scripting issue than DB administration. Here is the URL for the question and solution worked out. The real credit goes to chepner & mbratch. Thanks guys!

https://stackoverflow.com/questions/16857726/linux-bash-script-doesnt-run-correctly-but-when-run-at-the-command-line-everyt?noredirect=1#comment24313493_16857726

Community
  • 1
Tim Sanders
  • 119
  • 1
  • 4