Backing up Databases in Production

1 Min. Read
Jun 17, 2020

Backing up and Restoring database using pg dump and pg restore

Let us start with basic dump and restore processes of a database. We are using Postgres here. The steps involved are:

Dumping DB:

  • Get access to postgres shell

    sudo su - postgres

  • get the name of your database if you don’t already know it, from the db list

    psql postgres interactive

    \l list db

    \q quit

  • dump the database using pg_dump

    pg_dump app_production > dbname.bak

  • access the dumped file

    cd /var/lib/postgresql

Restoring DB:

Restoring from the dumped backup file is pretty straight forward. For Rails application, follow these steps:

  • cd into the project folder and drop the existing database and create a new one

    rails db:drop:all db:create:all

  • assign current postgres user as superuser

    sudo su - postgres

    psql

    ALTER ROLE dbuser WITH SUPERUSER

    \q

  • restore the database from the backup file

    psql app_production < dbname.bak

  • run the migration

    rake db:migrate RAILS_ENV=production

That’s it.

Automatic database backups with cron

We shall be using cron to create automatic hourly backups of the database with pg_dump as above. The file will be saved with today’s date as the filename and data will be over-written to it every hour.

The steps to do so are as follows:

  • create a script file

    nano ~/bin/db_backup.sh

  • edit the file’s content

    #!/bin/bash

    function date_today { date +"%F" }

    declare T="$(date_today)_appname_db.bak"

    pg_dump app_production>~/backups/appname/"${T}"

  • make the file executable

    chmod +x /root/bin/db_backup.sh

  • add a cron task

    crontab -e

  • the task has to run every hour. Add this line

    0 * * * * ~/bin/db_backup.sh

  • save and exit

  • verify that the task is running at the specified time by checking the logs

    tail /var/log/syslog -f -n 100 | grep CRON

    or

    grep CRON /var/log/syslog

  • after the cron task has run, verify the existence of file on the target location

    cd ~/backups/appname

    ls

  • check if the file is being overwritten at the specified time (1 hour in our case)

    stat -c '%y' 2020-06-17_appname_db.bak

Done. Thank you for reading.

Failed attemps

I tried to give dynamic file name from today’s date on the cron task itself.

0 * * * * /bin/bash -l -c 'declare T="$(date_today)_appname_db.bak" && pg_dump app_production>~/backups/appname/"${T}"'

the function date_today was defined in .bashrc

function date_today { date +"%F" }

This process didn’t return the required filename as it could not properly append the date, as apparently, the date function and subsequently, the date_today function is only accessible when the interactive bash shell is run. I may have been wrong in my understanding, though. I would love a clarification in the comments below, if you are interested.