CODEWITHSUNDEEP
bashpostgresqlshellcron
mpen
mpen

Reputation: 282825

How to pass in password to pg_dump?

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:

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?

Upvotes: 498

Views: 543016

Answers (19)

not2savvy
not2savvy

Reputation: 4243

Another option to pass the database password is using the PGPASSFILE environment variable or the passfile URI parameter.

Both expect a password file in the format

hostname:port:database:username:password

for example

localhost:5432:mydatabase:dbuser:userspassword

as described in other answers for ~/.pgpass, but you are not confined to this specific path and and file name. This is especially useful if you need to run pg_dump under a user that does not have a home directory, or if you want to keep all passwords in a specific location.

The location of the password file can be provided in one of the following two ways:

PGPASSFILE environment variable

Set the environment variable PGPASSFILE before executing pg_dump.

Example:

PGPASSFILE=/opt/custom/backup/.pgpass
pg_dump -U dbuser -w mydatabase >/var/custom/backups/pg_dump.mydatabase.sql

passfile URI parameter

If you use the URI format to provide the database, you can add the passfile parameter to it.

Example:

pg_dump -w "postgresql://dbuser@localhost/mydatabase?passfile=/opt/custom/backup/.pgpass" >/var/custom/backups/pg_dump.mydatabase.sql

(Option -w or --no-password means never issue a password prompt.)

Upvotes: 2

gitaarik
gitaarik

Reputation: 46280

If you want to do it in one command:

PGPASSWORD="mypass" pg_dump mydb > mydb.dump

When you prepend a command with a VAR="val" prefix, those variables will be set as environment variables for the context of that command execution. So it won't be set globally in your shell session. Depending on your situation, it could be an appropriate way of providing a password to the pg_dump command.

Upvotes: 326

miguel_vivanco
miguel_vivanco

Reputation: 51

In windows you can set the variable with the password before use pg_dump.exe, and can automate all in a bat file, for example:

C:\>SET PGPASSWORD=dbpass
C:\>"folder_where_is_pg_dump\pg_dump.exe" -f "dump_file" -h "db_host" -U db_usr --schema "db_schema" "db_name"

Upvotes: 5

ognjenkl
ognjenkl

Reputation: 1477

For Windows the pgpass.conf file should exist on path:

%APPDATA%\postgresql\pgpass.conf

On my Windows 10 absolute path it is:

C:\Users\Ognjen\AppData\Roaming\postgresql\pgpass.conf

Note: If there is no postgresql folder in %APPDATA%, create one with pgpass.conf file inside it.

Content of pgpass.conf could be:

*:5432:*:*:myDbPassword

Or more specific content could be:

localhost:5432:dbName:username:password

Note: Content of pgpass.conf must NOT end with white spaces (after password) or the error will occur.

Upvotes: 4

araqnid
araqnid

Reputation: 133432

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.

Upvotes: 492

stefanoz
stefanoz

Reputation: 51

You just need to open pg_hba.conf and sets trust in all methods. That's works for me. Therefore the security is null.

Upvotes: 0

saintlyzero
saintlyzero

Reputation: 1842

A secure way of passing the password is to store it in .pgpass file

Content of the .pgpass file will be in the format:

db_host:db_port:db_name:db_user:db_pass

#Eg
localhost:5432:db1:admin:tiger
localhost:5432:db2:admin:tiger

Now, store this file in the home directory of the user with permissions u=rw (0600) or less

To find the home directory of the user, use echo $HOME

Restrict permissions of the file chmod 0600 /home/ubuntu/.pgpass

Upvotes: 2

Larry Spence
Larry Spence

Reputation: 471

You can pass a password into pg_dump directly by using the following:

pg_dump "host=localhost port=5432 dbname=mydb user=myuser password=mypass" > mydb_export.sql

Upvotes: 36

Fernando Meneses Gomes
Fernando Meneses Gomes

Reputation: 867

Note that, in windows, the pgpass.conf file must be in the following folder:

%APPDATA%\postgresql\pgpass.conf

if there's no postgresql folder inside the %APPDATA% folder, create it.

the pgpass.conf file content is something like:

localhost:5432:dbname:dbusername:dbpassword

cheers

Upvotes: 11

manfall19
manfall19

Reputation: 428

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.

Upvotes: 9

Rajan Verma - Aarvy
Rajan Verma - Aarvy

Reputation: 2117

This one liner helps me while creating dump of a single database.

PGPASSWORD="yourpassword" pg_dump -U postgres -h localhost mydb > mydb.pgsql

Upvotes: 102

Michael M
Michael M

Reputation: 8733

Backup over ssh with password using temporary .pgpass credentials and push to S3:

#!/usr/bin/env bash
cd "$(dirname "$0")"

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=$1
PASSWORD=$2

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.

This script deletes the credentials in .pgpass afterward because in some environments, the default SSH user can sudo without a password, for example an EC2 instance with the ubuntu user, so using .pgpass with a different host account in order to secure those credential, might be pointless.

Upvotes: 4

Dofri
Dofri

Reputation: 126

the easiest way in my opinion, this: you edit you main postgres config file: pg_hba.conf there you have to add the following line:

host <you_db_name> <you_db_owner> 127.0.0.1/32 trust

and after this you need start you cron thus:

pg_dump -h 127.0.0.1 -U <you_db_user> <you_db_name> | gzip > /backup/db/$(date +%Y-%m-%d).psql.gz

and it worked without password

Upvotes: -4

Jauyzed
Jauyzed

Reputation: 511

@Josue Alexander Ibarra answer works on centos 7 and version 9.5 if --dbname is not passed. 

pg_dump postgresql://username:[email protected]:5432/mydatabase

Upvotes: 25

Josue Alexander Ibarra
Josue Alexander Ibarra

Reputation: 8995

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

Upvotes: 246

Francisco Luz
Francisco Luz

Reputation: 2943

$ PGPASSWORD="mypass" pg_dump -i -h localhost -p 5432 -U username -F c -b -v -f dumpfilename.dump databasename

Upvotes: 59

Max
Max

Reputation: 6791

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

Upvotes: 349

Tobias
Tobias

Reputation: 2561

Correct me if I'm wrong, but if the system user is the same as the database user, PostgreSQL won't ask for the password - it relies on the system for authentication. This might be a matter of configuration.

Thus, when I wanted the database owner postgres to backup his databases every night, I could create a crontab for it: crontab -e -u postgres. Of course, postgres would need to be allowed to execute cron jobs; thus it must be listed in /etc/cron.allow, or /etc/cron.deny must be empty.

Upvotes: 5

szymond
szymond

Reputation: 1310

Another (probably not secure) way to pass password is using input redirection i.e. calling

pg_dump [params] < [path to file containing password]

Upvotes: -1

Related Questions