postgresl backup and restore

First grab users and roles

pg_dumpall -U postgres --globals-only > postgres.sql

get a list of databases

psql -U postgres -t -c "select datname from pg_catalog.pg_database" | awk '$1 !~ /^(postgres|template)/ {print}'

backup each database, ignoring templates and postgres backup up above

for i in $(psql -U postgres -t -c "select datname from pg_database" | awk '$1 !~ /^(postgres|template)/ {print}')
   do pg_dump -U postgres $i > psql-${i}.sql
   done

drop if exists and create (all) the databases

create database my_db

restore the users and roles

psql -U postgres -d postgres

then grant privilages and change owner

grant all privileges on database my_db to my_db_user;
ALTER DATABASE my_db OWNER TO my_db_user;

finally restore the database

psql -U postgres -d my_db < psql-my_db.sql