Change from current user to root user
$ sudo -s
Now change to postgres
user
$ su - postgres
Create a simple backup. (Optional, just for test)
$ pg_dump -U postgres your_db_name | gzip > your_db_name_backup.gz
Now, Create a backup storage folder
-
If your are in postgres user mode, no need to type this command if you are already in root user mode.
$ exit
-
Create a backup storage folder
$ sudo mkdir -p /backups/postgresql
-
Provide the postgres user permissions to the directory
$ sudo chown postgres /backups/postgresql
-
Change back to the postgres user mode to set a cronjob.
$ su - postgres
-
Open crontab by running the command
$ crontab -e
-
Add this line at the end, replacing db_name with the name of your database
0 0 * 0 0 pg_dump -U postgres your_db_name | gzip > /backups/postgresql/your_db_name_backup.gz
- Switch back to root user.
$ sudo -s
- Create a new folder where the backups will be stored.
$ mkdir /home/ubuntu/backups
- Give permission to the postgres user of this folder.
$ chown postgres /home/ubuntu/backups
- Create a shell script
$ nano backup_script.sh
and put the config below:
`
db_name=your_db_name
backupfolder=/home/ubuntu/backups
keep_day=10 sqlfile=$backupfolder/your_db_name-database-$(date +%d-%m-%Y_%H-%M-%S).sql zipfile=$backupfolder/your_db_name-database-$(date +%d-%m-%Y_%H-%M-%S).zip
#create backup folder mkdir -p $backupfolder
if pg_dump $db_name > $sqlfile ; then echo 'Sql dump created' else echo 'pg_dump return non-zero code' exit fi
if gzip -c $sqlfile > $zipfile; then echo 'The backup was successfully compressed' else echo 'Error compressing backup' exit fi rm $sqlfile
find $backupfolder -mtime +$keep_day -delete
- Give permission of the script to
postgres
user and make the shell script executable.
$ chown /home/ubuntu/backup_script.sh $ chmod +x backup_script.sh
6. Now change back to the postgres user so that we can update our cron job to run the new script by removing previous one.
$ su - postgres $ crontab -e
- Replace the previous command with the new one.
0 0 * 0 0 /home/ubuntu/backup_script.sh
- Done..