Retrieving data from postgreSQL with a shell script and send it by mail

Question

I want to daily retrieve data from my postgreSQL database and send the results by mail, what is the best way to do it please ?

I think about a shell script that execute my SELECT and then send it with mail function but I do not know how to do the SELECT part.

#!/bin/sh
todayDate = $(date);
nbUsers = mySelect;
nbPosts = mySelect;

echo "We are ".$date." dans there are ".$nbUsers." users and ".$nbPosts." posts " | mail -s "[DAILY]" [email protected]

EDIT (Updated code) :

#!/bin/sh
todayDate=$(date +%y%m%d%H%M%S)
nbUsers=$(su postgres -c "psql -d database -c 'SELECT COUNT(*) FROM table1'")
nbPosts=$(su postgres -c "psql -d database -c 'SELECT COUNT(*) FROM table2'")

echo "We are $todayDate. There are $nbUsers users and $nbPosts posts." | mail -s "[DAILY] Databse" [email protected]

EDIT2 (Updated code)

#!/bin/sh
su - postgres
todayDate=$(date +"%d/%m/%y")
todayTime=$(date +"%T")
nbUsers=$(psql -A -t -d database -c "SELECT COUNT(id) FROM table1;")
nbPosts=$(psql -A -t -d database -c "SELECT COUNT(id) FROM table2;")
echo "We are $todayDate. There are $nbUsers users and $nbPosts posts." | mail -s "[DAILY] Databse" [email protected]

possible duplicate of Postgresql query from bash script as postgres user — Tomas Greif
– Tomas Greif, Commented Jun 11, 2014 at 11:42

Rahul R Dhobi · Accepted Answer · 2014-06-11 11:41:11Z

2

You can retrieve data using psql command from postgresql database

nbUsers=`su postgres -c "psql -d databasename -c 'SELECT * FROM tableName'"`

after that you can send output of this command to mail

answered Jun 11, 2014 at 11:41

Rahul R Dhobi

5,8661 gold badge32 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

fallais Over a year ago

Hi, thanks a lot. Just edited my post to add my code. Can I just assign your command to my variables ?

fallais Over a year ago

Thanks. I update my code but I get an error whhen I execute my script : could not change directory to "/root"

fallais Over a year ago

I fixed this error. The mail is comming with the date but the other fields are empty.

Collectives™ on Stack Overflow

Retrieving data from postgreSQL with a shell script and send it by mail

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related