我想要实现的是让 pg_dump 脚本作为 cron 作业运行。现在我只是试图通过在命令行中自己调用脚本来运行它。我搜索了 postgresql 邮件列表并按照 postgresql 文档进行操作,但没有找到解决方案。我也查看了 StackOverflow,但在阅读了 StackOverflow 上的类似帖子后,似乎这种问题应该出现在这个网站上。
系统规格:
RHEL5
PostgreSQL 9.0.3
这是我的做法:
脚本位于此处~/database_backup/pg_dump_script
我从目录运行它
./pg_dump_script.sh
脚本如下:
/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"
这个脚本当然是经过修改的,而不是实际的,但可以让您了解我在做什么。我尝试了其他建议的主机名:127.0.0.1、10.32.123.22(当然不是真正的 IP 地址)。端口是正确的。5433 是 pg_hba.conf 中的内容
我收到的错误:
./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
其他需要配置的文件
.pgpass
我的 ~ 目录中有一个。遵循以下约定:
http://www.postgresql.org/docs/9.0/static/libpq-pgpass.html
我也确保我做到了chmod 0600 ~/.pgpass
我可以pg_dump
在 Windows 机器上使用 PgAdmin3 运行。因此我认为这不是连接问题。我也可以psql
不提供密码运行,所以这应该意味着它pgpass
工作正常吗?
如果需要更多信息,请告诉我!
更新
我一直在使用大家的建议,并且已经到了一次调试一个参数的地步。
这就是我所拥有的:
/home/username/lappstack-1.2-5/postgresql/bin/pg_dump --host=127.0.0.1 --port=5433 --username=myusername mydb_name
这将给出错误:
$ ./pg_dump_script.sh
Password:
" does not exist database "my_dbnamenection to database "mydb_name
^ 它没有正确输出错误。这不是拼写错误。我只是用“mydb_name”替换了实际的数据库名称。
我让它提示输入密码,它确实输入了密码,然后我输入了密码。我很感谢这些评论。它们确实对故障排除过程有帮助,并将在未来节省我的时间!
答案1
根据您的错误消息,您应该尝试使用反斜杠\:
/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"
答案2
问题是有 DOS 回车符。我使用的是 putty,显然 DOS 编码正在进入脚本。我所做的就是在脚本上运行 dos2unix。在发现这更多的是脚本问题而不是数据库管理问题后,我将这个问题发布在 stackoverflow 上。这是问题和解决方案的 URL。真正的功劳归于 chepner 和 mbratch。谢谢大家!