How to Backup and Restore PostgreSQL Database on Ubuntu

Published on December 29, 2023

Zignuts Technolab

How to Backup and Restore PostgreSQL Database on Ubuntu
How to Backup and Restore PostgreSQL Database on Ubuntu
Software Development

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.

connect with database experience team

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.

looking for professional support to manage optimize review your database
How to Backup and Restore PostgreSQL Database on Ubuntu

How to Backup and Restore PostgreSQL Database on Ubuntu
linkedin-blog-share-iconfacebook-blog-share-icontwitter-blog-icon

Portfolio

Recent

explore-projects

Testimonials

Why they’re fond of us?

tm img

A reliable and flexible technical partner, Zignuts Technolab enables a scalable development process. The team offers a comprehensive array of expertise and scalability that yields an optimized ROI. Direct contact with specialists maintains a seamless workflow and clear communication.

Joeri

Technical Architect
Blockchain-based Real Estate Platform Company, Belgium

Zignuts Technolab transformed our platform by simplifying code, redesigning key aspects, and adding new features, all within impressive timelines. Their project management and communication were exceptional.

Ali

Managing Director
Automobile Company, UAE

Zignuts team has been instrumental in our platform’s development including backend, frontend and mobile apps, delivering excellent functionality and improving speed over time. Their project management, pricing and communication are top-notch.

Shoomon

Co-Founder
AI-Based Fintech Startup, UK

Zignuts has delivered excellent quality in developing our website and mobile apps. Their genuine interest in our business and proactive approach have been impressive.

Jacob

Technical Architect
Blockchain-based Real Estate Platform Company, Belgium

Their team's dedication and knowledge in handling our relocation information platform made the collaboration seamless and productive. Highly recommend their services.

Stephen

CEO & Founder
Social Community Platform, Germany

Zignuts Technolab provided highly skilled full-stack developers who efficiently handled complex tasks, from backend development to payment gateway integration. Their responsiveness and quality of work were outstanding.

Houssam

Chief Product Officer
Enterprise Solutions, Jordan

Zignuts Technolab has been highly efficient and responsive in developing our rewards and wellness app. Their ability to integrate feedback quickly and their solid expertise make them a great partner.

Namor

Developer
Wellness Startup, Thailand