PostgreSQL and MySQL Database Backup and Restore
Backup is one of the key procedures that ensures data security. Even with reliable hosting, application errors, incorrect updates, or accidental data deletion may occur.
In this guide, we will show how to create and restore backups of PostgreSQL and MySQL databases using standard command-line utilities — pg_dump and mysqldump.
1. PostgreSQL Backup
1.1. What is pg_dump
pg_dump is a standard PostgreSQL utility for creating a logical database backup. It saves both structure (DDL) and data (DML) into a text SQL file that can be used later for restoration.
1.2. Command syntax
pg_dump -U <user> -h <host> -p <port> -d <database> -F p -f <path_to_file.sql>
1.3. Backup example
pg_dump -U myuser -h localhost -d mydatabase -F p -f /backups/mydatabase_$(date +%F).sql
Parameter explanation:
- -U myuser — PostgreSQL username;
- -h localhost — server host (IP can be specified);
- -d mydatabase — database name;
- -F p — output format (plain — regular SQL);
- -f — path to the file where the backup will be saved;
- $(date +%F) — appends the current date to the filename (e.g. 2025-11-03).
1.4. Compressed backup
To reduce file size, you can use gzip compression:
pg_dump -U myuser mydatabase | gzip > /backups/mydatabase_$(date +%F).sql.gz
2. Restoring PostgreSQL Database
2.1. Using psql
To restore a database from a text backup, use:
psql -U myuser -d mydatabase -f /backups/mydatabase_2025-11-03.sql
If the backup is compressed:
gunzip -c /backups/mydatabase_2025-11-03.sql.gz | psql -U myuser -d mydatabase
2.2. If the database does not exist
Create a new database first:
createdb -U myuser newdatabase
psql -U myuser -d newdatabase -f /backups/mydatabase_2025-11-03.sql
3. MySQL Backup
3.1. What is mysqldump
mysqldump is a standard utility for creating MySQL dumps. It saves SQL commands needed to recreate tables and insert data.
3.2. Command syntax
mysqldump -u <user> -p -h <host> <database> > <path_to_file.sql>
3.3. Backup example
mysqldump -u myuser -p mydatabase > /backups/mydatabase_$(date +%F).sql
Parameter explanation:
- -u myuser — MySQL username;
- -p — prompts for a password;
- mydatabase — database name;
- > — redirects output to a file.
3.4. Compressed dump
mysqldump -u myuser -p mydatabase | gzip > /backups/mydatabase_$(date +%F).sql.gz
4. Restoring MySQL Database
4.1. Restoring from SQL file
mysql -u myuser -p mydatabase < /backups/mydatabase_2025-11-03.sql
4.2. If the database does not exist
Create a new database before restoring:
mysql -u myuser -p -e "CREATE DATABASE newdatabase;"
mysql -u myuser -p newdatabase < /backups/mydatabase_2025-11-03.sql
4.3. Restoring from compressed dump
gunzip -c /backups/mydatabase_2025-11-03.sql.gz | mysql -u myuser -p mydatabase
5. Automation and Security Recommendations
5.1. Automated Backups with Cron
For regular backup processes without manual intervention, set up automation using cron.
Basic cron setup:
# Open cron editor crontab -e
Add a line, for example:
0 2 * * * /usr/local/bin/db_backup.sh
This command will run the script daily at 2:00 AM.
Advanced approach using a script. Create /usr/local/bin/db_backup.sh:
#!/bin/bash DATE=$(date +%F) BACKUP_DIR="/backup/$DATE" mkdir -p $BACKUP_DIR # PostgreSQL backup pg_dump -U myuser mydatabase | gzip > $BACKUP_DIR/postgres_mydatabase.sql.gz # MySQL backup mysqldump -u myuser -p mydatabase | gzip > $BACKUP_DIR/mysql_mydatabase.sql.gz # Remove backups older than 7 days find /backup/ -type f -mtime +7 -delete
Make the script executable:
chmod +x /usr/local/bin/db_backup.sh
And add to cron:
0 2 * * * /usr/local/bin/db_backup.sh
5.2. Security and Storage Best Practices
- Off-server storage: store backups in cloud storage (AWS S3, Google Cloud) or on external drives.
- Access control: restrict access to the /backups folder, as files may contain confidential data.
chmod 700 /backup
chmod 600 /backup/*.sql.gz
- Integrity verification: perform test restores monthly to verify backup functionality.
- Confidentiality: backup files contain database contents — treat them as confidential information.
Conclusion
Using pg_dump and mysqldump is an easy, reliable, and universal way to back up PostgreSQL and MySQL databases. These tools are suitable for both manual backups and automated processes with minimal effort.
Regular backups are the key to your project's stability and data safety.
