How to Backup and Restore PostgreSQL Database on Ubuntu

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:

Code

    $ pg_dump -U your_username -h your_postgresql_server_ip -p 5432 -Fc -f your_backup_file.dump your_database_name
            

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.

Code

    $ docker exec <container-name > pg_dump -U your_username -h your_postgresql_server_ip -p 5432 -Fc -f your_backup_file.dump your_database_name
            

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. 

Code

    $ pg_restore -U postgres -P -h your_postgresql_server_ip -p 5432 -d your_database_name -Fc -c your_backup_file.dum
            

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

Copy the backup archive into the container. 

Code

    $ docker cp your_database_name.tar <container-name>:/
            

Connect to the PostgreSQL database inside the running container

Code

    $ docker exec -it <container-name > psql -U postgres
            

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

Code

    $ pg_restore -U postgres -P -C -d <your_database_name> your_database_name.tar 
            

‍

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,

Code

    $ nano /path/to/scripts/database-backup.sh
            

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

#Current Timestamp

Code

    NOW="$(date +'%d_%m_%Y')"
            

#Name of the database to be backed up

Code

    DATABASE="your_database_name"
            

#File name of the backup file

Code

    FILENAME="$DATABASE"_"$NOW".dump
            

#Directory in which backup file will be stored

Code

    BACKUPFOLDER="/backup/database"
            

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

Code

    CID=(sudo docker ps -aqf name=^postgres)
            

#Absolute path of the backup file

Code

    FULLPATHBACKUPFILE="$BACKUPFOLDER/$FILENAME"
            

Code

	 echo "Backup file will be stored at $FULLPATHBACKUPFILE" echo "Starting the backup of $DATABASE ..."
            

#Exporting the database

Code

   pg_dump -h localhost -U postgres -d $DATABASE > $FULLPATHBACKUPFILE
            

Code

   echo "Backup completed."
            

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

Code

    $ find "$BACKUPFOLDER" -mtime +7 -exec rm {} ;echo "Old files deleted"
            

Code

    $ echo"Backup process completed successfully...!"
            

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

Code

    $ crontab -e
            

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.