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



Question:

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?


Solution:1

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.


Solution:2

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


Solution:3

Your environment variable are maybe not set in cron.

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

PG_PORT  PG_HOST  PG_DATABASE  PG_USERNAME  PG_PASSWORD  

Add an "env" into yout script.


Solution:4

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

localhost:5432:DATABASENAME:USER:PASSWORD  

Sorry my english is not good!


Solution:5

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.


Solution:6

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.


Solution:7

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


Solution:8

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
Previous
Next Post »