Shell Script to Export PostgreSQL SQL Query in CSV Format and Emailing it as Attachment

Recently I came across a need to create a Linux shell script to run a PostgreSQL SQL query, export it in CSV Format and send it attached to an email. The following is a self-explanatory Linux shell script outlining the process.

 

#!/bin/bash
# Export PATH in case SENDMAIL is not found by the script
export PATH=/usr/sbin:/usr/bin
# EXPORT POSTGRES SQL COMMAND AS CSV
# Adjust the Select SQL command to your liking
# Note: You may need to define the path to export.csv file
./psql -d DBNAME -t -A -F"," -c "SELECT * FROM TABLE" > export.csv
# SEND AN EMAIL
# Note: You may need to define the path to export.csv file on the bottom line
( echo "to: recipient@domain.com"
echo "from: POSTGRESQL SERVER <server@domain.com>"
echo "Subject: PostgreSQL Export as of $(date +%F)" 
echo "mime-version: 1.0"
echo "content-type: multipart/related; boundary=messageBoundary"
echo
echo "--messageBoundary"
echo "content-type: text/plain"
echo
echo "Please find the export in CSV format attached to this email."
echo "Created: $(date)."
echo
echo "--messageBoundary"
echo "content-type: text; name=export.csv"
echo "content-transfer-encoding: base64"
echo
openssl base64 < export.csv) | sendmail -t -i

I hope this helped someone.

Facebook Comments