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]

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.

Running bash commands in NodeJS

Create a new directory, we can run the following command:

mkdir <project name>

then move into the newly created directory:

cd <project name>

then  run this command : 

npm init

and fill this information like that :

package name: (project name)
version: (1.0.0)
description:
entry point: (index.js) server.js
test command:
git repository:
keywords:
author:
license: (ISC) 

After creating project install node package run command:

npm install --save @getvim/execute dotenv node-cron

const {
	execute
} = require('@getvim/execute');
const dotenv = require('dotenv');
var cron = require('node-cron');

dotenv.config();

const DBPASSWORD = "Database Password"
const DBUSERNAME = "Database User Name"
const DBNAME = "Database Name"

function backup() {
	const date = new Date();
	const currentDate = `${date.getFullYear()}.${date.getMonth() + 1}.${date.getDate()}.${date.getHours()}.${date.getMinutes()}`;
	execute(`PGPASSWORD="${DBPASSWORD}" pg_dump -U ${DBUSERNAME} -d ${DBNAME} -f database-backup-${currentDate}.pgsql`).then(async () => {
		console.log("Finito");
	}).catch(err => {
		console.log(err);
	})
}

cron.schedule('* * * * *', () => {
	console.log('running a task every minute');
	backup()
});

Thank you :)