Timely Backups of PG Database

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

  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}"

  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

  ./db_backup.sh

• 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

  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

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.