Postgres backup and restore the whole DB

1. Get a backup of the whole database

pg_dumpall  -U pg_user -p 2222 > /pgdata/backup/all_pg_dbs_$(date +%d-%m-%Y_%H-%M-%S).sql

2. Restore the data

psql -f all_pg_dbs.sql -U pg_user -p 2222

Note: you may have to drop the database using the command below

DROP DATABASE mydb

Note: if PostgreSQL complains about open connections stop any application connected to the PostgreSQL and kill the connections by using the commands below

SELECT * FROM pg_stat_activity WHERE datname = 'mydb';
#Find the pids and run the next command to terminate the connection
SELECT pg_terminate_backend(1234);
DROP DATABASE mydb;
CREATE DATABASE mydb;