How to backup Postgre SQL database to cloud storage

Backing up your Postgre database to cloud can often be challenging or frustrating, especially if you have to use one or multiple jump hosts. 

You can simplify this process with the below and have your database backup directly in your Dropbox, Google Drive, AWS or whatever cloud storage you choose without working through barriers.

In this walkthrough we are using SCP however, SFTP and Rsync can also be used. As the forementioned protocols are encrypted (-e SSH when using Rsync) it ensures a secure transfer of your backup to your cloud storage.

First you must pull a database dump from your database to your machine that you access your database on.

pg_dump -U [username] -W -F -t -h [IP/Hostname] > ~/[path]/[filename].tar

• -U to specify which user will connect to the PostgreSQL database server.
• -W or --password will force pg_dump to prompt for a password before connecting to the server.
• -F is used to specify the format of the output file, which can be one of the following:
• • p – plain-text SQL script
  • c – custom-format archive
  • d – directory-format archive
  • t – tar-format archive
• -h is to specify the location of the remote database

Once you have the database dump, you can then transfer your file to your cloud storage with ease.  

scp ~/[path]/[filename].tar username@scp.couchdrop.io:/

This can be easily scripted with a cronjob, and it is also recommended after a period that you remove older database dumps from your system to prevent storage bloat.