Automating backup of PostgreSQL database

PostgreSQL is very popular open source relational databse. It is widely used with Django and serveral other open source web frameworks.

Among, several other tasks of database management, backing up database is most important. We need to have backup of our database to ensure we can restore, if we loose our current db accidently.

Though, backup can be done manually, its a good idea to automate this taks. Because of 2 reasons:

It's a regular job
It's critical and humans might forget to create backup on someday.

In this article I'll show you how can you backup your PostgreSQL database and automate this taks, so it happens daily by itself.

Server ethernet

Backup all database

To backup databases we need to login as postgres user first.

su - postgres

Now we can create backup by following command.

pg_dumpall > pg_backup.bak

This will create a backup file with name pg_backup.bak in current working directory. If you want to create this backup file in different directory, you can specify full path after >

There are multiple formats available in which we can generate backup.

*.bak - This generates a compressed binary file that contains backup.
*.sql - This will create a plain text backup file.
*.tar - This will create a tarball file of backup.

Restoring all databases from backup

To restore all databases from backup file we can use following comand:

psql -f pg_backup.bak postgres

Here pg_backup.bak is the name of backup file.

Backing up single database

To backup any specific database we can use following command after loggin in as postgres user.

pg_dump dbname > dbname.bak

Here, dbname is the name of the database that i want to backup and dbname.bak is the name of the file that i want to give to backup file.

Restoring a single database

To restore a database from its backup file we can use following command:

psql dbname < dbname.bak

Here, i am backing up a database with name dbname from a backup file dbname.bak

Backing up remote database

To create backup, we dont need to be available on same server. We can pass -h and -p flags with host address and ip address respectively, to pg_dump to create backup of any remote database.

pg_dump -h 136.49.102.0 -p 5432 dbname > dbname.bak

Automating backup using crontask

Now, we have understood how can we backup manually. In this section we'll automate this process, so at every midnight, a backup of our PostgreSQL database will be created.

1. We need to be logged in as postgres user.

su - postgres

2. Now, let's create a directory where our backups will be stored.

mkdir -p ~/dbbackups

3. Let's create a crontask to do the work automatically daily.

Open crontask window.

crontab -e

Put this line in the end:

@daily pg_dump -U postgres dbname > ~/dbbackups/dbname.bak

Save and exit the crontask window.

Now, this crontask will create a backup of dbname database daily at midnight and store it under dbbackups folder. Your work is done here.