Setting Up Automated Database (PostgreSQL) Backups Using Node.js and Bash

by Harvey Delaney

In the event of a hardware or software failure, you risk losing your application’s entire database along with your important data. So obviously you should have a disaster recovery plan in place that will cause the least user disruption and data loss.

To be sure you can recover your data with minimal data loss:

  • Have a detailed backup of your database.
  • Restore it in production in case of failure.

You can schedule automated database backups and save yourself from the hassle of doing them manually. This way if there is a database server failure, you can always use the latest backup to restore the database.

In this post, you will learn how to use Node.js to run the bash commands. These commands will prepare a backup of the PostgreSQL database. This database backup will be uploaded on another server using Node.js. Then we will write a cron job to schedule this database backup process so it can be performed in a defined window. Let’s get started.

Taking PostgreSQL database backup

The pg_dump is a utility used to take the logical backups of a PostgreSQL database. It is automatically installed with the PostgreSQL installation. Below is the syntax to use the pg_dump command for backups.

 ubuntu@ubuntu:~$ pg_dump [connection options] [options] [database name]

NOTE: This post contains commands specific for PostgreSQL 13. For other versions, commands may vary.

The following connection options are used with the pg_dump command to take the database backup.

  • -U option specifies the database user used to run the command.
  • -h is used to specify the hostname for the PostgreSQL server. It may be an IP address or a DNS name.
  • -p is used to specify the port the database server is listening on.

Other options for database backup include:

  • -f specifies the name of the output file.
  • -F specifies the output file format.
  • -d specifies the database name to have a backup of.

If you wish to dig deep and explore more options, there is a list of them here.

Advancing ahead, in order to take backup of the entire database using the pg_dump utility along with the necessary options, use this syntax:

ubuntu@ubuntu:~$ pg_dump -U admin -h localhost -p 5432 -f db_backup.tar -F t -d pg_database

The above command will generate a file db_backup.tar, which will be the backup of the entire PostgreSQL database named pg_database. We will run this command using Node.js code and then upload the backup file to another server.

Running the bash command using Node.js

In this section, we will write a Node.js code that will run the above mentioned bash “pg_dump” command to take the PostgreSQL database backup and then upload the backup file to another server.

First of all, install the required dependencies using the npm, which is a package manager for Node.js. We will use the dotenv and @getvim/execute packages. The dotenv is used to manage environment variables and we will use it to pass the database connection parameters. The @getvim/execute package will be used to run the bash commands in Node.js code.

ubuntu@ubuntu:~$ npm install dotenv @getvim/execute

Create a file .env in the root directory of the Node.js project and enter the database parameters like database user, host, and database name.

DB_USER=admin
DATABASE=pg_db
DB_HOST=localhost
DB_PORT=5432

Now create a file index.js and write the Node.js code to take the backup of the database.

// importing required modules
const { execute } = require('@getvim/execute');
const dotenv = require('dotenv').config();

// getting db connection parameters from environment file
const username = process.env.DB_USER;
const database = process.env.DATABASE;
const dbHost = process.env.DB_HOST;
const dbPort = process.env.DB_PORT;

// defining backup file name
const date = new Date();
const today = `${date.getFullYear()}-${date.getMonth()}-${date.getDate()}`;
const backupFile= `pg-backup-${today}.tar`;

// writing postgresql backup function
const takePGBackup = () => {
    execute(`pg_dump -U ${username} -h ${dbHost} -p ${dbPort} -f ${backupFile} -F t -d ${database}`);
    .then( async () => {
    	console.log(`Backup created successfully`);
    })
    .catch( (err) => {
    	console.log(err);
    });
}

// calling postgresql backup function
takePGBackup();

The above code gets db parameters from the .env file and then takes the backup from the PostgreSQL database. Now this database backup needs to be compressed, as the original database backup may be very large in size.

Extend the existing takePGBackup() function and add the code to compress the backup file. Before writing the code, install the gzipme package that is used to compress the files.

ubuntu@ubuntu:~$ npm install gzipme

After installing the package, do not forget to import fs and gzipme packages into the index.js file using the require() methos.

