Dev Tips

 #!/bin/bash

logfile=”/home/user/db_backups/pgsql.log”
backup_dir=”/home/user/db_backups”
touch $logfile
databases=`psql -h localhost -U postgres -q -c “\l” | sed -n 4,/\eof/p | grep -v rows\) | grep -v template0 | grep -v template1 | awk {‘print $1’}`

echo “Starting backup of databases ” » $logfile
for i in $databases; do
 dateinfo=`date ‘+%Y-%m-%d %H:%M:%S’`
 timeslot=`date ‘+%Y%m%d%H%M’`
 /usr/bin/vacuumdb -z -h localhost -U postgres $i >/dev/null 2>&1
 /usr/bin/pg_dump -U postgres —inserts $i -h 127.0.0.1 -f $backup_dir/$i-database-$timeslot.backup 
 echo “Backup and Vacuum complete on $dateinfo for database: $i ” » $logfile
done

In /root  You should  have .pgpass file  with chmod 600

cat /root/.pgpass 

*:*:*:postgres:password_for_postgres_user 

su  postgres

psql

CREATE USER test_username WITH PASSWORD secretPass;

CREATE DATABASE test_db;

GRANT ALL PRIVILEGES ON DATABASE test_db TO test_username; 

su postgres

psql 

CREATE DATABASE mynew_db WITH TEMPLATE myOld_db  OWNER myUser1;