How to pass in password to pg_dump?
If you want to do it in one command:
PGPASSWORD="mypass" pg_dump mydb > mydb.dump
Create a .pgpass
file in the home directory of the account that pg_dump
will run as.
The format is:
hostname:port:database:username:password
Then, set the file's mode to 0600
. Otherwise, it will be ignored.
chmod 600 ~/.pgpass
See the Postgresql documentation libpq-pgpass for more details.
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"
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 -Fc
generates the most flexible export format and is already compressed. For more info see: pg_dump documentation
E.g.
# dump the database in custom-format archive
pg_dump -Fc mydb > db.dump
# restore the database
pg_restore -d newdb db.dump
For a one-liner, like migrating a database you can use --dbname
followed by a connection string (including the password) as stated in the pg_dump manual
In essence.
pg_dump --dbname=postgresql://username:[email protected]:5432/mydatabase
Note: Make sure that you use the option --dbname
instead of the shorter -d
and use a valid URI prefix, postgresql://
or postgres://
.
The general URI form is:
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 .pgpass
file I would save the connection string as an environment variable.
export MYDB=postgresql://username:[email protected]:5432/mydatabase
then have in your crontab
0 3 * * * pg_dump --dbname=$MYDB | gzip > ~/backup/db/$(date +%Y-%m-%d).psql.gz