How to Backup PostgreSQL to Google Drive with Bash

Introduction

Google Drive is a free cloud storage service that allows users to save and access files online. You can sync and share all your stored documents to multiple remote devices such as mobiles, tablets, laptops, and PCs.

SnapShooter is a cost-effective backup solution provider for keeping servers backed up, very easy to get everything set up and running. SnapShooter provides an easy, and secure way to backup your PostgreSQL database. SnapShooter will work with PostgreSQL hosted on your own VPS or dedicated servers.

This post will show you how to backup the PostgreSQL database to Google Drive manually and using SnapShooter.

Backup PostgreSQL Database to Google Drive Manually

In this section, we will show you how to backup PostgreSQL database to Google Drive using gdrive utility.

Install and Configure Gdrive

You will need to install gdrive on the server from where you want to backup PostgreSQL databases to Google Drive. Gdrive is a command-line utility used for interacting with Google Drive. With gdrive, you can manage, upload, download, delete and share files on Google Drive via the command line.

First, install the required dependencies with the following command:

apt-get install musl gnupg2 wget -y

Next, download the latest version of gdrive from the GitHub using the following command:

wget https://github.com/prasmussen/gdrive/releases/download/2.1.1/gdrive_2.1.1_linux_amd64.tar.gz

Once the download is completed, extract the downloaded file with the following command:

tar -xvzf gdrive_2.1.1_linux_amd64.tar.gz

Next, install the gdrive to the system path:

sudo install gdrive /usr/bin/gdrive

Next, you will need to configure gdrive to access Google Drive. You can do it by running the following command:

gdrive about

You will get the following URL to authenticate with your Google account:

Authentication needed
Go to the following url in your browser:
https://accounts.google.com/o/oauth2/auth?access_type=offline&client_id=367116221053-7n0vf5akeru7on6o2fjinrecpdoe99eg.apps.googleusercontent.com&redirect_uri=urn%3Aietf%3Awg%3Aoauth%3A2.0%3Aoob&response_type=code&scope=https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fdrive&state=state

Now, open your web browser and access the above URL. You will get the following page:

Click on the Allow button. You should see the Authorization Code on the following page:

Copy the authorization code from the above page and paste it into your command-line interface:

Enter verification code: 4/1AX4XfsfdsgdgdstRgB_BJsdgdhdhhDmb0TxwbVwbTM49D8aiaeG4nFlz6ILQgzUO18

Once gdrive is connected with your Google Drive, you will get the following output:

User: Hitesh Jethva, hitjethva75@gmail.com
Used: 550.7 KB
Free: 16.1 GB
Total: 16.1 GB
Max upload size: 5.2 TB

You can verify the gdrive version using the following command:

gdrive version

You will get the following output:

gdrive: 2.1.1
Golang: go1.11.13
OS/Arch: linux/amd64

Use Gdrive to Backup PostgreSQL Database to Google Drive

At this point, gdrive is configured to manage Google Drive. You can list all available files on your Google Drive using the following command:

gdrive list

You will get the following output:

Id                                  Name           Type   Size   Created
1CUc3AmOVY9V4cJkNNE0ZVvdb-JOtZsnJ   Article        dir           2022-02-08 12:49:10

Now, log in to your PostgreSQL server and backup all databases using the following command:

pg_dumpall -U postgres > all_pg_dbs.sql

Next, upload the PostgreSQL dump file to the Google Drive using the following command:

gdrive upload all_pg_dbs.sql

You will get the following output:

Uploading all_pg_dbs.sql
Uploaded 1BSlSXJKVRSLSnENQ7fP0Lszn9VUHU5jD at 5.8 MB/s, total 10.5 MB

To verify the uploaded dump file, run the following command:

gdrive list

You will get the following output:

Id                                  Name             Type   Size      Created
1BSlSXJKVRSLSnENQ7fP0Lszn9VUHU5jD   all_pg_dbs.sql   bin    10.5 MB   2022-04-13 08:00:31
1CUc3AmOVY9V4cJkNNE0ZVvdb-JOtZsnJ   Article          dir              2022-02-08 12:49:10

You can also access your Google Drive via a web browser to see your uploaded dump file as shown below: