bash 如何将密码传递给 pg_dump？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2893954/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to pass in password to pg_dump?
提问by mpen
I'm trying to create a cronjob to back up my database every night before something catastrophic happens. It looks like this command should meet my needs:
我每天晚上都在尝试创建一个 cronjob 来备份我的数据库，以免发生灾难性事件。看起来这个命令应该满足我的需求：
0 3 * * * pg_dump dbname | gzip > ~/backup/db/$(date +%Y-%m-%d).psql.gz
Except after running that, it expects me to type in a password. I can't do that if I run it from cron. How can I pass one in automatically?
除了运行之后，它希望我输入密码。如果我从 cron 运行它，我就不能这样做。我怎样才能自动通过一个？
回答by araqnid
Create a .pgpassfile in the home directory of the account that pg_dumpwill run as. See Postgresql documentation libpq-pgpassfor details of the format (including the last paragraph where it explains it will be ignored if you don't set the mode to 0600).
.pgpass在pg_dump将运行的帐户的主目录中创建一个文件。有关格式的详细信息，请参阅 Postgresql 文档libpq-pgpass（包括最后一段，其中解释了如果不将模式设置为 将被忽略0600）。
回答by Max
Or you can set up crontab to run a script. Inside that script you can set an environment variable like this:
export PGPASSWORD="$put_here_the_password"
或者您可以设置 crontab 来运行脚本。在该脚本中，您可以像这样设置环境变量：
export PGPASSWORD="$put_here_the_password"
This way if you have multiple commands that would require password you can put them all in the script. If the password changes you only have to change it in one place (the script).
这样，如果您有多个需要密码的命令，您可以将它们全部放入脚本中。如果密码更改，您只需在一处（脚本）更改密码。
And I agree with Joshua, using pg_dump -Fcgenerates the most flexible export format and is already compressed. For more info see: pg_dump documentation
我同意 Joshua 的观点，使用pg_dump -Fc生成最灵活的导出格式并且已经被压缩。有关更多信息，请参阅：pg_dump 文档
E.g.
例如
# dump the database in custom-format archive
pg_dump -Fc mydb > db.dump
# restore the database
pg_restore -d newdb db.dump
回答by gitaarik
If you want to do it in one command:
如果您想在一个命令中执行此操作：
PGPASSWORD="mypass" pg_dump mydb > mydb.dump
回答by Josue Alexander Ibarra
For a one-liner, like migrating a database you can use --dbnamefollowed by a connection string (including the password) as stated in the pg_dump manual
对于单行，如迁移数据库，您可以使用--dbname后跟pg_dump 手册中所述的连接字符串（包括密码）
In essence.
在本质上。
pg_dump --dbname=postgresql://username:[email protected]:5432/mydatabase
pg_dump --dbname=postgresql://username:[email protected]:5432/mydatabase
Note:Make sure that you use the option --dbnameinstead of the shorter -dand use a valid URI prefix, postgresql://or postgres://.
注意：确保您使用该选项--dbname而不是较短的选项-d并使用有效的 URI 前缀，postgresql://或postgres://.
The general URI form is:
一般的 URI 形式是：
postgresql://[user[:password]@][netloc][:port][/dbname][?param1=value1&...]
postgresql://[user[:password]@][netloc][:port][/dbname][?param1=value1&...]
Best practice in your case (repetitive task in cron) this shouldn't be done because of security issues. If it weren't for .pgpassfile I would save the connection string as an environment variable.
在您的情况下的最佳实践（cron 中的重复任务）由于安全问题不应该这样做。如果不是.pgpass文件，我会将连接字符串保存为环境变量。
export MYDB=postgresql://username:[email protected]:5432/mydatabase
export MYDB=postgresql://username:[email protected]:5432/mydatabase
then have in your crontab
然后在你的 crontab 中
0 3 * * * pg_dump --dbname=$MYDB | gzip > ~/backup/db/$(date +%Y-%m-%d).psql.gz
0 3 * * * pg_dump --dbname=$MYDB | gzip > ~/backup/db/$(date +%Y-%m-%d).psql.gz
回答by Francisco Luz
$ PGPASSWORD="mypass" pg_dump -i -h localhost -p 5432 -U username -F c -b -v -f dumpfilename.dump databasename
回答by Aarvy
This one liner helps me while creating dump of a single database.
在创建单个数据库的转储时，这个衬垫可以帮助我。
PGPASSWORD="yourpassword" pg_dump -U postgres -h localhost mydb > mydb.pgsql
回答by Jauyzed
@Josue Alexander Ibarra answer works on centos 7 and version 9.5 if --dbname is not passed.
如果 --dbname 未通过，@Josue Alexander Ibarra 的回答适用于 centos 7 和 9.5 版。
pg_dump postgresql://username:[email protected]:5432/mydatabase
回答by Fernando Meneses Gomes
Note that, in windows, the pgpass.conffile must be in the following folder:
请注意，在 Windows 中，该pgpass.conf文件必须位于以下文件夹中：
%APPDATA%\postgresql\pgpass.conf
if there's no postgresqlfolder inside the %APPDATA%folder, create it.
如果postgresql文件夹内没有文件%APPDATA%夹，则创建它。
the pgpass.conffile content is something like:
该pgpass.conf文件内容是这样的：
localhost:5432:dbname:dbusername:dbpassword
cheers
干杯
回答by MikeM
Backup over ssh with password using temporary .pgpass credentials and push to S3:
使用临时 .pgpass 凭据通过 ssh 备份密码并推送到 S3：
#!/usr/bin/env bash
cd "$(dirname "##代码##")"
DB_HOST="*******.*********.us-west-2.rds.amazonaws.com"
DB_USER="*******"
SSH_HOST="[email protected]_domain.com"
BUCKET_PATH="bucket_name/backup"
if [ $# -ne 2 ]; then
echo "Error: 2 arguments required"
echo "Usage:"
echo " my-backup-script.sh <DB-name> <password>"
echo " <DB-name> = The name of the DB to backup"
echo " <password> = The DB password, which is also used for GPG encryption of the backup file"
echo "Example:"
echo " my-backup-script.sh my_db my_password"
exit 1
fi
DATABASE=
PASSWORD=
echo "set remote PG password .."
echo "$DB_HOST:5432:$DATABASE:$DB_USER:$PASSWORD" | ssh "$SSH_HOST" "cat > ~/.pgpass; chmod 0600 ~/.pgpass"
echo "backup over SSH and gzip the backup .."
ssh "$SSH_HOST" "pg_dump -U $DB_USER -h $DB_HOST -C --column-inserts $DATABASE" | gzip > ./tmp.gz
echo "unset remote PG password .."
echo "*********" | ssh "$SSH_HOST" "cat > ~/.pgpass"
echo "encrypt the backup .."
gpg --batch --passphrase "$PASSWORD" --cipher-algo AES256 --compression-algo BZIP2 -co "$DATABASE.sql.gz.gpg" ./tmp.gz
# Backing up to AWS obviously requires having your credentials to be set locally
# EC2 instances can use instance permissions to push files to S3
DATETIME=`date "+%Y%m%d-%H%M%S"`
aws s3 cp ./"$DATABASE.sql.gz.gpg" s3://"$BUCKET_PATH"/"$DATABASE"/db/"$DATETIME".sql.gz.gpg
# s3 is cheap, so don't worry about a little temporary duplication here
# "latest" is always good to have because it makes it easier for dev-ops to use
aws s3 cp ./"$DATABASE.sql.gz.gpg" s3://"$BUCKET_PATH"/"$DATABASE"/db/latest.sql.gz.gpg
echo "local clean-up .."
rm ./tmp.gz
rm "$DATABASE.sql.gz.gpg"
echo "-----------------------"
echo "To decrypt and extract:"
echo "-----------------------"
echo "gpg -d ./$DATABASE.sql.gz.gpg | gunzip > tmp.sql"
echo
Just substitute the first couple of config lines with whatever you need - obviously. For those not interested in the S3 backup part, take it out - obviously.
只需用您需要的任何内容替换前几行配置行 - 显然。对于那些对 S3 备份部分不感兴趣的人，把它拿出来——很明显。
This script deletes the credentials in .pgpassafterward because in some environments, the default SSH user can sudo without a password, for example an EC2 instance with the ubuntuuser, so using .pgpasswith a different host account in order to secure those credential, might be pointless.
此脚本.pgpass随后会删除凭据，因为在某些环境中，默认 SSH 用户可以在没有密码的情况下执行 sudo，例如ubuntu用户的 EC2 实例，因此使用.pgpass不同的主机帐户来保护这些凭据可能毫无意义。
回答by manfall19
As detailed in this blog post, there are two ways to non interactively provide a password to PostgreSQL utilities such as the "pg_dump" command: using the ".pgpass"file or using the "PGPASSWORD"environment variable.
正如这篇博客文章中所详述的，有两种方法可以非交互地向 PostgreSQL 实用程序（例如“pg_dump”命令）提供密码：使用“.pgpass”文件或使用“PGPASSWORD”环境变量。
