Automated Backup for PostgreSQL Using Cron Job (Crontab) on Ubuntu Server 18.04.2

If you guys are using PostgreSQL as your database server and you want to automate the backup process of your database in a daily basis this article will help you do it.

First of all, after you install Ubuntu Server 18.04.2 make sure to check the current date and time of the server. If the date and time is not correct or not aligned with your local time then change it.

We will use the crontab owned by the "postgres linux user". Bear in mind that "postgres linux user" is different from "postgres database user".

By default (after installation) the postgres linux user password is set to "ident" meaning, there's no password.

You need to set the password for the postgres linux user so that you can access the PostgreSQL database that you want to automate the backup. You can change it by typing the following command:

'sudo passwd postgres'

After you set the password type the following command to login the postgres linux user:

'su - postgres"

Then followed by this command to open the postgres crontab:

'crontab -e'

• If it asked you what editor you want to use choose number 1 (Nano) because it is the easiest to use).

At the end of the crontab type the following:

'59 11 * * 1 pg_dump -U postgres dbname > /media/partition2/backup/monday.bak'

59 - this is minute (0-59)

11 - this is hour (0-23, 0 = midnight)

First (*) sign - this is day (1-31)

Second (*) sign - this is month (1-12)

1 - this is weekday (0-6, 0 = Sunday)

dbname - is the name of the database that you want to backup

monday.bak - is the name of the backup

I point the backup to be saved on /media/partition2/backup/ which is the secondary hard disk partition of Ubuntu Server.

To check if you got permission on the location that you want to save your backup just issue this command to check (just change the path and backup name):

'pg_dump -U postgres dbname > /media/partition2/backup/monday.bak'

If you got a "permission denied" error then you have to write permission on the folder that you want to save your backup.

If you want to have permission just issue this command (use postgres if you want the linux user "postgres" to have access:

'sudo chown -R postgres:postgres /path/to/the/folder'

Try typing this command again to check if you got permission now (just change the path and the backup name:

'pg_dump -U postgres dbname > /media/partition2/backup/monday.bak'

If there's no error then you already have permission.

Go and check the folder if the backup is successfully copied.

It's done.

---

I am an I.T. professional (Computer Engineer) working in a private company, a blogger, a father and a husband.