Skip to content

Instantly share code, notes, and snippets.

@FaridLU
Last active August 24, 2023 12:06
Show Gist options
  • Save FaridLU/0a1dd0d233779eda43134d13917b5250 to your computer and use it in GitHub Desktop.
Save FaridLU/0a1dd0d233779eda43134d13917b5250 to your computer and use it in GitHub Desktop.
automate_postgresql_scheduled_db_backup

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

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

$ mkdir /home/ubuntu/backups

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

$ chown postgres /home/ubuntu/backups

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

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

  1. Replace the previous command with the new one.

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

  1. Done..
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment