Learn how to backup and restore PostgreSQL database dump in Ubuntu (Linux) and Mac.
Taking backup/dump of the database:
pg_dump -U db_user database_name > database_dump_name.sql
Restoring backup dump
Creating new user
CREATE USER your_user_name WITH PASSWORD 'your_user_password';
Creating new database
CREATE DATABASE your_db_name;
GRANT ALL PRIVILEGES ON DATABASE your_db_name TO your_user_name;
Restore using psql
psql -U your_user_name -d your_db_name < dump_name.sql
Few Helpful Commands
# change owner ALTER DATABASE your_db_name OWNER TO your_user_name; # allow user to connect GRANT CONNECT ON DATABASE your_db_name TO your_user_name; # grant public schema access GRANT USAGE ON SCHEMA public TO your_user_name; # grant access to all tables GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO your_user_name;