How to pass in password to pg dump

Question

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  gt    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

User · Answer

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 password 127 0 0 1 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 amp       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 password 127 0 0 1 5432 mydatabase   then have in your crontab  0 3       pg dump --dbname  MYDB   gzip  gt    backup db   date   Y- m- d  psql gz

User · Answer

This one liner helps me while creating dump of a single database   PGPASSWORD  yourpassword  pg dump -U postgres -h localhost mydb  gt  mydb pgsql

User · Answer

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  lt you db name gt   lt you db owner gt  127 0 0 1 32 trust  and after this you need start you cron thus   pg dump -h 127 0 0 1 -U  lt you db user gt   lt you db name gt    gzip  gt   backup db   date   Y- m- d  psql gz  and it worked without password

User · Answer

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

User · Answer

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

User · Answer

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   gt  mydb export sql

User · Answer

Josue Alexander Ibarra answer works on centos 7 and version 9 5  if --dbname is not passed     pg dump postgresql   username password 127 0 0 1 5432 mydatabase

User · Answer

If you want to do it in one command   PGPASSWORD  mypass  pg dump mydb  gt  mydb dump

User · Answer

Another  probably not secure  way to pass password is using input redirection i e  calling  pg dump  params   lt   path to file containing password

User · Answer

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

User · Answer

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  gt  db dump    restore the database pg restore -d newdb db dump

User · Answer

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  my user host my domain com  BUCKET PATH  bucket name backup   if      -ne 2    then     echo  Error  2 arguments required      echo  Usage       echo    my-backup-script sh  lt DB-name gt   lt password gt       echo     lt DB-name gt    The name of the DB to backup      echo     lt password gt    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  gt     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  gt    tmp gz echo  unset remote PG password     echo               ssh   SSH HOST   cat  gt     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  gt  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

User · Answer

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

User · Answer

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

User · Answer

PGPASSWORD  mypass  pg dump -i -h localhost -p 5432 -U username -F c -b -v -f dumpfilename dump databasename

User · Answer

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

[bash] How to pass in password to pg_dump?

Examples related to bash

Examples related to postgresql

Examples related to shell

Examples related to crontab