Backing up your Postgres database

Creating a simple cron job process and getting notified through Postfix

Photo by Hunter Harritt on Unsplash

Your database is always subject to the risk of damage. There are many reasons why data might be lost or become corrupted:

• Disks crash
• Interruption of power supply
• User mistakes
• Virus damage
• Fire or earthquake

Backing up databases is one of the most critical tasks in database administration. Before backing up the databases, you should consider the following points:

There are many backup solutions out there, but I believe that my choosing was the simplest one by backing it up using straight PostgreSQL backup tools.

PostgreSQL provides pg_dump and pg_dumpall tools to help you backup databases easily and effectively.

pg_dump — no-owner -h <host>-p <port> -U <user> — verbose <database name> > <destination.sql>

To do that, first you need to install the postgres client tools in your VPS instance (We’re using Clouding.io at a&m consulting).

sudo apt-get install postgresql-client

And now you’ll be able to use pg_dump to connect to your database server. (Be sure you have credentials to do so).

Cron job

We don’t want to run backups manually.

Cron is a system daemon used to execute desired tasks (in the background) at designated times. To use cron for tasks meant to run only for your user profile, add entries to your own user’s crontab file. To edit the crontab file enter:

crontab -e

Each line has five time-and-date fields, followed by a command, followed by a newline character (‘\n’). The fields are separated by spaces. The five time-and-date fields cannot contain spaces. The five time-and-date fields are as follows: minute (0–59), hour (0–23, 0 = midnight), day (1–31), month (1–12), weekday (0–6, 0 = Sunday).

Our crontab will launch the cronjob_dump.sh script at 12:01 am (00:01) every day:

01 0 * * * /bin/sh /home/cronjob_dump.sh >> /home/dump_errors.log 2>&1

Inside the cronjob_dump.sh script will be the pg_dump line described above. Also a dump_errors.log file will be generated in the home folder since the –verbose option was included in the pg_dump order. This dump_errors.log file will be attached in the email alert. You’ll see in a moment. 😀

It’d be very nice if somehow, we had an alert when anytime the backup proceeded.

There is no easy solution, but probably the easiest one is to install Postfix to enable the sending of emails, and then include the email order into the cronjob_email.sh script.

Postfix

Let’s go to use a Gmail account as a free SMTP server on your Ubuntu-Linux VPS instance (We’re using Clouding.io at a&m consulting).

To relay Postfix mails via smtp.gmail.com, first, install all necessary packages:

sudo apt-get install postfix mailutils libsasl2-2 ca-certificates libsasl2-modules

Postfix configuration wizard will ask you some questions. Just select your server as Internet Site and for FQDN use something like mail.example.com

Then open your Postfix config file:

vim /etc/postfix/main.cf

And modify it as shown:

# See /usr/share/postfix/main.cf.dist for a commented, more complete version
# Debian specific:  Specifying a file name will cause the first
# line of that file to be used as the name.  The Debian default
# is /etc/mailname.
myorigin = /etc/mailname
smtpd_banner = $myhostname ESMTP $mail_name (Ubuntu)
biff = no
# appending .domain is the MUA's job.
append_dot_mydomain = no
# Uncomment the next line to generate "delayed mail" warnings
#delay_warning_time = 4h
readme_directory = no
# See http://www.postfix.org/COMPATIBILITY_README.html -- default to 2 on
# fresh installs.
compatibility_level = 2
# TLS parameters
smtpd_tls_received_header = yes
smtpd_tls_cert_file=/etc/ssl/certs/ssl-cert-snakeoil.pem
smtpd_tls_key_file=/etc/ssl/private/ssl-cert-snakeoil.key
#smtpd_use_tls=yes
smtpd_tls_session_cache_database = btree:${data_directory}/smtpd_scache
smtp_sasl_auth_enable = yes
smtp_sasl_password_maps = hash:/etc/postfix/sasl_passwd
smtp_sasl_security_options = noanonymous
smtp_tls_CAfile = /etc/postfix/cacert.pem
#smtp_tls_wrappermode = yes
smtp_tls_security_level = encrypt
smtp_tls_note_starttls_offer = yes
smtp_use_tls=yes
smtp_enforce_tls = yes
smtp_tls_session_cache_database = btree:${data_directory}/smtp_scache
# See /usr/share/doc/postfix/TLS_README.gz in the postfix-doc package for
# information on enabling SSL in the smtp client.
smtpd_relay_restrictions = permit_mynetworks permit_sasl_authenticated defer_unauth_destination
myhostname = host.local.domain
alias_maps = hash:/etc/aliases
alias_database = hash:/etc/aliases
mydestination = host.local.domain, storage, localhost.localdomain, , localhost
relayhost = [smtp.gmail.com]:587
mynetworks = 127.0.0.0/8 [::ffff:127.0.0.0]/104 [::1]/128
mailbox_command = procmail -a "$EXTENSION"
mailbox_size_limit = 0
recipient_delimiter = +
inet_interfaces = all
inet_protocols = ipv4

You might have noticed that we haven’t specified our Gmail username and password in above lines. They will go into a different file. Open/Create:

vim /etc/postfix/sasl_passwd

And add the following line:

[smtp.gmail.com]:587    USERNAME@gmail.com:PASSWORD

Fix permission and update postfix config to use sasl_passwd file:

sudo chmod 400 /etc/postfix/sasl_passwd
sudo postmap /etc/postfix/sasl_passwd

Next, validate certificates to avoid running into error. Just run following command:

cat /etc/ssl/certs/thawte_Primary_Root_CA.pem | sudo tee -a /etc/postfix/cacert.pem

Finally, reload postfix config for changes to take effect:

sudo /etc/init.d/postfix reload

Testing

If you have configured everything correctly, following command should generate a test mail from your server to your mailbox.

echo "Test mail from postfix" | mail -s "Test Postfix" you@example.com

To further verify, if the mail sent from above command is actually sent via Gmail’s SMTP server, you can log into your Gmail account USERNAME@gmail.com with PASSWORD and check “Sent Mails” folder in that Gmail account. By default, Gmail always keeps a copy of mail being sent through its web-interface as well as SMTP server. This logging is one strong reason that we often use Gmail when mail delivery is critical.

Once configured, all emails from your server will be sent via Gmail. This method will be useful if you have many sites on your server and want them all to send emails via Gmail’s SMTP server.

Sending the email from the cronjob_dump.sh script

The idea behind is that once the DB dump is done, then in terms of notification, the cron will also send an email to the admin.

In the previous step, we actually don’t know if something was wrong or not. But maybe you noticed the –verbose option in the pg_dump order. So, we’ll have all the information we need in that dump_errors.log file, which will attached in the email.

echo "Test Postfix Gmail Relay" | mail -s "Backup done at $BACKUPTIME" -A /home/dump_errors.log you@example.com

Therefore, our final crontab would be:

01 0 * * * /bin/sh /home/cronjob_dump.sh >> /home/dump_errors.log 2>&1
06 0 * * * /bin/sh /home/cronjob_email.sh >> /home/email_errors.log 2>&1

The dump itself will be at 12:01 am every day, and then the email will be sent 5 minutes later, at 12:06 am also every day. Into the email you’ll be able to see the list of actions taken during the dump trough the attached dump_errors.log file.

That’s all! ⛱