automate_postgresql_scheduled_db_backup

postgres_db_backup_scheduler.md

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
  

Extended Solution

1. Switch back to root user.

$ sudo -s

2. Create a new folder where the backups will be stored.

$ mkdir /home/ubuntu/backups

3. Give permission to the postgres user of this folder.

$ chown postgres /home/ubuntu/backups

4. Create a shell script $ nano backup_script.sh and put the config below:

`

Database name

db_name=your_db_name

Backup storage directory

backupfolder=/home/ubuntu/backups

Number of days to store the backup

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

Create a backup

if pg_dump $db_name > $sqlfile ; then echo 'Sql dump created' else echo 'pg_dump return non-zero code' exit fi

Compress backup

if gzip -c $sqlfile > $zipfile; then echo 'The backup was successfully compressed' else echo 'Error compressing backup' exit fi rm $sqlfile

Delete old backups

find $backupfolder -mtime +$keep_day -delete

5. 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

7. Replace the previous command with the new one.

0 0 * 0 0 /home/ubuntu/backup_script.sh

8. Done..