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.
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 18.104.22.168 -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.
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.
However, its not a good idea to store db and backup on the same server, you should move backup file to diffrenet server using SSH.