How To Backup PostgreSQL Databases on an Ubuntu VPS

If you have important data stored in your PostgreSQL databases, it's essential to create regular backups to protect against data loss. This tutorial will guide you through the process of creating backups for your PostgreSQL databases on an Ubuntu VPS.

Step 1: Install PostgreSQL on Ubuntu VPS

If you haven't already installed PostgreSQL on your Ubuntu VPS, you can install it using the following command:

sudo apt-get install postgresql

Step 2: Create a Backup Script

You can create a backup script to automate the backup process. Create a new file using your preferred text editor, for example:

sudo nano /home/username/postgres_backup.sh

Then, add the following lines to the file:

#!/bin/bash BACKUP_DIR="/home/username/postgres_backup" DUMP_FILE="$BACKUP_DIR/postgres_$(date +%Y-%m-%d_%H-%M-%S).sql" sudo -u postgres pg_dumpall > $DUMP_FILE

Save the file and make it executable:

sudo chmod +x /home/username/postgres_backup.sh

Step 3: Schedule the Backup

You can use the cron daemon to schedule the backup at regular intervals. Edit the crontab file:

sudo crontab -e

Add the following line to run the backup script every day at 3 am:

0 3 * * * /home/username/postgres_backup.sh

Save the file and exit the editor.

Step 4: Verify the Backup

You can verify that the backup is working correctly by checking the backup directory for new backup files:

ls /home/username/postgres_backup

You should see a new file with a name similar to postgres_2023-03-06_03-00-01.sql.

Conclusion

Backing up your PostgreSQL databases on an Ubuntu VPS is an essential step to protect your data from loss. By following this tutorial, you can create automated backups and schedule them to run at regular intervals.

Keywords: PostgreSQL, database, Ubuntu, VPS, backup, script, cron, pg_dumpall.