PostgreSQL: make a copy of an existing database (includes tables and all records)

If the source database is owned by another user, you must login as super user, then run the command in psql:

CREATE new_db WITH TEMPLATE old_db OWNER src_db_owner

or run the command in terminal:

createdb -O src_db_owner -T src_db new_db

You may get errors if the source database is in use:

ERROR:  source database "src_db" is being accessed by other users

You can kill all the connections to the source databse:

SELECT pg_terminate_backend(pg_stat_activity.procpid) FROM pg_stat_activity 
WHERE pg_stat_activity.datname = 'src_db' AND procpid <> pg_backend_pid();

then run the command

CREATE new_db WITH TEMPLATE src_db OWNER src_db_owner