How to Automate Dumping for SQL Databases
Tags: webdev, sql, bash, linux
Published: 2019-03-01 00:52:02

---
title: How to Automate Dumping for SQL Databases
published: true
description: How to Automate Dumping for SQL Databases
tags: Web Development, SQL, Bash, Linux
---

# **Introduction** 😸

#### <span style="font-weight:normal"> 
Due to the ever-increasing probability of things going haywire in production, the need for redundancy cannot be overemphasized. This leads us to create a simple automated flow of backing up our DB. Hosting and Deployment services like AWS already provide this, but we will be doing a little hack for smaller projects.

</span>

# **Pipeline** 👽

#### <span style="font-weight:normal"> 
We will be using a remote psql(postgres) database and a linux terminal for this example. Users on Windows OS can run the windows susbsystem for Linux(WSL).

</span>

![Image for Workflow](https://thepracticaldev.s3.amazonaws.com/i/6ewgqhlkky2ohj5mu2ev.jpg)

#### <span style="font-weight:normal"> 
So we will create a dump of our live DB, and store the **_dump.sql_** file and then obviously, we don't want it on our local host for security purposes, so we'll finally transfer our dump to a secure server.

</span>

#### <span style="font-weight:normal"> 
We will then assign this script to a cron job, and then our dumps will be created on the days we choose.

</span>

# **Automate.sh**

#### <span style="font-weight:normal"> 
The bash script that will handle this will be named 'Automate.sh'. Let's write our script.

</span>

```sh
#!/bin/bash
#Filename: Automate.sh
#Description: Create a dump of my PSQL DB

pg_dump -U 'YOUR USERNAME' -h 'YOUR HOSTNAME' -d 'DATABASE NAME' > dump.sql
```

#### <span style="font-weight:normal">
With the **_'pg_dump'_** tool, we can easily create a dump of our database. When this code is run, a password prompt will come up and then we can put in the password of the database. Manually inputting the password defeats the purpose of the automation, so we can attach a **_'--no-password'_** flag to stop the prompt from coming up and then we can pass the password when running the script.

</span>

#### <span style="font-weight:normal"> 
Our **Automate.sh** file should look like this now

</span>

```sh
pg_dump -U 'YOUR USERNAME' -h 'YOUR HOSTNAME' -d 'DATABASE NAME' --no-password > dump.sql
```

#### <span style="font-weight:normal">
Then we can run the script like this:

</span>

```sh
root@L: PGPASSWORD='YOUR_PASSWORD' ./Automate.sh
```

#### <span style="font-weight:normal">
We can also add the password to our environmental variables when scheduling our cron job. After the dump file is created, we want to send this file to a secure server, an AWS Instance for example, and then we can delete the file from our localhost.

</span>

### <span style="font-weight:normal">
We can transfer files to remote servers using the **_scp_** (Secure copy) tool, and we can quickly install this if we don't have it

</span>
```sh
sudo apt-get install openssh-client
```

#### <span style="font-weight:normal">
Our **Automate.sh** file should look like this now:
</span>

```sh
#!/bin/bash
#Filename: Automate.sh
#Description: Create a dump of my PSQL DB

pg_dump -U 'YOUR USERNAME' -h 'YOUR HOSTNAME' -d 'DATABASE NAME' > dump.sql

#Transfer file to server
scp -i "PRIVATE_KEY.pem" dump.sql  'SERVER IP'

#Delete file from localhost
if [ $? -eq 0 ];
then
     echo "Backup successful!" && rm dump.sql
else
    echo "Oops!"
fi
```

#### <span style="font-weight:normal">
 To ensure your private key is not overwritten accidentally, we can set permissions to 400 and then we can also set the permissions of our dump.sql file to make it writable and readable.

 </span>

```sh
root@L: chmod 400 'PRIVATE_KEY.pem'
root@L: chmod 777 dump.sql
```

# Scheduling With Cron ✈️

### <span style="font-weight:normal">
Finally, we can set the job to run by 12:59 AM everyday.
</span>

```sh
root@L: crontab <<EOF
> PGPASSWORD='YOUR_DB_PASSWORD'
> 59 12 * * * ./Automate.sh
> EOF
```

#### <span style="font-weight:normal">
 Other ways to accomplish this will be by creating a .cron file or using the **_crontab -e_** command.

</span>

```sh
root@L: crontab -e
```

#### <span style="font-weight:normal">
 This opens up a file that allows you schedule your cron.
</span>