Introduction

Backing up your PostgreSQL database is a critical task to ensure data integrity, security, and availability. Regular backups protect you from data loss caused by unexpected events such as hardware failures, software issues, or accidental deletions. This process is vital for maintaining the reliability of your database and ensuring you can recover your data in case of any issues. PostgreSQL supports cross-platform capabilities, making it a versatile choice for many users. Its ability to integrate seamlessly with other technologies further enhances its utility.

This article shows you how to back up a PostgreSQL database.

Prerequisites

Before you begin, ensure you've:

• A VPS server. We recommend a Digital Ocean VPS server.
• A running PostgreSQL server. Follow the guides below to install PostgreSQL:
  • How to Install PostgreSQL on Ubuntu 24.04
  • How to Install PostgreSQL on Rocky Linux 9
• A non-root user with sudo privileges.

Back up PostgreSQL Database

This section walks you through the steps to back up a PostgreSQL database.

1. SSH to your server.

2. Create a directory to store the backup files.

   CONSOLE

   $ sudo mkdir -p /var/backups/postgresql
   

3. Take ownership of the new directory.

   CONSOLE

   $ sudo chown -p /var/backups/postgresql
   

4. Use the pg_dump command to back up the database. Replace your_database_name with the name of your database and backup_file.sql with the desired backup file name. You can append the date and time in the SQL file.

   CONSOLE

   $ sudo pg_dump -d your_database_name -U postgres > /var/backups/postgresql/backup_file.sql
   

Verify PostgreSQL Backup

You should verify your PostgreSQL backup by listing the files in the backup directory to ensure all necessary files are present.

1. Check the contents of the backups directory to ensure it has been created correctly.

   CONSOLE

   $ ls -l /var/backups/postgresql/
   

   Output:

   137479 Jan  2 11:45 backup_file.sql
   

Conclusion

This guide shows you how to backup a PostgreSQL database. You created a backup directory, performed the backup using pg_dump, and verified the backup file. For further data protection, consider setting up automated backup schedules and storing backups in secure locations. Ensure you periodically test your backups to confirm data reliability.