const { execute } = require(‘@getvim/execute’);
const dotenv = require(‘dotenv’).config();
const compress = require(‘gzipme’);
const fs = require(‘fs’);
const takePGBackup = () => {

execute(`pg_dump -U ${username} -h ${dbHost} -p ${dbPort} -f ${backupFile} -F t -d ${database}`)
    .then( async () => {
        // add these lines to compress the backup file
        await compress(backupFile);
        fs.unlinkSync(backupFile);
        console.log("Zipped backup created");
    })
    .catch( (err) => {
   		console.log(err);
    });
}
// calling postgresql backup function
takePGBackup();

So far, this code takes the backup from the database and compresses it into a ZIP file. Now write a function to upload this backup to another server. This function will make a POST request with the backup file in the body to another server.

Next on the to-do list is to install the axios and form-data modules. The axios modules will be used to make a POST request while the form-data module will be used to send the file in the body of the POST request.

ubuntu@ubuntu:~$ npm install axios form-data
const { execute } = require(‘@getvim/execute’);
const dotenv = require(‘dotenv’).config();
const compress = require(‘gzipme’);
const fs = require(‘fs’);
const axios = require(‘axios’);
const formData = require(‘form-data’);
// function to upload backups
const uploadBackups = (backupFile) =>
{
    // making post request using axios
    const form = new formData();
    form.append('file', backupFile)
    axios.post('http://backup-server.url/api/upload-form', form, { headers: form.getHeaders()});
    .then(result => {
        fs.unlinkSync(backupFile);
        console.log(result.data);
    })
    .catch(err => {
    	console.log(err);
    })
}

// calling uploadBackups function
uploadBackups(backupFile);

Schedule the backups using cron job

You want to be sure your data is backed up regularly, otherwise if an event causes you to lose your database, your backup will only be as current as the last time you remembered to back it up. With all the precautions you take, from elaborate passwords to gaming VPNs, it would be a shame if you suffered a major setback simply because you didn’t schedule your backups.

Up till now, the code to take the automated backup and upload it to the server is complete. Now it is time to schedule the execution of this code. We will use cron job to schedule this task.

Cron job is a command line program used to schedule tasks on Linux machines. The task may be a command or a script and can be scheduled using a set of fields. This is the syntax to use the cron job:

<m> <h> <dom> <mon> <dow> <cmd>

  • minute: This is the first field from the left and defines the minute of the hour the job runs. It ranges from 0 to 59.
  • hour: Second field from the left and specifies the hour. It has values from 0 to 23 where 0 is 12 at midnight.
  • dayOfMonth: Third field from the left and specifies the day of the month the command runs. It has values from 1 to 31.
  • month: Fourth field from the left and specifies the month the commands run. It has values from 1 to 12.
  • dayOfWeek: Fifth field from the left and specifies the day of the week the task runs. It has values from 0 to 6 with 0 being Sunday.
  • Command: The last field is the command that is executed on a specified schedule.

In order to run this code using cron job, first find the location of the node binaries using the which command as cron job accepts the absolute path of the command.

ubuntu@ubuntu:~$ which node

Open the crontab in the terminal using the -e option along with the crontab command.

ubuntu@ubuntu:~$ sudo crontab -e

It will open a crontab. At the end of the file, use the following pattern to run the code every day at 8:00 AM.

0 8 * * * /usr/local/bin/node <absolute path to node script>

The asterisk (*) in the above pattern means every possible value for the field. There are some other options that can be used to customise the schedule of the cron job. Explore further and play around with all the different options here. After scheduling the job, verify by listing all the scheduled jobs using the following command.

ubuntu@ubuntu:~$ sudo crontab -l

At the end of the file, it lists all the scheduled jobs. In order to remove the scheduled job, use the following command.

ubuntu@ubuntu:~$ sudo crontab -r

After applying any change to the crontab, restart the cron service.

ubuntu@ubuntu:~$ sudo systemctl restart cron

OR

ubuntu@ubuntu:~$ sudo service cron reload

Wrapping up

In this post, we’ve looked at how to use the Node.js program to make database backups. We’ve identified how to schedule this task using cron job and how to use bash commands inside our Node.js code to automate database backup, compress the backup, and upload the compressed database backup to another server for disaster recovery.