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?

at least on redhat, % symbols needs to be escaped. not escaping it will result in cron trying to execute your command only up until the %. See - fahdshariff.blogspot.com/2009/05/percent-sign-in-crontab.html — chrismarx
– chrismarx, Commented Apr 17, 2012 at 16:06

user100766 · Accepted Answer · 2009-05-12 18:48:35Z

19

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.

answered May 12, 2009 at 18:48

user100766

Sign up to request clarification or add additional context in comments.

3 Comments

Volomike Over a year ago

This is what I ultimately ended up doing and it worked fine only when I did chown postgres.root /opt/postgresql (where the files were going).

Clint Pachl Over a year ago

''sudo su postgres'' That's crazy! Try ''sudo -u postgres''. Setup a sudo spec, it's much safer.

Jeremy Leipzig Over a year ago

You (postgres) are not allowed to access to (crontab) because of pam configuration.

Imraan Parker · Accepted Answer · 2009-06-05 21:20:20Z

17

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 [email protected]

answered Jun 5, 2009 at 21:20

Imraan Parker

3 Comments

Imraan Parker Over a year ago

You can add '--analyze' to the /usr/bin/vacuumdb command to perform an analyze as well.

Raffael Luthiger Over a year ago

Here is another good script: wiki.postgresql.org/wiki/Automated_Backup_on_Linux

add-semi-colons Over a year ago

@Imraan Parker how do you manage to skip using the password? I want to backup as admin account of the database not the root user postgres how can I do that?

daronwolff · Accepted Answer · 2016-02-02 19:01:05Z

11

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!

answered Feb 2, 2016 at 19:01

daronwolff

2,07223 silver badges18 bronze badges

Comments

Luc M · Accepted Answer · 2009-05-12 18:47:45Z

4

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.

answered May 12, 2009 at 18:47

Luc M

17.5k27 gold badges78 silver badges91 bronze badges

4 Comments

Volomike Over a year ago

I need a little more help than that I think. I put 'env' in there before the commands and after the #!/bin/bash and all it did was dump out the environment to the log file (because I'm now dumping the output to a logfile as it runs).

user100766 Over a year ago

The way to set these variables would be 'export PG_PORT=5432' etc.

liviucmg Over a year ago

Those aren't exactly the variables used by pg_dump and vacuumdb. You can see the full list here: postgresql.org/docs/8.4/static/libpq-envars.html

Luc M Over a year ago

@bilitgates Nice!! Thank you. I've learned something today :-)

Floris · Accepted Answer · 2011-06-19 11:30:15Z

1

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.

answered Jun 19, 2011 at 11:30

Floris

111 bronze badge

Comments

Muammer Cavusoglu · Accepted Answer · 2013-02-20 13:14:05Z

0

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.

answered Feb 20, 2013 at 13:14

Muammer Cavusoglu

1

Comments

user2172798 · Accepted Answer · 2013-03-15 06:26:00Z

0

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

answered Mar 15, 2013 at 6:26

user2172798

1

Comments

Pascal Polleunus · Accepted Answer · 2013-04-04 21:08:53Z

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 |

Collectives™ on Stack Overflow

How Do I Backup My PostgreSQL Database with Cron?

8 Answers 8

3 Comments

3 Comments

Comments

4 Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

3 Comments

3 Comments

Comments

4 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related