Tutorial :How Do I Backup My PostgreSQL Database with Cron?


I can run commands like vacuumdb, pg_dump, and psql just fine in a script if I preface them like so:

/usr/bin/sudo -u postgres /usr/bin/pg_dump -Fc mydatabase > /opt/postgresql/prevac.gz  /usr/bin/sudo -u postgres /usr/bin/vacuumdb --analyze mydatabase  /usr/bin/sudo -u postgres /usr/bin/pg_dump -Fc mydatabase > /opt/postgresql/postvac.gz  SCHEMA_BACKUP="/opt/postgresql/$(date +%w).db.schema"  sudo -u postgres /usr/bin/pg_dump -C -s mydatabase > $SCHEMA_BACKUP  

These run at command line on Redhat when I am sudo to root and then as you see in the commands above I do a sudo -u to postgres.

But when I try to kick this off from cron, I get zero bytes in all the files -- meaning it didn't run properly. And I don't get a clue in the logs that I can see.

My /etc/crontab file has this entry at the bottom

00 23 * * * root /etc/db_backup.cron  

And yes, /etc/db_backup.cron is chmod ug+x, owned by root, and the top of the file says "#!/bin/bash" (minus doublequotes).

Anyone know what gives?


Since you seem to have superuser rights anyway, you could put those commands into the crontab of the postgres user like so:

sudo su postgres  crontab -e  

and then put the pg_dump/vacuumdb commands there.


I have a dynamic bash script that backs up all the databases on the server. It gets a list of all the databases and then vacuums each DB before performing a backup. All logs are written to a file and then that log is emailed to me. This is something you could use if you want.

Copy the code below into a file and add the file to your crontab. I have setup my pg_hba.conf to trust local connections.

#!/bin/bash  logfile="/backup/pgsql.log"  backup_dir="/backup"  touch $logfile  databases=`psql -h localhost -U postgres -q -c "\l" | sed -n 4,/\eof/p | grep -v rows\) | grep -v template0 | grep -v template1 | awk {'print $1'}`    echo "Starting backup of databases " >> $logfile  for i in $databases; do          dateinfo=`date '+%Y-%m-%d %H:%M:%S'`          timeslot=`date '+%Y%m%d%H%M'`          /usr/bin/vacuumdb -z -h localhost -U postgres $i >/dev/null 2>&1          /usr/bin/pg_dump -U postgres -i -F c -b $i -h -f $backup_dir/$i-database-$timeslot.backup          echo "Backup and Vacuum complete on $dateinfo for database: $i " >> $logfile  done  echo "Done backup of databases " >> $logfile    tail -15 /backup/pgsql.log | mailx youremail@domain.com  


Your environment variable are maybe not set in cron.

In your normal session, you probably have defined these variables:


Add an "env" into yout script.


I have set my cron like this. Every 59 minutes since monday to friday

*/59 * * * 1-5 sh /home/my_user/scripts/back_my_bd.sh   

The script to run the backup is inside back_my_bd.sh file and the content is:

pg_dump -U USERDATABASE DATABASENAME > /home/my_user/sql/mybackup.sql  

And i created the .pgpass file inside home directory to allow the backup whithout specify the user and password


Sorry my english is not good!


you probably have "ident" authentication in your pg_hba.conf for your postgres user. The option "-u postgres" fails when that is the case. either change user to postgres in your backup script or configure a different authentication method.


Instead of the following command: databases=psql -h localhost -U postgres -q -c "\l" | sed -n 4,/\eof/p | grep -v rows\) | grep -v template0 | grep -v template1 | awk {'print $1'}

You can use below: databases=psql -t -c "select datname from pg_database where datname not like 'template%';" | grep -v '^$'

The first one return '|' for template databases and an empty line.

The second one is cleaner.


databases=psql -h localhost -U postgres -q -x -t -c "\l" | grep 'Name' | sed 's/ //g' | sed 's/Name|//g'


Another version to get the list of databases:
psql -lqt | grep -vE '^ +(template[0-9]+|postgres)? *\|' | cut -d'|' -f1| sed -e 's/ //g' -e '/^$/d'

As my psql -lqt output is:

 abcdefghij         | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |    postgres           | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |    template0          | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +                      |          |          |             |             | postgres=CTc/postgres   template1          | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +                      |          |          |             |             | postgres=CTc/postgres   abc                | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |   

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Next Post »