We hope that you have gone through the basic installation and setup required for PostgreSQL before moving on to the advanced concepts mentioned in this article. If not, feel free to check out our How to Install and Configure PostgreSQL on Ubuntu guide and then return to this article.  

It is essential to regularly take the backup of your database to prevent any unforeseen situations such as data corruption, server outage, or any situations that are out of your control and irrecoverable. 

Importance of Regular Backups

Schedule regular backups to prevent data loss in case of hardware failure, accidental deletions, or other unforeseen events. 

Use tools like pg_dump for creating logical backups.

Backup the Database

We will be using a utility called pg_dump that is available when you install the postgreSQL database. 

Execute the following command to create a backup of your database:

If your database is running inside a docker containers, you can use the following command to take the backup. The following command will take the backup on your host machine in the your_database_name.tar file. Adjust the following command with the names of your container, database name, server or localhost and exported file name to match your needs. Enter the password of the Postgres user you may have set earlier.

Restoring from a Backup

To restore your database from a backup, use the following command. Enter the password of the Postgres user you may have set earlier. 

If you would like to restore in PostgreSQL Database running in a Docker container, follow these steps:

Copy the backup archive into the container. 

Connect to the PostgreSQL database inside the running container

Run the following command inside the connected container to start the restoration. Enter the password of the Postgres user you may have set earlier.

‍

‍

Automating Backup Tasks

Schedule automated backup tasks using tools like cron to ensure regular and consistent backups. You can use the following shell scripts to take the backups and schedule that in the crontab to run it at periodic intervals. Store the file at /path/to/scripts/database-backup.sh for use in the next step.  

Preparing the Backup Script

In the terminal,

Paste the content below. IMPORTANT - Review the code and make the changes to the shell script code as per your requirements.

#Current Timestamp

#Name of the database to be backed up

#File name of the backup file

#Directory in which backup file will be stored

#Getting the running container ID in case of running the database inside Docker

#Absolute path of the backup file

#Exporting the database

It is also important to keep removing the older backups to free up the storage space.

Here are  removing the backups older than 7 days.

Removing the backups older than 7 days

Save and Exit the editor. Ctrl+X is the shortcut to exit. Press Y when prompted to save the changes.

Scheduling the Cron Job

Now we will schedule the above script to run periodically. It is important to log the output of the command in a log file so that we can monitor the log files to ensure that backups are being taken regularly.

Open the crontab in any editor of your choice

Enter the following line. Set the interval as per your requirements. The following script takes the backup at every 6-hour interval.

0 */6 * * * sh /path/to/scripts/database-backup.sh > /var/log/database-backup.log

Why It Matters: Regular backups are your safety net, allowing you to recover data in case of unexpected events. Automating backup tasks ensures that your data protection strategy is reliable and consistently applied.