Timely Backups of PG Database

2 Min. Read
Jun 17, 2020

Related Article: Timely Backups of Mongo Database

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




  • 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


    function date_today { date +"%F" ; }

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

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

    Here, app_production is the name of the database.

  • Make the file executable

    chmod +x ~/bin/db_backup.sh

  • Verify that the script is working properly by running the following commands

    cd ~/bin


  • After it is successful in creating a backup, we can proceed to 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


    grep CRON /var/log/syslog

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

    cd ~/backups/appname


  • 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

If you don’t want data to be overwritten to the same file every hour and instead want different files for each hour, simply replace the content of function date_today in the script with function date_today { date +"%Y-%m-%d_%H:%M" ; }

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.