Tags | postgresql | postgres

PostgreSQL cronjob

Backing up your PostgreSQL server with a cron job can be done by using the following steps.

First, you need to decide WHO is going to backup the database. The who in this case means what local user account you are going to run the cronjob as.

Inside that users home folder you need to create a file called .pgpass

Here is what the file should look like:

What that means can be found on the postgres site but in short you need to name the database, the port the database is listening on, what database you want to have access to (wild card is ok here), the username to connect as and the password.

pg_dump and pg_dumpall will NOT work if the file does not have the following permission:

chmod 0600 .pgpass

Now in the crontab you just the following example where I backup once a day:

* 23 * * * pg_dumpall -U postgres -h localhost > PG_Database.$(date "+%Y%m%d").backup

That will make a file with the date that contains all of the databases on your server.

Need more info, just ask below!